Why clean data enables analysis in Google Sheets - Dashboard Impact

Dashboard Mode - Why clean data enables analysis

Business Question

How does cleaning data improve the accuracy and usefulness of sales analysis?

Sample Data

Order ID	Product	Quantity	Price	Region
1001	Pen	10	1.5	East
1002	Notebook	5	3	West
1003	Pen	abc	1.5	East
1004	Marker	7		North
1005	Pen	8	1.5
1006	Notebook	3	3	West
1007	Marker	4	2	North

Dashboard Components

• KPI Card: Total Sales (Raw Data)
  Formula: =SUMPRODUCT(IFERROR(VALUE(C2:C8),0), IFERROR(VALUE(D2:D8),0))
  Shows total sales amount including errors and missing data.
• KPI Card: Total Sales (Cleaned Data)
  Formula: =SUMPRODUCT(FILTER(C2:C8, ISNUMBER(C2:C8), ISNUMBER(D2:D8)), FILTER(D2:D8, ISNUMBER(C2:C8), ISNUMBER(D2:D8)))
  Shows total sales amount after removing invalid or missing values.
• Table: Cleaned Data
  Formula in new columns:
  F2: =IF(AND(ISNUMBER(C2), ISNUMBER(D2)), C2*D2, "Invalid")
  Shows sales per row only if quantity and price are numbers.
• Chart: Sales by Region (Cleaned)
  Formula for summary:
  =QUERY(FILTER(A2:E8, ISNUMBER(C2:C8), ISNUMBER(D2:D8), LEN(E2:E8)), "select Col5, sum(Col3*Col4) group by Col5 label sum(Col3*Col4) 'Total Sales'")
  Shows total sales per region excluding bad data.

Dashboard Layout

+----------------------+-----------------------+
| Total Sales (Raw)    | Total Sales (Cleaned) |
|       [KPI]          |        [KPI]          |
+----------------------+-----------------------+
|                      Sales by Region Chart                      |
|                             [Chart]                            |
+----------------------------------------------------------------+
|                          Cleaned Data Table                    |
|                             [Table]                            |
+----------------------------------------------------------------+

Interactivity

User can filter by Region using a dropdown. When a region is selected, the KPI cards, chart, and cleaned data table update to show only data for that region. This helps focus analysis on specific areas.

Self Check

If you add a filter for Region = West, which components update?

• The Total Sales (Raw) and Total Sales (Cleaned) KPI cards will show sales only for West region.
• The Sales by Region Chart will update to show only West region sales.
• The Cleaned Data Table will list only rows where Region is West and data is valid.

Key Result

This dashboard shows how cleaning data removes errors and missing values to improve sales analysis accuracy.