Why Excel is essential for data work - Dashboard Impact

Dashboard Mode - Why Excel is essential for data work

Business Question

How can Excel help us quickly understand and analyze sales data to make better decisions?

Sample Data

Product	Region	Sales Q1	Sales Q2	Sales Q3	Sales Q4
Apples	North	120	150	130	170
Bananas	South	80	90	100	110
Cherries	East	200	210	190	220
Dates	West	50	60	55	65
Elderberries	North	90	100	95	105

Dashboard Components

• Total Annual Sales: Shows total sales for all products and regions combined.
  Formula: =SUM(C2:F6)
  Result: 2390
• Average Quarterly Sales: Shows average sales per quarter.
  Formula: =AVERAGE(C2:F6)
  Result: 119.5
• Highest Sales Product: Finds the product with the highest total sales.
  Formula (helper column G): =SUM(C2:F2) copied down rows 2-6.
  Formula to find max total sales: =MAX(G2:G6)
  Formula to find product name: =INDEX(A2:A6,MATCH(MAX(G2:G6),G2:G6,0))
  Result: Cherries (820 total sales)
• Sales by Region Table: Summarizes total sales by region.
  Formula for North: =SUMIF(B2:B6,"North",G2:G6)
  Similarly for other regions.
  Results: North=960, South=380, East=820, West=230

Dashboard Layout

+----------------------+-----------------------+
| Total Annual Sales    | Average Quarterly Sales|
|        2390          |         119.5          |
+----------------------+-----------------------+
| Highest Sales Product: Cherries (820)          |
+-----------------------------------------------+
| Sales by Region Table                          |
| +---------+-------+                            |
| | Region  | Sales |                            |
| | North   | 960   |                            |
| | South   | 380   |                            |
| | East    | 820   |                            |
| | West    | 230   |                            |
| +---------+-------+                            |
+-----------------------------------------------+

Interactivity

Add a filter for Region. When you select a region, all components update to show data only for that region:

• Total Annual Sales recalculates sum for selected region.
• Average Quarterly Sales recalculates average for selected region.
• Highest Sales Product updates to show top product in selected region.
• Sales by Region Table highlights or filters to show only the selected region.

Self Check

If you add a filter for Region = North, which components update and what are their new values?

• Total Annual Sales: 960
• Average Quarterly Sales: 120
• Highest Sales Product: Apples (570)
• Sales by Region Table: Shows only North with 960 sales

Key Result

This dashboard shows total and average sales, top product, and sales by region with interactive filtering.