¿Cómo crear un Dashboard profesional en Excel?

How to Create a Dashboard from Scratch

Introduction to Dashboards

• The video introduces the concept of dashboards, emphasizing their role as visual interfaces that display key information on specific topics using graphs, tables, and other visual elements.

• Dashboards facilitate data analysis by summarizing large amounts of information quickly, providing insights efficiently.

Preparing Data for the Dashboard

• To create a dashboard, one must start with a database containing relevant information; in this example, it involves HR department tickets or requests.

• Formatting the data into a table has two main advantages: it keeps the dashboard updated with changes and maintains consistent styling across rows and columns.

Creating and Naming Tables

• Users can format their data range as a table via the "Format as Table" option or by using the shortcut Ctrl + T. Choosing an appropriate style is recommended.

• It’s advised to give specific names to tables instead of default names like "Table1" to avoid confusion when multiple tables exist in one file.

Adding Pivot Tables

• A new sheet named "Dashboard" will be created where all visual information will be placed. Pivot tables are then added to synthesize important data.

• To add a pivot table, select "Insert," then "Pivot Table," choosing the original table as the data source and specifying its location on the dashboard sheet.

Configuring Pivot Tables

• Users should drag fields (e.g., country) into designated areas (like Rows and Values), allowing for unique listings and counts of requests per country.

• Renaming pivot tables and sorting them in descending order enhances clarity when adding graphs later.

Creating Graphical Representations

• Each pivot table can be transformed into graphs by selecting it and choosing “Pivot Chart” from the menu; users can opt for different types of charts based on preference.

• After creating all necessary graphs, they should be organized neatly on the dashboard while leaving space for future filters.

Managing Data Visibility

• To keep things tidy, users can hide unnecessary columns by selecting them and using the “Consolidate” option under “Data,” making adjustments easier if needed later.

Adding Filters (Slicers)

• Slicers allow filtering of graph values; they can be added through “Insert Slicer” found in the Dynamic Chart Analysis menu.

• Once slicers are added, they need connections established with corresponding pivot tables to ensure proper functionality across all visuals.

Final Adjustments

• Right-clicking on slicers allows users to set report connections; this step is crucial for linking each slicer with relevant pivot tables effectively.

How to Enhance Excel Dashboards with Macros

Updating Connections for Pivot Tables

• To maintain updated connections for pivot tables in the workbook, a specific line of code should be included in the activate event of the mentioned sheet. This ensures that users do not need to manually refresh information when switching between sheets.

Improving Visualization with Toggle Buttons

• A toggle button will be added to enhance space for better visualization of graphs. The click event for this button will include code that allows easy switching between full-screen mode and conventional mode, which displays the formula bar and ribbon.

Creating Functional Dashboards Quickly