Why formatting improves readability in Excel - Dashboard Impact

Dashboard Mode - Why formatting improves readability

Dashboard Goal

Understand how formatting helps make spreadsheet data easier to read and understand quickly.

Sample Data

Product	Sales Q1	Sales Q2	Sales Q3	Sales Q4
Apples	1200	1350	1100	1450
Bananas	900	950	1000	1050
Cherries	500	600	550	700
Dates	300	350	400	450
Elderberries	200	250	300	350

Dashboard Components

• KPI Card: Total Annual Sales
  Formula: =SUM(B2:E6)
  Shows the total sales for all products and quarters combined.
• Table with Conditional Formatting
  Highlights sales above 1000 in green and below 500 in red.
  Helps quickly spot high and low sales numbers.
• Bar Chart: Quarterly Sales by Product
  Visualizes sales trends across quarters for each product.
• Average Sales per Quarter
  Formula: =AVERAGE(B2:B6) for Q1 (similar for others)
  Shows average sales to compare quarters easily.

Dashboard Layout

+----------------------+-------------------------+
| Total Annual Sales    | Average Sales per Quarter|
| [KPI Card]           | [Table with Averages]    |
+----------------------+-------------------------+
|                          Bar Chart: Quarterly Sales by Product          |
+------------------------------------------------------------------------+

Interactivity

Adding a filter for Product allows users to select one or more products. When a product filter is applied:

• The Total Annual Sales KPI updates to show sales only for selected products.
• The Table highlights sales for the selected products.
• The Bar Chart updates to show only the selected products' sales trends.
• The Average Sales per Quarter recalculates for the filtered products.

Self Check

If you add a filter to show only "Bananas" and "Cherries", which components update and how?

• The Total Annual Sales KPI shows the sum of sales for Bananas and Cherries only.
• The Table highlights sales for Bananas and Cherries, ignoring others.
• The Bar Chart displays sales trends only for Bananas and Cherries.
• The Average Sales per Quarter recalculates averages based on these two products.

Key Result

This dashboard shows how formatting and simple formulas improve data readability by highlighting key sales figures and trends.