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.