How to Calculate KPI in Excel

Calculating your Key Performance Indicators (KPIs) in a spreadsheet doesn’t have to feel like a chore. With the right setup and a few key formulas, you can turn your static sales or marketing data into a powerful performance dashboard. This guide will walk you through setting up your data, calculating meaningful KPIs using common formulas, and visualizing them in a simple Excel dashboard.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, What Exactly is a KPI?

A Key Performance Indicator (KPI) is a measurable value that tracks how effectively you are achieving a specific business goal. It’s more than just a regular metric, a KPI is a key metric that is directly tied to your strategic objectives.

For example, while "Website Pageviews" is a useful metric, "Website Conversion Rate" is a stronger KPI because it measures the efficiency of your website in achieving a core goal - turning visitors into customers. Think of it this way:

• Metric: A countable measure. (e.g., number of sales calls made)
• KPI: A measure that evaluates the success of a business objective. (e.g., lead-to-close conversion rate)

Excel remains one of the most accessible and powerful tools for getting started with KPI tracking. Its flexibility, familiar interface, and robust functions make it perfect for business owners, marketers, and sales managers who need to see their performance without investing in complex software right away.

Step 1: Get Your Data Organized in Excel

The foundation of any good report is clean, well-structured data. Before you write a single formula, take a moment to format your raw data correctly. The best way to do this is by converting your data range into an official Excel Table.

Here’s how:

1. Click anywhere inside your data set.
2. Go to the Insert tab and click on Table, or just press the shortcut Ctrl + T (Cmd + T on Mac).
3. Make sure the "My table has headers" box is checked if you have column titles.

Why use an Excel Table? It offers several advantages:

• Structured References: Formulas become easier to read. Instead of =SUM(C2:C100), you can write =SUM(Sales[Revenue]).
• Automatic Expansion: When you add a new row of data, the table expands automatically, and your formulas and charts that reference it will update. No more manually adjusting ranges!
• Easy Formatting: You get built-in sorting, filtering, and styling options.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Calculate Your KPIs with Essential Formulas

Now that your data is neatly organized in a table (let’s call our table "SalesData"), you can start calculating your KPIs. We'll set up a new sheet called "Dashboard" where we'll perform these calculations and later build our visuals.

Here are five common business KPIs and how to calculate them.

1. Total Sales Revenue

This is one of the most straightforward KPIs. It's the total sum of all your sales over a period.

• Formula: SUM
• How to use it: To calculate the total revenue from our "SalesData" table:

2. Sales Growth (Month-over-Month)

Tracking growth month-over-month (MoM) shows your business's momentum. This calculation is a bit more involved as it compares two periods.

• Formulas: SUMIFS, EOMONTH, TODAY

First, we need to calculate the revenue for the current month and the previous month in separate cells. For this example, let's assume we are in the month of May.

To calculate the Current Month revenue (assumes dates are in the current year):

=SUMIFS(SalesData[Revenue], SalesData[Date], ">="&EOMONTH(TODAY(),-1)+1, SalesData[Date], "<="&EOMONTH(TODAY(),0))

To calculate the Previous Month revenue:

=SUMIFS(SalesData[Revenue], SalesData[Date], ">="&EOMONTH(TODAY(),-2)+1, SalesData[Date], "<="&EOMONTH(TODAY(),-1))

Now, with "Current Month Revenue" in cell B2 and "Previous Month Revenue" in cell B3, you can calculate the growth percentage.

MoM Growth Formula:

=(B2 - B3) / B3

After entering the formula, format the cell as a Percentage to see your growth rate clearly.

3. Customer Acquisition Cost (CAC)

CAC helps you understand how much it costs to acquire a new customer. To calculate this, you'll need two separate pieces of information: your total marketing and sales spend and the number of new customers acquired in a given period.

Often, this data isn't in one file. You would simply enter your total marketing spend in one cell (e.g., C2) and the number of new customers in another (e.g., C3).

• Formula: Basic division

=C2 / C3

So if you spent $5,000 on marketing (cell C2) and acquired 50 new customers (cell C3), your CAC is $100.

Graphed

Still Building Reports Manually?

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

Watch the Demo

4. Average Deal Size

This KPI tells you the average revenue you generate from a single sale, helping you understand the value of a typical customer.

• Formula: AVERAGE
• How to use it: To find the average of all deals in your table:

=AVERAGE(SalesData[Revenue])

5. Lead-to-Sale Conversion Rate

What percentage of your leads become actual sales? This KPI is vital for evaluating the effectiveness of a sales team. For this, your data table needs a way to track the total number of leads and how many of them closed.

Let's imagine your table has a column called "Lead Count" and another called "Deals Closed" (with a 1 for a closed deal and 0 for not).

• Formula: SUM and division

=SUM(SalesData[Deals Closed]) / SUM(SalesData[Lead Count])

This gives you a decimal, format the cell as a Percentage to see your conversion rate.

Step 3: Build a Simple KPI Dashboard

Now that you have your KPI values calculated, you can bring them together in a visually appealing dashboard for an at-a-glance view of your business performance.

1. Create a KPI Summary Area

On your "Dashboard" sheet, create a small, neat table that holds all your calculated KPI values. Give each KPI a clear label. This area will be the engine of your dashboard, feeding all your charts and visuals.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Visualize with the Right Charts and Cards

Raw numbers are great, but visuals make insights pop. Here are a few ways to display your KPIs.

KPI Cards for Single Metrics

For standalone numbers like Total Revenue or CAC, a "card" visual is very effective. You can create one easily with a Text Box.

1. Go to the Insert tab > Text > Text Box.
2. Draw a box on your dashboard grid.
3. With the Text Box selected, click into the Formula Bar (not the text box itself), type "=", and then click on the cell containing your KPI value (e.g., the cell with your Total Revenue calculation).
4. Hit Enter. The Text Box is now dynamically linked to your cell!
5. Style the text box and the font to make it look clean and professional. Add another smaller Text Box below it for a label, like "Total Revenue."

Line Charts for Trends Over Time

Line charts are perfect for visualizing KPIs like Month-over-Month Revenue. To do this, you’ll first need a summary table with months in one column and sales totals in another. You can generate this using SUMIFS formulas for each month.

Once you have that table, select it, go to Insert > Charts, and choose a Line Chart.

Bar Charts for Comparison

If you want to compare performance across categories - like Sales by Region or Revenue by Sales Rep - a bar chart is your best friend. Create another simple summary table (e.g., list of regions and their total sales using SUMIFS), select it, and insert a Bar Chart.

3. Put it all Together

Arrange your cards and charts on a clean worksheet. Here are a few final design tips:

• Go to the View tab and uncheck Gridlines to get a clean, white background.
• Place your most important KPIs in the top-left, as that's where the eye naturally looks first.
• Use a simple and consistent color scheme.
• Keep it simple! A dashboard's purpose is clarity, not clutter.

Final Thoughts

Building a KPI report in Excel is a fantastic way to develop a deeper understanding of your business drivers. By properly structuring your data and using core formulas like SUMIFS and AVERAGE, you can transform a plain spreadsheet into a dynamic dashboard that helps you track progress toward your goals.

Despite how powerful Excel is, this process often relies on manually downloading CSVs, cleaning the data, and updating reports every week or month. At a certain point, the data wrangling can take up more time than the actual analysis. We built Graphed to eliminate that friction completely. You can connect your marketing and sales platforms (like Google Analytics, Shopify, or Facebook Ads) in a few clicks, and our AI will build real-time dashboards for you automatically, just by asking for what you want in plain English. No more formulas, no more stale reports - just current insights, ready when you are.