Why clean data entry prevents errors in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to show how clean data entry helps prevent errors in sales reporting.

📊 Data: You have a sales data table with columns: Date, Region, Product, Quantity Sold, and Price per Unit. Some entries have inconsistent region names and missing prices.

🎯 Deliverable: Create a clean version of the data using data validation and formulas, then show total sales by region without errors.

Sample Data

Date	Region	Product	Quantity Sold	Price per Unit
2024-06-01	East	Widget A	10	15
2024-06-02	east	Widget B	5	20
2024-06-03	West	Widget A	8
2024-06-04	North	Widget C	12	25
2024-06-05	South	Widget B	7	20
2024-06-06	WEST	Widget C	6	25
2024-06-07	North	Widget A	9	15
2024-06-08	South	Widget B	4	20

1

Step 1: Create a new column 'Clean Region' to standardize region names by converting all to proper case.

=PROPER(B2)

Expected Result

For 'east' and 'WEST', the formula returns 'East' and 'West' respectively.

2

Step 2: Create a new column 'Clean Price' to replace missing prices with the average price of that product.

=IF(E2="", AVERAGEIFS(E:E,C:C,C2), E2)

Expected Result

Missing price in row 3 (West, Widget A) is replaced with average price 15.

3

Step 3: Create a 'Total Sales' column multiplying Quantity Sold by Clean Price.

=D2*F2

Expected Result

Calculates total sales correctly even for rows with initially missing prices.

4

Step 4: Use a pivot table with Rows = Clean Region, Values = SUM of Total Sales to get total sales by region.

Insert Pivot Table: Rows = Clean Region, Values = SUM of Total Sales

Expected Result

Shows total sales per region without errors caused by inconsistent region names or missing prices.

Final Result

Region   | Total Sales
---------------------
East     | 250
West     | 270
North    | 435
South    | 220

✓Standardizing region names prevents splitting sales data incorrectly.

✓Replacing missing prices with average avoids calculation errors.

✓Clean data entry leads to accurate sales summaries.

Bonus Challenge

Add data validation to the 'Region' column to allow only the four valid regions: East, West, North, South.

Show Hint

Use Excel's Data Validation feature with a list of allowed regions.