KPI Chart in Excel: Build, Format, and Track Performance

Charts & pivot tables · Updated November 20, 2026

Excel does not have a standalone chart type called “KPI chart,” but you can make one by charting a key measure against its target and, if useful, showing its trend or status. Put the measure, actual result, and target in columns, select the data, then choose Insert > Insert Column or Bar Chart > Clustered Column; you can also use a line for the target to make gaps easier to see.

What a KPI chart shows in Excel

A key performance indicator (KPI) is a measurable value used to assess progress toward a defined goal. A KPI chart presents that value in a form that helps someone judge performance quickly. For example, a sales team might track monthly revenue against a revenue goal, while a support team might track average response time against a maximum acceptable time.

“KPI chart” describes a purpose, not one specific Excel chart type. A column chart, line chart, or carefully designed dashboard card can all communicate KPI information. The right choice depends on what a reader needs to compare: performance against a threshold, change over time, or several measures side by side.

Excel also has KPI functionality associated with the Data Model and Power Pivot. That is different from a chart type: a KPI can be defined from measures and displayed with status indicators in PivotTables or related reports. For a simple worksheet dashboard, a regular chart with actuals and targets is usually easier to set up and maintain.

Choose a chart that fits the question

Start by deciding what the audience should be able to conclude. If the main question is “Did we reach the target this month?”, show actual and target together. If it is “Are results improving?”, show the KPI over time. Combining every possible measure in one chart often makes the answer less clear.

  • Actual versus target: A clustered column chart compares two values for each period or category. It works well when the goal is to compare actual results with a target side by side.
  • Actual trend: A line chart is useful for a KPI recorded at regular intervals, such as weekly defects or monthly revenue. Add a target line if the threshold should remain visible.
  • Progress toward a goal: A simple bar can show completion, but label the scale and target clearly. A percentage alone can hide whether the underlying value is large or small.
  • Several headline measures: Separate KPI cards can show each current value, its target, and its direction of change. These are dashboard elements rather than a special Excel chart type.

Use a consistent definition for each KPI. A lower number may mean better performance for response time or error rate, while a higher number may be better for sales. Do not assume that “above target” always means success.

Prepare a small, reliable data table

For a basic example, enter the following data in A1:C5. The figures are illustrative monthly revenue values, in the same unit, with a target of 100 each month.

Month Actual Target
Jan 92 100
Feb 105 100
Mar 98 100
Apr 112 100

Keep one header row, use one row per period, and avoid blank rows inside the range. Actuals and targets must use comparable units and time periods. If actuals are monthly totals but targets are quarterly totals, the chart will be mathematically valid but misleading.

For a workbook that will grow, turn the range into an Excel Table: select a cell in the data and choose Insert > Table, then confirm that the table has headers. Tables expand when new rows are added and can make it easier to keep chart data current. Check the chart’s source range after adding data, especially if the chart does not include new periods automatically.

Calculate variance and attainment

Variance tells you the difference between actual and target. With actual in B2 and target in C2, enter =B2-C2 in D2. For January, the result is -8 because 92 minus 100 equals -8. Copy the formula down the column to calculate the other months; February’s variance is 5.

Attainment expresses actual as a share of target. With actual in B2 and target in C2, enter =B2/C2 in E2. January returns 0.92, or 92% when formatted as a percentage; February returns 105%. Select the results and choose Home > Number > Percent Style to display them as percentages. If the target can be zero, decide how to handle that case before using this calculation, because division by zero produces an Excel error.

A text status can make a summary easier to scan. With actual in B2 and target in C2, enter =IF(B2>=C2,"On target","Below target") in F2. For January it returns “Below target”; for February it returns “On target.” This comparison assumes higher values are better. For a KPI where lower is better, reverse the comparison and define the status wording to match the business rule. These formulas use standard Excel syntax and can be filled down for the remaining rows.

Build the chart

  1. Select the month, actual, and target data, including the headers in A1:C5.
  2. Choose Insert > Insert Column or Bar Chart > Clustered Column. Excel inserts a chart with separate actual and target columns for each month.
  3. Replace the default chart title with a specific name such as “Monthly revenue: actual vs target.” Add the unit if it is not obvious from the surrounding report.
  4. To show actual as columns and target as a line, select the chart and choose Chart Design > Change Chart Type > Combo. Set Actual to Clustered Column and Target to Line, then confirm. Because both series use the same unit in this example, they can share the same vertical axis.

A target line is often easier to follow than a row of target columns, especially when the target is constant. If the target changes by month, the line shows those changes. Avoid adding a secondary axis just to make the target appear more prominent: different scales can make the relationship between actual and target harder to interpret.

Format for quick, honest reading

Use a descriptive title, readable axis labels, and a legend that identifies actual and target. If the vertical axis starts above zero, make that choice obvious; a truncated scale can exaggerate small differences in a column chart. For a chart of counts or money, include units such as “orders” or “$ thousands” where readers will see them.

Keep color consistent across related charts. For example, use one color for actual values and a neutral contrasting line for targets. Color alone should not carry the message: retain a legend, labels, or status text so that readers can understand the chart when colors are difficult to distinguish. Data labels can help when there are only a few points, but labels on every point in a large time series can create clutter.

Conditional formatting can highlight values in the worksheet, but it does not automatically turn the chart into a traffic-light KPI display. If you want red, amber, and green status bands, define the thresholds first and make sure the colors match the KPI’s rules. “Below target” may be good for a cost or delay measure, so status logic needs to reflect whether higher or lower is desirable.

Use the chart in a dashboard

A KPI chart is most useful when its context is visible. Place it near a headline value and include the reporting period, target, and a short definition if the measure could be interpreted in more than one way. For example, “Revenue” might mean invoiced sales, recognized revenue, or cash received; a label or note can prevent readers from assuming the wrong definition.

For a compact dashboard, use a small number of charts that answer distinct questions. A headline card can show the latest actual and its variance, while a nearby trend chart shows whether performance is sustained or a one-period exception. If the worksheet uses filters or PivotTables, check that the displayed target changes appropriately when the selected category or period changes.

When sharing the file, consider whether readers need to edit the source data or only view results. Keep input cells and calculations distinguishable, and test the chart after sorting, filtering, or adding rows. A dashboard that updates visually but uses an incomplete source range can give a false impression of current performance.

Common problems and how to avoid them

  • Unclear KPI definition: State what is counted, the time period, and whether higher or lower is better.
  • Incompatible comparisons: Match actuals and targets by unit, period, and scope. A team-level actual should not be compared with a company-wide target without explanation.
  • Too many series: If the chart is hard to scan, remove secondary measures or split the view into separate charts.
  • Misleading axes: Check axis bounds and units, particularly when using columns. Avoid a secondary axis unless readers genuinely need two different scales.
  • Missing or stale periods: Verify the chart source range and check for blank or text-formatted values that Excel may not plot as expected.
  • Ambiguous status colors: Define thresholds and confirm that the colors follow the KPI’s direction and business meaning.

One common misconception is that a chart showing a value in green is automatically a valid KPI. A visual status is only as reliable as the measure, target, period, and comparison rule behind it. Document those choices so that another person can understand what “good” means.

Other Excel options and limitations

A standard chart is a practical starting point, but it is not the only option. Conditional formatting can add data bars, color scales, or icon sets to a worksheet range. Sparklines, available from Insert > Sparklines, place small trend charts inside cells. They can complement a KPI table, although they provide less room for axis labels and explanatory detail than a full chart.

For interactive reporting, PivotCharts can summarize data organized for PivotTables, and slicers can help filter supported tables or PivotTables. These tools are useful when viewers need to explore categories, but they require a well-structured source and careful checking of filters. Excel’s Power Pivot and Data Model KPI features are more specialized and may be unnecessary for a simple comparison of actual and target.

Some dashboard designs use gauge charts or bullet-style displays. Excel does not offer a single dedicated built-in “KPI chart” that automatically creates these designs; they are typically assembled from other chart elements or represented with formatted cells. Use such designs only when they make the measure easier to understand than a straightforward bar or line chart.

Frequently Asked Questions

Does Excel have a built-in KPI chart?
There is no standard chart type named KPI chart. You can build a KPI view with ordinary charts such as columns or lines, or use Data Model KPI functionality for more specialized PivotTable reporting.

What is the best chart for actual versus target?
A clustered column chart is an easy way to compare actual and target side by side. If the target is a reference threshold, a column chart for actuals with a target line can be clearer.

How do I show whether a KPI is above its target?
Calculate actual minus target, or compare the two values with an IF formula, then display the result beside the chart or in the source table. Confirm whether higher or lower values represent success before setting the rule.

Can I make a KPI dashboard in Excel?
Yes. Combine a small set of charts, headline values, and clearly labeled targets on a worksheet. Keep the underlying data organized and test that charts and summaries update when the data changes.

Why is my target missing from the chart?
Check that the target column is included in the chart’s source data and contains numeric values. Select the chart and review its data source; also confirm that the target is not hidden by an unsuitable axis scale or chart type.

Related

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.