Sales Performance Dashboard in Excel

Transform 149K coffee shop transactions into an interactive Excel dashboard.

Introduction

30 Second Summary

Every coffee shop, retail store, and online business generates thousands of transactions per day. The analysts who get hired are the ones who can turn those raw numbers into clear stories that drive decisions.

In this project, you will transform 149,000+ real coffee shop sales transactions into a professional interactive dashboard in Microsoft Excel. You will clean raw data, build analytical formulas, create PivotTables, and design a polished KPI dashboard that filters dynamically.

What You'll Build

A complete Excel workbook analyzing 149,000+ coffee shop sales transactions across multiple store locations, taking raw data through the full analyst workflow to an interactive KPI dashboard.

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

  • A Power Query transformation pipeline that cleans and reshapes raw data into analysis-ready format.
  • Analytical formulas (SUMIFS, XLOOKUP, MAXIFS) calculating key business metrics across store locations.
  • Multiple PivotTables and an interactive dashboard with KPI cards, charts, slicers, and a timeline that filter simultaneously.
  • Secret Mission: Add a peak-hours heatmap and What-If scenario analysis to prove advanced Excel proficiency.

Are there any prerequisites?

You need Microsoft Excel 2016 or later (or Microsoft 365) installed on your computer. Power Query is built into all of these versions. No prior experience with PivotTables or dashboards is required.

Set Up and Import Your Data

Every data analysis project begins with sourcing and loading the right dataset. You'll be working with over 149,000 real coffee shop transactions covering multiple store locations, product categories, and time periods.

In this step, you'll download the dataset and bring it into Excel using Power Query. Power Query is Excel's built-in data import engine that remembers every transformation you apply. This makes your import process a repeatable recipe you can re-run whenever the source data updates.

In this step, get ready to:
  • Download the Coffee Shop Sales dataset from Maven Analytics.
  • Import the CSV file into Excel using Power Query.
  • Verify column headers loaded correctly and open the Power Query Editor.
Download the Coffee Shop Sales dataset

The dataset you'll use comes from Maven Analytics, a free data education platform that hosts practice datasets for analysts. The Coffee Shop Sales dataset contains 149,116 transaction records with 11 fields covering dates, times, store locations, product categories, and prices.

  • Open your web browser and navigate to the Maven Analytics Data Playground:
  • https://mavenanalytics.io/data-playground
  • Scroll down the page or use the browser's search (Cmd+F on macOS or Ctrl+F on Windows) to find Coffee Shop Sales.
  • Click on the Coffee Shop Sales dataset card. You'll see it described as "Transaction records from a fictitious coffee shop in New York City" with 149,116 records and 11 fields.
  • Download the dataset CSV file.
  • Save the file to your working folder on your machine.

What is this dataset?

The Coffee Shop Sales dataset simulates transaction data from a coffee chain with multiple store locations in New York City. Each row is one transaction line item, recording when and where the sale happened, what product was sold, its size, price, and quantity.

With 149,116 rows and 11 columns, the dataset is large enough to feel like a real business scenario. It includes time, location, and product dimensions that let you build rich analytical views.

Can't find the dataset?

The Data Playground page lists datasets as cards. Coffee Shop Sales appears under the Food & Beverage category. Look for the card showing "Multiple tables 149,116 records 11 fields." If the page layout has changed, try the browser search shortcut to jump directly to the name.

help me find and download the Coffee Shop Sales dataset from Maven Analytics

Import the CSV into Excel using Power Query

Now that the dataset is saved locally, you'll bring it into Excel through Power Query. This approach gives you a live connection to the file and records your import steps automatically.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Excel and press Enter to open Microsoft Excel.
  • Create a new blank workbook.
  • Click the Data tab in the ribbon at the top of Excel.
  • Click Get Data in the Get & Transform Data group on the left side of the ribbon.
  • Select From File, then click From Text/CSV.
  • Navigate to your working folder, select the Coffee Shop Sales CSV file, and click Import.

A preview window appears showing the first rows of your data. You should see columns for transaction dates, times, store locations, product categories, product types, sizes, and unit prices.

Why Power Query instead of just opening the CSV?

Opening a CSV directly in Excel works, but gives you a one-time snapshot with no memory of how you loaded it. Power Query records every step you take as a repeatable pipeline.

If the source data ever updates, you can refresh your query and all your transformations re-apply automatically. This is the modern approach that interviewers expect to see from analysts.

Don't see Get Data on the ribbon?

Power Query is built into Excel 2016, Excel 2019, Excel 2021, and Microsoft 365. If you're on an older version, you may need to download the Power Query add-in separately from Microsoft. Make sure you're looking at the Data tab, not the Home or Insert tab.

help me find Power Query in my version of Excel

Verify your data and open the Power Query Editor

Before transforming anything, you'll confirm the data loaded correctly. The preview window shows you what Power Query detected from the CSV file.

  • In the preview window, check that column headers loaded correctly. You should see columns like transaction_date, transaction_time, store_location, product_category, product_type, unit_price, and similar field names in the header row.
  • Scan the first few rows for obvious data quality issues. Look for blank cells, garbled characters, or inconsistent formatting.
  • Click Transform Data to open the Power Query Editor.

The Power Query Editor opens in a new window showing your full dataset. You should see all 149,116 rows loaded with 11 columns visible.

What is the Power Query Editor?

The Power Query Editor is a dedicated workspace for cleaning and transforming data before it reaches your Excel sheets. Every action you take here is recorded as a named step in the Applied Steps pane on the right side.

You can add, remove, or reorder steps at any time. Think of it as a recipe card for your data preparation. In the next step, you'll use it to set data types and create calculated columns.

Seeing garbled data or wrong column headers?

If your column headers appear as data rows (the first row shows field names like transaction_date in the data rather than the header), click Use First Row as Headers on the Home tab in the Power Query Editor. If you see garbled characters, check that the file encoding is set to UTF-8 in the import settings.

help me fix my Power Query column headers

Your data is loaded and ready for transformation. Next up, you'll clean this raw dataset and build calculated columns that turn basic transaction records into analysis-ready fields.

Clean and Transform Data with Power Query

Raw data is rarely ready for analysis straight out of the box. Your CSV has dates stored as text, prices that Excel might misinterpret, and no revenue column at all.

In this step, you will shape the raw transaction data into an analysis-ready table using Power Query's transformation tools. You will set proper data types, add calculated columns for revenue and time-based analysis, and load the result into Excel as a named table.

In this step, get ready to:
  • Set correct data types and rename columns for clarity.
  • Create calculated columns for Revenue, Hour, Day of Week, and Month Name.
  • Load the cleaned data into an Excel table named SalesData.
Set data types and rename columns

Power Query auto-detects data types when it imports your CSV, but it often guesses wrong. Setting explicit types ensures dates sort correctly, numbers calculate properly, and your formulas work without errors downstream.

  • In the Power Query Editor, select the transaction_date column by clicking its header.
  • On the Home tab, click the Data Type dropdown and select Date.
  • If a "Change Column Type" prompt appears, select Replace Current.
  • Select the transaction_time column and set its type to Time.
  • Select the unit_price column and set its type to Decimal Number.
  • Select the transaction_qty column and set its type to Whole Number.

Why set data types explicitly?

Power Query defaults most columns to "Any" or text. If transaction_date stays as text, you cannot extract the month or day of week from it later.

Setting unit_price to Decimal Number means multiplication will produce numeric revenue values instead of errors.

  • Rename your columns to business-friendly names. Double-click a column header to edit it.
  • Rename transaction_date to Transaction Date.
  • Rename transaction_time to Transaction Time.
  • Rename unit_price to Unit Price.
  • Rename transaction_qty to Quantity.
  • Rename store_location to Store Location.
  • Rename product_category to Product Category.
  • Rename product_type to Product Type.
  • Rename product_detail to Product Detail.
  • Remove any blank rows by clicking the Home tab, selecting Remove Rows, then choosing Remove Blank Rows.

Check the Applied Steps pane on the right side of the Power Query Editor. You should see new steps for each type change and rename you performed.

Column type won't change?

Make sure you selected Replace Current when prompted. Selecting "Add New Step" can conflict with existing type detection steps.

If a column shows an error icon after changing its type, the data may contain unexpected text values. Right-click the column header and select Remove Errors to clear those rows.

help me fix a data type error in Power Query

Create calculated columns for revenue and time analysis

The raw dataset has unit price and quantity, but no revenue total. You also need hour-of-day and day-of-week columns to analyze when customers buy the most.

  • Click the Add Column tab in the Power Query Editor ribbon.
  • Click Custom Column. The Custom Column dialog appears.
  • In the New column name box, type Revenue.
  • In the Custom column formula box, enter the following formula:
[Unit Price] * [Quantity]
  • Confirm the green checkmark appears at the bottom of the dialog and click OK.

What does this formula do?

Power Query M formulas reference columns using square brackets. [Unit Price] * [Quantity] multiplies the price of each item by how many were purchased, giving you the total revenue for that transaction.

  • Add a second custom column by clicking Add Column tab, then Custom Column again.
  • Name it Hour and enter this formula:
Time.Hour([Transaction Time])
  • Click OK.
  • Add a third custom column. Name it Day of Week and enter this formula:
Date.DayOfWeekName([Transaction Date])
  • Click OK.
  • Add a fourth custom column. Name it Month Name and enter this formula:
Date.MonthName([Transaction Date])
  • Click OK.

What do these time formulas do?

Time.Hour extracts the hour as a number (0 through 23) from a time value. This lets you analyze peak trading hours later.

Date.DayOfWeekName returns the full day name ("Monday", "Tuesday", etc.) from a date value. Date.MonthName returns the full month name ("January", "February", etc.) for trend analysis.

Scroll right in the Power Query Editor preview. You should see four new columns at the end: Revenue, Hour, Day of Week, and Month Name.

Seeing "Expression.Error" in a column?

This usually means the column name in your formula does not match the renamed column header exactly. Check capitalization and spacing. For example, [Transaction Time] must match the header Transaction Time precisely.

If Time.Hour returns errors, confirm that the Transaction Time column's data type was set to Time in the previous substep.

help me fix a Power Query custom column formula error

Load the cleaned data into an Excel table

Your transformation pipeline is complete. Every step you applied is recorded in the Applied Steps pane, making this a repeatable recipe you can re-run whenever new data arrives.

  • Look at the Applied Steps pane on the right. Confirm you see steps for type changes, renames, blank row removal, and four "Added Custom" entries.
  • Click the Home tab in the Power Query Editor ribbon.
  • Click Close & Load. Power Query processes all 149,000+ rows and loads them into an Excel worksheet as a formatted table.

What is the Applied Steps pane?

Every action you take in Power Query is recorded as a named step. This creates a repeatable data-cleaning pipeline.

If the source CSV is updated with new transactions later, you can simply right-click the query and refresh. All your transformations re-apply automatically.

  • Once the data loads into Excel, click anywhere inside the table.
  • Go to the Table Design tab that appears when the table is selected.
  • In the Table Name field on the left side of the ribbon, replace the default name with SalesData and press Enter.

Your table should have your original columns (renamed) plus the four calculated columns: Revenue, Hour, Day of Week, and Month Name. Check the bottom-left of the Excel window for the row count, which should be approximately 149,116.

Table loaded but row count seems wrong?

A slightly lower count (such as 149,100) is normal if blank rows were removed. If the count is drastically lower, reopen Power Query by clicking Data > Queries & Connections. Then double-click your query and check the Applied Steps for any accidental filter steps.

help me troubleshoot my Power Query row count

Your data is now clean, enriched, and loaded into a named table. Next up, you will build summary formulas and KPIs on a separate Metrics sheet to answer key business questions.

Build Summary Metrics with Analytical Formulas

Your cleaned data is loaded and structured in a table with 149,000+ rows. The next challenge is turning all that raw transaction data into meaningful numbers that answer business questions at a glance.

In this step, you will create a dedicated Metrics worksheet with formulas that calculate key business KPIs, compare store performance side by side, and dynamically identify each store's top-selling product.

In this step, get ready to:
  • Build KPI formulas for total revenue, transaction count, and averages.
  • Create a store comparison table using SUMIFS and COUNTIFS.
  • Add a dynamic product lookup with XLOOKUP and MAXIFS.
Create the Metrics worksheet and build KPI formulas

Before building charts or dashboards, analysts create a summary layer. This gives you a quick-reference sheet with the headline numbers a stakeholder would ask for first.

  • Right-click any sheet tab at the bottom of your workbook.
  • Select Insert, then choose Worksheet.
  • Double-click the new tab and rename it to Metrics.
  • Press Enter to confirm the name.

Now you will add labels and formulas for four key metrics. Each formula references the SalesData table you created in the previous step.

  • Click cell A1 and type KPI Summary as a section heading.
  • Click cell A2 and type Total Revenue.
  • Click cell A3 and type Total Transactions.
  • Click cell A4 and type Average Order Value.
  • Click cell A5 and type Avg Items Per Transaction.
  • Enter the following formulas in column B by clicking each cell, typing the formula, and pressing Enter:
B2: =SUM(SalesData[Revenue])
B3: =COUNTA(SalesData[transaction_id])
B4: =B2/B3
B5: =AVERAGE(SalesData[transaction_qty])

What do these formulas do?

Each formula pulls data directly from your named table using structured references (the TableName[ColumnName] syntax).

  • SUM totals every value in the Revenue column you created in Power Query.
  • COUNTA counts every non-empty cell in the transaction_id column, giving you the total number of transactions.
  • Dividing total revenue by total transactions gives you the average dollar value per order.
  • AVERAGE calculates the mean of transaction_qty across all 149,000+ rows.
  • Save your workbook by pressing Ctrl+S.
  • Confirm that cell B2 shows a large dollar value (the total revenue across all stores and months) and B3 shows approximately 149,116.

Seeing a #REF! or #NAME? error?

  • Make sure your table name is exactly SalesData. Check by clicking on your data worksheet tab and looking at the Table Design tab in the ribbon for the table name field.
  • Verify your column names match. Click inside the table and confirm the column header says Revenue and transaction_id exactly.

Help me fix my SalesData table reference errors

Build a store comparison table with SUMIFS and COUNTIFS

KPI totals tell you how the business is doing overall. A hiring manager will want to see that you can slice data by dimension. A store comparison answers the question: which location drives the most revenue?

  • In cell A7 of your Metrics sheet, type Store Comparison as a section header.
  • In cell A8 type Store Location, in B8 type Revenue, and in C8 type Transactions.

Your dataset contains three store locations. You need to type each store name exactly as it appears in your data.

  • Switch to your SalesData worksheet tab and click the dropdown arrow on the store_location column header to see the three unique store names in the filter menu.
  • Return to your Metrics sheet and enter each store name in cells A9, A10, and A11 (one per row). The names must be character-for-character matches.

Finding unique values quickly

The filter dropdown on any column header in an Excel table shows every unique value in that column. Copy the three store names from there to guarantee exact spelling and avoid SUMIFS returning 0.

  • Enter the SUMIFS and COUNTIFS formulas in row 9 by clicking cell B9 and typing the first formula, then clicking C9 and typing the second:
B9: =SUMIFS(SalesData[Revenue], SalesData[store_location], A9)
C9: =COUNTIFS(SalesData[store_location], A9)

What do SUMIFS and COUNTIFS do?

SUMIFS adds up only the Revenue values where the store_location matches the value in your criteria cell (A9). It filters then sums in one formula.

COUNTIFS works the same way but counts matching rows instead of summing a value column. Together, they give you revenue and traffic per location.

  • Select cells B9:C9, then drag the fill handle (small square at the bottom-right corner of the selection) down to row 11 to apply the formulas to all three stores.
  • Save your workbook.
  • Confirm that each store now shows a revenue total and transaction count. The three Revenue values should roughly sum to match your Total Revenue in B2.

All stores showing 0?

  • SUMIFS returns 0 when no rows match the criteria. Double-check that the store name in A9 exactly matches the spelling, capitalization, and spacing in your store_location column.
  • Check for trailing spaces by clicking a store name cell and looking at the formula bar. If you see extra spaces after the name, delete them.

Help me debug why SUMIFS is returning 0 for my store comparison

Add a dynamic product lookup with conditional formatting

The store comparison tells you which location earns the most. The next question a stakeholder asks is: what product drives each store's revenue? You will combine MAXIFS with a lookup function to answer this dynamically.

  • In cell D8 type Max Revenue and in cell E8 type Top Product.
  • Enter the MAXIFS formula in cell D9 to find the highest single-transaction revenue for the first store:
D9: =MAXIFS(SalesData[Revenue], SalesData[store_location], A9)

What does MAXIFS do?

MAXIFS returns the largest value in the Revenue column, but only among rows where store_location matches the store name in A9. This identifies the single highest-revenue transaction for that location.

Now you need to find which product generated that maximum revenue. This requires looking up the product_type from the row that matches both the store and the max revenue value.

XLOOKUP (Microsoft 365 / Excel 2021+)

  • Enter this formula in cell E9 and press Enter:
E9: =XLOOKUP(1, (SalesData[store_location]=A9)*(SalesData[Revenue]=D9), SalesData[product_type])

How does this XLOOKUP work?

This formula builds a TRUE/FALSE array by multiplying two conditions together. When both store_location matches AND Revenue equals the max value, the product of the two conditions equals 1.

XLOOKUP finds that 1 and returns the corresponding product_type. In Microsoft 365, this works as a dynamic array formula. Just press Enter.

INDEX/MATCH (Excel 2016-2019)

  • Enter this formula in cell E9, then press Ctrl+Shift+Enter (not just Enter) to confirm it as an array formula:
E9: =INDEX(SalesData[product_type], MATCH(1, (SalesData[store_location]=A9)*(SalesData[Revenue]=D9), 0))

How does INDEX/MATCH work here?

MATCH searches for 1 inside a multiplied array of two conditions (store match times revenue match). When both are true, the result equals 1. MATCH returns the row position, and INDEX retrieves the product_type from that position.

In Excel 2016-2019, you must press Ctrl+Shift+Enter to tell Excel this is an array formula. You will see curly braces {...} appear around the formula in the formula bar.

  • Select cells D9:E9 and drag the fill handle down to row 11 to apply the formulas to all three stores.

Finally, add conditional formatting to make your comparison table visually scannable.

  • Select cells B9:B11 (the Revenue column in your comparison table).
  • Click the Home tab in the ribbon.
  • Click Conditional Formatting, then select Top/Bottom Rules, then select Top 10 Items.
  • Change the number to 1 and choose a green fill. Click OK.
  • With B9:B11 still selected, click Conditional Formatting, then select Top/Bottom Rules, then select Bottom 10 Items.
  • Change the number to 1 and choose a red fill. Click OK.

Why use conditional formatting?

Conditional formatting highlights the highest and lowest values automatically. If the underlying data changes (new transactions added), the highlights update without manual intervention. This makes your workbook dynamic rather than a static snapshot.

  • Save your workbook.
  • Verify that one store's revenue cell is highlighted green (highest) and another is highlighted red (lowest). Each store's Top Product column should display a product type name.

Seeing #N/A or #VALUE! in the Top Product column?

  • If you see #VALUE!, make sure the MAXIFS value in column D populated correctly before the lookup formula references it.
  • If you see #N/A and you are using INDEX/MATCH in Excel 2016-2019, confirm you pressed Ctrl+Shift+Enter. The formula bar should show curly braces around the formula.
  • If the fill-down produced errors in rows 10-11, delete those cells and re-enter the formula in E10 and E11 manually. Array formulas sometimes need individual entry in older Excel versions.

Help me fix my XLOOKUP or INDEX/MATCH lookup errors

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook, then move on to the next step.

ⓧ I'd like to double check the full formulas

Here is the complete set of formulas on your Metrics worksheet:

=== KPI Section (cells B2:B5) ===
B2: =SUM(SalesData[Revenue])
B3: =COUNTA(SalesData[transaction_id])
B4: =B2/B3
B5: =AVERAGE(SalesData[transaction_qty])

=== Store Comparison (cells B9:C11, store names in A9:A11) ===
B9: =SUMIFS(SalesData[Revenue], SalesData[store_location], A9)
C9: =COUNTIFS(SalesData[store_location], A9)
(Copy B9:C9 down to rows 10 and 11)

=== Dynamic Product Lookup (cells D9:E11) ===
D9: =MAXIFS(SalesData[Revenue], SalesData[store_location], A9)

E9 (Microsoft 365 / Excel 2021+):
=XLOOKUP(1, (SalesData[store_location]=A9)*(SalesData[Revenue]=D9), SalesData[product_type])

E9 (Excel 2016-2019, enter with Ctrl+Shift+Enter):
=INDEX(SalesData[product_type], MATCH(1, (SalesData[store_location]=A9)*(SalesData[Revenue]=D9), 0))

(Copy D9:E9 down to rows 10 and 11)

=== Conditional Formatting ===
Applied to B9:B11: Top 1 item = green fill, Bottom 1 item = red fill

Your Metrics sheet now answers three critical business questions at a glance: how is the business performing overall, which store leads in revenue, and what product drives each store. Next up, you will slice this data further with PivotTables to reveal trends over time and across product categories.

Analyze Data with PivotTables

Your Metrics sheet gives you a solid overview of the business, but it answers questions you already knew to ask. PivotTables let you explore data interactively, slicing 149,000+ rows by time, product, and location in seconds.

In this step, you will build four PivotTables that answer core business questions: when does revenue peak, which products drive the most sales, what hours are busiest, and how do product categories compare. These PivotTables also become the engine behind the interactive dashboard you will build next.

In this step, get ready to:
  • Build PivotTables showing monthly revenue trends and top-selling products.
  • Build PivotTables showing peak trading hours and category performance.
  • Format all PivotTables with professional number formats, styles, and data bars.
Build revenue trend and top products PivotTables

The first PivotTable answers the question: how does revenue change month over month across each store location? The second answers: which individual products generate the most revenue overall?

  • Switch to the worksheet tab containing your SalesData table.
  • Click any cell inside the SalesData table to select it.
  • Click the Insert tab in the ribbon.
  • Click PivotTable on the left side of the ribbon.
  • In the Create PivotTable dialog, select New Worksheet and click OK.
  • Right-click the new worksheet tab at the bottom and rename it to Analysis.

You should now see an empty PivotTable placeholder on the left and the PivotTable Fields pane on the right.

  • In the PivotTable Fields pane, drag Month Name to the Rows area.
  • Drag store_location (or your renamed store location column) to the Columns area.
  • Drag Revenue to the Values area.
  • Confirm the Values field reads Sum of Revenue. If it shows Count instead, click the dropdown arrow next to the field in the Values area, select Value Field Settings, and choose Sum.

What does this PivotTable show?

Each row represents a month and each column represents a store location. The values show total revenue for that month at that store. You can immediately spot seasonal patterns and compare store performance side by side.

Now create the second PivotTable showing top products.

  • Click a cell below the first PivotTable, leaving a few empty rows of space between them.
  • Switch back to your SalesData worksheet tab and click any cell in the table.
  • Click Insert > PivotTable again.
  • This time, select Existing Worksheet and click into an empty cell on the Analysis sheet below your first PivotTable.
  • Click OK.
  • Drag product_type (or your renamed product column) to Rows.
  • Drag Revenue to Values.
  • Sort the PivotTable by revenue descending: click the dropdown arrow on Row Labels, select More Sort Options, choose Descending (Z to A) by Sum of Revenue, and click OK.
  • Filter to the top 15 products: click the Row Labels dropdown, select Value Filters, then Top 10. Change the number from 10 to 15 and click OK.

Why filter to 15 products?

Showing every product makes the PivotTable noisy. Filtering to the top 15 highlights the revenue drivers that matter most for business decisions. A hiring manager scanning your workbook can instantly see which products to focus on.

PivotTable Fields pane disappeared?

Click anywhere inside the PivotTable to bring it back. The Fields pane only appears when a PivotTable cell is selected.

If you see a different PivotTable's fields, click directly inside the PivotTable you want to edit.

Why isn't the PivotTable Fields pane showing up when I click my PivotTable?

Build peak hours and category performance PivotTables

The third PivotTable reveals which hours of the day generate the most revenue. The fourth breaks down performance by product category and adds a custom metric.

  • Create a third PivotTable on the Analysis sheet using the same process: Insert > PivotTable > Existing Worksheet, pointing to an empty area below the second PivotTable.
  • Drag Hour to Rows.
  • Drag Revenue to Values.
  • Confirm the Values field shows Sum of Revenue.

You should see each hour of the day listed as a row with total revenue beside it. The busiest hours will have the highest values.

  • Create a fourth PivotTable on the Analysis sheet in an empty area below the third.
  • Drag product_category to Rows.
  • Drag Revenue to Values.
  • Now add the calculated field. Click anywhere inside this fourth PivotTable.
  • Click the PivotTable Analyze tab in the ribbon.
  • Click Fields, Items & Sets, then select Calculated Field.
  • In the Name box, type Avg Transaction Value.
  • In the Formula box, clear the existing text and type = Revenue / transaction_id.
  • Click OK.

How does the calculated field formula work?

PivotTable calculated fields operate on the sum of the underlying data. The formula = Revenue / transaction_id divides the total revenue for each product category by the sum of transaction IDs in that category.

This gives you a ratio that reflects the average revenue generated per transaction within each category. You reference field names directly in the formula. Cell references and worksheet functions are not allowed in calculated fields.

Don't see Fields, Items & Sets?

Make sure your cursor is clicked inside the fourth PivotTable. The PivotTable Analyze tab only appears when a PivotTable is selected.

If you see Analyze instead of PivotTable Analyze, that is the same tab. The label varies slightly depending on your Excel version.

Help me find the Calculated Field option in my Excel ribbon.

Format PivotTables for professional presentation

Raw numbers without formatting are hard to scan. Consistent currency formats, a clean table style, and data bars make your PivotTables instantly readable.

  • Click inside your first PivotTable (Revenue by Month).
  • Right-click any revenue value cell and select Number Format.
  • In the Format Cells dialog, select Currency, set Decimal places to 0, and click OK.
  • Repeat this currency formatting for the revenue values in PivotTables 2, 3, and 4.
  • For the Avg Transaction Value column in PivotTable 4, use Currency with 2 decimal places to preserve precision.

Now apply a consistent table style across all four PivotTables.

  • Click inside the first PivotTable.
  • Click the Design tab in the ribbon (under PivotTable Tools).
  • Click the More button (small arrow) in the PivotTable Styles gallery to see all available styles.
  • Pick a subtle style with banded rows (any light-colored style in the Light or Medium section works well).
  • Apply the same style to PivotTables 2, 3, and 4 by clicking each one and selecting the same style from the gallery.

Finally, add data bars to make revenue values visually scannable.

  • In PivotTable 2 (Top 15 Products), select all the revenue value cells in the column.
  • Click the Home tab.
  • Click Conditional Formatting, hover over Data Bars, and choose a gradient fill style (blue works well).
  • Repeat for the revenue columns in PivotTables 1, 3, and 4.

What are data bars?

Data bars are a type of conditional formatting that draws a horizontal bar inside each cell proportional to its value. A longer bar means a higher number. They let you compare revenue across rows at a glance without reading each number individually.

  • If your Month Name column in PivotTable 1 is not displaying months in calendar order, right-click any month label, select Sort, and choose More Sort Options to sort by the underlying date field. Alternatively, group the transaction date field by month if you used the raw date instead of Month Name.
  • Add a label above each PivotTable describing what it shows (e.g., type "Revenue by Month and Store" in a cell above PivotTable 1).

Data bars not appearing?

Make sure you selected only the numeric revenue cells, not the row labels or headers. Conditional formatting applies to the selected range only.

If data bars appear on some cells but not others, check that the cells contain numbers and not text formatted as numbers.

Why are my data bars not showing up on my PivotTable revenue column?

Your analysis foundation is solid. You now have four PivotTables answering key business questions about trends, products, peak hours, and categories. Next up, you will turn these into an interactive dashboard with KPI cards, charts, and slicers that filter everything at once.

Build the Interactive KPI Dashboard

Your PivotTables have answered the key business questions. You know which months generate the most revenue, which products sell best, when customers visit, and how each store compares.

Now it's time to bring all of those answers into a single interactive view. In this step, you'll build a professional dashboard with KPI cards, charts, slicers, and a timeline that anyone can explore without touching the underlying data.

In this step, get ready to:
  • Build KPI cards displaying Total Revenue, Total Transactions, and Average Order Value.
  • Insert PivotCharts visualizing revenue trends, store performance, product categories, and top products.
  • Add slicers and a timeline that filter the entire dashboard interactively.
Create the Dashboard worksheet and build KPI cards

A dashboard is a dedicated worksheet designed for presentation. It shows only the final visuals and hides the raw analysis behind the scenes.

  • Right-click any worksheet tab at the bottom of your workbook.
  • Select Insert to create a new blank worksheet.
  • Double-click the new tab name and rename it to Dashboard.
  • Select cells B2:D2 and click Merge & Center in the Home tab to create your first KPI card area.
  • Type Total Revenue as the card label.
  • In the merged cell directly below (select B3:D3, merge, and center), enter a formula referencing your Metrics sheet:
=Metrics!B2
  • Set the font size to 28 and apply bold formatting so the number stands out.
  • Repeat this pattern for two more KPI cards to the right: Total Transactions referencing your transactions metric, and Average Order Value referencing your AOV metric.

What are KPI cards?

KPI cards are large, prominent numbers at the top of a dashboard that communicate the most important metrics at a glance. They answer the question "how is the business doing?" before the viewer even looks at the charts below.

  • Now create your first chart. Switch to your Analysis worksheet and click any cell inside the Revenue by Month PivotTable.
  • Click the Insert tab in the ribbon.
  • Click PivotChart.
  • Select Line as the chart type and click OK.
  • Cut the chart (Ctrl+X) and paste it onto your Dashboard worksheet below the KPI cards.
  • Resize the chart to fill roughly half the width of the dashboard area.
  • Return to the Analysis worksheet. Click any cell inside the Revenue by Month PivotTable again (this PivotTable has Store Location in columns, which makes it suitable for a store comparison).
  • Click Insert then PivotChart.
  • Select Clustered Bar as the chart type and click OK.
  • Cut this chart and paste it onto your Dashboard worksheet next to the line chart.

Why PivotCharts instead of regular charts?

A PivotChart stays linked to its PivotTable. When you add a slicer or timeline later, it automatically filters the PivotChart too. A regular chart would need manual data range updates every time you filter.

You should now see your Dashboard worksheet with three KPI cards across the top, a line chart showing monthly revenue trends, and a bar chart comparing store performance.

Charts showing on the Analysis sheet instead?

If you accidentally placed the chart on the wrong sheet, right-click the chart border, select Move Chart, then choose Object in and pick the Dashboard worksheet from the dropdown.

Help me move my PivotChart to a different worksheet.

Add remaining charts, slicers, and timeline

Two more charts will round out the story: category breakdown and top-selling products. After that, you'll wire up interactive filters.

  • Switch to the Analysis worksheet and click any cell inside the Revenue by Product Category PivotTable.
  • Click Insert then PivotChart.
  • Select Clustered Column as the chart type and click OK.
  • Cut and paste the chart onto your Dashboard worksheet below the first row of charts.
  • Click any cell inside the Top 15 Products PivotTable on the Analysis sheet.
  • Click Insert then PivotChart.
  • Select Bar as the chart type and click OK. This will display products sorted by revenue.
  • Cut and paste it onto the Dashboard next to the column chart.

Now add the interactive filtering controls. Slicers are clickable buttons that let anyone filter the entire dashboard without touching PivotTable fields directly.

  • Click any cell in one of your PivotTables on the Analysis sheet.
  • Click the PivotTable Analyze tab in the ribbon.
  • Click Insert Slicer.
  • In the Insert Slicers dialog, check Store Location and Product Category.
  • Click OK. Two slicers will appear stacked on top of each other.
  • Drag both slicers onto the Dashboard worksheet and position them to the right of your charts.

Next, add a Timeline for date-based filtering. A timeline works like a slicer but gives you a visual slider to select date ranges.

  • Click any cell in one of your PivotTables on the Analysis sheet.
  • Click PivotTable Analyze then Insert Timeline.
  • Check the transaction date field and click OK.
  • Drag the timeline onto the Dashboard worksheet and position it above or below the slicers.

Why connect slicers to multiple PivotTables?

By default, a slicer only controls the single PivotTable you used to create it. To make your entire dashboard filter together, you need to connect each slicer to every PivotTable feeding your charts.

  • Click the Store Location slicer to select it. The Slicer tab appears in the ribbon.
  • Click Report Connections on the Slicer tab.
  • In the dialog box, check every PivotTable that feeds your dashboard charts. Click OK.
  • Repeat this for the Product Category slicer. Select it, click Report Connections, and check all the same PivotTables.
  • Click the Timeline to select it. On the Timeline tab, click Report Connections and check all PivotTables.

Test the interactivity by clicking a store name in the Store Location slicer. All four charts should immediately update to show only that store's data.

Only one chart is filtering?

This means the slicer is still connected to only one PivotTable. Select the slicer, click Report Connections again, and confirm that every PivotTable in the list is checked.

Slicers can only connect to PivotTables that share the same data source. Since all your PivotTables were built from the SalesData table, they should all appear in the dialog.

Help me connect my slicers to all my PivotTables.

Apply professional formatting and verify full interactivity

A professional dashboard tells a story at a glance. Formatting removes visual noise and helps the viewer focus on insights, not decorations.

  • Select a consistent color scheme of 2-3 colors. Apply the same palette to all chart series by right-clicking each data series, choosing Format Data Series, and setting the fill color.
  • Add a dashboard title in cell A1. Type something like Coffee Shop Sales Dashboard | Jan - Jun 2023 (adjust dates to match your data range). Set the font size to 18 and bold.
  • Add clear chart titles by clicking each chart title and typing a descriptive label: Monthly Revenue Trend, Revenue by Store, Revenue by Category, Top 10 Products.
  • Remove gridlines from the Dashboard sheet. Click the View tab and uncheck Gridlines.
  • Align all charts to a visual grid by selecting each chart and using the arrow keys to nudge it into position. Make sure chart edges line up horizontally and vertically.
  • Resize slicers to be compact. Right-click each slicer, select Size and Properties, and adjust the height and width so they fit neatly beside the charts.

Keep chart formatting minimal

Remove chart elements that don't add information. Right-click any chart and use the + icon (Chart Elements) to uncheck gridlines, legends you don't need, or field buttons cluttering the chart area.

  • Test the full interactivity. Click a single store in the Store Location slicer and confirm all four charts and the KPI values update.
  • Drag the Timeline slider to select a two-month range. Confirm the charts update to show only that time period.
  • Click the Clear Filter button on both the slicer (the icon with an X in the top-right corner of the slicer) and the timeline to reset all filters.
  • Save your workbook.

Your dashboard should now look clean and professional. Clicking any slicer button or adjusting the timeline filters every chart and KPI card simultaneously.

KPI cards not updating when you filter?

If your KPI cards reference the Metrics sheet formulas directly (using SUM on the full SalesData table), they will not respond to slicer filters. To make them dynamic, create small dedicated PivotTables behind the dashboard that feed the KPI values, then reference those PivotTable cells instead. Connect those PivotTables to your slicers through Report Connections.

How do I make my KPI cards respond to slicer filters?

Your interactive dashboard is complete. You've taken 149,000+ rows of raw transaction data and turned them into a story anyone can explore with a click. Next up, push your Excel skills even further with the Secret Mission.

Secret mission

Peak Hours Heatmap and What-If Analysis

Your dashboard tells a great story already. But senior analyst interviews test two specific skills: conditional-formatting-based heatmaps that reveal operational patterns at a glance, and What-If scenario modelling that answers "what would happen if..." questions for leadership. Add both to your workbook and you will have something most junior candidates cannot produce.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your files for portfolio use, or delete them to free up disk space.

Resources you used:

  • Excel workbook (contains SalesData table, Metrics, Analysis, Dashboard, and Scenario Summary worksheets).
  • Coffee Shop Sales CSV file (downloaded from Maven Analytics).

Keep everything running

No action needed. Choose this if you plan to use this workbook as a portfolio piece or want to extend it further.

  • Save a final copy of your workbook with a clear filename like Sales-Dashboard-Portfolio.xlsx.
  • Take a screenshot of your Dashboard worksheet for LinkedIn or GitHub.
  • Consider connecting this workbook to Power BI or recreating the analysis in Tableau to demonstrate cross-tool fluency.

Pause - I'll come back to this later

Since this is a local Excel project, there are no running processes to pause. Your workbook stays on disk until you reopen it.

  • Save and close the Excel workbook.
  • Keep both the workbook and the original CSV in your working folder so you can pick up where you left off.

Delete - I don't want to use this again

Remove all project files from your machine. This permanently deletes your workbook and the downloaded dataset.

  • Close Excel if the workbook is still open.
  • Open File Explorer and navigate to the folder where you saved your workbook and CSV file.
  • Select both your Excel workbook file and the Coffee Shop Sales CSV file.
  • Press Delete or right-click and select Delete.
  • Empty your Recycle Bin to permanently remove the files.

Nice Work!

Nice Work!

You just transformed 149,000+ rows of raw coffee shop transaction data into a professional interactive dashboard that tells a clear business story. That is the full analyst workflow in one workbook.

Here is what you learned:

  • Built a repeatable Power Query pipeline that cleans, transforms, and loads raw CSV data into a structured Excel table with calculated columns.
  • Created PivotTables revealing monthly revenue trends, top-selling products, peak trading hours, and store-level performance comparisons.
  • Designed an interactive KPI dashboard with cards, charts, slicers, and a timeline that filters every visual simultaneously.
  • Secret Mission: Added a peak-hours heatmap with conditional formatting and a What-If scenario analysis using Scenario Manager and Goal Seek to model revenue impact.

Ready to quiz yourself?