What is a KPI in Excel?

Tracking your business performance can feel like navigating without a map unless you’re focused on the right signals. Key Performance Indicators, or KPIs, are those signals, turning abstract goals into concrete numbers you can act on. This guide will walk you through what KPIs are, how to choose the right ones, and how to build a simple, effective KPI tracker using Microsoft Excel.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is a KPI (Key Performance Indicator)?

A Key Performance Indicator is a measurable value that demonstrates how effectively you’re achieving your main business objectives. Think of it like the dashboard of your car: the speedometer, fuel gauge, and engine temperature warning light don't show you everything happening under the hood, but they give you the critical information you need to operate the vehicle safely and efficiently.

In business, KPIs do the same thing. They cut through the noise of countless data points to highlight the metrics that matter most to your success.

Metric vs. KPI: What's the Difference?

People often use the terms "metric" and "KPI" interchangeably, but they're not the same. A metric is simply a measurement. Your website getting 10,000 visitors last month is a metric. A KPI is a metric that is tied directly to a key business goal and indicates performance.

For example, if your goal is to grow sales-qualified leads, the total number of website visitors is just a metric. But the percentage of those visitors who download a whitepaper and become a marketing-qualified lead might be a KPI. It’s a measure of your performance against your specific objective of generating leads.

A good way to frame your KPIs is to use the SMART goal framework. This ensures they are:

• Specific: Clearly defined and not ambiguous.
• Measurable: Can be quantified and tracked.
• Achievable: Realistic to accomplish.
• Relevant: Aligns with your broader business goals.
• Time-bound: Has a clear timeframe for completion.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Why Use Excel for KPI Tracking?

While there are dozens of advanced business intelligence tools available, Excel remains an incredibly popular choice for KPI tracking, especially for small to medium-sized businesses. There are several good reasons for this:

• Accessibility: Most businesses already have Microsoft Office, so there’s no extra cost.
• Familiarity: Millions of people already know the basics of Excel, which lowers the learning curve.
• Flexibility: Excel is a blank canvas. You can build anything from a very simple tracking table to a complex interactive dashboard with charts, a feature that provides tons of flexibility.
• Control: You have complete control over your data, formulas, and visualizations without being locked into the limitations of a specific software platform.

The main drawback is that it requires manual data entry and updates, which can be time-consuming and prone to errors. But for many, the benefits of control and accessibility make it the perfect starting point.

How to Choose the Right KPIs

The most important step in tracking is choosing KPIs that genuinely reflect your business goals. A KPI that's crucial for an e-commerce store might be irrelevant for a B2B SaaS company. Your choice should always start with the question: "What is our primary objective?"

Here are some examples of common KPIs across different business functions:

Marketing KPIs

• Customer Acquisition Cost (CAC): Total marketing and sales spend / number of new customers acquired. This tells you how much it costs to get a new customer.
• Marketing Qualified Leads (MQLs): The number of leads that the marketing team deems likely to become customers.
• Conversion Rate: The percentage of visitors or leads who take a desired action (e.g., sign up for a newsletter, request a demo).
• Customer Lifetime Value (CLV): The total revenue you can expect to earn from a single customer over the course of their relationship with your business.

Sales KPIs

• Sales Growth: (Current period sales - previous period sales) / previous period sales. A fundamental measure of how fast your company is growing.
• Lead-to-Opportunity Conversion Rate: The percentage of leads that sales accepts and converts into qualified opportunities.
• Average Deal Size: Total value of all closed deals / number of deals closed.
• Sales Cycle Length: The average time it takes for a lead to become a customer.

E-Commerce KPIs

• Average Order Value (AOV): Total revenue / number of orders. Shows how much customers typically spend in a single transaction.
• Cart Abandonment Rate: The percentage of shoppers who add items to their cart but don’t complete the purchase.
• Customer Retention Rate: The percentage of customers who make a repeat purchase over a given period.

How to Track KPIs in Excel: A Step-by-Step Guide

Let's build a simple Excel KPI report for a marketing team. Our goal is to track lead generation and see how effectively we turn advertising spend into customers.

Step 1: Organize Your Raw Data

The foundation of any good report is a clean, organized data table. The best practice is to have one sheet dedicated to your raw data and another sheet for your dashboard/summary.

Open a new Excel workbook. Name one sheet "Data" and another "Dashboard". On the "Data" sheet, create a simple table like this:

Month | Website Traffic | Leads Generated | Ad Spend | New Customers | Revenue Jan-23 | 5,500 | 120 | $1,000 | 10 | $5,000 Feb-23 | 6,200 | 155 | $1,200 | 14 | $7,000 Mar-23 | 7,100 | 190 | $1,500 | 18 | $9,000 …and so on.

Pro Tip: Convert this range into an official Excel Table. Click anywhere inside your data and press Ctrl + T (or Cmd + T on Mac). This transforms your data into a dynamic table, which makes adding new data and writing formulas much easier.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Calculate Your KPIs Using Formulas

Now, let's add columns to our data table to calculate our key performance indicators.

1. Lead Conversion Rate

This KPI tells us what percentage of our website traffic becomes a lead. In the next available column in your "Data" sheet, title it "Lead Conversion Rate". The formula is:

=[@[Leads Generated]]/[@[Website Traffic]]

Format this column as a Percentage.

2. Cost Per Lead (CPL)

This tells us how much we pay for each lead. Add a column named "CPL" with this formula:

=[@[Ad Spend]]/[@[Leads Generated]]

Format this column as Currency.

3. Customer Acquisition Cost (CAC)

This is the total cost to acquire a paying customer. Add a column named "CAC" with this formula:

=[@[Ad Spend]]/[@[New Customers]]

Format this column as Currency.

Your data table should now include these calculated KPI columns, which will automatically update as you add new rows of monthly data.

Step 3: Visualize Your KPIs with Conditional Formatting and Charts

Now, let's head over to the "Dashboard" sheet. This is where we will create a clean visual summary. Numbers are great, but visualizations tell a story at a glance.

A simple yet powerful "KPI" feature in Excel is Conditional Formatting. It allows you to automatically change a cell's appearance (like its color or icon) based on its value.

Let’s say our goal is to keep our Customer Acquisition Cost (CAC) below $100.

1. On your Dashboard sheet, create a small summary section that pulls the most recent month's data. For CAC, you might have a cell that uses a VLOOKUP or INDEX/MATCH formula to pull the latest CAC figure from your Data tab.
2. Select the cell containing your CAC value.
3. Go to the Home tab on the Ribbon, click Conditional Formatting > Icon Sets, and choose a set (e.g., traffic lights).
4. Again, go to Conditional Formatting > Manage Rules…
5. Select your Icon Set rule and click Edit Rule.
6. Let's set our rules. We want a green light if CAC is good (low), and red if it's bad (high). Configure it like this:
7. Check the box for "Show Icon Only" if you don't want the number visible, giving you a clean status indicator.

You can do this for any KPI, visually flagging whether you are hitting, approaching, or missing your targets without anyone having to interpret the raw numbers.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 4: Create a Dashboard with Charts

Dashboards are all about making data easy to digest. Charts are perfect for this.

1. Tracking Revenue Over Time (Line Chart)

1. Go to your "Data" sheet and highlight your "Month" and "Revenue" columns.
2. Go to the Insert tab and select Line Chart. Choose the 2-D line chart.
3. Excel will generate your chart. Right-click the chart and select Move Chart. Choose your "Dashboard" sheet as the destination.
4. Once on the dashboard, you can resize and style it. Give it a clear title like "Monthly Revenue."

2. Comparing CPL and CAC (Combo Chart)

A combo chart is great for comparing metrics with different scales, like costs.

1. Go to the "Data" sheet, highlight the "Month," "CPL," and "CAC" columns.
2. Go to Insert > Recommended Charts > All Charts. Find the Combo chart option at the bottom.
3. Set both "CPL" and "CAC" as Clustered Column charts. This will place them side-by-side for each month, making them easy to compare.
4. Move the chart to your dashboard and give it a title like "Cost per Lead vs. Customer Acquisition Cost."

Arrange your charts and conditional formatting indicators on the "Dashboard" sheet to create a clean, one-page summary of your most important marketing performance metrics.

Final Thoughts

Tracking KPIs is vital for making informed decisions and steering your business toward its goals. Excel offers a powerful and accessible platform to build a customized KPI dashboard that can grow with you. By setting up your data correctly, using formulas to calculate your indicators, and using charts and conditional formatting to visualize performance, you can turn a simple spreadsheet into a valuable command center for your business.

While Excel is a great tool, the process of manually downloading reports, copying data, and updating formulas each week can lock away valuable time you could be using for analysis. At Graphed, we eliminate that routine. You simply connect your data sources - like Google Analytics, Shopify, QuickBooks, or your CRM - once, and from then on your dashboards are always live and update automatically. We help you skip the spreadsheet wrangling and go straight to getting answers. Instead of building formulas, you can get insights in seconds just by asking questions like, "What was our customer acquisition cost last month across all campaigns?" Graphed turns hours of reporting busywork into a quick conversation, giving you more time to act on your data.