How to Make a KPI Report in Excel

Building a powerful KPI report in Excel can feel like a daunting task, but it doesn't have to be. When done right, an Excel report transforms raw numbers into clear insights that drive smart decisions. This guide will walk you through creating a dynamic and professional KPI report, from organizing your raw data to visualizing it with interactive charts and dashboards.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, What Makes a Good KPI Report?

Before jumping into formulas and charts, it’s important to understand the goal. A great KPI report isn’t just a random collection of metrics, it tells a clear story about performance. It should be:

• Clear: Anyone on your team should be able to understand the report's main takeaways in seconds. It uses simple visuals and avoids clutter.
• Relevant: It focuses exclusively on the Key Performance Indicators (KPIs) that matter most for your specific goals, cutting out the noisy vanity metrics.
• Contextual: Numbers mean nothing in isolation. The report should provide context by comparing performance to goals, benchmarks, or past periods (like last month or last year).
• Actionable: A good report should empower you to make a decision. After reviewing it, you should know what’s working, what isn't, and what to do next.

With those principles in mind, let’s build one.

Step-by-Step Guide to Building Your Excel KPI Report

We’ll build a sample marketing KPI report, but you can apply these same steps to sales, finance, operations, or any other area of your business.

Step 1: Get Your Raw Data in Order

This is the most critical step. Your report is only as good as the data it’s built on. The golden rule is to keep your raw data separate from your report dashboard.

1. Create a new Excel workbook.
2. Rename the first tab to something like "Raw Data". This sheet will be a simple "data dump" and nothing else. Don’t add any formatting, formulas, or summaries here.
3. Organize your data in a clean table format. Each column should have a clear header, and each row should represent a single entry (e.g., a day, a campaign, or a transaction).

Pro-Tip: Format your data as an official Excel Table by selecting your data and pressing Ctrl + T (or Cmd + T on Mac). This makes formulas and PivotTables much easier to manage later on, as they will automatically update when you add new data to the table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Define Your KPIs and Calculations

Now that your data is organized, it's time to create the "brains" of your report. Create a second sheet and name it "Dashboard" or "Report". This is where you'll build the visualizations and summary metrics.

First, let’s calculate the main headline KPIs. These are the big-picture numbers you want to see immediately. Off to the side of your main dashboard area, create a small section for these calculations to keep things organized.

Based on our raw data, some valuable KPIs would be:

• Total Ad Spend
• Total Conversions
• Cost Per Conversion (CPC)
• Conversion Rate (CVR)

Using simple formulas that reference your "Raw Data" sheet, you can calculate these:

Total Ad Spend:

=SUM('Raw Data'!C:C)

Total Conversions:

=SUM('Raw Data'!F:F)

Cost Per Conversion (Ad Spend / Conversions):

=SUM('Raw Data'!C:C)/SUM('Raw Data'!F:F)

Conversion Rate (Conversions / Clicks):

=SUM('Raw Data'!F:F)/SUM('Raw Data'!E:E)

Arrange these on your sheet, and you’ll have the foundation for your KPI "cards."

Step 3: Create Summary Tables with PivotTables

PivotTables are the easiest way to summarize your data without writing dozens of complex formulas. They let you quickly group and aggregate your data to see performance by channel, date, or any other dimension.

Let's create a PivotTable to see our ad spend by marketing channel.

1. Go to your "Raw Data" sheet and click anywhere inside your data table.
2. Navigate to Insert > PivotTable.
3. In the dialog box, tell Excel to place the PivotTable on your existing "Dashboard" worksheet.
4. The PivotTable Fields pane will appear. Now, just drag and drop:

Just like that, you have a summary table showing spend and conversions for each channel. You can create several PivotTables to summarize different cuts of your data - for example, one for weekly performance trends.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Visualize Your KPIs with Charts

Now for the fun part: turning those summary tables and numbers into professional-looking charts. Use PivotCharts for this, as they will connect directly to your PivotTables.

Creating KPI Cards

For your main headline numbers, you can create "KPI cards," which are just large, easy-to-read numbers. To do this:

1. Go to Insert > Text > Text Box.
2. Draw a text box on your dashboard.
3. With the text box selected, click on the formula bar, type =, and then click on the cell containing your KPI calculation (e.g., the cell with the "Total Ad Spend" formula).
4. Hit Enter. The text box will now display the value from that cell.

Style the text to be large and clear. Repeat this for each of your headline KPIs.

Creating Charts from PivotTables

A good chart tells one story well. Choose the right chart type to match the story you're telling.

• Bar Chart: Best for comparing categories, like performance by marketing channel.
• Line Chart: Perfect for showing trends over time, like daily or weekly conversions.

To create a bar chart showing spending by channel:

1. Click inside your channel performance PivotTable.
2. Go to the PivotTable Analyze tab and click PivotChart.
3. Select a column or bar chart and click OK.

Excel will instantly generate a chart linked to your data. Now, clean it up for readability: give it a clear title (e.g., "Ad Spend by Channel"), remove unnecessary elements like field buttons or gridlines (right-click on them and select "Hide"), and use your brand colors.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 5: Make It Interactive with Slicers

Slicers are filters that look like buttons, and they make your report interactive. Instead of manually filtering tables, users can simply click a button to see the data they care about.

1. Click on any of your PivotCharts.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. A dialog box will appear with all your data columns. Check the box for the field you want to filter by - let's choose "Channel".
4. A slicer will appear on your sheet. Now, if you click "Google Ads," all the charts linked to that PivotTable will update to show data for only Google Ads.

Pro-Tip: To make one slicer control multiple charts, right-click the slicer and select "Report Connections." In the dialog box, you can check the boxes for all the PivotTables you want that single slicer to control. This is how you make your entire dashboard fully interactive.

Best Practices for an Effective Excel KPI Report

With the structure in place, here are a few final tips to elevate your report from good to great.

• Use Conditional Formatting: Use color scales, data bars, or icons to automatically highlight good or bad performance in your tables. A green up-arrow for positive growth or a red highlight for a missed target instantly draws attention where it's needed.
• Lock It Down: Once your report is built, protect the sheet to prevent users from accidentally breaking formulas or changing the layout. Unlock only the cells or slicers you want people to interact with. You can do this under the Review > Protect Sheet menu.
• White Space is Your Friend: Don't try to cram too much onto one dashboard. A cluttered report is an ignored report. Keep an organized layout and leave enough empty space around elements to make the report easy to read.
• Automate Your Data Pulls with Power Query: If you find yourself manually exporting and pasting data every week, look into Power Query (a free tool built into Excel, also called Get & Transform Data). It can connect directly to many sources and automate the entire "Raw Data" update process for you.

Final Thoughts

Creating a thoughtful KPI report in Excel is a powerful skill that transforms you from someone who just provides numbers into someone who provides valuable business insights. By structuring your data correctly and leveraging tools like PivotTables, charts, and slicers, you can build a dynamic dashboard that helps your team make smarter, data-driven decisions.

Of course, manually exporting CSVs and wrestling spreadsheets is still a major time sink, especially as your data sources grow. We built Graphed to automate all of this tedious work. We connect directly to your tools like Shopify, Google Analytics, and Facebook Ads, so your data is always live and up-to-date. Instead of building PivotTables, you just ask questions in plain English, and Graphed builds the interactive dashboard for you in seconds.