Cleaning and Standardizing Historical Data

Raw business data is rarely perfect. Before building any forecast, you need to identify and fix four types of problems that silently corrupt results: duplicate records, missing values, inconsistent text, and wrong data types.

The Four Data Quality Problems

• Duplicate records — the same transaction appears twice, inflating revenue and volume totals. A forecast trained on inflated history will over-predict every future period;
• Missing values — blank cells in Revenue, Profit, or Total_Cost cause formula errors or skew averages downward. A missing Profit value looks like zero profit to AVERAGE and SUMIF;
• Inconsistent text — "north america", "North America", and "NORTH AMERICA" are treated as three separate regions by PivotTables and SUMIF formulas. Your North America total silently splits into thirds;
• Wrong data types — dates stored as text can't be used in time-series formulas. Numbers stored as text break SUM and AVERAGE without showing an obvious error.

Checking for Duplicate Order IDs

Duplicate Order_IDs are the most common source of inflated revenue in transactional datasets. There are two ways to catch them.

Visual method — Conditional Formatting:

1. Select column A (Order_ID);
2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values;
3. Any highlighted cell is a duplicate.

Finding Missing Values

You can find blank cells in Excel in several easy ways:

Method 1: Use "Go To Special" (Fastest)

1. Select the range of cells (or the whole sheet);
2. Press Ctrl + G (Win) or Fn+F5 (Mac);
3. Click Special;
4. Choose Blanks;
5. Click OK.

Excel will highlight all blank cells in the selected range.

Method 2: Use Filter for Blank Cells

1. Select your data (e.g. column T);
2. Go to the Data tab;
3. Click Filter;
4. Open the filter dropdown on a column;
5. Select (Blanks).

This shows rows with empty cells.

Method 3: Use Ctrl + Arrow Keys to Detect Blanks

This method is useful for quickly spotting gaps in a column.

1. Click the cell with heading (e.g. T1); Press:
2. Ctrl + ↓.

Excel jumps to the next filled region.

If there's a blank cell, Excel stops at the last filled cell before the blank. The next cell below will usually be the blank cell.

Once you find blanks, decide on a fill strategy before touching anything:

• If blank means zero (e.g. a cancelled order with no revenue), replace with 0 using Find & Replace (Ctrl+H → find nothing, replace with 0, limit to the column);
• If blank means genuinely missing data, add a helper column called Data_Status and flag those rows as "Incomplete" — exclude them from trend calculations until confirmed;
• Never silently delete rows. Document every removal with a note.

Standardizing Text Fields

The Region, Product_Category, Customer_Segment, and Sales_Channel columns all need consistent casing and no stray spaces. These three functions handle it:

=TRIM(G2)              → removes leading, trailing, and double spaces
=PROPER(G2)            → capitalizes the first letter of each word
=TRIM(PROPER(G2))      → both at once — the most useful combination

After cleaning with formulas, always paste the results back as values over the original column so the clean text is permanent, not formula-dependent:

• Copy the cleaned column → right-click the original column → Paste Special → Values.

Verifying Data Types

=ISNUMBER(O2)    → TRUE means Revenue is numeric; FALSE means it's text
=ISTEXT(B2)      → TRUE means Order_Date is stored as text, not a real date
=ISNUMBER(C2)    → TRUE means Year is numeric

Fix text-dates: Select the column → Data → Text to Columns → Finish. Excel re-interprets the values as real dates.

Fix text-numbers: Multiply by 1 in a helper column (=B2*1), then paste as values back over the original.

Task: Clean and Standardize

Goal: Fix any inconsistencies found in the audit before building any formulas on top of the data.

1. In a helper column next to Region (column G), enter =TRIM(PROPER(G2)) and autofill down all 1,500 rows.
2. Copy the helper column → Paste Special → Values over the original Region column. Delete the helper column.
3. Repeat the same process for Product_Category (col J), Sales_Channel (col I), and Customer_Segment (col L).
4. Verify Order_Date is stored as real dates: in a blank cell enter =ISNUMBER(B2). It must return TRUE. If FALSE, use Data → Text to Columns to fix the column.
5. In a new helper column, add the header Revenue_Check and enter =IF(O2=M2*N2,"OK","MISMATCH") to verify that Revenue = Units_Sold × Unit_Price for every row.
6. Filter the Revenue_Check column for "MISMATCH" and investigate any flagged rows before proceeding.
7. Find all blank cells in the Discount_Percent column (T) and replace them with 0 to ensure calculations and formulas work correctly without returning blank or error values.

A completed version of the workbook is attached to this lesson for comparison and troubleshooting purposes. If something went wrong during the task, you can use the final file to compare formulas, formatting, and results with your own workbook. Try completing the task independently first before checking the solution file.