Why clean data enables analysis in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to analyze monthly sales data to find trends and insights.

📊 Data: You have a sales data table with columns: Date, Product, Region, Units Sold, and Revenue. The data contains some errors like missing values, inconsistent text cases, and extra spaces.

🎯 Deliverable: A clean and organized sales data table ready for analysis, with formulas to fix errors, and a summary showing total revenue by region.

Sample Data

Date	Product	Region	Units Sold	Revenue
2024-01-05	Widget A	East	10	200
2024-01-07	widget a	east	5	100
2024-01-10	Widget B	West	8	160
2024-01-12	Widget C	North		120
2024-01-15	Widget A	South	7
2024-01-18	Widget B	west	6	120
2024-01-20	Widget C	North	9	180
2024-01-22	Widget A	South	4	80

1

Step 1: Remove extra spaces and fix text case in the Region column to make all region names consistent.

In a new column 'Clean Region', use formula: =PROPER(TRIM(C2)) and copy down.

Expected Result

Region names like ' East ', 'east', and 'west' become 'East', 'East', and 'West' respectively.

2

Step 2: Fix inconsistent product names by making all product names proper case.

In a new column 'Clean Product', use formula: =PROPER(B2) and copy down.

Expected Result

Product names like 'widget a' become 'Widget A'.

3

Step 3: Fill missing Units Sold values with 0 to avoid errors in calculations.

In a new column 'Clean Units Sold', use formula: =IF(D2="",0,D2) and copy down.

Expected Result

Empty Units Sold cells become 0.

4

Step 4: Fill missing Revenue values by calculating Units Sold multiplied by unit price (assume unit price for Widget A=20, Widget B=20, Widget C=20).

In a new column 'Clean Revenue', use formula: =IF(E2="", IF(G2="Widget A", H2*20, IF(G2="Widget B", H2*20, IF(G2="Widget C", H2*20, 0))), E2) and copy down.

Expected Result

Missing Revenue cells are filled with Units Sold * 20.

5

Step 5: Create a summary table showing total revenue by region using the cleaned data.

Use formula: =SUMIF('Clean Region' range, region_name, 'Clean Revenue' range) for each region.

Expected Result

Total revenue sums for East, West, North, and South regions.

Final Result

Region   | Total Revenue
-----------------------
East     | 300
West     | 280
North    | 300
South    | 220

✓Cleaning data fixed inconsistent region and product names.

✓Filling missing values allowed accurate revenue calculations.

✓East and North regions have the highest total revenue.

✓Data cleaning is essential for reliable analysis.

Bonus Challenge

Create a pivot table to show total units sold and total revenue by product and region.

Show Hint

Use the cleaned columns for Product, Region, Units Sold, and Revenue as source data.

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