KPI Chart in Excel Using a Pivot Table: Setup and Tips

To make a KPI chart in Excel, summarize your source data in a PivotTable, insert a PivotChart, and compare the result with a clearly defined target. In Microsoft 365 for Windows, select a cell in your data and choose Insert > PivotTable; after arranging the fields, choose PivotTable Analyze > PivotChart.

What a KPI chart shows

A key performance indicator (KPI) is a measure tied to a specific objective, such as monthly sales, orders delivered on time, or customer support response time. A chart helps show how that measure changes or compares across categories. The PivotTable does the summarizing; the chart makes the summarized result easier to interpret.

Excel does not have one universal chart type called a KPI chart. The phrase may refer to a column or line chart that tracks a measure, a chart comparing actual results with a target, or a compact scorecard showing a value and whether it meets a goal. Those are related but different displays. Choose based on the question: a trend, a comparison, or a quick status check.

For example, a sales manager might want monthly sales by region and a $100,000 monthly target. A PivotTable can calculate sales totals from transaction records, and a PivotChart can show those totals by month. A separate target reference makes it possible to judge performance rather than just see sales values.

Plan the metric and target first

Before building the workbook, decide what the KPI measures, how it is aggregated, and what counts as success. A target should use the same units and time period as the result. Comparing quarterly sales with a monthly target, or an average response time with a maximum allowed time, can make a chart misleading even when the PivotTable is technically correct.

Metric: the value to measure, such as revenue, units sold, or the percentage of cases resolved on time.
Period and category: the comparison, such as month, product, or sales region.
Aggregation: whether the metric should be summed, averaged, counted, or calculated as a percentage.
Target: a goal that applies to each period or category, including any exceptions.

Aggregation matters. Summing transaction revenue is usually sensible, while summing individual response times usually is not. A percentage KPI may need to be calculated from its numerator and denominator—for example, on-time cases divided by all cases—rather than by averaging percentages that have different underlying case counts.

Prepare clean source data

Use a simple, rectangular data set with one header row and one record per row. Avoid merged cells, blank header names, subtotal rows, and completely blank rows or columns inside the data. Give each column a unique, descriptive heading, such as Date, Region, Product, Sales, and Units. If possible, convert the range into an Excel table with Insert > Table and confirm that the table has headers. Tables expand more reliably when new records are added.

Store dates as real Excel dates, numbers as numbers, and categories consistently. For example, values such as North, north, and North Region may be treated as separate categories. Do not mix text such as “$500” into a numeric Sales column. If the source contains one row per transaction, do not repeat a monthly target on every transaction row and then sum it: the PivotTable would count the same target many times.

For a reliable target comparison, keep targets in a separate small table at the correct grain—for example, one row per month and region—or place the target values in a separate chart helper range. This avoids accidentally inflating a target when the PivotTable adds up transaction-level data.

Create the PivotTable

Click a cell in the source data or Excel table.
Choose Insert > PivotTable, check the selected table or range, and choose New Worksheet or an appropriate location.
In the PivotTable Fields pane, drag the category you want to compare, such as Date or Region, to Rows. Drag the measure, such as Sales, to Values.
Check the Values field summary. Excel may choose Sum, Count, or another calculation based on the data. Open the value field settings and select the appropriate summary, such as Sum or Average.

For monthly results, put a genuine date field in Rows. If Excel displays individual dates, right-click a date label and choose Group, then select Months and, where needed, Years. Keeping years in the grouping prevents January from different years from being combined into one January total. If Group is unavailable or grouping fails, check for blank cells, text-formatted dates, or invalid date values in the source.

Format the summarized values so the scale is clear. For currency, use a currency or accounting number format; for a percentage KPI, use Percentage and choose a useful number of decimal places. Number formatting changes how results appear, not the underlying calculation.

Insert and configure the PivotChart

Click inside the PivotTable and choose PivotTable Analyze > PivotChart. Select a chart type suited to the comparison, then select OK. You can also create a PivotChart from the Insert tab when the PivotTable is selected. The resulting chart is connected to the PivotTable: changing field selections or applying a PivotTable filter changes the chart as well.

Line chart: useful for showing a trend over time, especially when periods are in chronological order.
Clustered column chart: useful for comparing a small number of categories or periods side by side.
Bar chart: useful when category names are long or there are many categories.

Use Chart Design > Add Chart Element to add a chart title, axis titles, or data labels where they clarify the message. Label units, not just the metric: “Sales ($)” is more informative than “Sales.” Avoid showing every data label if it makes the display crowded. A chart title such as “Monthly Sales by Region” identifies what is being summarized, while a separate note can state the target and its time period.

Field buttons on a PivotChart let viewers filter or rearrange fields. To hide them, select the chart and use PivotChart Analyze > Field Buttons > Hide All. The exact tab labels can vary slightly by Excel update. On Mac, the contextual PivotChart and PivotTable tabs may appear with different names or ribbon placement; the field-list and chart controls still provide the equivalent operations. Excel for the web has a more limited editing interface, so use desktop Excel if a command is not available there.

Show actual results against a target

A basic PivotChart can show actual results, but a target line often needs an additional data series. PivotCharts are tied to PivotTable fields and do not work like ordinary charts for adding an arbitrary extra series. One practical approach is to build a small helper range beside the PivotTable, link its actual values to the PivotTable output, and put the corresponding targets in another column. Create a regular chart from that helper range rather than trying to insert a standalone series into the PivotChart.

For example, suppose a PivotTable displays monthly sales in cells B5:B7 for January through March, with results of $92,000, $108,000, and $97,000. A helper table can list those months, link the actual values to the PivotTable cells, and list a $100,000 target for each month. Select the helper table, then choose Insert > Recommended Charts or Insert > Column or Bar Chart. To show actuals as columns and the target as a line, select the regular chart and choose Chart Design > Change Chart Type, then select Combo if that option is offered for the selected chart. Keep the actual and target on the same scale when they use the same units.

Assuming the actual value is in D2 and the target is in E2, the variance formula is =D2-E2. If D2 is 108000 and E2 is 100000, the result is 8000, meaning sales are $8,000 above target. Assuming those same cells, the attainment formula is =IFERROR(D2/E2,0); with those example values it returns 1.08, or 108% when formatted as a percentage. The IFERROR wrapper prevents a divide-by-zero error if the target is blank or zero, but a zero result should not be mistaken for a meaningful performance score.

Assuming D2 contains actual sales and E2 contains the target, a simple text status can be calculated with =IF(D2>=E2,"On target","Below target"). With 108000 and 100000, it returns On target. A separate status cell can work well as a scorecard next to the chart. For a negative KPI such as defects, reverse the comparison: fewer defects may be better, so meeting the goal could mean the actual value is less than or equal to the limit.

Filter the view and refresh the results

PivotTable filters help answer focused questions without rebuilding the summary. For instance, add Region to Filters to view one region at a time, or add a slicer by selecting the PivotTable and choosing PivotTable Analyze > Insert Slicer. A timeline can filter a date field when Excel recognizes it as a date. Slicers are clickable controls; they can be useful for presentations, but too many can make a dashboard harder to scan.

When source records change, refresh the PivotTable using PivotTable Analyze > Refresh, or choose Data > Refresh All to refresh workbook connections and PivotTables. A normal chart built from a fixed helper range may not automatically include extra periods added below the original range. Check the helper range after refresh, and extend it or base it on an Excel table or another appropriately expanding range. A chart linked to a PivotChart updates with its PivotTable, but it still depends on the source range including the new records.

Common problems and how to fix them

Values show Count instead of Sum: the source measure may contain text or blanks. Correct the source values, refresh the PivotTable, and check the value field settings.
Dates will not group: check for blank cells, text dates, or inconsistent entries. Convert the source to valid dates, refresh, and try grouping again.
The target total looks far too large: a target may be repeated on every transaction row and summed. Store targets separately or use a correctly designed helper range.
New source rows are missing: convert the source to an Excel table or update the PivotTable source, then refresh.
The chart is hard to read: reduce the number of categories, use a chronological date axis, label units, and avoid mixing unrelated scales.

Also check whether a percentage is being averaged when it should be recalculated from totals. If one region has 9 on-time deliveries out of 10 and another has 90 out of 100, their rates are 90% each. But averaging a set of group percentages generally gives each group equal weight, which may not represent the overall rate. For an overall rate, summarize the counts and calculate the ratio from the combined numerator and denominator.

PivotChart, regular chart, or KPI card?

Use a PivotChart when viewers need to filter and explore categories through PivotTable fields, filters, or slicers. Use a regular chart based on a helper range when you need a target line, custom series, or more control over a combination of chart types. A compact KPI card—one large value, a target, and a status—can be clearer than a chart when the main question is simply whether the current result meets its goal.

These approaches can be combined on a worksheet dashboard: a KPI card for the latest result, a regular actual-versus-target chart for the trend, and a PivotChart for category exploration. Keep the target definition, date range, and units visible. If the workbook will be shared, explain how to refresh it and whether the displayed period is filtered.

Frequently Asked Questions

Can I add a target line directly to a PivotChart?
A PivotChart is constrained by its PivotTable fields and does not accept an arbitrary series in the same way as a regular chart. Use a helper range and a regular chart when you need a separate target series.

Which chart type is best for a KPI?
Use a line chart for change over time, a bar or column chart for category comparisons, and a KPI card for one current result against a goal. The right choice depends on the question and the amount of data.

Why does my PivotTable count values instead of adding them?
Excel may treat the source values as text or may have blanks or inconsistent entries in the field. Correct the source column, refresh, and set the value field summary to Sum.

Will a PivotChart update when I add data?
It updates after its PivotTable is refreshed, provided the PivotTable source includes the new records. An Excel table is a useful source because it expands as records are added.

Can I make a KPI chart without a PivotTable?
Yes. A regular chart can use a clean summary table directly and may be simpler if the data is already summarized or if you need a custom target series. A PivotTable is most useful when you need to aggregate and explore detailed records.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.