Analyze Sales Data Like a Data Analyst

Clean messy sales data, build formulas, and create a visual dashboard.

Introduction

30 Second Summary

Every business decision starts with a spreadsheet full of messy numbers that someone has to make sense of. That someone is a data analyst.

In this project, you will clean a messy sales dataset, analyze it with formulas, and build a visual dashboard in Google Sheets. Along the way, you will use a free AI chatbot to help you write formulas and plan your analytical approach.

What You'll Build

You will open your finished workbook and show someone exactly which region leads in revenue, who the top salesperson is, and how product categories break down across the business.

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

  • A cleaned sales dataset that produces accurate totals when you run formulas against it, proving you can spot and fix the problems that make raw data unreliable.
  • A formula-driven analysis sheet that answers real business questions on demand using SUMIFS, COUNTIF, and AVERAGEIF.
  • A visual dashboard with pivot tables, charts, and a written summary that a hiring manager can read in under 10 seconds.
  • An optional Secret Mission to push your skills further.

Are there any prerequisites?

You need a Google account and access to a free AI chatbot like Google Gemini, ChatGPT, or Claude. No software to install, no paid subscriptions required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. You'll spend the next 45 minutes turning messy sales data into a polished dashboard, and being clear on the goal now keeps you focused through the cleaning, formulas, and charts ahead.

Set Up Your Analysis Workspace

Every data analysis project starts with two things: the data you want to explore and the tools you will use to explore it.

In this step, you will create a Google Sheets workbook, load it with a sample sales dataset, and open a free AI chatbot as your analysis assistant.

In this step, get ready to:
  • Create a Google Sheets workbook called "Sales Analysis Project".
  • Paste in a 40-row sales dataset.
  • Open a free AI chatbot and confirm it is working.
Create your Google Sheets workbook

Google Sheets is where all of your analysis work will live. You need a blank workbook with a clearly named sheet tab before you bring in any data.

  • Open your browser and go to sheets.google.com.
  • Sign in with your Google account if you are not already signed in.
  • Click Blank spreadsheet (the large + card at the top) to create a new spreadsheet.
  • Click the title in the top left corner (it says "Untitled spreadsheet") and rename it to Sales Analysis Project.
  • At the bottom of the screen, right-click the sheet tab that says Sheet1.
  • Select Rename and change the name to Raw Data.

You should see your spreadsheet title as "Sales Analysis Project" in the top left corner, and the sheet tab at the bottom labelled "Raw Data".

Don't see the sheet tab at the bottom?

Make sure you are looking at the very bottom of the spreadsheet window. The tab bar sits below the grid, just above the browser's bottom edge. If your browser window is too small, try maximising it.

Help me find the sheet tab in Google Sheets

Paste the sales dataset

This sample dataset contains 40 rows of sales transactions from a fictional company. It spans six months and includes details like the salesperson, region, product, and revenue for each order.

  • Click on cell A1 in your Raw Data sheet.
  • Copy the first batch of data below (including the header row) and paste it into cell A1:
Order ID	Date	Region	Product Category	Product	Salesperson	Units Sold	Unit Price	Total Revenue
1001	01/15/2025	East	Electronics	Wireless Mouse	Sarah Chen	25	29.99	749.75
1002	01/18/2025	West	Clothing	Running Shoes	James Wilson	12	89.99	1079.88
1003	01/22/2025	east	Home	Desk Lamp	Maria Garcia	30	45.00	1350.00
1004	01/25/2025	North	Sports	Yoga Mat	David Kim	20	35.00	700.00
1005	01/28/2025	South	Electronics	USB Hub	Emily Brown	15	24.99	374.85
1006	02/03/2025	East	Clothing	Winter Jacket	Sarah Chen	8	149.99	1199.92
1007	02/07/2025	EAST	Sports	Basketball	Tom Rivera	18	29.99	539.82
1008	02/10/2025	West	Home	Throw Pillow	Maria Garcia	40	19.99	799.60
1009	02/14/2025	north	Electronics	Bluetooth Speaker	David Kim	10	79.99	799.90
1010	02/18/2025	South	Clothing	Polo Shirt	Emily Brown	22	39.99	879.78
1011	02/22/2025	East	Home	Coffee Maker	Sarah Chen	5	129.99	649.95
1012	02/26/2025	West	Sports	Tennis Racket	James Wilson	7	89.99	629.93
1013	03/03/2025	East	Electronics	Portable Charger	Sarah Chen	35	19.99	699.65
1014	03/07/2025	east	Clothing	Denim Jeans	Tom Rivera	14	59.99	839.86
1015	03/12/2025	North	Home	Scented Candle	Maria Garcia	50	14.99	749.50
1016	03/15/2025	South	Sports	Dumbbell Set	David Kim	6	199.99	
1017	03/19/2025	East	Electronics	Webcam	Emily Brown	9	69.99	629.91
1018	03/22/2025	West	Clothing	Hiking Boots	Sarah Chen	11	119.99	1319.89
1019	03/26/2025	North	Sports	Jump Rope	James Wilson	28	15.99	447.72
1020	03/30/2025	South	Home	Bath Towel Set	Maria Garcia	16	34.99	559.84
1021	04/02/2025	East	Electronics	Mechanical Keyboard	Sarah Chen	15	149.99	2249.85
1022	04/05/2025	West	Sports	Resistance Bands	Tom Rivera	35	24.99	874.65
1023	04/09/2025	east	Clothing	Rain Jacket	David Kim	10	79.99	899.90
1024	04/14/2025	North	Electronics	Wireless Earbuds	James Wilson	20	59.99	1199.80
1025	04/18/2025	South	Home	Air Purifier	Emily Brown	4	249.99	999.96

You should see 25 rows of data appear across 9 columns. The headers should be in row 1, with the first data entry (Order ID 1001) in row 2.

  • Now click on cell A27 (the first empty row below your data) and paste the remaining rows:
1026	04/22/2025	East	Sports	Running Belt	Sarah Chen	45	18.99	854.55
1027	04/28/2025	West	Electronics	Monitor Stand	Maria Garcia	8	89.99	
1028	05/02/2025	North	Clothing	Cargo Pants	Tom Rivera	17	49.99	849.83
1008	02/10/2025	West	Home	Throw Pillow	Maria Garcia	40	19.99	799.60
1030	05/08/2025	East	Home	Standing Desk Mat	Sarah Chen	12	39.99	479.88
1031	05/12/2025	South	Electronics	Phone Case	David Kim	38	15.99	607.62
1032	05/16/2025	West	Clothing	Sunglasses	James Wilson	20	69.99	1399.80
1033	05/20/2025	East	Electronics	Laptop Stand	Sarah Chen	10	109.99	
1034	05/24/2025	North	Sports	Water Bottle	Emily Brown	30	22.99	689.70
1035	05/28/2025	South	Home	Wall Clock	Maria Garcia	14	44.99	729.86
1013	03/03/2025	East	Electronics	Portable Charger	Sarah Chen	35	19.99	699.65
1037	06/03/2025	East	Electronics	USB-C Dock	Sarah Chen	8	189.99	1519.92
1038	06/08/2025	West	Sports	Foam Roller	Tom Rivera	25	34.99	
1039	06/14/2025	North	Clothing	Windbreaker	David Kim	15	64.99	974.85
1006	02/03/2025	East	Clothing	Winter Jacket	Sarah Chen	8	149.99	1199.92

What is this data?

This dataset represents six months of sales transactions from a fictional company. Each row is a single order, with details about the region, product, salesperson, and revenue.

You will use this data to practice the exact workflow that entry-level data analysts perform daily: exploring raw numbers, summarizing them with formulas, and building visual dashboards for stakeholders.

  • Scroll down to the bottom of your data to confirm row 41 is the last row with data in it (row 1 is the header, rows 2 through 41 are your 40 data rows).
  • Scroll right to confirm you see all 9 columns: Order ID through Total Revenue.

All your data ended up in one column?

If everything landed in column A instead of spreading across columns, Google Sheets did not detect the tab separators. Select the column A data, click Data in the menu bar, then click Split text to columns. Choose Tab as the separator.

Help me split my data into separate columns in Google Sheets

Open your AI chatbot

Throughout this project, you will use a free AI chatbot as your analysis assistant. It can help you write complex formulas, plan your analytical approach, and interpret your findings.

  • Open a new browser tab (keep your Google Sheets tab open).
  • Go to gemini.google.com and sign in with your Google account.

Prefer a different chatbot?

Any free AI chatbot works for this project. If you prefer, go to chatgpt.com for ChatGPT or claude.ai for Claude instead. The instructions will reference Gemini, but every prompt works the same way in any chatbot.

  • Type the following test message into the chatbot and press Enter:
Hello, I'm about to analyze some sales data. Can you help me?

You should see the chatbot respond with a friendly confirmation that it can help with your sales data analysis.

Why use a separate chatbot instead of Gemini in Sheets?

Google Sheets does have built-in Gemini AI features, but they require a paid Google AI Pro or Google AI Ultra plan for personal accounts. A standalone chatbot is free and teaches a more transferable skill: directing AI tools to help you solve analytical problems step by step.

Check your workspace is ready

Before you move on, take a moment to predict what your workspace should look like right now. What two things should be open in your browser?

  • Switch back to your Google Sheets tab.
  • Confirm you see: a spreadsheet titled Sales Analysis Project, a sheet tab labelled Raw Data, and 40 rows of sales data across 9 columns.
  • Confirm your AI chatbot is open in a second browser tab and has responded to your test message.

Your workspace is set. You have real data in front of you and an AI assistant standing by. Next up, you will dig into the data to understand what it contains, and discover firsthand why jumping straight to analysis can go very wrong.

Explore the Data and Hit a Problem

Your workspace is ready. You have a full sales dataset loaded into Google Sheets and an AI assistant standing by in another tab.

The first thing any data analyst does with a new dataset is explore it. Before building dashboards or summary reports, you need to understand what the columns represent and try answering a real question with the data.

In this step, get ready to:
  • Explore the dataset and ask your AI chatbot for business questions to investigate.
  • Use SUMIF formulas to total revenue by region.
  • Investigate the data quality with COUNTA, COUNTBLANK, and UNIQUE formulas.
Get to know your data with AI help

Before writing any formulas, take a minute to scroll through the data and get familiar with what each column represents.

  • Switch back to your Sales Analysis Project spreadsheet in Google Sheets.
  • Scroll through the Raw Data sheet from top to bottom. Note the nine column names: Order ID, Date, Region, Product Category, Product, Salesperson, Units Sold, Unit Price, and Total Revenue.
  • Get a rough feel for the data: How many rows are there? What kinds of products do you see? Which regions appear?

Now let your AI chatbot help you figure out what questions to ask.

  • Switch to the browser tab where your AI chatbot is open.
  • Type the following prompt and send it:
I have sales data with columns Order ID, Date, Region, Product Category, Product, Salesperson, Units Sold, Unit Price, and Total Revenue. What are 3 business questions I could answer with this data?
  • Read the three questions the chatbot suggests. Copy them into a note or keep the tab open so you can reference them later.

Why ask an AI chatbot for questions?

In a real data analyst role, you often receive a raw dataset with no instructions. Knowing what questions to ask is half the job. An AI chatbot is useful here because it can quickly brainstorm angles you might not think of on your own.

Try a quick analysis with SUMIF

One of the most common first questions for sales data is "which region brings in the most revenue?" Let's try to answer it right now.

  • Switch back to the Raw Data sheet in Google Sheets.
  • Click on cell K1 to move to a blank area of the sheet, away from your data.
  • Type Region in cell K1 and Total Revenue in cell L1 as headers for your summary.
  • Type the four region names you noticed while scrolling. In cells K2 through K5, enter East, West, North, and South.

Now write a SUMIF formula to total the revenue for each region.

  • Click on cell L2 (next to "East") and enter this formula:
=SUMIF(C:C, K2, I:I)

What does this formula do?

SUMIF adds up values in one column when a condition is met in another column. This formula says: look through the entire Region column (C:C), find every row where the region matches the value in K2 ("East"), and add up the corresponding Total Revenue values from column I.

  • Press Enter to confirm the formula. You should see a revenue number appear in L2.
  • Copy cell L2 and paste it into cells L3, L4, and L5 to calculate revenue for West, North, and South.

You now have a revenue total for each region. Do the numbers look right?

  • In cell L6, add a grand total by entering this formula:
=SUM(L2:L5)
  • Compare this grand total to a direct sum of the entire Total Revenue column. In cell L7, enter:
=SUM(I:I)

Look at L6 and L7. Do the two totals match? They should be the same if every row's revenue is accounted for and counted exactly once.

What should I see?

The two numbers probably do not match. Your region-by-region SUMIF total (L6) and your direct column total (L7) likely show a gap. Something is off with this data. The next substep digs into exactly what is wrong.

Investigate what is wrong with the data

Your numbers do not add up. A data analyst's instinct at this point is to investigate the raw data for quality problems before trusting any results.

  • Click on cell K9 and type Filled cells as a label.
  • In cell L9, count how many Total Revenue cells contain a value by entering:
=COUNTA(I2:I)

What does COUNTA do?

COUNTA counts every cell in the range that is not empty. By starting at I2 (skipping the header), you get a count of how many data rows actually have a revenue value filled in.

  • In cell K10, type Blank cells as a label.
  • In cell L10, count how many Total Revenue cells are empty by entering:
=COUNTBLANK(I2:I)

If COUNTBLANK returns a number greater than 0, that means some rows are missing their revenue value entirely. Those missing values are excluded from your SUMIF totals, which means your analysis is based on incomplete data.

  • Now check the Region column for inconsistencies. In cell K12, type Unique regions as a label.
  • In cell L12, enter this formula to see every distinct region value in the data:
=UNIQUE(C2:C)

Why does UNIQUE show more than four regions?

The UNIQUE function is case-sensitive. It treats "East", "east", and "EAST" as three completely different values. You should see more than four entries spilling down from L12, proving that the Region column has inconsistent casing.

Your SUMIF formulas earlier happened to group these together because SUMIF is case-insensitive. But the inconsistency is still a real problem. If you export this data to a case-sensitive tool, sort it, or show it to a stakeholder, the sloppy naming will cause confusion and errors.

Before you move on, predict what you think is causing the gap between your two totals in L6 and L7.

What went wrong?

You have uncovered at least two data quality problems. Missing values in the Total Revenue column mean some sales are not counted at all. Inconsistent region names like "East" and "east" look messy and will cause issues in other tools.

There may also be duplicate rows in the data, which inflate your totals by counting the same order more than once. These are exactly the kinds of problems hiding in real-world datasets. Catching them before anyone else sees your analysis is what separates a careful analyst from one who reports wrong numbers.

You have just learned the most important lesson in data analysis: never trust raw data. Your SUMIF results, COUNTBLANK count, and UNIQUE list all prove that this dataset needs cleaning before any analysis can be trusted. In the next step, you will fix every one of these problems.

Clean Your Data Like a Pro

In the last step, you tried to analyze your sales data and got wrong results. Regions appeared multiple times under different casing, blank cells silently excluded revenue from your totals, and duplicate rows inflated numbers.

This is the reality of working with data. Cleaning it is where real data analysts spend the majority of their time. Skipping this step guarantees wrong conclusions that erode trust. In this step, you will fix every issue you uncovered and prove your data is ready for real analysis.

In this step, get ready to:
  • Standardize all region names so "East", "east", and "EAST" become one clean entry.
  • Remove duplicate rows that are inflating your totals.
  • Fill every missing revenue value so no data is left out of your calculations.
Fix region name inconsistencies

Your UNIQUE formula showed that "East", "east", and "EAST" are all treated as separate values. Google Sheets formulas like SUMIF are case-sensitive when matching criteria, so you need every region name capitalized the same way.

  • Open Find and Replace by pressing Cmd+Shift+H (macOS) or Ctrl+H (Windows).
  • In the Find field, type east.
  • In the Replace with field, type East.
  • Check the Match case checkbox so that only the lowercase version is targeted.
  • Click Replace all.

Why Match case?

Without Match case checked, searching for "east" would also match "East" (the correct version) and replace it too. Checking Match case makes the search only find the exact casing you typed, so your correct entries stay untouched.

  • Now repeat the same process for the uppercase version. In the Find field, type EAST and keep Replace with as East.
  • Click Replace all.
  • Repeat Find and Replace for every other region that has casing variants: north to North, NORTH to North, west to West, south to South, and so on for any other variants you see.

Now verify that your regions are clean. Find the UNIQUE formula you wrote in the previous step and check its output.

You should see exactly four entries: East, West, North, South. No duplicates, no casing variants.

Still seeing duplicate region names?

Check for trailing spaces. A cell that reads "East " (with a space after) looks identical to "East" but is treated as a different value. Use Find and Replace to search for "East " (with a trailing space) and replace it with "East" (no trailing space).

Also make sure Match case was checked for each replacement. Without it, you may have accidentally overwritten correct entries.

Still stuck? Help me figure out why UNIQUE still shows duplicate region names after Find and Replace.

Remove duplicate rows

Your dataset contains exact duplicate rows where the same order appears more than once. These duplicates inflate your totals and make every calculation unreliable.

  • Click on any cell inside your data (not in the blank area where your exploratory formulas are).
  • Select all your data including headers. Click the cell in the top-left corner of your data (A1), then press Cmd+Shift+End (macOS) or Ctrl+Shift+End (Windows) to select through the last row of data.
  • Click Data in the top menu bar.
  • Click Data cleanup, then click Remove duplicates.
  • Check the Data has header row checkbox.
  • Make sure Select All is checked so every column is considered when identifying duplicates.
  • Click Remove duplicates.

Google Sheets shows a confirmation dialog telling you how many duplicate rows were found and removed. Note the number.

What counts as a duplicate?

Google Sheets considers two rows duplicates when every selected column has identical values. Cells with the same text but different letter cases are also treated as duplicates. By selecting all columns, you ensure only truly identical rows get removed.

Don't see the Remove duplicates option?

Make sure you clicked Data in the top menu bar (not right-clicking on a cell). The option sits under Data cleanup as a submenu.

Need a hand? Help me find the Remove duplicates option in Google Sheets.

Fill missing revenue values

Your COUNTBLANK formula from Step 2 revealed that some cells in the Total Revenue column are blank. Any blank cell is silently excluded from your SUMIF totals, which means your revenue numbers are lower than they should be.

  • Scroll through the Total Revenue column (column I) and find the first blank cell.
  • Click on the blank cell. You need to calculate Total Revenue as Units Sold multiplied by Unit Price for that row.

Type a formula that multiplies the Units Sold cell by the Unit Price cell for that row. For example, if the blank cell is in row 15:

=G15*H15

What does this formula do?

The =G15*H15 formula multiplies the value in the Units Sold column (G) by the value in the Unit Price column (H) for that specific row. This is the same calculation that produced the Total Revenue values in the other rows.

Your row numbers will differ. Adjust the formula to match whichever row you are filling.

  • Press Enter to confirm the formula. The cell should now show a revenue number.
  • Find and fill every other blank cell in the Total Revenue column with the same approach. Adjust the row number in each formula to match the row you are filling.

Ask your AI chatbot for help

If you are not sure how to write the formula for a specific row, switch to your AI chatbot tab and ask something like: "Write a Google Sheets formula to calculate total revenue as units sold times unit price for row 22." Adapt the answer it gives you.

Now verify that all blanks are filled. Find the COUNTBLANK formula you wrote in the blank area of your sheet during Step 2 and check its output.

It should now return 0. Every cell in the Total Revenue column has a value.

COUNTBLANK still showing a number greater than 0?

You may have missed a blank cell. Use Cmd+Shift+H (macOS) or Ctrl+H (Windows) to open Find and Replace. Leave the Find field empty and search within the Total Revenue column to locate any remaining blank cells.

Also make sure your COUNTBLANK formula still covers the correct range. If rows were removed when you deleted duplicates, the range may need updating to match your current data.

Still stuck? Help me find remaining blank cells in my Total Revenue column.

Verify your cleaned data with a re-run

The real test is whether your original analysis now produces correct results. Find the SUMIF formulas you wrote in Step 2 to total revenue by region.

  • Before you look at the results, predict: do you think the revenue totals will be higher or lower than what you saw in Step 2? Why?
  • Check your SUMIF results. If they still reference the old region names (with casing variants), update them to use the standardized names: East, West, North, South.

You should now see exactly four regions, each with a single accurate total. The grand total is higher than before because the previously missing revenue values are now included.

Why is the grand total different?

Three things changed. First, casing variants like "east" and "EAST" are now consolidated under "East", so revenue that was split across phantom regions is now combined correctly. Second, blank revenue cells that were silently excluded from sums now have values. Third, duplicate rows that were inflating some totals are gone.

The net result is a set of numbers you can actually trust. This is the foundation every piece of analysis in the next steps builds on.

Your data is clean and your formulas produce accurate results. Next up, you will build a proper analysis sheet with powerful formulas like SUMIFS, COUNTIF, and AVERAGEIF to answer real business questions about this sales data.

Analyze with Formulas

Your data is clean and trustworthy. The region names are consistent, duplicates are gone, and every revenue cell has a value.

Clean data on its own does not answer business questions. You need formulas that can slice the dataset by region, by product category, and by salesperson on demand. These conditional aggregation formulas are the core technical skill that data analyst interviews test, and this step builds the muscle memory you will use daily.

In this step, get ready to:
  • Build a summary table using SUMIFS to break down revenue by region and product category.
  • Calculate business metrics with COUNTIF, AVERAGEIF, MAX, and MIN.
  • Create a top salesperson leaderboard ranked by total revenue.
Build a revenue summary table with SUMIFS

A summary table shows total revenue for every combination of region and product category in a single grid. You will use SUMIFS to power it, which sums values based on multiple criteria at once.

  • Click the + icon next to your sheet tabs at the bottom of Google Sheets to create a new sheet.
  • Double-click the new tab label and rename it to Analysis.
  • Set up the table headers. Type Region in cell A1.
  • Type your four product category names across the top row: Electronics in B1, Clothing in C1, Home in D1, and Sports in E1.
  • Type Total in F1 for a row-total column you will add later.
  • In cells A2 through A5, type your four region names: East, West, North, South.

Your grid is ready. You should see a table outline with region names down the left and product categories across the top, with all the intersection cells still empty.

Now you need a formula that sums revenue only when both the region and the product category match. This is where your AI chatbot comes in.

  • Switch to your AI chatbot tab and type this prompt:
Write a Google Sheets SUMIFS formula that sums Total Revenue
where Region equals the value in cell A2 AND Product Category
equals the value in cell B1. Total Revenue is in column I of a
sheet called 'Raw Data', Region is in column C, and Product
Category is in column D.

Why ask AI for formula help?

Getting formula syntax right the first time is tedious, especially with multi-criteria functions like SUMIFS. Using an AI chatbot to draft formulas and then verifying the output yourself is the exact workflow professional analysts use to move faster without sacrificing accuracy.

  • Review the formula your chatbot gives you. It should look similar to the formula below.
  • Click cell B2 on your Analysis sheet and enter this formula:
=SUMIFS('Raw Data'!$I:$I, 'Raw Data'!$C:$C, $A2, 'Raw Data'!$D:$D, B$1)

What does this formula do?

SUMIFS adds up values in one column only when every criterion you specify is met.

  • 'Raw Data'!$I:$I is the sum range. It tells Google Sheets which column contains the numbers to add up (Total Revenue).
  • 'Raw Data'!$C:$C, $A2 is the first criterion pair. It checks that the Region column matches the region name in column A of your table.
  • 'Raw Data'!$D:$D, B$1 is the second criterion pair. It checks that the Product Category column matches the category name in row 1.
  • The $ signs lock certain references so the formula stays correct when you copy it to other cells. $A2 locks the column. B$1 locks the row.
  • Press Enter. You should see a dollar amount appear in B2. This is the total revenue for the East region in the Electronics category.
  • Click cell B2 again. Drag the small blue square in the bottom-right corner of the cell to the right, all the way across to E2. This fills the formula across all four product categories for East.
  • Select the range B2:E2. Drag the blue square down to row 5 to fill formulas for all four regions.

Every cell in the grid should now show a dollar amount. You have a complete revenue breakdown by region and product category.

  • Add row totals by clicking cell F2 and entering this formula:
=SUM(B2:E2)
  • Drag F2 down to F5 to add totals for each region.
  • Quick sanity check: add a grand total in cell F6 with =SUM(F2:F5). Then switch to the Raw Data sheet and check that the sum of the entire Total Revenue column matches this grand total.

Grand total doesn't match?

  • Check your category names in row 1 of the Analysis sheet. They must match the exact spelling in the Raw Data sheet. Even a trailing space will cause a mismatch.
  • Check your region names in column A. Make sure they match the cleaned values exactly (East, West, North, South).
  • Click on one of the SUMIFS cells and check the formula bar. Confirm that the $ signs are in the right places.

Why doesn't my SUMIFS grand total match the raw data total?

Add business metrics below the summary table

The summary table answers "how much revenue did each region-category pair generate?" Now you will add metrics that answer three more business questions: how many orders per region, what is each salesperson's average order value, and what are the largest and smallest single orders.

  • Leave a blank row below your summary table for spacing. In the next row, type Orders per Region as a section label in column A.
  • In the rows below the label, type the four region names down column A: East, West, North, South.
  • In column B next to the first region name, enter a COUNTIF formula to count how many orders belong to that region:
=COUNTIF('Raw Data'!$C:$C, A8)

What does COUNTIF do?

COUNTIF counts the number of cells in a range that match a single criterion. Here it counts how many rows in the Region column match the region name you typed. This tells you how many orders each region placed.

  • Adjust the cell reference (e.g. A8) to match the actual row where you typed the first region name. Then drag the formula down to fill for all four regions.

You should see a number next to each region showing its order count. Confirm the four counts add up to the total number of data rows in your Raw Data sheet.

  • Leave another blank row for spacing. Type Avg Revenue per Salesperson as a section label.
  • Below the label, type each salesperson's name down column A (check the Raw Data sheet for the exact names).
  • In column B next to the first salesperson name, enter an AVERAGEIF formula:
=AVERAGEIF('Raw Data'!$F:$F, A14, 'Raw Data'!$I:$I)

What does AVERAGEIF do?

AVERAGEIF calculates the average of a range, but only for rows where the criterion is met. Here it averages Total Revenue only for rows where the Salesperson column matches the name you specified. This reveals each salesperson's typical order size.

  • Adjust the cell reference (e.g. A14) to match the actual row of your first salesperson name. Drag the formula down for all salesperson names.

You should see average revenue values next to each name. Notice which salesperson has the highest average order value.

  • Leave another blank row. Type Largest Order in column A and enter this formula in the cell to the right:
=MAX('Raw Data'!I:I)
  • In the row below, type Smallest Order in column A and enter this formula in the cell to the right:
=MIN('Raw Data'!I:I)

What do MAX and MIN tell you?

MAX finds the single highest value in the column. MIN finds the lowest. Together they show the spread of your data. A huge gap between the two tells you the dataset includes both tiny and very large orders.

You now have three sections of business metrics below your summary table: order counts per region, average revenue per salesperson, and the largest and smallest orders.

Seeing 0 or an error in your formulas?

  • If COUNTIF returns 0, the region name in your formula cell does not match the Raw Data. Check for trailing spaces or different capitalization.
  • If AVERAGEIF shows a #DIV/0! error, the salesperson name you typed does not match any entry in the Raw Data. Double-check the exact spelling.
  • Make sure the sheet name in your formulas ('Raw Data') exactly matches the tab name, including the space.

Help me debug my COUNTIF or AVERAGEIF formulas.

Build a top salesperson leaderboard

Average order value is useful, but stakeholders want to know who brought in the most total revenue. A ranked leaderboard answers that question instantly.

  • Leave a blank row below your MIN formula. Type Top Salesperson Leaderboard as a section label in column A.
  • In the cell below the label, add column headers: Salesperson in column A and Total Revenue in column B.
  • In the cell below the Salesperson header, use UNIQUE to pull a list of every distinct salesperson name automatically:
=UNIQUE('Raw Data'!F2:F)

What does UNIQUE do here?

UNIQUE scans the Salesperson column and returns each name only once, discarding duplicates. The result spills downward automatically. If you add a new salesperson to the Raw Data later, it appears here too.

You should see a list of salesperson names appear in column A. Now you need each person's total revenue next to their name.

  • In column B next to the first salesperson name, enter a SUMIFS formula to total their revenue:
=SUMIFS('Raw Data'!$I:$I, 'Raw Data'!$F:$F, A23)
  • Adjust the cell reference (e.g. A23) to match the row where your first UNIQUE result starts. Drag the formula down to fill for every salesperson in the list.

Each salesperson now has a total revenue figure. The last step is to rank them from highest to lowest.

  • In a clear area to the right (for example, columns D and E in the same rows), type Ranked Salesperson and Revenue as column headers.
  • Below the headers, enter a SORT formula that takes the name-and-revenue range and sorts it by revenue descending:
=SORT(A23:B28, 2, FALSE)

What does SORT do here?

SORT rearranges a range by the column you choose. The 2 means "sort by the second column" (Total Revenue). FALSE means descending order, so the highest revenue appears at the top. This gives you a leaderboard.

  • Adjust the range A23:B28 to match the actual rows where your UNIQUE names and SUMIFS totals sit.

You should see the salesperson names reappear in a new order, with the top earner at the top. This is your leaderboard.

SORT formula showing an error?

  • Make sure the range in your SORT formula covers both columns (names and revenue) and includes all salesperson rows.
  • Check that the cells where the SORT results need to spill are empty. If there is any content in those cells, the formula cannot expand and will error.

Help me fix my SORT or UNIQUE formula in Google Sheets.

Verify your Analysis sheet

Before you move on, take a moment to predict: if you add up all four region totals in your summary table, should that number match the grand total of the Total Revenue column on the Raw Data sheet?

  • Check your grand total in cell F6 against the Raw Data sheet. The two values should match exactly. If they do, every row in your dataset is accounted for.
  • Scroll down through the Analysis sheet. You should see four distinct sections: the summary table at the top, order counts per region, average revenue per salesperson, and the sorted leaderboard at the bottom.
  • Confirm the leaderboard is sorted from highest to lowest revenue. The top name is your highest-earning salesperson.

Your Analysis sheet now answers four real business questions with formulas alone. Numbers in a table are powerful, but stakeholders need visuals. In the next step, you will turn these numbers into a dashboard with pivot tables and charts that tell the story at a glance.

Build a Dashboard with Pivot Tables and Charts

Your Analysis sheet is packed with answers. Revenue by region, order counts, averages, and a salesperson leaderboard are all backed by formulas that update when the data changes.

Numbers in a table are powerful, but they do not drive decisions on their own. A visual dashboard tells the story at a glance. In this step, you will build the deliverable you would actually hand to a manager: a pivot table, two charts, and a written summary.

In this step, get ready to:
  • Create a pivot table that breaks down revenue by region and product category.
  • Build a bar chart and a pie chart from the pivot table data.
  • Add a title and executive summary to complete your dashboard.
Create a pivot table from your cleaned data

A pivot table summarizes a large dataset into a compact grid, grouping and totaling values automatically. You will use one to cross-tabulate revenue by region and product category in a single view.

  • Switch to your Raw Data sheet tab at the bottom of the workbook.
  • Click cell A1.
  • Select all your data including headers by pressing Cmd+Shift+End (macOS) or Ctrl+Shift+End (Windows).
  • Click Insert in the top menu bar.
  • Click Pivot table.

A dialog appears asking where to place the pivot table.

  • Select New sheet.
  • Click Create.

A blank pivot table appears on a new sheet, with an editor panel on the right side. This editor panel is where you configure what your pivot table displays.

  • Right-click the new sheet tab at the bottom of the workbook.
  • Select Rename and type Dashboard.
  • Press Enter to confirm.
  • In the pivot table editor panel on the right, click Add next to Rows.
  • Select Region.
  • Click Add next to Columns and select Product Category.
  • Click Add next to Values and select Total Revenue.

The pivot table fills with numbers. Each row is a region, each column is a product category, and each cell shows the summed revenue for that combination. A Grand Total row and column appear automatically.

What is a pivot table?

A pivot table takes a flat list of data and rearranges it into a cross-tabulation. Instead of scrolling through 40 rows, you see the totals for every region-category combination in a single compact grid.

The "Rows", "Columns", and "Values" fields in the editor panel control how the data is grouped and summarized. Changing these fields rearranges the entire table instantly.

Why does it default to SUM?

Google Sheets detects that Total Revenue is a number column and defaults to SUM as the aggregation method. If you ever see COUNT or COUNTA instead, click the aggregation dropdown inside the Values section of the editor and change it to SUM.

Pivot table editor panel not showing?

The editor panel only appears when the pivot table is selected. Click anywhere inside the pivot table area on the sheet to bring it back.

If the pivot table is completely blank, check that your Raw Data sheet has column headers in row 1. Each column in the source data must have a header for the pivot table to work.

Still stuck? Help me troubleshoot my pivot table in Google Sheets. I selected my data, clicked Insert > Pivot table, and created a new sheet, but the editor panel is not showing or the data is blank.

Build a bar chart from the region totals

The pivot table has the numbers, but a chart makes the comparison between regions instant. You will create a bar chart first, then a pie chart.

  • In your pivot table, find the Grand Total column on the far right. This column shows each region's total revenue across all product categories.
  • Select the region names (East, West, North, South) and their corresponding Grand Total values. Do not include the Grand Total row at the bottom.
  • Click Insert in the top menu bar.
  • Click Chart.

A chart appears and the Chart editor panel opens on the right. Google Sheets may suggest a chart type, but you need a bar chart specifically.

  • In the Chart editor under the Setup tab, click the Chart type dropdown.
  • Select Bar chart.
  • Switch to the Customize tab in the Chart editor.
  • Expand Chart & axis titles.
  • Type Revenue by Region into the Title text field.
  • Click the X on the Chart editor to close it.

Your bar chart now shows each region as a horizontal bar. You can instantly see which region leads in total revenue and how the others compare.

Chart showing the wrong data?

If the chart looks off, double-check your selection. You need exactly the region names in one column and their Grand Total values in the adjacent column. Do not include the header row or the Grand Total row at the bottom.

You can also fix the data range after creating the chart. Double-click the chart to open the editor, then adjust the Data range field under the Setup tab.

Still stuck? Help me fix my bar chart data range.

Add a pie chart for product categories
  • Look at the bottom of your pivot table and find the Grand Total row. This row shows the total revenue for each product category.
  • Select the product category names across the top of the pivot table and their matching Grand Total values in the bottom row.
  • Click Insert in the top menu bar.
  • Click Chart.
  • In the Chart editor under the Setup tab, change the chart type to Pie chart.
  • Switch to the Customize tab.
  • Expand Chart & axis titles and type Revenue by Product Category as the title.
  • Expand Pie chart in the Customize tab and check Slice label to show the category names on each slice.
  • Close the Chart editor.

Your pie chart now shows the revenue share for each product category. The largest slice is immediately obvious.

Choose colors that pop

To change chart colors, double-click the chart, go to the Customize tab, and expand Chart style. Pick a color palette where the differences between regions and categories are easy to see at a glance.

Add a title and executive summary

Charts without context leave the viewer guessing what to take away. A title and a short written summary tell the reader exactly what matters.

  • Right-click row 1 at the top of your Dashboard sheet.
  • Click Insert 2 rows above to make room for a title.
  • Select cells A1 through F1 (or enough columns to span the width of your pivot table).
  • Click Format in the top menu bar.
  • Click Merge cells, then select Merge all.
  • Type Sales Performance Dashboard into the merged cell.
  • Make the title bold by pressing Cmd+B (macOS) or Ctrl+B (Windows).
  • Increase the font size to 18 using the font size dropdown in the toolbar.

Your dashboard now has a clear title. Next, you will write the executive summary that tells the reader what to take away.

  • Click on an empty cell below your charts.
  • Type 2-3 bullet points summarizing your key findings. Look at the bar chart and pivot table Grand Totals to answer: which region leads in total revenue, which product category has the largest share, and any other pattern that stands out.

What makes a good executive summary?

A strong summary names specific numbers, not vague trends. "East region leads at $12,450" is far more useful than "East is the top region."

Aim for findings that answer a question a manager would actually ask: "Where should we focus sales effort?" or "Which product line is underperforming?"

Let AI draft your summary

Switch to your AI chatbot tab and try a prompt like: "I have sales data broken into East, West, North, and South regions and four product categories. East leads at $X and Electronics is the biggest category at $Y. Write a 3-bullet executive summary for a hiring manager." Replace the dollar amounts with your actual Grand Total values.

Before you step back to review your finished dashboard, predict: could someone who has never seen this data identify the top-performing region and the biggest product category within 10 seconds?

  • Scroll through your Dashboard sheet from top to bottom.

You should see a bold title at the top, the pivot table with revenue broken down by region and product category, a bar chart comparing regions side by side, a pie chart showing each category's share, and your written findings at the bottom. If a hiring manager opened this sheet for the first time, the story would land immediately.

Charts overlapping or hard to read?

Click and drag each chart to reposition it. Use the corner handles to resize. Place the bar chart and pie chart side by side, or stack them vertically with a few rows of spacing between them.

If labels are cut off, double-click the chart to open the editor, go to Customize, and adjust the font size under Chart & axis titles or Legend.

Need more help? Help me arrange my Google Sheets dashboard.

Secret mission

Uncover Monthly Trends

Your dashboard shows what happened across regions and categories. But what about when it happened? Add monthly trend analysis and SPARKLINE mini-charts to reveal whether revenue is growing, declining, or spiking in certain months.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in your browser using free tools, so there are no ongoing costs. Decide whether to keep your workbook in Google Drive or delete it.

Resources you used:

  • "Sales Analysis Project" workbook in Google Drive (contains your Raw Data, Analysis, and Dashboard sheets).
  • AI chatbot conversation history (in Gemini, ChatGPT, or Claude).

Keep everything running

No action needed. Choose this if you want to keep building on your analysis or revisit the workbook later.

  • Your "Sales Analysis Project" workbook stays in your Google Drive at no cost.
  • Your AI chatbot conversations are saved automatically in your chat history.
  • You can open the workbook anytime by going to drive.google.com and searching for "Sales Analysis Project".

Pause - I'll come back to this later

There are no running processes or ongoing costs to pause. Your Google Sheets workbook is saved automatically in Google Drive.

  • Come back anytime by opening drive.google.com and finding your "Sales Analysis Project" file.
  • Your AI chatbot history will still be available when you sign back in.

Delete - I don't want to use this again

Remove your project workbook and chatbot history to start fresh.

  • Go to drive.google.com.
  • Find the "Sales Analysis Project" file.
  • Right-click the file and select Move to trash.
  • To permanently delete it, click Trash in the left sidebar, right-click the file, and select Delete forever.
  • To delete your AI chatbot conversation, open your chatbot (Gemini, ChatGPT, or Claude), find the conversation in your history sidebar, and delete it from there.

Nice Work!

Nice Work!

You just built a complete sales analysis workbook from scratch in Google Sheets. You took messy, unreliable data and turned it into a polished dashboard that tells a clear story about sales performance.

You've learned how to:

  • Cleaned a messy dataset by standardizing inconsistent names, removing duplicates, and filling missing values. You discovered firsthand how dirty data produces wrong results and learned to always check data quality before drawing conclusions.
  • Built formula-driven analysis using SUMIFS, COUNTIF, AVERAGEIF, and SORT to answer real business questions and rank top salespeople.
  • Assembled a visual dashboard with a pivot table, bar chart, pie chart, and a written executive summary that tells the story at a glance.
  • Secret Mission: Added monthly trend analysis with a line chart and SPARKLINE mini-charts revealing seasonal patterns across regions.

Ready to quiz yourself?