Why formatting communicates data meaning in Google Sheets - Dashboard Impact

Dashboard Mode - Why formatting communicates data meaning

Dashboard Goal

Help users quickly understand sales performance by using formatting to highlight important data.

Sample Data

Region	Sales	Target	Difference
North	1200	1000	=B2-C2
South	800	900	=B3-C3
East	950	950	=B4-C4
West	1100	1050	=B5-C5
Central	700	750	=B6-C6

Dashboard Components

• KPI Cards: Show total sales and average difference.
  Formulas:
  Total Sales: =SUM(B2:B6) (Result: 4750)
  Average Difference: =AVERAGE(D2:D6) (Result: 20)
• Sales Table: Shows Region, Sales, Target, Difference columns.
  Uses conditional formatting:
  - Sales cells green if sales >= target
  - Sales cells red if sales < target
  - Difference cells green if positive, red if negative, gray if zero
• Bar Chart: Visualizes Sales vs Target by Region.
  Uses colored bars matching conditional formatting colors.

Dashboard Layout

+----------------------+----------------------+
|      KPI Cards       |      KPI Cards       |
|  Total Sales (4750)  | Average Diff (20)    |
+----------------------+----------------------+
|                      Sales Table                 |
|  Region | Sales | Target | Difference             |
|  (with colors)                                  |
+------------------------------------------------+
|                  Bar Chart                       |
|          Sales vs Target by Region               |
+------------------------------------------------+

Interactivity

Adding a filter for Region lets users select one or more regions. When filtered:

• KPI Cards update totals and averages for selected regions only.
• Sales Table shows only filtered rows with formatting.
• Bar Chart updates to show only selected regions.

Self Check

If you add a filter to show only regions where sales are below target, which components update and how?

• KPI Cards show totals and averages only for those regions.
• Sales Table shows only rows with sales below target, with red formatting on sales and difference.
• Bar Chart displays bars only for those regions, colored red.

Key Result

Dashboard uses formatting to highlight sales performance vs targets, making data meaning clear at a glance.