Sales Analytics Dashboard in Excel

Clean messy CSV data and build an interactive sales dashboard in Excel.

Introduction

30 Second Summary

Every business generates sales data, but raw spreadsheets full of typos, missing values, and inconsistent formats don't help anyone make decisions. The real skill is turning that mess into clear, actionable insights.

In this project, you will take two messy CSV files and transform them into an interactive sales dashboard in Excel. You will clean the data, build a relational model, write business metrics, and create visual charts that filter dynamically.

What You'll Build

An interactive executive sales dashboard in Excel that combines Power Query data cleaning, Power Pivot data modeling, DAX measures, and PivotCharts with slicers.

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

  • A Power Query workflow that imports, profiles, and cleans raw CSV data with real-world quality issues like duplicates, missing values, and wrong data types.
  • A Power Pivot data model with two related tables and three DAX measures calculating total revenue, average order value, and year-to-date sales.
  • An interactive dashboard with PivotCharts, category slicers, and a timeline control that filter all visuals together.
  • Secret Mission: Add sparklines for trend analysis and conditional formatting to flag sales anomalies.

Are there any prerequisites?

You need Excel 2024 installed on Windows and basic familiarity with Excel (opening files, navigating the ribbon, entering formulas like SUM and IF). Power Pivot is included with Excel 2024 but needs to be activated. You will enable it in Step 1.

Set Up Power Pivot and Create Sample Data

Every data analysis project starts with data. In this step, you'll create two CSV files that simulate a real-world scenario: messy sales transaction records and a clean product catalog.

These files have intentional data quality issues baked in. Duplicate rows, inconsistent date formats, missing values, and text where numbers should be. These are the exact problems data analysts face every day, and you'll clean them up in the next step using Power Query.

In this step, get ready to:
  • Create a sales transactions CSV file with realistic data quality issues.
  • Create a product catalog CSV file.
Create the sales transactions CSV file

You'll build a CSV file that contains sales orders with several intentional problems. Later, you'll use Power Query to detect and fix every one of them.

  • Press the Windows key to open your search bar.
  • Type Notepad and press Enter to open it.
  • Copy and paste the following data into the blank Notepad window:
OrderID,ProductID,Region,OrderDate,Revenue
1001,101,North,01/15/2024,250.00
1002,102,South,2024-02-20,175.50
1003,101,East,03/10/2024,320.00
1001,101,North,01/15/2024,250.00
1004,103,West,2024-04-05,N/A
1005,102,North,05/22/2024,410.00
,101,South,2024-06-15,189.99
1006,104,East,07/01/2024,525.00
1007,103,North,2024-08-12,275.00
1008,101,West,09/30/2024,sold out
1009,104,South,10/18/2024,450.00
1010,102,East,2024-11-05,199.99
,103,North,12/20/2024,305.00
1011,101,South,2024-01-28,280.00
1012,104,West,2024-03-15,475.50

What data quality issues are hiding in here?

This file has four intentional problems that mirror real-world messy data:

  • Duplicate row: Row 5 is an exact copy of row 2 (OrderID 1001). Power Query will detect this.
  • Inconsistent date formats: Some dates use MM/DD/YYYY (like 01/15/2024) and others use YYYY-MM-DD (like 2024-02-20).
  • Missing values: Two rows have no OrderID (the field before the first comma is empty).
  • Text in numeric fields: The Revenue column contains N/A and sold out instead of numbers.
  • Save this file by clicking File in the Notepad menu bar.
  • Click Save As.
  • In the Save As window, navigate to C:\DataAnalystProject\ in the folder pane.
  • Click the Save as type dropdown and select All Files (*.*).
  • In the File name field, type sales_transactions.csv.
  • Click Save.
  • Open File Explorer and navigate to C:\DataAnalystProject\.
  • You should see sales_transactions.csv listed in the folder.

Don't see a .csv file?

  • If you see sales_transactions.csv.txt instead, you forgot to change the Save as type dropdown to All Files (*.*). Delete the file, go back to Notepad, and save again with the correct type selected.
  • If the file is not in the folder at all, double-check you navigated to C:\DataAnalystProject\ before clicking Save.

Still having trouble? Help me save a CSV file correctly in Notepad

✔️ Awesome, I've got everything!

Great. Make sure Notepad is still open for the next file.

ⓧ I'd like to double check the full code

Here is the complete content of sales_transactions.csv:

OrderID,ProductID,Region,OrderDate,Revenue
1001,101,North,01/15/2024,250.00
1002,102,South,2024-02-20,175.50
1003,101,East,03/10/2024,320.00
1001,101,North,01/15/2024,250.00
1004,103,West,2024-04-05,N/A
1005,102,North,05/22/2024,410.00
,101,South,2024-06-15,189.99
1006,104,East,07/01/2024,525.00
1007,103,North,2024-08-12,275.00
1008,101,West,09/30/2024,sold out
1009,104,South,10/18/2024,450.00
1010,102,East,2024-11-05,199.99
,103,North,12/20/2024,305.00
1011,101,South,2024-01-28,280.00
1012,104,West,2024-03-15,475.50
Create the products CSV file

This second file is a clean product catalog. It gives each ProductID a name, a category, and a unit cost. You'll relate it to the sales data later in Power Pivot.

  • In Notepad, start a new blank file by pressing Ctrl+N.
  • Copy and paste the following data into the blank window:
ProductID,ProductName,Category,UnitCost
101,Widget Pro,Electronics,45.00
102,Gadget Plus,Electronics,32.50
103,Office Chair,Furniture,125.00
104,Standing Desk,Furniture,275.00

What does this file represent?

This is a dimension table. It holds descriptive attributes for each product. The ProductID column is the key that connects this table to the sales transactions. One product can appear in many transactions.

This two-table structure (fact table plus dimension table) is the foundation of relational data modeling. You'll create the formal relationship between them in Power Pivot.

  • Click File in the Notepad menu bar, then click Save As.
  • Navigate to C:\DataAnalystProject\ in the folder pane.
  • Click the Save as type dropdown and select All Files (*.*).
  • In the File name field, type products.csv.
  • Click Save.
  • Switch back to File Explorer and confirm products.csv now appears alongside sales_transactions.csv in C:\DataAnalystProject\.

Only seeing one file?

  • Make sure you pressed Ctrl+N for a new file before pasting. If you accidentally overwrote the sales file, reopen Notepad and redo both files.
  • Check that both files end in .csv and not .csv.txt. Select All Files (*.*) in the Save as type dropdown every time you save.

Still stuck? Help me create my CSV files in the right folder

✔️ Awesome, I've got everything!

Great. You now have both data files ready for import.

ⓧ I'd like to double check the full code

Here is the complete content of products.csv:

ProductID,ProductName,Category,UnitCost
101,Widget Pro,Electronics,45.00
102,Gadget Plus,Electronics,32.50
103,Office Chair,Furniture,125.00
104,Standing Desk,Furniture,275.00

Your sample data is ready. Next up, you'll import these messy files into Power Query and see how data profiling reveals the quality issues you just planted.

Import and Clean Data with Power Query

Your project folder has two CSV files ready to go. Now it's time to bring that raw data into Excel and transform it into something usable.

The sales CSV has several data quality issues baked in: duplicates, inconsistent dates, missing values, and text where numbers should be. You'll use Power Query to profile those problems, fix them, and load clean data into the Data Model.

In this step, get ready to:
  • Import the sales CSV and profile its data quality.
  • Clean the sales data by removing duplicates, fixing types, and filtering nulls.
  • Import the products CSV and load both tables into the Power Pivot Data Model.
Import the sales CSV and profile data quality

Before you can clean data, you need to see what's wrong with it. Power Query has built-in data profiling that shows you error counts, empty values, and value distributions for every column.

  • In Excel, click the Data tab on the ribbon.
  • Click Get Data.
  • Select From File, then From Text/CSV.
  • Navigate to C:\DataAnalystProject\ and select sales_transactions.csv.
  • Click Transform Data to open the Power Query Editor (do not click Load).

Why Transform Data instead of Load?

Clicking Load dumps the raw data straight into a worksheet. Clicking Transform Data opens the Power Query Editor, where you can inspect and clean the data before it ever touches your workbook.

Now enable data profiling so you can see quality indicators for every column.

  • In the Power Query Editor, click the View tab on the ribbon.
  • In the Data Preview group, check the boxes for Column quality, Column distribution, and Column profile.
  • Look at the status bar in the lower-left corner. It says Column profiling based on top 1000 rows. Click that text and switch to Column profiling based on entire data set.

What is data profiling?

Data profiling adds visual indicators above each column. Column quality shows the percentage of valid, error, and empty values. Column distribution shows how many distinct and unique values exist.

By default, Power Query only profiles the first 1,000 rows. Switching to the entire data set gives you an accurate picture of quality across all your data.

Look at the profiling bars above each column. You should see green (valid), red (error), and grey (empty) indicators. The Revenue column should show some red errors and the OrderID column should show some grey empty values.

Don't see the profiling indicators?

Make sure all three checkboxes (Column quality, Column distribution, Column profile) are checked on the View tab. If the bars appear but show no errors, click any column header to refresh the profile display.

Help me find the data profiling options in Power Query

Clean the sales data

The profiling revealed problems in your data. Now you'll fix them one by one: remove duplicate rows, correct data types, handle errors, filter out nulls, and trim whitespace.

  • Remove duplicate rows by clicking Home on the ribbon, then Remove Rows, then Remove Duplicates.

What does Remove Duplicates do?

This compares every column in each row. If two rows are identical across all columns, one copy is removed. Watch the row count in the lower-left corner decrease after applying this step.

Next, fix the data types so Power Query can interpret dates and numbers correctly.

  • Click the OrderDate column header to select it.
  • Click the type icon (ABC or 123) to the left of the column name and select Date.
  • Click the Revenue column header to select it.
  • Click the type icon and select Decimal Number.

After changing Revenue to Decimal Number, some cells will show "Error" because they contain text values that cannot be converted. You need to replace those errors with 0.

  • Right-click the Revenue column header.
  • Select Replace Errors.
  • In the dialog, enter 0 and click OK.

Why replace errors with 0?

The original CSV has text like "N/A" or "pending" in the Revenue column. When Power Query tries to convert those to numbers, it produces errors. Replacing with 0 keeps the rows in your dataset while flagging them as zero-revenue transactions.

Now remove rows where the OrderID is missing, since those are incomplete records.

  • Click the filter dropdown arrow on the OrderID column header.
  • Uncheck (null) from the filter list.
  • Click OK to apply the filter.

Finally, trim extra whitespace from the Region column so values like " West" and "West" are treated as the same region.

  • Click the Region column header to select it.
  • Click the Transform tab on the ribbon.
  • Click Format, then select Trim.

What does Trim do?

Trim removes leading and trailing whitespace from text values. This prevents "West" and " West" from being treated as separate categories in your reports.

Check the Applied Steps pane on the right side of the Power Query Editor. You should see steps like Removed Duplicates, Changed Type, Replaced Errors, Filtered Rows, and Trimmed Text listed in order.

Seeing unexpected errors after changing types?

If the entire OrderDate column shows errors, your CSV may have date formats that Power Query cannot auto-detect. Try right-clicking the column, selecting Change Type, then Using Locale to specify your region's date format.

Help me fix date type errors in Power Query

Import and clean the products CSV, then load both to the Data Model

You need a second table in your model to create relationships later. The products CSV is already clean, so you just need to verify its data types and load both tables into the Power Pivot Data Model.

  • In the Power Query Editor, click the Home tab.
  • Click New Source.
  • Select File, then Text/CSV.
  • Navigate to C:\DataAnalystProject\ and select products.csv.
  • Click OK to import the file.

Now verify the data types are correct for the products table.

  • Confirm that ProductID is set to Whole Number.
  • Confirm that ProductName is set to Text.
  • Confirm that Category is set to Text.
  • Confirm that UnitCost is set to Decimal Number.
  • If any type is incorrect, click the type icon to the left of the column name and select the correct type.

How does Power Query detect types automatically?

Power Query automatically detects column delimiters, uses the first row as column names, and changes each column's data type based on the values it finds. Numbers become Number, dates become Date, and everything else stays as Text. You're verifying this automatic detection worked correctly.

Both queries are ready. Now load them into the Data Model instead of onto worksheets.

  • Click the sales_transactions query in the Queries pane on the left to select it.
  • On the Home tab, click the dropdown arrow next to Close & Load.
  • Select Close & Load To.
  • In the dialog, select Only Create Connection.
  • Check the box for Add this data to the Data Model.
  • Click OK.

Why Only Create Connection?

Loading data directly to a worksheet creates a copy of every row on a sheet. This wastes memory and creates data you'll never look at directly. Loading as a connection-only keeps the data inside the Data Model where Power Pivot can use it for relationships and DAX calculations.

  • Repeat for the products query: click it in the Queries pane, then Home > Close & Load dropdown > Close & Load To.
  • Select Only Create Connection and check Add this data to the Data Model.
  • Click OK.

The Power Query Editor closes. Back in Excel, you should see a Queries & Connections pane on the right showing both queries with "Connection only" status.

Don't see Queries & Connections pane?

Click the Data tab, then click Queries & Connections in the ribbon to toggle the pane open.

Help me verify my queries loaded to the Data Model

Both tables are now loaded into the Data Model, cleaned and ready for relationships. Next up, you'll open Power Pivot and connect these two tables together.

Build the Data Model in Power Pivot

In the previous step, you cleaned both CSV files and loaded them into the Data Model. Right now those two tables are sitting side by side with no connection between them.

Power Pivot lets you build a relational data model inside Excel. You will connect the sales and products tables through a shared column, then write custom calculations called DAX measures that work across the entire model.

In this step, get ready to:
  • Open Power Pivot and verify both tables loaded correctly.
  • Create a one-to-many relationship between the sales and products tables.
  • Write three DAX measures to calculate key business metrics.
Open Power Pivot and verify tables

The Power Pivot window is a separate interface from the main Excel workbook. It gives you direct access to the Data Model where you can manage tables, define relationships, and write calculations.

  • Click the Power Pivot tab on the ribbon.
  • Click Manage.

The Power Pivot window opens in a new interface. You should see tabs at the bottom of this window for each table in your Data Model.

  • Verify that you see two tabs at the bottom: sales_transactions and products.
  • Click each tab to confirm both tables contain your cleaned data.

Don't see both tables?

If only one table appears (or neither), the queries may not have been loaded to the Data Model in the previous step. Return to the Power Query Editor, right-click each query, select Close & Load To, choose Only Create Connection, and check Add this data to the Data Model.

Help me troubleshoot missing tables in Power Pivot

Create a relationship between tables

A relationship connects two tables through a shared column. Both your tables have a ProductID column. Linking them means you can combine product details (like category and cost) with sales transaction data in a single analysis.

  • In the Power Pivot window, click the Design tab on the ribbon.
  • Click Create Relationship.

The Create Relationship dialog box opens. You need to specify which columns connect the two tables.

  • Set Table to sales_transactions.
  • Set Column to ProductID.
  • Set Related Table to products.
  • Set Related Column to ProductID.
  • Click OK.

What is a one-to-many relationship?

This creates a one-to-many relationship. One product in the products table can appear in many rows of the sales_transactions table.

This mirrors real-world databases where a product catalog (the "one" side) is referenced by many individual sale records (the "many" side). Power Pivot uses this relationship to correctly aggregate data across both tables.

To verify the relationship, click Home > View > Diagram View in the Power Pivot window. You should see a line connecting the two tables through their ProductID columns.

Don't see a connecting line?

Make sure both dropdowns in the Create Relationship dialog pointed to ProductID (not ProductName or another column). If the relationship failed, delete it from Design > Manage Relationships and recreate it.

Help me fix my Power Pivot table relationship

Create DAX measures

DAX (Data Analysis Expressions) measures are custom calculations stored in your Data Model. Unlike regular Excel formulas that live in worksheet cells, DAX measures calculate dynamically based on whatever filters are active in your PivotTables and charts.

  • Switch back to Data View by clicking Home > View > Data View (or click the grid icon at the bottom-right of the Power Pivot window).
  • Click the sales_transactions tab at the bottom of the Power Pivot window.
  • Look for the Calculation Area below the data grid. If it is not visible, click Home > View > Calculation Area to show it.

The Calculation Area is the blank grid below your data. Each cell in this area can hold one DAX measure.

  • Click an empty cell in the Calculation Area.
  • Type the following formula in the formula bar and press Enter:
Total Revenue:=SUM(sales_transactions[Revenue])
  • Click the next empty cell in the Calculation Area.
  • Type this formula and press Enter:
Average Order Value:=AVERAGE(sales_transactions[Revenue])
  • Click one more empty cell in the Calculation Area.
  • Type this formula and press Enter:
Revenue YTD:=TOTALYTD(SUM(sales_transactions[Revenue]), sales_transactions[OrderDate])

What do these measures calculate?

  • Total Revenue uses SUM to add up all values in the Revenue column. When filtered by region or category, it recalculates for just those rows.
  • Average Order Value uses AVERAGE to find the mean revenue per transaction. This tells you how much each sale is worth on average.
  • Revenue YTD uses TOTALYTD to calculate a running total of revenue from the start of the year to the current date in context. This is a time intelligence function that automatically resets at each year boundary.

After pressing Enter on each formula, you should see a calculated value appear in the Calculation Area cell. All three cells should display numbers (not error messages).

Seeing an error in the Calculation Area?

Check that you are typing the formula exactly as shown, including the colon and equals sign (:=) after the measure name. Also confirm the column names match your data exactly: sales_transactions[Revenue] and sales_transactions[OrderDate].

If the Revenue YTD measure shows an error, make sure your OrderDate column was set to the Date data type during the Power Query cleaning step.

Help me fix my DAX measure error

✔️ Awesome, I've got everything!

Great. Make sure you see three measure values in the Calculation Area with no errors, then you are ready to move on.

ⓧ I'd like to double check the full model

Here is what your Power Pivot Data Model should contain at this point:

  • Two tables: sales_transactions and products.
  • One relationship: sales_transactions[ProductID] linked to products[ProductID] (one-to-many).

Three DAX measures in the sales_transactions Calculation Area:

Total Revenue:=SUM(sales_transactions[Revenue])

Average Order Value:=AVERAGE(sales_transactions[Revenue])

Revenue YTD:=TOTALYTD(SUM(sales_transactions[Revenue]), sales_transactions[OrderDate])

Your data model is complete. You have two connected tables and three business metrics that will recalculate dynamically in any PivotTable or chart. Next up, you will use this model to build an interactive dashboard with PivotCharts and slicers.

Build the Interactive Dashboard

Your data model is complete with relationships and DAX measures. Now it is time to turn those numbers into something stakeholders can actually explore.

In this step, you will create PivotTables and PivotCharts from the Data Model, then wire up interactive filters so anyone can slice the data by region, category, or time period without touching a formula.

In this step, get ready to:
  • Create PivotTables from the Data Model to summarize revenue.
  • Build PivotCharts to visualize revenue by category and over time.
  • Add slicers and a timeline for interactive dashboard filtering.
Create a PivotTable from the Data Model

A PivotTable lets you summarize thousands of rows into a compact, interactive table. Because your data lives in the Power Pivot Data Model, you can pull fields from both the sales and products tables into one view.

  • Close the Power Pivot window to return to the Excel workbook.
  • Click the Insert tab on the ribbon.
  • Click PivotTable.
  • Select From Data Model.
  • Choose New Worksheet and click OK.

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

  • In the PivotTable Fields pane, expand the products table.
  • Drag Category to the Rows area.
  • Expand the sales_transactions table.
  • Drag Total Revenue (the DAX measure you created) to the Values area.
  • Drag Region from the sales_transactions table to the Columns area.

What is a PivotTable?

A PivotTable summarizes large datasets into compact, grouped views. Instead of scrolling through hundreds of rows, you drag fields into Rows, Columns, and Values areas to instantly see totals and breakdowns.

Because both tables are in the Data Model with a relationship, you can mix fields from the products table (Category) and the sales_transactions table (Revenue, Region) in a single PivotTable.

You should see a table showing Total Revenue broken down by product category in the rows and by region across the columns.

Don't see both tables in the Fields pane?

Make sure both queries were loaded with Add this data to the Data Model checked in the previous step. If only one table appears, return to Power Query, right-click the missing query, and reload it to the Data Model.

If you see the field names but Total Revenue is missing, open the Power Pivot window and confirm the measure appears in the Calculation Area of the sales_transactions table.

Help me troubleshoot my PivotTable Fields pane not showing both tables from the Data Model.

Create PivotCharts to visualize revenue

Numbers in a table are useful, but charts make patterns jump out immediately. You will create two PivotCharts: a bar chart comparing categories and regions, and a line chart showing revenue trends over time.

  • Click inside the PivotTable you just created.
  • Click the Insert tab on the ribbon.
  • Click PivotChart.
  • Select Clustered Bar and click OK.

A bar chart appears showing revenue by category and region. Now you will create a second PivotTable for time-based analysis.

  • Click an empty cell on the same worksheet, away from the first PivotTable and chart.
  • Click the Insert tab.
  • Click PivotTable.
  • Select From Data Model.
  • Choose Existing Worksheet, confirm the location points to the empty cell you selected, and click OK.
  • In the PivotTable Fields pane, expand the sales_transactions table.
  • Drag OrderDate to the Rows area.
  • Drag Total Revenue to the Values area.

Why did Excel auto-group my dates?

When you drag a Date field into Rows, Excel automatically groups it by Year, Quarter, and Month. This gives you a collapsible hierarchy so you can drill from yearly totals down to monthly detail without manually creating formulas.

  • Click inside the second PivotTable.
  • Click the Insert tab.
  • Click PivotChart.
  • Select Line and click OK.

You should now have two charts on the same sheet: a Clustered Bar chart showing revenue by category and region, and a Line chart showing revenue over time.

Chart looks empty or shows zero?

Make sure you dragged the Total Revenue measure (not the raw Revenue column) into Values. The measure uses your DAX formula. If you accidentally dragged the column, remove it from Values and re-drag the measure.

If the Line chart shows a single flat point, expand the date grouping in the PivotTable by clicking the small + icon next to the year label.

Help me figure out why my PivotChart is empty or showing incorrect values.

Add slicers and format the dashboard

A slicer is a visual filter button that lets anyone click to filter PivotTables and PivotCharts instantly. You will add Category and Region slicers plus a Timeline control for date filtering.

  • Click on either PivotTable to select it.
  • Click the Insert tab on the ribbon.
  • Click Slicer.
  • In the Insert Slicers dialog, check both Category and Region, then click OK.

Two slicer panels appear on the sheet. By default they only filter the PivotTable you selected. You need to connect them to both PivotTables.

  • Right-click the Category slicer.
  • Select Report Connections.
  • In the dialog, check the boxes for both PivotTables and click OK.
  • Right-click the Region slicer and repeat the same steps to connect it to both PivotTables.

What are Report Connections?

Slicers are initially connected only to the PivotTable you used to create them. Report Connections lets you link one slicer to multiple PivotTables that share the same data source. Once connected, clicking a slicer button filters all linked PivotTables and their charts simultaneously.

Now add a Timeline control for date-based filtering.

  • Click on either PivotTable.
  • Click the Insert tab.
  • Click Timeline.
  • Select OrderDate and click OK.
  • Right-click the Timeline and select Report Connections.
  • Check both PivotTables and click OK.

Finally, arrange and label the dashboard.

  • Drag and resize the charts, slicers, and Timeline so they form a clean, non-overlapping layout on the sheet.
  • Right-click the sheet tab at the bottom of the screen.
  • Select Rename.
  • Type Dashboard and press Enter.

Click different buttons on the Category slicer. Both charts should update immediately, showing only the data for the selected category.

Slicer not filtering one of the charts?

If one chart does not respond when you click a slicer button, the slicer is not connected to that PivotTable. Right-click the slicer, select Report Connections, and confirm both PivotTables are checked.

Both PivotTables must use the same data source (the Data Model) for Report Connections to work. If one was created from a cell range instead of the Data Model, delete it and recreate it using Insert > PivotTable > From Data Model.

Help me connect my slicer to both PivotTables on the dashboard.

Secret mission

Add Trend Analysis and Anomaly Detection

Your dashboard shows the current state of sales, but executives want to spot trends and problems at a glance. In this secret mission, you will add sparklines for in-cell trend visualization and conditional formatting rules that automatically flag underperforming segments with traffic light icons.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Everything you created lives on your machine as files you can keep or delete at any time.

Resources you created:

  • Excel workbook file (contains your dashboard, Power Query connections, Power Pivot data model, Trends sheet, and Data Notes sheet).
  • Two CSV files in C:\DataAnalystProject\ (sales_transactions.csv and products.csv).

Keep everything running

No action needed. Choose this if you want to keep your dashboard as a portfolio piece or continue experimenting with it.

  • Save your workbook as .xlsx to preserve all Power Query connections, Power Pivot model, DAX measures, and dashboard elements.
  • Consider saving a backup copy to a different folder or cloud storage.
  • The Power Pivot add-in will remain enabled for future projects.

Pause - I'll come back to this later

Since everything is local files with zero cost, there is nothing to pause. Your workbook and CSV files sit on disk without consuming any resources while idle.

  • Save and close the Excel workbook.
  • Your Power Query connections and Power Pivot model are saved inside the workbook file. They will reload when you reopen it.

Delete - I don't want to use this again

Remove all project files from your machine. This deletes the workbook and both CSV source files.

  • Close Excel if the workbook is still open.
  • Delete the entire project folder by opening Command Prompt and running the command below.
rmdir /s /q C:\DataAnalystProject
  • If you saved the workbook outside the project folder, delete that copy manually through File Explorer.
  • The Power Pivot add-in remains enabled in Excel. It uses no resources when not in use, so there is no need to disable it.

Nice Work!

Nice Work!

You just built an interactive sales analytics dashboard in Excel, starting from raw messy CSV files and ending with a polished, filterable executive report. That is the full data analyst workflow in one project.

Here's what you learned:

  • Imported and cleaned raw CSV data using Power Query, including data profiling to identify quality issues like duplicates, missing values, and type mismatches.
  • Built a relational data model in Power Pivot with a one-to-many relationship between tables and three DAX measures: Total Revenue, Average Order Value, and Revenue YTD using the TOTALYTD time intelligence function.
  • Created an interactive dashboard with PivotCharts, slicers, and a Timeline control that filter dynamically across multiple PivotTables.
  • Secret Mission: Added sparklines for trend analysis and conditional formatting for anomaly detection, plus documented the data quality process for stakeholders.

Ready to quiz yourself?