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.

Practice

1. Why is it important to clean data before analyzing it in Google Sheets?

easy

A. Because cleaning data makes the sheet look colorful

B. Because cleaning data deletes all the numbers

C. Because clean data helps avoid errors and gives accurate results

D. Because clean data takes more space in the sheet

Solution

1. Step 1: Understand the purpose of data cleaning

   Cleaning data removes mistakes like extra spaces or wrong values that cause errors.

2. Step 2: Connect cleaning to analysis accuracy

   Accurate data means formulas and charts show correct information.

3. Final Answer:

   Because clean data helps avoid errors and gives accurate results -> Option C

4. Quick Check:

   Clean data = Accurate results [OK]

Hint: Clean data avoids errors and improves trust in results [OK]

Common Mistakes:

• Thinking cleaning changes data meaning
• Believing cleaning deletes important data
• Assuming cleaning only changes colors

2. Which formula helps remove extra spaces from text in Google Sheets?

easy

A. =TRIM(A1)

B. =CLEAN(A1)

C. =REMOVE(A1)

D. =SPACES(A1)

Solution

1. Step 1: Identify the function that removes extra spaces

   The TRIM function removes extra spaces before and after text.

2. Step 2: Check other options for correctness

   CLEAN removes non-printable characters, REMOVE and SPACES are not valid functions.

3. Final Answer:

   =TRIM(A1) -> Option A

4. Quick Check:

   TRIM removes spaces = =TRIM(A1) [OK]

Hint: Use TRIM to clean spaces around text quickly [OK]

Common Mistakes:

• Using CLEAN instead of TRIM for spaces
• Typing wrong function names like REMOVE
• Confusing TRIM with other text functions

3. Given this data in A1:A4:
10
20
30
" "
What is the result of =SUM(A1:A4) if the last cell contains a space character?

medium

A. 60

B. Error

C. 30

D. 0

Solution

1. Step 1: Understand how SUM treats spaces

   SUM ignores text and spaces, only adds numbers.

2. Step 2: Add numeric values only

   10 + 20 + 30 = 60; space is ignored.

3. Final Answer:

   60 -> Option A

4. Quick Check:

   SUM ignores spaces = 60 [OK]

Hint: SUM ignores spaces and text, adds only numbers [OK]

Common Mistakes:

• Expecting error due to space
• Counting space as zero
• Confusing text with zero

4. You have this formula:
=A1/B1
But it shows #DIV/0! error when B1 is zero. What is the fix?

medium

A. =IF(B1="", "Error", A1/B1)

B. =IFERROR(A1*B1, "Error")

C. =A1/B1

D. =IFERROR(A1/B1, "Error")

Solution

1. Step 1: Identify the error cause

   #DIV/0! occurs when dividing by zero (B1=0).

2. Step 2: Use IFERROR to handle it

   IFERROR wraps the risky division and returns "Error" if any error occurs.

3. Final Answer:

   =IFERROR(A1/B1, "Error") -> Option D

4. Quick Check:

   IFERROR catches #DIV/0! = "Error" [OK]

Hint: Wrap risky formulas like division in IFERROR to handle errors [OK]

Common Mistakes:

• Checking for empty string "" instead of numeric zero
• Removing error handling entirely
• Multiplying instead of dividing by mistake

5. You have a list with some cells containing extra spaces and some with #N/A errors. Which combination of formulas cleans spaces and replaces errors with zero?

hard

A. =IFERROR(CLEAN(A1), 0)

B. =TRIM(IFERROR(A1, 0))

C. =IF(TRIM(A1) = "", 0, A1)

D. =CLEAN(IFERROR(A1, ""))

Solution

1. Step 1: Replace errors with zero first

   IFERROR(A1, 0) changes errors to 0 so TRIM can work safely.

2. Step 2: Remove extra spaces after error fix

   TRIM removes spaces from the cleaned value.

3. Final Answer:

   =TRIM(IFERROR(A1, 0)) -> Option B

4. Quick Check:

   Fix errors then trim spaces = =TRIM(IFERROR(A1, 0)) [OK]

Hint: Use IFERROR inside TRIM to fix errors then clean spaces [OK]

Common Mistakes:

• Trimming before fixing errors causes errors
• Using CLEAN instead of TRIM for spaces
• Replacing errors with empty text instead of zero