PivotTable formatting in Google Sheets - Dashboard Guide

Dashboard Mode - PivotTable formatting

Dashboard Goal

See total sales by product category and region, with clear formatting to highlight top sales.

Sample Data

Order ID	Product	Category	Region	Sales
1001	Chair	Furniture	East	250
1002	Desk	Furniture	West	450
1003	Pen	Office Supplies	East	120
1004	Notebook	Office Supplies	West	200
1005	Monitor	Technology	East	600
1006	Keyboard	Technology	West	300
1007	Mouse	Technology	East	150

Dashboard Components

• Pivot Table: Shows total sales by Category (rows) and Region (columns). Formula: Use Google Sheets menu Data > Pivot table with Category as Rows, Region as Columns, and SUM of Sales as Values.
• Conditional Formatting: Applied to pivot table sales values to highlight top sales in green and low sales in red. Use Format > Conditional formatting with color scale from red (lowest) to green (highest).
• Total Sales KPI: Cell showing total sales sum with formula =SUM(E2:E8) referencing original sales data.

Dashboard Layout

+----------------------+-----------------------+
|      Total Sales      |                       |
|       (KPI)           |     Pivot Table        |
|    =SUM(E2:E8)        |  Categories x Regions  |
|                       |  with Conditional      |
|                       |  Formatting            |
+----------------------+-----------------------+

Interactivity

Add a filter slicer on Region to select one or multiple regions. When a region is selected, the pivot table updates to show sales only for that region.

Use Data > Slicer in Google Sheets, set to filter the Region column.

Self Check

If you add a filter to show only East region, which components update?

• The pivot table will show sales only for the East region.
• Conditional formatting colors will adjust based on the filtered sales values.

Key Result

Dashboard shows total sales by product category and region with conditional formatting and a total sales KPI.