Build a Sales Dashboard in Excel

Import, clean, and visualize sales data using Power Query and PivotTables.

Introduction

30 Second Summary

Every workplace runs on spreadsheets. Whether it's tracking sales numbers, managing budgets, or reporting team performance, the person who can turn raw data into clear insights is the person everyone relies on.

In this project, you will build a complete Sales Performance Dashboard in Excel that takes messy CSV data and transforms it into an interactive, professional report. You'll cover the full data pipeline from import to visualization.

What You'll Build

A refreshable Excel dashboard workbook that imports raw sales data, cleans and analyzes it with formulas, and presents it visually with charts and interactive filters.

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

  • A Power Query pipeline that imports, cleans, and transforms a CSV file automatically with one-click refresh.
  • Automated formulas (XLOOKUP, SUMIF, IF) that calculate revenue, commissions, and performance ratings across your data.
  • A complete interactive dashboard with PivotTables, charts, and conditional formatting that tells a data story at a glance.
  • Secret Mission: Use dynamic arrays (SORT and FILTER) to build a live, auto-updating "Top Performers" report that filters by region.

Are there any prerequisites?

None. This project includes setup for everything you need. You just need Microsoft 365 or Excel 2021+ installed on a Windows PC.

Create Your Raw Data CSV File

Every dashboard starts with data. In the real world, data arrives messy. Columns are combined, rows are blank, and nothing is formatted the way you need it.

In this step, you will create a deliberately messy CSV file that simulates real business data. You will also set up the Excel workbook where your dashboard will live.

In this step, get ready to:
  • Create a CSV file with intentionally messy data (combined columns, blank rows).
  • Set up your Excel workbook for the dashboard project.
Open Notepad and type the header row

Your CSV file needs a header row that names each column. This header tells Excel what each piece of data represents when you import it later.

  • Press the Windows key to open your search bar.
  • Type Notepad and press Enter to open it.
  • Type the following header row on the first line:
Date,SalesRep-Region,Product,Quantity,Notes

Why is this header messy on purpose?

The SalesRep-Region column combines two pieces of information (the rep's name and their region) into one cell. The Notes column contains useless filler text.

This is exactly how messy data arrives in real jobs. You will use Power Query in the next step to split, clean, and fix it automatically.

Add 20 rows of sample sales data

Below your header, you need realistic sales data. Each row represents one sale with a date, the rep who made it, the product sold, and a quantity.

  • Press Enter after the header row to move to line 2.
  • Type the following 20 rows of data, including the 2 blank lines exactly where shown:
2026-01-05,Alice-North,Widget Pro,8,follow up next week
2026-01-08,Bob-South,Gadget X,3,new customer
2026-01-12,Carol-West,Super Tool,12,referred by partner
2026-01-15,Dave-North,Widget Lite,5,repeat buyer
2026-01-19,Alice-South,Gadget Pro,7,demo went well
2026-01-22,Bob-West,Widget Pro,2,needs invoice
2026-01-26,Carol-North,Gadget X,15,bulk order
2026-01-30,Dave-South,Super Tool,4,quarterly check

2026-02-03,Alice-West,Widget Lite,11,trade show lead
2026-02-07,Bob-North,Gadget Pro,6,quarterly review
2026-02-10,Carol-South,Widget Pro,18,urgent request
2026-02-14,Dave-West,Gadget X,9,cold call
2026-02-18,Alice-North,Super Tool,1,sample order
2026-02-21,Bob-South,Widget Lite,14,online inquiry

2026-02-25,Carol-West,Gadget Pro,20,large account
2026-03-01,Dave-North,Widget Pro,10,renewal
2026-03-05,Alice-South,Gadget X,7,cross-sell
2026-03-09,Bob-West,Super Tool,16,referral
2026-03-12,Carol-North,Widget Lite,3,needs approval
2026-03-15,Dave-South,Gadget Pro,13,end of quarter push

What does this data include?

  • 4 sales reps: Alice, Bob, Carol, and Dave.
  • 3 regions: North, South, and West.
  • 5 products: Widget Pro, Widget Lite, Gadget X, Gadget Pro, and Super Tool.
  • Quantities ranging from 1 to 20.
  • Dates spread across January to March 2026.
  • 2 intentionally blank rows (lines 9 and 16) that simulate messy real-world data.

Use your own names if you prefer

The exact names, dates, and notes do not matter. What matters is having 4 reps, 3 regions, 5 products, quantities between 1 and 20, and 2 blank rows somewhere in the middle. If you change the product names, remember them for later when you build the lookup table.

✔️ Awesome, I've got everything!

Great. Make sure your file has the header on line 1, then 20 data rows and 2 blank lines below it. You should have 23 lines total (1 header + 20 data + 2 blanks).

ⓧ I'd like to double check the full code

Here is the complete CSV file from top to bottom. Your Notepad content should look like this:

Date,SalesRep-Region,Product,Quantity,Notes
2026-01-05,Alice-North,Widget Pro,8,follow up next week
2026-01-08,Bob-South,Gadget X,3,new customer
2026-01-12,Carol-West,Super Tool,12,referred by partner
2026-01-15,Dave-North,Widget Lite,5,repeat buyer
2026-01-19,Alice-South,Gadget Pro,7,demo went well
2026-01-22,Bob-West,Widget Pro,2,needs invoice
2026-01-26,Carol-North,Gadget X,15,bulk order
2026-01-30,Dave-South,Super Tool,4,quarterly check

2026-02-03,Alice-West,Widget Lite,11,trade show lead
2026-02-07,Bob-North,Gadget Pro,6,quarterly review
2026-02-10,Carol-South,Widget Pro,18,urgent request
2026-02-14,Dave-West,Gadget X,9,cold call
2026-02-18,Alice-North,Super Tool,1,sample order
2026-02-21,Bob-South,Widget Lite,14,online inquiry

2026-02-25,Carol-West,Gadget Pro,20,large account
2026-03-01,Dave-North,Widget Pro,10,renewal
2026-03-05,Alice-South,Gadget X,7,cross-sell
2026-03-09,Bob-West,Super Tool,16,referral
2026-03-12,Carol-North,Widget Lite,3,needs approval
2026-03-15,Dave-South,Gadget Pro,13,end of quarter push
Save the CSV and set up your Excel workbook

Now you need to save this file in the correct format and create the workbook where your dashboard will live.

  • In Notepad, click File in the menu bar.
  • Click Save As.
  • Navigate to your Desktop.
  • Change the Save as type dropdown from "Text Documents (*.txt)" to All Files (*.*).
  • In the File name field, type sales_raw.csv.
  • Click Save.
  • Close Notepad.

Why change the file type to All Files?

If you leave the default "Text Documents" type, Notepad adds .txt to the end of your filename. Your file would become sales_raw.csv.txt which Excel cannot import as a CSV. Switching to All Files preserves the exact name you typed.

Now set up the workbook where your dashboard will live.

  • Switch back to Excel (it should still be open from earlier).
  • Click Blank workbook to create a new file.
  • Press Ctrl+S to save.
  • Navigate to your Desktop.
  • In the File name field, type SalesDashboard.
  • Make sure the Save as type is set to Excel Workbook (*.xlsx).
  • Click Save.

Your title bar should now show SalesDashboard.xlsx. You have an empty workbook ready to receive cleaned data.

Don't see the .xlsx extension in the title bar?

Some Windows installations hide file extensions by default. As long as the title bar shows SalesDashboard and you selected the Excel Workbook format when saving, the file is correct.

If you accidentally saved as a different format, press Ctrl+S again and change the Save as type to Excel Workbook (*.xlsx).

Help me confirm my workbook is saved correctly.

Your raw data is ready and your workbook is waiting. Next up, you will use Power Query to import that messy CSV and transform it into clean, structured data.

Import and Clean Data with Power Query

Your raw CSV file is ready on the Desktop. Now it's time to bring that messy data into Excel the professional way.

Instead of copying and pasting CSV data manually, you'll use Power Query to import, clean, and transform it automatically. Power Query records every step you take, so if your source data changes, you can refresh everything with one click.

In this step, get ready to:
  • Import your CSV file into the Power Query Editor.
  • Clean the data by removing blanks, deleting a useless column, and splitting a combined column into two.
  • Load the transformed data into a formatted Excel Table.
Connect to your CSV file with Power Query

Power Query lives inside the Data tab in Excel. You'll point it at your CSV file and open the editor where all the cleaning happens.

  • In your open SalesDashboard.xlsx workbook, click the Data tab in the ribbon at the top.
  • Click Get Data.
  • Hover over From File, then click From Text/CSV.
  • In the file browser, navigate to your Desktop and select sales_raw.csv.
  • Click Open.

A preview window appears showing your data in columns. Power Query auto-detects the delimiter, uses the first row as column headers, and guesses each column's data type.

  • Click Transform Data (not Load) to open the Power Query Editor.

Why Transform Data instead of Load?

Clicking Load would dump the raw data straight into Excel without any cleaning. Transform Data opens the Power Query Editor first, where you can fix problems before the data ever touches your spreadsheet.

You should now see the Power Query Editor window with your CSV data displayed in a table. The column headers (Date, SalesRep-Region, Product, Quantity, Notes) appear across the top.

Don't see your CSV file?

Make sure you saved sales_raw.csv to your Desktop in the previous step. If the file browser shows a different folder, navigate to your Desktop manually.

If the file doesn't appear, check that you saved it with the .csv extension (not .txt).

help me find my CSV file in the Power Query file browser

Clean and reshape the data

Your raw CSV has two problems: blank rows scattered through it, and a combined column (SalesRep-Region) that mixes two pieces of information together. You'll fix both in the Power Query Editor.

  • In the Power Query Editor, click the Home tab.
  • Click Remove Rows, then click Remove Blank Rows.

The blank rows disappear immediately. Next, remove the useless Notes column.

  • Right-click the Notes column header.
  • Select Remove.

Now split the combined SalesRep-Region column into two separate columns.

  • Click the SalesRep-Region column header to select it.
  • Click the Transform tab in the ribbon.
  • Click Split Column, then click By Delimiter.
  • In the dialog, select Custom from the dropdown and type - (a single hyphen) as the delimiter.
  • Click OK.

The column splits into two new columns. Rename them so they're clear and professional.

  • Double-click the first new column header (likely named SalesRep-Region.1) and rename it to Sales Rep.
  • Double-click the second new column header (likely named SalesRep-Region.2) and rename it to Region.

What is the Applied Steps pane?

Look at the right side of the Power Query Editor. The Applied Steps pane lists every transformation you've made, in order. Each step is like an undo point.

If you make a mistake, click the X next to any step to remove it. This non-destructive approach means you can always go back.

Split created more than two columns?

If any of your data rows contained extra hyphens (like a product name with a dash), the split may create unexpected columns. Click the X next to the split step in Applied Steps to undo it.

Then try again. In the Split Column dialog, make sure Split at is set to Left-most delimiter so it only splits on the first hyphen.

help me fix my column split in Power Query

Fix data types and load the cleaned data

Power Query guessed some data types when it imported your file, but you need to make sure the Date and Quantity columns are set correctly. Then you'll load everything into Excel.

  • Click the Date column header to select it.
  • Go to the Transform tab, click Data Type, and select Date.
  • Click the Quantity column header to select it.
  • Click Data Type again and select Whole Number.

Why set data types manually?

Power Query's auto-detection is good but not perfect. Setting the Date column explicitly to Date type ensures Excel can sort chronologically and use it in date-based calculations later.

Setting Quantity to Whole Number ensures all your math formulas (revenue, commission) produce clean numeric results without decimal errors.

Your data is now clean and properly typed. Time to send it to Excel.

  • Click the Home tab in the Power Query Editor.
  • Click Close & Load.

The Power Query Editor closes and your cleaned data appears as a formatted Table in a new sheet. You should see columns for Date, Sales Rep, Region, Product, and Quantity with no blank rows and no Notes column.

  • Right-click the new sheet tab at the bottom of Excel.
  • Select Rename and type CleanData.
  • Press Enter to confirm.

What just happened?

Power Query created a live connection between your CSV file and this Excel Table. The Table auto-formats your data with headers, banded rows, and filter dropdowns.

If you ever update sales_raw.csv, you can refresh this table (Data > Refresh All) and all your cleaning steps run again automatically. No manual re-work needed.

Data loaded but looks wrong?

If you still see blank rows or the Notes column, Power Query may not have saved your steps. Go to Data > Queries & Connections in the sidebar, double-click your query to reopen the editor, and check that all your Applied Steps are listed.

If the columns are named SalesRep-Region.1 instead of Sales Rep, you missed the rename step. Double-click the column headers in the loaded Table to rename them directly, or reopen Power Query to add rename steps.

help me fix my Power Query output

Your raw CSV data is now clean, structured, and sitting in a professional Excel Table. Next up, you'll add formulas that calculate revenue, commissions, and performance ratings automatically.

Build Formulas That Calculate Automatically

Your data is clean and loaded into Excel as a formatted Table. Now it's time to make that data work for you.

Raw numbers like quantities don't tell a business story on their own. You need calculated columns that turn quantities into revenue, commissions, and performance ratings. In this step, you'll use XLOOKUP to pull prices from a lookup table, then build formulas that calculate everything automatically.

In this step, get ready to:
  • Create a Products lookup table and use XLOOKUP to pull unit prices into your CleanData table.
  • Add Revenue, Commission, and Performance columns with formulas that auto-fill.
  • Build a Summary sheet with key metrics using SUM, AVERAGE, SUMIF, and COUNTIF.
Create a Products lookup table and use XLOOKUP

Your CleanData table has product names and quantities, but no prices. You need a separate lookup table that maps each product to its unit price. Then XLOOKUP can pull the right price for each row automatically.

  • In your SalesDashboard.xlsx workbook, right-click on any sheet tab at the bottom and select Insert to create a new worksheet.
  • Double-click the new sheet tab and rename it to Products.
  • In cell A1, type Product Name. In cell B1, type Unit Price.
  • Fill in the product names in cells A2 through A6, matching the names in your CleanData exactly:
Widget Pro
Widget Lite
Gadget X
Gadget Pro
Super Tool
  • In cells B2 through B6, enter the corresponding unit prices:
25
50
99
150
299

Why a separate lookup table?

Keeping prices in their own sheet means you can update a price once and every formula that references it updates automatically. This is how real businesses manage pricing. It avoids hard-coding values into every row.

  • Switch back to the CleanData sheet by clicking its tab at the bottom.
  • Click the first empty column header cell in your table (the cell to the right of the Quantity header). Type Unit Price and press Enter.
  • Click the first data cell under Unit Price (the cell directly below the header you just typed). Enter this XLOOKUP formula:
=XLOOKUP([@Product],Products!A:A,Products!B:B,"Not Found")
  • Press Enter. The formula auto-fills down the entire column because your data is a formatted Table.

What does this formula do?

  • [@Product] is a structured reference that grabs the Product value from the current row.
  • Products!A:A tells Excel to search the entire A column on the Products sheet for a match.
  • Products!B:B tells Excel to return the corresponding value from column B (the price).
  • "Not Found" is the fallback value if no match exists. This helps you catch typos in product names.

You should see price values (like 25, 50, 99, 150, or 299) appearing in every row of the Unit Price column. If any row shows "Not Found", the product name in that row doesn't match the Products sheet exactly.

Seeing "Not Found" or a #NAME? error?

  • If you see "Not Found" in a cell, double-check that the product name in that row matches the Products sheet exactly (including capitalization and spacing).
  • If you see a #NAME? error, your Excel version may not support XLOOKUP. XLOOKUP requires Microsoft 365, Excel 2021, or Excel 2024.
  • Make sure the Products sheet tab is named exactly Products with no extra spaces.

help me fix my XLOOKUP formula in the Unit Price column

What if I have Excel 2019 or older?

XLOOKUP is only available in Microsoft 365, Excel 2021, and Excel 2024. If you have an older version, you can use VLOOKUP instead:

=VLOOKUP([@Product],Products!A:B,2,FALSE)

This searches column A of the Products sheet and returns the value from column 2 (the price). The FALSE at the end means it requires an exact match.

Add Revenue, Commission, and Performance columns

Now that you have unit prices, you can calculate the actual revenue for each sale. From there, you'll derive commissions and performance ratings.

  • Click the next empty column header cell (to the right of Unit Price). Type Revenue and press Enter.
  • In the first data cell under Revenue, enter this formula:
=[@Quantity]*[@[Unit Price]]
  • Press Enter. The formula auto-fills and you should see revenue values in every row.
  • Click the next empty column header. Type Commission (10%) and press Enter.
  • In the first data cell under Commission (10%), enter this formula:
=[@Revenue]*0.10
  • Press Enter. Each row now shows 10% of its revenue as the commission amount.
  • Click the next empty column header. Type Performance and press Enter.
  • In the first data cell under Performance, enter this formula:
=IF([@Revenue]>500,"High",IF([@Revenue]>200,"Medium","Low"))
  • Press Enter. Every row now has a text label: "High", "Medium", or "Low".

What do these formulas do?

  • Revenue multiplies the quantity sold by the unit price. Simple multiplication, but automated for every row.
  • Commission (10%) takes 10% of each sale's revenue. In a real company, this is how sales rep payouts get calculated.
  • Performance uses a nested IF formula. If revenue is above $500, the sale is rated "High". If it's above $200, it's "Medium". Everything else is "Low".

Your CleanData table should now have 9 columns total: Date, Sales Rep, Region, Product, Quantity, Unit Price, Revenue, Commission (10%), and Performance.

  • Save your workbook by pressing Ctrl+S.

Formulas not auto-filling down?

  • This happens if your data isn't formatted as a Table. Click any cell in your data, then press Ctrl+T to convert it to a Table. Then re-enter the formula in the first data cell.
  • If you see [@[Unit Price]] showing as text instead of a structured reference, make sure the column header is named exactly Unit Price with no trailing spaces.

help me fix my Revenue or Performance formulas

Create a Summary sheet with key metrics

Individual row calculations are useful, but stakeholders want the big picture. A Summary sheet pulls the most important numbers into one place using aggregation formulas.

  • Right-click a sheet tab at the bottom and select Insert to create a new worksheet.
  • Double-click the new sheet tab and rename it to Summary.
  • In cell A1, type Total Revenue. In cell B1, enter this formula:
=SUM(CleanData[Revenue])
  • In cell A2, type Average Sale. In cell B2, enter:
=AVERAGE(CleanData[Revenue])
  • In cell A3, type Alice Total. In cell B3, enter:
=SUMIF(CleanData[Sales Rep],"Alice",CleanData[Revenue])
  • In cell A4, type High Performers. In cell B4, enter:
=COUNTIF(CleanData[Performance],"High")

What do these summary formulas do?

  • SUM adds up all revenue values across the entire table.
  • AVERAGE calculates the mean revenue per sale.
  • SUMIF adds up revenue only for rows where the Sales Rep column equals "Alice". It's a conditional sum.
  • COUNTIF counts how many rows have "High" in the Performance column. This tells you how many big sales happened.
  • Save your workbook by pressing Ctrl+S.

Your Summary sheet should display four numbers in column B: a total revenue figure, an average sale amount, Alice's total, and a count of high-performing sales.

Seeing a #REF! or 0 in your summary cells?

  • A #REF! error means the table or column name in your formula doesn't match. Check that your CleanData sheet tab is named exactly CleanData and the column headers match what's in the formula (e.g., Revenue, Sales Rep, Performance).
  • If SUMIF returns 0, make sure the name in your formula matches exactly. "Alice" won't match "alice" or " Alice" (with a leading space).

help me fix my Summary sheet formulas

✔️ Awesome, I've got everything!

Great. Double check you've saved your workbook with Ctrl+S.

ⓧ I'd like to double check the full setup

Here's what your workbook should look like at this point:

  • CleanData sheet — a formatted Table with 9 columns: Date, Sales Rep, Region, Product, Quantity, Unit Price, Revenue, Commission (10%), Performance.
  • Products sheet — Column A has the header "Product Name" with 5 products (Widget Pro, Widget Lite, Gadget X, Gadget Pro, Super Tool). Column B has the header "Unit Price" with prices (25, 50, 99, 150, 299).
  • Summary sheet — A1: Total Revenue, B1: =SUM(CleanData[Revenue]). A2: Average Sale, B2: =AVERAGE(CleanData[Revenue]). A3: Alice Total, B3: =SUMIF(CleanData[Sales Rep],"Alice",CleanData[Revenue]). A4: High Performers, B4: =COUNTIF(CleanData[Performance],"High").

The formulas in the CleanData table:

  • Unit Price: =XLOOKUP([@Product],Products!A:A,Products!B:B,"Not Found")
  • Revenue: =[@Quantity]*[@[Unit Price]]
  • Commission (10%): =[@Revenue]*0.10
  • Performance: =IF([@Revenue]>500,"High",IF([@Revenue]>200,"Medium","Low"))

Your workbook now has three sheets working together: Products holds the price data, CleanData calculates everything per row, and Summary shows the big picture. Next up, you'll make this data visually pop with conditional formatting.

Apply Conditional Formatting

Your formulas are now calculating revenue, commissions, and performance ratings automatically. But staring at columns of numbers makes it hard to spot patterns quickly.

Conditional formatting solves this by turning numbers into visual signals. Colors, bars, and icons let you see who's performing well and which sales are largest without reading a single value.

In this step, get ready to:
  • Add data bars to Revenue so you can see sale size at a glance.
  • Color-code the Performance column so High and Low ratings jump off the page.
  • Add arrow icons to Quantity to categorize values into top, middle, and bottom thirds.
Add data bars to the Revenue column

Data bars draw a horizontal bar inside each cell, sized relative to the value. Larger revenues get wider bars, giving you an instant visual comparison.

  • Switch to the CleanData sheet in your workbook.
  • Click the column header letter for the entire Revenue column to select it.
  • Go to the Home tab in the ribbon.
  • Click Conditional Formatting.
  • Hover over Data Bars.
  • Choose a blue gradient fill from the options.

You should now see blue gradient bars inside each Revenue cell. Wider bars represent bigger sales.

Why gradient over solid fill?

Gradient bars fade from dark to light, making it easier to distinguish similar values. Solid bars work too, but gradients add a subtle depth that helps when values are close together.

Don't see any bars?

Make sure you selected the data cells in the Revenue column, not the header cell alone. Click the column header letter to select the entire column, or select just the data range beneath the header.

Also confirm you're on the Home tab, not the Data tab. Conditional Formatting only appears on the Home tab.

Help me troubleshoot why my conditional formatting data bars aren't showing up in Excel.

Highlight Performance ratings with color rules

Your Performance column contains text values like "High", "Medium", and "Low". Color-coding these makes it effortless to scan for top and bottom performers.

  • Click the column header letter for the Performance column to select it.
  • Go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  • Type High in the text field.
  • From the dropdown, choose Green Fill with Dark Green Text.
  • Click OK.

Now repeat the process for the "Low" values.

  • With the Performance column still selected, go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  • Type Low in the text field.
  • From the dropdown, choose Light Red Fill with Dark Red Text.
  • Click OK.

Your Performance column now shows green cells for top performers and red cells for low performers. "Medium" stays unformatted, which keeps your attention on the extremes.

Why only format High and Low?

Highlighting everything dilutes the signal. By only color-coding the extremes, your eye is drawn to what matters most. The absence of color on "Medium" tells you those values are fine without needing to read them.

Colors not appearing on the right cells?

Check that you typed High and Low exactly as they appear in your cells. The rule is case-sensitive to the values your IF formula produces.

If colors are applying to the header cell too, select only the data range (skip the header row) and reapply the rule.

Help me fix my conditional formatting color rules for the Performance column.

Add icon sets to the Quantity column

Icon sets place small graphical indicators inside each cell based on where the value falls relative to others. This is perfect for Quantity, where you want a quick sense of high, mid, and low order sizes.

  • Click the column header letter for the Quantity column to select it.
  • Go to Home > Conditional Formatting > Icon Sets.
  • Choose the 3-arrow set (colored arrows) from the directional section.

Each Quantity cell now shows an arrow icon. Green up arrows mark the top third of values, yellow sideways arrows mark the middle third, and red down arrows mark the bottom third.

How does Excel decide the arrow thresholds?

By default, Excel splits values into thirds using percentiles. The top 33% of values get green up arrows, the middle 34% get yellow sideways arrows, and the bottom 33% get red down arrows.

You can customize these thresholds later by going to Conditional Formatting > Manage Rules > Edit Rule and adjusting the percentage values.

Arrows all showing the same direction?

This happens if your Quantity values don't have enough variation. Check that your data includes a range of quantities (1 through 20). If all values are similar, the percentile thresholds won't separate them into distinct groups.

Help me understand why my icon set arrows are all the same in Excel.

  • Save your workbook by pressing Ctrl+S.

Your spreadsheet now tells a visual story. In the next step, you'll build a PivotTable that lets you slice and summarize this data any way you want without writing new formulas.

Create a PivotTable to Summarize Everything

Your conditional formatting makes individual data points pop off the screen. But what if your manager asks "how much did Alice sell in total?" or "which region had the highest revenue?" Scanning row by row is not the answer.

A PivotTable lets you summarize thousands of rows into a compact cross-tab in seconds. You drag fields around and Excel does all the grouping and math for you.

In this step, get ready to:
  • Create a PivotTable from your CleanData table on a new sheet.
  • Configure fields to show revenue by sales rep and region.
  • Add an interactive Slicer for one-click filtering.
Insert a PivotTable on a new sheet

A PivotTable needs a data source. Since your CleanData is already formatted as a Table, Excel knows exactly where the data starts and ends.

  • Switch to your CleanData sheet.
  • Click any cell inside your data table.
  • Go to the Insert tab in the ribbon.
  • Click PivotTable.
  • In the dialog that appears, select New Worksheet.
  • Click OK.

Excel creates a new sheet with an empty PivotTable placeholder and a PivotTable Fields pane on the right side.

  • Right-click the new sheet tab at the bottom of the screen.
  • Select Rename and type PivotAnalysis.
  • Press Enter to confirm.

What is a PivotTable?

A PivotTable takes your raw rows and groups them by categories you choose. Instead of writing SUMIF formulas for every combination of rep, region, and product, you drag fields into zones (Rows, Columns, Values, Filters) and Excel calculates the totals instantly.

The "pivot" comes from the ability to rotate your view of the data. The same data can be sliced by rep, by region, by date, or by product with a simple drag.

Configure fields and format the values

The PivotTable Fields pane on the right lists every column from your CleanData table. You build your summary by dragging fields into the four zones at the bottom of that pane: Rows, Columns, Values, and Filters.

  • In the PivotTable Fields pane, drag Sales Rep to the Rows zone.
  • Drag Revenue to the Values zone.

You should now see each sales rep listed in the left column with their total revenue next to them. One drag and Excel calculated every rep's total for you.

  • Drag Region to the Columns zone.

Now you see a cross-tab showing each rep's revenue broken down by region. This is the power of PivotTables. You get a multi-dimensional summary without writing a single formula.

  • In the Values zone, click the dropdown arrow next to Sum of Revenue.
  • Select Value Field Settings.
  • Click Number Format.
  • Choose Currency from the Category list.
  • Set Decimal places to 2.
  • Click OK twice to close both dialogs.

Your revenue values now display as currency with dollar signs and two decimal places.

Why format inside Value Field Settings?

Formatting through Value Field Settings applies the format to the entire PivotTable value field permanently. If you used the Home tab's number format instead, it would reset every time the PivotTable refreshes.

Don't see the PivotTable Fields pane?

Click any cell inside the PivotTable area. The Fields pane only appears when your cursor is inside the PivotTable. If it still does not show, go to the PivotTable Analyze tab and click Field List.

help me find and open the PivotTable Fields pane

Explore different views and add a Slicer

One of the biggest advantages of a PivotTable is that you can rearrange it instantly. Let's try a few different views to see how flexible this is.

  • Drag Product to the Rows zone, below Sales Rep.

You now see a product breakdown under each rep. This reveals which products each person sells most.

  • Drag Product out of the Rows zone (drag it away from the pane to remove it).
  • Drag Date to the Rows zone, below Sales Rep.

Now you see sales over time per rep. The same data, a completely different story.

  • Drag Date out of the Rows zone to remove it.

Your PivotTable should now be back to showing Sales Rep in Rows, Region in Columns, and Sum of Revenue in Values. This is the layout you will keep for the dashboard.

Why try different PivotTable layouts?

In a real job, you will constantly rearrange PivotTables to answer different questions from stakeholders. The ability to restructure your analysis in seconds (without rewriting formulas) is what makes PivotTables one of the most valued Excel skills.

Now let's add a Slicer to make filtering even more visual and interactive.

  • Click any cell inside your PivotTable.
  • Go to the PivotTable Analyze tab in the ribbon.
  • Click Insert Slicer.
  • Check the box next to Region.
  • Click OK.

A floating panel with buttons for North, South, and West appears on your sheet. Click any region button and the PivotTable instantly filters to show only that region's data.

  • Try clicking North in the Slicer to filter the PivotTable.
  • Click the clear filter icon (funnel with an X) in the top-right corner of the Slicer to show all regions again.

What is a Slicer?

A Slicer is a visual filter control. Instead of using dropdown menus or writing filter formulas, you (or anyone viewing your workbook) can click buttons to slice the data. Slicers are especially useful on dashboards where non-technical viewers need to interact with the data.

Don't see the PivotTable Analyze tab?

This tab only appears when your cursor is inside the PivotTable. Click any cell within the PivotTable area and the tab will appear in the ribbon.

help me find the PivotTable Analyze tab to insert a Slicer

Your PivotTable now gives you an interactive summary of your entire dataset. Next up, you will turn this analysis into a polished visual dashboard with charts that auto-update when the data changes.

Build Your Visual Dashboard

Your PivotTable from the last step lets you slice data any direction you want. Numbers in a grid are powerful for analysis, but visuals communicate the story instantly to anyone glancing at your screen.

In this step, you'll create PivotCharts linked to your data, move them onto a dedicated Dashboard sheet, and wire up a one-click refresh workflow. When the CSV changes, everything updates automatically.

In this step, get ready to:
  • Create PivotCharts from your PivotTable data.
  • Arrange charts, a title, and a Slicer on a dedicated Dashboard sheet.
  • Test the one-click refresh workflow that keeps everything in sync.
Create a Clustered Column PivotChart

A PivotChart is a chart directly linked to a PivotTable. When the underlying data changes, the chart updates automatically without you needing to rebuild it.

  • Switch to your PivotAnalysis sheet.
  • Click any cell inside the PivotTable.
  • Go to the PivotTable Analyze tab in the ribbon.
  • Click PivotChart.
  • In the Insert Chart dialog, select Clustered Column and click OK.

A column chart appears on your PivotAnalysis sheet. Now move it to its own dedicated sheet.

  • Right-click the chart.
  • Select Move Chart.
  • Choose New Sheet.
  • Type Dashboard as the sheet name and click OK.

Excel creates a new sheet called Dashboard with your column chart filling the entire page.

Why move the chart to its own sheet?

A chart sheet gives your visual the full page. This is the sheet you'll eventually share with stakeholders or present in meetings. Keeping analysis (PivotAnalysis) separate from presentation (Dashboard) is a professional best practice.

Add a Bar Chart and arrange your Dashboard

One chart shows revenue by person. A second chart showing revenue by product completes the picture. To create it, you'll adjust the PivotTable layout first.

  • Switch back to the PivotAnalysis sheet.
  • In the PivotTable Fields pane, drag Region out of the Columns area (drag it away from the box to remove it).
  • Drag Product into the Rows area.

Your PivotTable now shows products in the rows with their total revenue. Create a second chart from this view.

  • Click inside the PivotTable.
  • Go to the PivotTable Analyze tab.
  • Click PivotChart.
  • Select Bar Chart and click OK.

Now move this second chart to the Dashboard sheet alongside the first one.

  • Right-click the bar chart.
  • Select Move Chart.
  • Choose Object in and select Dashboard from the dropdown.
  • Click OK.

Both charts are now on your Dashboard sheet. Arrange them so they each take up half the space.

  • Switch to the Dashboard sheet.
  • Drag and resize the column chart so it fills the top half of the page.
  • Drag and resize the bar chart so it fills the bottom half of the page.
  • Click the title text on the column chart and change it to Revenue by Sales Rep.
  • Click the title text on the bar chart and change it to Revenue by Product.

Your Dashboard now shows two clearly labeled charts that tell complementary stories about your sales data.

Don't see two separate charts?

If both charts show the same data, the second chart may still be linked to the unchanged PivotTable layout. Go back to PivotAnalysis and confirm that Product is in the Rows area and Region has been removed from Columns.

help me fix my PivotCharts so they show different data

Add a dashboard title and test the refresh workflow

A professional dashboard needs a clear title at the top. You'll also move the Region Slicer here so stakeholders can filter both charts interactively.

  • On the Dashboard sheet, go to the Insert tab.
  • Click Text Box.
  • Draw a text box at the very top of the sheet.
  • Type Q1 2026 Sales Performance Dashboard inside the text box.
  • Select all the text inside the text box.
  • On the Home tab, click Bold and change the font size to 18.

Now bring the Region Slicer from the PivotAnalysis sheet onto your Dashboard.

  • Switch to the PivotAnalysis sheet.
  • Click the Region Slicer to select it.
  • Press Ctrl+X (Windows) or Cmd+X (macOS) to cut.
  • Switch to the Dashboard sheet.
  • Press Ctrl+V (Windows) or Cmd+V (macOS) to paste.
  • Position the Slicer to the side of your charts where it's easy to reach.

Click any region button on the Slicer. Both charts should filter simultaneously, showing only data for the selected region.

Why does the Slicer filter both charts?

Both PivotCharts are linked to the same PivotTable on the PivotAnalysis sheet. The Slicer filters that PivotTable, and any chart connected to it updates automatically.

The final piece is testing the refresh workflow. This proves your entire pipeline is live.

  • Open sales_raw.csv from your Desktop in Notepad.
  • Change one of the Quantity values (pick any row and increase the number).
  • Save and close sales_raw.csv.
  • Switch back to Excel.
  • Go to the Data tab.
  • Click Refresh All.

Watch your CleanData table, formulas, PivotTable, and charts all update to reflect the new number. That single click refreshes the entire pipeline from CSV to dashboard.

What just happened?

Power Query re-imported and re-cleaned the CSV. The structured Table in CleanData recalculated all formulas (Revenue, Commission, Performance, XLOOKUP). The PivotTable refreshed its cache. The PivotCharts on your Dashboard updated to show the new values.

This is the power of building a connected pipeline instead of doing everything manually. In a real job, you'd point Power Query at a shared network drive or database, and your dashboard stays current with zero effort.

Data didn't update after Refresh All?

Make sure you saved and closed sales_raw.csv before clicking Refresh All. Excel cannot read the file while Notepad still has it open.

Also confirm the CSV is still on your Desktop with the exact original file name. If you accidentally renamed it or moved it, the Power Query connection will fail to find it.

help me fix my Refresh All workflow

Secret mission

Dynamic Arrays for a Live Top Performers Report

Your dashboard uses PivotTables and charts to summarize data. But what if you could build a live report that updates instantly from a single formula, no PivotTable required? In this secret mission, you'll use dynamic array functions (SORT and FILTER) to create a self-updating Top Performers report that responds to a single cell change.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your files for future reference or delete them entirely.

Resources you used:

  • SalesDashboard.xlsx on your Desktop (your workbook with all sheets, formulas, PivotTables, and dashboard).
  • sales_raw.csv on your Desktop (your raw data source file).

Keep everything running

No action needed. Choose this if you want to keep practicing with your dashboard or plan to extend it further.

  • Your SalesDashboard.xlsx workbook stays on your Desktop, ready to open anytime.
  • Your sales_raw.csv file stays on your Desktop. You can update it and hit Refresh All to see your dashboard update.
  • Microsoft Excel remains installed on your machine as normal.

Pause - I'll come back to this later

Since this is a local project with no running processes, there is nothing to pause. Your files sit on your Desktop and consume no resources while closed.

  • Close Excel if it is still open.
  • Your files remain on your Desktop until you are ready to return.

Delete - I don't want to use this again

Remove both project files from your Desktop to start fresh.

  • Close Excel if it is still open.
  • On your Desktop, right-click SalesDashboard.xlsx and select Delete.
  • Right-click sales_raw.csv and select Delete.
  • Empty your Recycle Bin (Windows) or Trash (macOS) if you want to free the disk space permanently.

Nice Work!

Nice Work!

Congratulations! You just built a complete Sales Performance Dashboard that covers the full Excel data pipeline, from importing messy raw data to presenting interactive visual reports.

You've learned how to:

  • Import and clean messy CSV data with Power Query, using transformations like removing blank rows, splitting columns, and fixing data types to build a refreshable data pipeline.
  • Build automated formulas (XLOOKUP, SUMIF, IF) and PivotTables with Slicers to calculate revenue, commissions, and performance ratings interactively.
  • Create a professional dashboard with conditional formatting, PivotCharts, and a one-click refresh workflow that updates everything when the source data changes.
  • Used dynamic arrays (SORT and FILTER) to build a live, auto-updating Top Performers report that filters by region instantly.

Ready to quiz yourself?