Running macros in Excel - Dashboard Guide

Dashboard Mode - Running macros

Dashboard Goal

Help users run macros easily to automate tasks like formatting and calculations in Excel.

Sample Data

Item	Quantity	Price
Apples	10	0.5
Bananas	5	0.3
Oranges	8	0.6
Grapes	15	0.4
Pears	7	0.55

Dashboard Components

• KPI Card: Total Quantity
  Formula: =SUM(B2:B6)
  Result: 45
• KPI Card: Total Sales
  Formula: =SUMPRODUCT(B2:B6,C2:C6)
  Result: 21.15
• Button: Run Macro to Format Table
  Description: When clicked, runs a macro that bolds headers, colors header background light blue, and formats price column as currency.
• Button: Run Macro to Calculate Discounts
  Description: When clicked, runs a macro that adds a new column "Discounted Price" with 10% off price for each item.
• Table: Data Table with Discounted Price Column
  Initially empty in column D, filled after running discount macro.

Dashboard Layout

+----------------------+----------------------+
| Total Quantity (KPI) | Total Sales (KPI)    |
+----------------------+----------------------+
| [Run Format Macro]    | [Run Discount Macro] |
+-------------------------------------------+
| Data Table with Items, Quantity, Price,   |
| and Discounted Price (after macro runs)   |
+-------------------------------------------+

Interactivity

Clicking the Run Format Macro button applies formatting to the data table headers and price column.

Clicking the Run Discount Macro button adds the "Discounted Price" column with prices reduced by 10%. This updates the data table to show new values.

Self Check

After clicking the Run Discount Macro button, what is the discounted price for Grapes?

Answer: Grapes price is 0.4, so discounted price = 0.4 * 0.9 = 0.36

Key Result

Dashboard to run macros that format data and calculate discounted prices automatically.