How to Create a Performance Dashboard in Excel

Dragging data into a spreadsheet to build the same reports week after week is a universal chore for many teams. That's why building a dynamic performance dashboard directly in Excel is such a powerful skill. This guide will walk you through creating a performance dashboard from start to finish, including structuring your data, building visualizations, and making it fully interactive.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Before You Build: Preparing Your Data for Success

The quality of your dashboard depends entirely on the quality of your source data. A clean, organized dataset is the foundation for everything that follows. Before you even think about charts and graphs, spend a few minutes getting your data in order.

Treat Your Data Like a Database

The most important rule is to structure your data in a simple, tabular format. This means:

• One Header Row: Your data should have a single header row at the very top with clear, unique names describing what's in each column (e.g., Date, Campaign, Clicks, Spend, Revenue).
• No Blank Rows or Columns: Ensure there are no empty rows or columns cutting through the middle of your dataset.
• One Record Per Row: Each row should represent a single, unique record. For example, a single day's performance for a specific marketing campaign.
• No Merged Cells: Merged cells are the enemy of data analysis in Excel. They break formulas, filters, and PivotTables. Unmerge any you find.

Once your data is clean, you need to format it as an official Excel Table. This is a non-negotiable step that makes your dashboard dynamic.

How to Format Your Data as an Excel Table

1. Click anywhere inside your clean data range.
2. Navigate to the Insert tab on the Excel ribbon.
3. Click the Table button.
4. Excel will automatically guess your data range. Confirm that it's correct and that the "My table has headers" box is checked.
5. Click OK.

Your data will now be formatted with colored bands. More importantly, this structure gives it superpowers. When you add new rows of data, the Table expands automatically, ensuring your charts and formulas update without any manual adjustments.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Calculating Your Key Performance Indicators (KPIs)

A good dashboard gives you a high-level snapshot of performance before you dig into the details. This is what your Key Performance Indicators (KPIs) are for. It's best practice to create a separate sheet for your dashboard's visuals.

Let's assume your data table (which Excel probably named "Table1") has columns like Date, Campaign, Impressions, Clicks, Spend, and Revenue. Here's how you'd calculate a few common marketing KPIs on your new dashboard sheet.

1. Total Revenue

In a cell, type: =SUM(Table1[Revenue])

2. Total Ad Spend

In another cell, type: =SUM(Table1[Spend])

3. Return On Ad Spend (ROAS)

This is your revenue for every dollar spent. The formula is Total Revenue / Total Spend. =[Cell with Total Revenue]/[Cell with Total Ad Spend]

4. Cost Per Click (CPC)

This shows how much you pay, on average, for each click. The formula is Total Spend / Total Clicks. =SUM(Table1[Spend])/SUM(Table1[Clicks])

Arrange these KPIs in a clean row or column at the top of your dashboard sheet. They'll serve as your at-a-glance summary.

From Numbers to Narratives: Building Your Visuals

Now for the fun part: turning your raw numbers into charts that tell a story. The best way to build dashboard charts in Excel is by using PivotTables and PivotCharts. This keeps them connected to your source data and makes them easy to filter.

To keep your workbook organized, it's a good idea to create a third sheet and name it something like "PivotTables." All your summary data will live here, keeping your main dashboard clean.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create Your First PivotTable

1. Go back to your data sheet and click anywhere inside your Excel Table.
2. Go to the Insert tab and click PivotTable.
3. In the popup window, under "Choose where to place the PivotTable," select "Existing Worksheet" and then click the cell A1 on your "PivotTables" sheet. Click OK.

You'll now see the PivotTable Fields pane. Let's create a summary of Revenue by Campaign:

• Drag the Campaign field into the Rows area.
• Drag the Revenue field into the Values area.

Instantly, you have a summarized table showing total revenue for each campaign.

Step 2: Create a PivotChart from Your PivotTable

1. Click anywhere inside the PivotTable you just made.
2. On the ribbon, go to the PivotTable Analyze tab.
3. Click PivotChart.
4. Select a chart type that makes sense. A Clustered Column or Bar chart is perfect for comparing campaign performance. Click OK.

Step 3: Clean Up and Move Your Chart

The default PivotChart is functional but not pretty. Let’s clean it up before moving it to the dashboard:

• Hide Field Buttons: Right-click on one of the gray field buttons (like "Sum of Revenue") on the chart itself and select "Hide All Field Buttons on Chart."
• Add a Clear Title: Click the chart title and change it to something descriptive, like "Total Revenue by Campaign."
• Remove clutter: You can delete the legend if it's redundant and remove the gridlines for a cleaner look.

Once it's looking sharp, right-click the chart border and select Move Chart. In the dialog box, choose "Object in:" and select your "Dashboard" sheet from the dropdown. Then, place your chart below your KPIs.

You can repeat these steps to create other useful visuals. For example:

• Spend vs. Revenue Over Time: Create a new PivotTable with Date in the Rows area, and SUM of Spend and SUM of Revenue in the Values area. Then, create a Line Chart to visualize the trend.
• Clicks by Campaign: Create another PivotTable with Campaign in the Rows area and SUM of Clicks in the Values area. A Donut Chart can be a nice way to show the proportion of clicks each campaign drove.

Making It Interactive: Connecting Everything with Slicers

This is where your static report becomes a dynamic dashboard. Slicers are user-friendly buttons that allow you (or your teammates) to filter the dashboard data without ever having to touch a PivotTable.

How to Add a Slicer

1. Go to your Dashboard sheet and click on any one of your charts.
2. From the ribbon, select the PivotChart Analyze tab.
3. Click Insert Slicer.
4. A dialog box will appear with all your data fields. Let's create a filter for the date. Check the box next to Date.
5. Click OK.

You'll now see a slicer for your dates on the sheet. But if you click a date, you'll notice it only filters the one chart you had selected. The next step is the key to a truly interactive dashboard.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Connecting a Slicer to All Your Charts

1. Right-click on the header of the slicer you just created.
2. Select Report Connections... from the menu.
3. You'll see a list of all the PivotTables in your workbook. Check the box for every single PivotTable that powers a chart on your dashboard.
4. Click OK.

Now, when you use the slicer to select a specific date range, all of your charts and even your KPI metrics (if they're based on the PivotTables) will update at the same time. You’ve just connected your entire dashboard to a single control pane.

Putting It All Together: Dashboard Design Best Practices

The final step is to arrange and polish everything, so it’s easy to read and understand at a glance.

• Use a Grid Layout: Arrange your charts and slicers in a logical order. Put your high-level KPIs at the top, followed by your main charts.
• Consistent Colors: Use your company’s brand colors or a simple, consistent color palette across all your charts to make them feel connected.
• Hide the Clutter: Go to the View tab and uncheck "Gridlines" and "Headings" to give your dashboard a clean, app-like feel. You can also hide the PivotTables sheet so users only see the final dashboard.
• Lock It Down: Once everything is perfect, protect your Dashboard sheet (but leave the slicers unlocked) to prevent anyone from accidentally moving charts or changing formulas.

Final Thoughts

By transforming raw tabular data into a series of interconnected visuals, you've created a reusable and dynamic tool. Building an Excel dashboard takes some setup, but it saves countless hours down the line by automating your reporting and allowing anyone to explore performance with a few simple clicks.

While creating dashboards in Excel is an invaluable skill, the process is still manual when it comes to consolidating data from different places. If you find yourself exporting CSV files from Google Analytics, Salesforce, Shopify, and your ad platforms every week, we built Graphed to solve this very problem. We provide one-click integrations with your marketing and sales accounts so you can skip the spreadsheet wrangling entirely and use simple, natural language - like "show me revenue by campaign from Facebook Ads for this month" - to create live dashboards that update automatically.