Find the Story in Messy Sales Data

Clean messy sales data in Google Sheets and reveal the true top product.

Introduction

30 Second Summary

A small spelling difference can quietly split one product into two groups. The resulting sales summary can point to the wrong winner.

In this project, you will turn a fictional sales table into a trustworthy analysis in Google Sheets. You will follow the full analysis loop from inspecting the raw records to presenting a corrected result.

What You'll Build

Your finished workbook will make the sales story visible at a glance through corrected rankings, a presentation-ready chart, and a plain-language finding.

By the end of this project, you'll have:

A filterable 12-row sales table that makes every record easy to inspect. Suspicious product labels stand out before you trust a summary.
A before-and-after pivot table that first shows the misleading winner. The corrected totals reveal the true leading product.
A presentation-ready column chart that shows the product rankings at a glance. A concise finding names the leading product and region.
Secret Mission: Build a two-dimensional pivot table that reveals the strongest region-product combination.

Are there any prerequisites?

You need a laptop or desktop computer with internet access. A personal Google Account gives you access to Google Sheets at no cost for this project.

Before We Start

You will find the top product and region by revenue. Setting that goal now keeps data quality at the center before you trust the result.

Create Your Analysis Workspace

A trustworthy sales analysis needs one reliable home. A dedicated workbook keeps every later result connected to the same source.

Google Sheets provides that home in your browser. You will create an editable workbook named Messy Sales Analysis.

In this step, get ready to:

Open Google Sheets with your confirmed Google Account.
Create the Messy Sales Analysis workbook.
Confirm the workbook is editable with Ready in cell A1.

Create a blank Google Sheets spreadsheet

A blank spreadsheet gives the analysis an empty grid. The Google Sheets home screen creates that grid inside your account.

Google may ask you to confirm your account. That extra check is expected.

Use your browser's address bar to search for Google Sheets.
Open the official Google Sheets result from Google.
Complete the sign-in flow with your confirmed Google Account if Google prompts you.
Confirm the account shown in the top-right corner is your confirmed Google Account.
Create a blank spreadsheet from the Google Sheets home screen.

You will see an empty grid inside a new spreadsheet.

Good start. Your sales analysis now has its blank home.

Spreadsheet Not Opening?

Confirm that your browser is showing the official Google Sheets site.
Complete any account confirmation that Google presents.

Help me open a blank spreadsheet with my Google Account.

Confirm your named workspace

The file title identifies the analysis when you return to it. A value in cell A1 proves that the initial sheet accepts edits.

Click Untitled document in the upper-left corner.
Enter Messy Sales Analysis in the title field.

The workbook title at the top now reads Messy Sales Analysis.

Select cell A1 in the grid.
Enter Ready in the selected cell.

Before you check the grid, what should cell A1 show if your edit worked?

Confirm the workbook title above the grid still reads Messy Sales Analysis.
Check cell A1 in the initial sheet.

You will see Messy Sales Analysis above the grid. You will see Ready in cell A1.

Title or Cell Entry Not Sticking?

Click Untitled document again if the old title remains.
Re-enter Messy Sales Analysis in the title field.
Select the cell containing Ready if it landed outside A1.
Clear the misplaced cell.
Enter Ready in cell A1.

Help me fix my workbook title or cell entry.

Your named workbook is open and accepting edits. Next, you will replace the test entry with the raw sales dataset.

Load and Explore the Sales Data

The Google Sheets workbook from the last step is ready for real sales records. You now have one reliable place for every later summary.

A summary is only as trustworthy as the table behind it. Before summarizing anything, you need to inspect the rows, columns, and category labels in the raw data.

In this step, get ready to:

Replace the editable check with a 12-row sales dataset.
Format the table so its structure remains easy to scan.
Filter the table to inspect its distinct product labels.

Paste the sales records

In tabular data, every row represents one transaction. Every column stores the same kind of detail for each transaction.

In the sheet from earlier, click cell A1 containing Ready.
Replace Ready in cell A1 by pasting this dataset:

Date	Region	Product	Revenue
2026-10-01	North	Desk Lamp	220
2026-10-02	South	Desk Lamp	230
2026-10-03	East	Monitor Stand	200
2026-10-04	West	Keyboard	120
2026-10-05	North	DeskLamp	210
2026-10-06	South	Monitor Stand	180
2026-10-07	East	Keyboard	130
2026-10-08	West	DeskLamp	240
2026-10-09	North	Monitor Stand	170
2026-10-10	South	Keyboard	140
2026-10-11	East	Desk Lamp	190
2026-10-12	West	Monitor Stand	160

What does this data show?

Each data row records one sale on a specific date.
The Region column records where the sale happened.
The Product column holds category labels that need inspection.
The Revenue column records the numeric value of each sale.

Confirm the pasted values fill the range A1:D13.

You will see one header row above 12 sales records across columns A through D.

Did the data land in four columns?

Undo the paste if all values landed in one column.
Click cell A1.
Paste the original tab-separated block again.

If the layout still looks wrong, help me fix a tab-separated dataset that pasted into Google Sheets incorrectly.

Format the table for scanning

A distinct header separates field names from sales records. Freezing that row keeps the meaning of each column visible while you move through the table.

Select the header range A1:D1 by dragging from A1 to D1.
Apply bold text from the formatting toolbar.
Apply a light background color from the formatting toolbar.
Click View in the top menu.
Open Freeze.
Choose the option that freezes the first row.
Scroll down until the first sales records move upward.

You will see the formatted header remain fixed at the top of the grid.

Select columns A:D by dragging across their lettered headers.
Right-click one of the selected column headers.
Choose the column resizing option from the menu.
Select Fit to data.
Click OK.

Every date, region, product name, and revenue value is now fully readable.

Header moving or text clipped?

Return to View if row 1 moves when you scroll.
Open Freeze.
Choose the option that freezes the first row.
Repeat Fit to data if any value remains clipped.

If the formatting still behaves unexpectedly, help me fix the frozen header or clipped columns in Google Sheets.

Add a filter and inspect the labels

Filtering exposes the distinct labels inside a column without rewriting the source data. This gives you a quick data-quality check before any totals are calculated.

Select the range A1:D13 by dragging from the top-left cell to the bottom-right cell.
Click Data in the top menu.
Click Create a filter.

You will see a filter control in each header cell. This confirms that the filter is active across the complete table.

Before you open the Product filter, consider whether you expect every product name to use one consistent spelling.

Click the filter control in the Product header.
Review the distinct product list without changing any selections.

You will see both Desk Lamp and DeskLamp in the list. The table still contains 12 data rows plus one header row.

Missing the Product filter?

Close the filter menu if the wrong column is open.
Confirm that the selected table range is A1:D13.
Repeat Data followed by Create a filter if the header controls are missing.

If the product list still looks incomplete, help me verify the filter range in my Google Sheets sales table.

You have caught the split between Desk Lamp and DeskLamp before trusting a summary. Next, you will build a pivot table and see how that inconsistency produces the wrong winner.

Build the Initial Product Pivot

Your raw sales table is ready in Google Sheets. Its Product filter exposed two labels that look like the same item.

Now you need a summary that reveals the consequence of that inconsistency. A pivot table groups records by category.

You will total revenue with SUM before cleaning any labels. The visible split will show why an early winner cannot be trusted.

In this step, get ready to:

Create a pivot table on a separate sheet.
Group the pivot rows by Product and summarize Revenue with SUM.
Capture the misleading ranking with a written note.

Create the pivot sheet

A separate sheet keeps the summary beside your raw records without replacing them. Your original table stays available for the cleaning step later.

Switch back to the initial data sheet from earlier.
Select the full range from A1 through D13.
Click Insert in the top menu.
Click Pivot table.
Choose the destination option that places the pivot table on a new sheet.
Confirm the destination to create the pivot table.

You will see a blank pivot table on its own sheet. The side panel contains the controls that shape the summary.

Why use a separate sheet?

The source sheet holds individual sales records. The pivot sheet turns those records into totals without changing the original rows.

Summarize revenue by product

The row field decides which categories the pivot compares. The value field decides which number gets totaled for each category.

Click Add beside Rows in the pivot table side panel.
Select Product.

Before you add the revenue values, which product do you expect to have the highest total?

Click Add beside Values.
Select Revenue.
Set the Revenue summary calculation to SUM.

The pivot updates as soon as the value field is added. You will see four product rows plus an overall total.

What should I see?

The Desk Lamp row shows 640.
The DeskLamp row shows 450.
The Keyboard row shows 390.
The Monitor Stand row shows 710.

Monitor Stand appears to have the highest product revenue. The split labels hide Desk Lamp's combined result.

This outcome is intentional. The two spellings prevent Desk Lamp revenue from combining into one category.

Do your totals look different?

Confirm that the pivot source range is A1:D13.
Check that Rows contains Product.
Check that Values contains Revenue summarized by SUM.

Help me diagnose different totals in my product pivot table.

Record the misleading result

This pivot captures a provisional conclusion. A written note preserves the data-quality warning beside the numbers.

Click an empty cell beside the pivot table.
Enter Initial result: Monitor Stand appears to lead, but Desk Lamp is split across two labels..
Press Enter to save the note in the cell.

Before your final check, what detail should stop someone from trusting Monitor Stand as the real winner?

Scan the product rows from top to bottom.
Read the note beside the pivot table.

You will see Monitor Stand at 710. Desk Lamp remains at 640.

You will also see DeskLamp at 450. The note identifies the split as the reason this ranking is provisional.

You have made the data-quality problem visible before touching the source. That evidence gives the next correction a clear purpose.

Your initial summary now exposes the misleading result. Next, you will standardize the product labels and watch the ranking change.

Clean Product Names and Correct the Pivot

The first summary in Google Sheets revealed a source-data problem. Two spellings split one product into separate rows.

Cleaning these names protects data quality before you trust the winner. This step standardizes the labels so the summary can calculate the true leader.

In this step, get ready to:

Standardize the inconsistent product values in the source table.
Confirm that only three product categories remain.
Update the pivot note with the corrected leader.

Standardize the source values

A pivot table gives every distinct text label its own row. Standardizing the source removes the false split between two spellings of the same product.

Switch back to the raw-data sheet from earlier.
Click Edit in the top menu.
Select Find and replace.
Enter DeskLamp beside Find.
Enter Desk Lamp beside Replace with.
Select Match entire cell contents.
Click Replace all.

The two former DeskLamp cells in the Product column should now read Desk Lamp.

Why Match the Entire Cell?

This option limits the replacement to cells whose complete value is DeskLamp. The cleanup stays focused on the inconsistent category label.

Confirm the cleaned categories

The Product filter lists the distinct labels found in the column. It gives you a quick check that the inconsistent spelling is gone.

Click the filter control in the Product header.
Check the product values shown in the filter menu.

You should see only Desk Lamp, Keyboard, and Monitor Stand.

You have closed the source-data gap. Every Desk Lamp sale now belongs to one consistent category.

Still Seeing DeskLamp?

Repeat the exact Find and replace steps above.

Ask for help if the compact label remains after the replacement: help me find why DeskLamp still appears in my Google Sheets Product filter

Verify the corrected pivot

A pivot table refreshes whenever its source cells change. The separate product rows should merge without rebuilding the summary.

Before you return to the pivot, which product do you expect to lead once both spellings are combined?

Switch back to the product pivot sheet from the previous step.
Click the cell beside the pivot that contains Initial result: Monitor Stand appears to lead, but Desk Lamp is split across two labels..
Type Corrected result: Desk Lamp leads with 1,090 in revenue. to replace the old note.
Press Enter.
Inspect the product rows in the refreshed pivot.
Confirm the note beside the pivot shows the corrected sentence.

You should see Desk Lamp at 1,090. You should also see Monitor Stand at 710.

Keyboard remains at 390. The separate DeskLamp row is gone.

The note now reads Corrected result: Desk Lamp leads with 1,090 in revenue..

That is the correction proven. Cleaning one category label reversed the product ranking.

Pivot Still Split?

Return to the Product filter on the raw-data sheet.
Confirm that DeskLamp no longer appears.

Ask for help if the source is clean but the pivot remains split: help me troubleshoot why my cleaned Google Sheets pivot still shows two Desk Lamp rows

Your product ranking now rests on clean source data. Next, you will turn the corrected analysis into a chart with a plain-language finding.

Chart and Communicate the Findings

Your cleaned product pivot table in Google Sheets now shows Desk Lamp as the true leader. That corrected ranking gives you a trustworthy product result.

A correct table still takes time for someone else to read. A column chart makes the product ranking visible at a glance. A plain-language finding gives the result business meaning.

In this step, get ready to:

Summarize revenue by region in a second pivot table.
Build a presentation-ready column chart from the corrected product totals.
Communicate the leading product plus the leading region in a concise written finding.

Summarize revenue by region

A regional summary uses the cleaned records to answer a second business question. Summing Revenue by Region shows where the most revenue came from.

Switch back to the raw-data sheet from earlier.
Select the cleaned range A1:D13.
Click Insert in the top menu.
Select Pivot table.
Choose the option that places the pivot table on a new sheet.
Use the dialog's confirmation button to create the pivot table.

You should see a blank pivot table on its own sheet. The side panel controls how the cleaned records are grouped.

Click Add beside Rows in the side panel.
Choose Region.
Click Add beside Values.
Choose Revenue.
Set the revenue summarization to SUM.

You should see North at 600, South at 550, East at 520, plus West at 520.

That second summary is working. North now answers the regional question with the highest total of 600.

Region totals look different?

Check that the source range is A1:D13.
Confirm Region appears under Rows.
Confirm Revenue appears under Values with SUM selected.

Help me diagnose different totals in my region pivot table.

Chart the product totals

Bar height makes category comparisons quick to scan. The cleaned product totals give the chart a trustworthy source.

Switch back to the product pivot sheet from earlier.
Select the two-column range containing the three product labels plus their revenue totals.
Leave the final grand-total row outside your selection.
Click Insert in the top menu.
Select Chart.

Google Sheets adds a chart based on the selected product rows. The chart remains on the product pivot sheet.

Double-click the chart.
Select Setup in the chart settings.
Open Chart type.
Choose a column chart.

You should see three product columns. The Desk Lamp column should reach 1,090, making it taller than the other two columns.

Chart showing the wrong data?

Confirm the final grand-total row is outside the selected chart range.
Reopen Setup to check that the three product labels are used as categories.
Choose the column-chart option again if Google Sheets selected another chart type.

Help me fix a Google Sheets chart that is using the wrong pivot-table rows.

Write the business finding

A useful finding names the leading category plus the evidence behind it. Your final sentence connects the product result with the regional result.

Double-click the product chart.
Select Customize in the chart settings.
Open Chart & axis title.
Select the Title text field.
Enter Revenue by Product as the chart title.
Click an empty cell beside the chart.
Enter Desk Lamp is the top product with 1,090 in revenue, while North is the top region with 600. into the selected cell.

Before you inspect the finished workbook, which visual detail should make the leading product obvious?

Inspect the chart on the product pivot sheet.
Count the product columns in the chart.
Compare the heights of the three columns.
Read the chart title.
Switch back to the region pivot sheet from earlier.
Inspect the regional revenue totals.
Return to the product pivot sheet.
Read the written finding beside the chart.

You should see a column chart titled Revenue by Product with three product columns. Desk Lamp should be the tallest column at 1,090.

The region pivot should show North as the leader at 600. The product pivot sheet should include your complete written finding beside the chart.

You did it. Your workbook now turns cleaned sales records into a result someone else can understand at a glance.

Secret mission

Build a Region-by-Product Matrix

Your product ranking and region ranking answer separate questions. Build a two-dimensional pivot matrix that reveals the strongest region-product combination in one view.

Clean Up Your Resources

Your Messy Sales Analysis workbook remains available in Google Sheets. This project costs $0 with your personal Google Account, so choose whether to keep the workbook, close it for now, or use Google Drive's trash action.

Resources you used:

The Messy Sales Analysis spreadsheet in Google Drive.

Keep everything running

No action needed. Choose this if you want to keep your completed analysis available for future practice.

Leave the Messy Sales Analysis spreadsheet in Google Drive.
Return to the workbook whenever you want to review the cleaned data, pivot tables, chart, or written findings.

Pause - I'll come back to this later

Close the workbook for now. Your spreadsheet remains saved in Google Drive for your next analysis session.

Close the browser tab containing Messy Sales Analysis.
Return later by opening Messy Sales Analysis from Google Drive.

Delete - I don't want to use this again

Deleting your completed workbook can feel drastic. Choose this option only when you no longer need the data, pivot tables, chart, or findings.

Go to Google Drive in the browser where you are already signed in.
Locate the Messy Sales Analysis spreadsheet.
Use Google Drive's trash action on the spreadsheet.
Confirm that Messy Sales Analysis disappears from its previous location in Google Drive.

Nice Work!

You did it! In Google Sheets, you transformed a messy sales table into a cleaned workbook that explains its corrected result visually.

You've learned how to:

Detect inconsistent product labels through data quality checks on a filterable sales table.
Use pivot tables with aggregation to reveal the corrected leaders: Desk Lamp at 1,090 plus North at 600.
Communicate the product ranking with a column chart plus a concise written finding.
Secret Mission: Build a region-by-product matrix that identifies West plus Desk Lamp as the strongest combination at 240.

Ready to quiz yourself?