Dashboard Mode - Recording macros
Dashboard Goal
This dashboard helps you understand how to record and use macros in Excel to automate repetitive tasks, like formatting sales data quickly.
0
0
Recording macros in Excel - Dashboard Guide
Sample Data
| Product | Region | Sales | Date |
|---|---|---|---|
| Apples | North | 120 | 2024-01-05 |
| Bananas | South | 150 | 2024-01-06 |
| Cherries | East | 90 | 2024-01-07 |
| Dates | West | 200 | 2024-01-08 |
| Elderberries | North | 170 | 2024-01-09 |
Dashboard Components
- KPI Card: Total Sales
Formula:=SUM(C2:C6)
Shows the total sales amount from the data. - Macro Button: Format Sales Data
When clicked, runs a recorded macro that:- Boldens the header row
- Formats the Sales column as currency
- Applies a light fill color to the data rows
- Sales Table: Displays the raw sales data for reference.
Dashboard Layout
+----------------------+----------------------+ | Total Sales | Format Sales Data | | (KPI) | (Macro Button) | +----------------------+----------------------+ | Sales Data Table | | | | Product | Region | Sales | Date | | Apples | North | 120 | 2024-01-05 | | Bananas | South | 150 | 2024-01-06 | | Cherries| East | 90 | 2024-01-07 | | Dates | West | 200 | 2024-01-08 | | Elderberries | North | 170 | 2024-01-09 | +----------------------------------------------------------+
Interactivity
The Format Sales Data macro button triggers a macro that formats the sales data table instantly. This helps users quickly apply consistent styling without manually formatting cells.
The Total Sales KPI updates automatically if the sales data changes, showing the new total.
Self Check
If you add a new sales row for "Figs" with 130 sales and run the macro button, what happens?
- The Total Sales KPI updates to include the new sales amount.
- The macro formats the new row with bold headers, currency format in Sales, and fill color, matching the existing style.
Key Result
Dashboard showing total sales and a macro button to format sales data automatically.