To add an 80% line to a Pareto chart in Excel, calculate a cumulative percentage and create a second series containing 80% for every category, then plot both series as lines on a secondary axis. A regular combo chart gives you control over the reference line; Excel’s built-in Pareto chart may not allow a constant line to be combined with its chart type.
What You’ll Need
- Excel for Microsoft 365 on Windows. The instructions use its standard ribbon commands; on Mac, chart options are similar but may appear in a different order or in a Format pane.
- A category column and a column of nonnegative counts, costs, or other values to compare. The example uses six categories and totals 100.
- A worksheet area for two helper columns: cumulative percentage and the 80% target.
Step-by-Step
- Set up and sort the source data. Put category names in A1:A7 and their values in B1:B7, with headers in row 1. For the example, enter causes in A2:A7 and values of 40, 25, 15, 10, 6, and 4 in B2:B7. The total is 100. Sort the records by value from largest to smallest: select the data and choose Data > Sort, sort by the value column, and choose Largest to Smallest. Keep each category with its matching value. Pareto charts use descending values so that the largest contributors appear first.
- Add a cumulative percentage column. Enter Cumulative % in C1. Assuming categories are in A2:A7, values are in B2:B7, and the data has been sorted, enter this formula in C2:
=SUM($B$2:B2)/SUM($B$2:$B$7). It returns 40% for the first example row. Copy or fill the formula down through C7; the results are 40%, 65%, 80%, 90%, 96%, and 100%. Format C2:C7 as percentages using Home > Number and the Percent Style button, or press Ctrl+Shift+% on Windows. On Mac, use Command+Shift+% where that shortcut is available, or use the ribbon. - Create the constant target series. Enter 80% target in D1. Assuming the target values belong beside the six data rows in D2:D7, enter
=80%in D2 and fill it down to D7. Every row should show 80%. This repeated value is what makes Excel draw a horizontal line across all the category positions, rather than a single marker. - Select the chart data. Select A1:D7, including the headers, category names, original values, cumulative percentages, and target percentages. If the columns are not next to each other in your worksheet, select the first range, then hold Ctrl on Windows while selecting the other ranges; on Mac, use Command. Verify that the headers are included so Excel can name each series correctly.
- Insert a combo chart. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the custom combo dialog, set the original value series to Clustered Column. Set both Cumulative % and 80% target to Line, and check Secondary Axis for each percentage series. Click OK. The value columns use the left axis; both percentages use the right axis, so their scales are comparable. If the combo option is not displayed, use Insert > Recommended Charts > All Charts > Combo to open the combo chart choices.
- Set the percentage axis to a consistent scale. Click the right-hand vertical axis and open Format Axis (right-click the axis and choose Format Axis on Windows). Under Axis Options, set the minimum bound to 0 and maximum bound to 1, since the percentage data is stored as decimal values from 0 to 1. Under Number, choose Percentage and set a suitable number of decimal places, usually zero. On Mac, select the axis and open the Format Axis pane; the available settings are equivalent. A fixed 0–100% scale makes the line’s position meaningful and easier to compare between charts.
- Make the 80% line easy to see. Click the target line, open Format Data Series, and choose a contrasting line color, width, or dash style. If the two lines overlap at a category that reaches exactly 80%, that is expected: the cumulative line meets the target at that point. Keep the line distinct from the cumulative series, and leave the target series without markers if the points make the reference look cluttered.
- Add labels and check the result. Use Chart Design > Add Chart Element to add a chart title, axis titles, and a legend if needed. Label the right axis as cumulative percentage and the left axis with the unit for the values, such as number of cases. In the example, the cumulative line reaches 80% at the third category because 40 + 25 + 15 = 80. The reference should cross the cumulative line at that third position. If you began with Excel’s built-in Pareto statistical chart, you can instead recreate it as this combo chart from the source table; this gives you direct control over the extra series and axes.
Common Mistakes
Not sorting the values first: A Pareto chart orders categories from largest to smallest. If the data is unsorted, the cumulative line still adds up, but the chart no longer highlights the biggest contributors first. Sort the complete rows, not only the values, so labels remain matched to their counts.
Putting the percentages on the primary axis: Counts and percentages usually have very different scales. If the cumulative and target series share the count axis, the 80% line can appear at the wrong height or be difficult to interpret. Assign both percentage series to the secondary axis.
Entering 80 instead of 80%: Excel stores 80% as 0.8. If the target values are 80 while the cumulative values are decimals, the target line can be far outside the visible scale. Enter 80% (or 0.8) and format the cells as percentages.
Using a changing denominator: The total in the cumulative formula needs absolute references. The formula shown locks the full value range while allowing the running sum to expand as it is filled down. If you add more categories, extend the total range and the chart source range.
Tips
Use a proper Excel Table if the source list will grow. Select the data and choose Insert > Table; when you add records, the table can expand automatically. Check that the chart includes new rows and that the cumulative formula and target series fill into them. If the chart does not expand as expected, update its data range through Chart Design > Select Data.
Keep the meaning of “80%” explicit. In a classic Pareto analysis, the line is a reference threshold for cumulative contribution; it does not mean that 80% of categories are included. The category where the cumulative line first reaches or passes 80% identifies how many of the largest categories account for that share of the total.
For a different threshold, change the target formula in D2 and fill it through the target range. For example, 75% or 90% works the same way. Update the series name so the legend reflects the new threshold. A constant target line should use the same percentage axis as the cumulative series.
Keep the chart readable by limiting unnecessary gridlines and using a clear line color that contrasts with the bars. If labels are long, widen the chart or rotate the horizontal-axis labels using Format Axis. If the data contains many categories, consider grouping small contributors into an “Other” category only when that grouping makes sense for the analysis.
Troubleshooting
The chart shows 80% as a column. Open Chart Design > Change Chart Type, choose Combo, and set the target series to Line on the secondary axis. Check the cumulative series there as well; it should also be a line on the secondary axis.
The target line is missing. Choose Chart Design > Select Data and confirm the 80% target series is listed and its values cover all category rows. If it is absent, add the D1:D7 range as a series. Make sure the series values contain numeric percentages, not text such as “80 percent,” and confirm the secondary axis maximum is 1 rather than a smaller value.
The line is flat at the bottom or top. The right axis may be using an inappropriate scale, or the target values may be entered as 80 instead of 0.8. Set its bounds to 0 and 1 and inspect the target cells. If the cells display 8000%, correct the underlying value to 80% and reapply percentage formatting.
The cumulative line ends below 100%. Check that the final formula includes every value in the total and that the copied running sum reaches the last data row. Look for blank, text, or negative entries that should not be part of a standard Pareto total. If categories were added, extend the denominator, formula fill, target series, and chart range together.
Excel will not combine the chart types. A built-in statistical Pareto chart may not support a custom line series in the way a regular combo chart does. Build the chart from the source table using the cumulative and target helper columns, then choose the combo chart type. This also makes it easier to set both percentage lines to the same secondary axis.
Frequently Asked Questions
Can I add an 80% line directly to Excel’s built-in Pareto chart?
Sometimes the chart’s available options depend on how it was created, and the built-in statistical chart may not allow the extra series to be combined as a line. The most dependable approach is to calculate the cumulative percentage and target in worksheet columns, then create a regular combo chart.
Why does the 80% line need a value in every row?
A line chart connects a sequence of points across the categories. Repeating 80% for each category gives Excel enough points to draw a horizontal reference across the plot area.
Should the 80% line use the secondary axis?
Yes. Put it on the same secondary axis as the cumulative percentage line. The bars can then use the primary count or value axis, while the percentage lines share a 0–100% scale.
Does the line show the category that reaches 80%?
The line marks the threshold; the cumulative series shows where it is reached. Find the first category whose cumulative percentage equals or exceeds 80%. If the line falls between two cumulative points, the next category is the first one that takes the total past the threshold.
Can I use a threshold other than 80%?
Yes. Replace the target value with the percentage you want, fill it down the target column, and update the series label. The chart type and axis settings do not need to change.
Will the reference line update when I change the data?
The line updates if its cells remain in the chart source and the target formulas or values are present for every category. The cumulative percentages recalculate when the source values change. If you add or remove categories, check the chart range and helper-column formulas.
Related
- How to Average Dates in Excel
- Hotel Laundry List Template Excel: Setup and Use
- Capitalize in Excel: Shortcuts and Reliable Alternatives
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Bubble Chart Different Colors: Set Each SeriesYes. In Excel for Microsoft 365, bubble-chart colors are usually assigned by data series: put each group you…
- Excel Pivot Table Instructions for Building Clear ReportsTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data…
- Excel Pivot Table: Filter and Sort AlphabeticallyYes. In Excel for Microsoft 365 on Windows, click a PivotTable row or column label, open its drop-down…
- How to Create a Waterfall Chart in ExcelTo create a waterfall chart in Excel, arrange your categories and positive or negative changes in two columns,…
- Excel Pie Chart Zero Labels: What Happens and How to Fix ItUsually, an Excel pie chart has no visible slice for a value of zero, so its label may…
- How to Put a Pivot Table in Google Sheets: Step by StepTo put a pivot table in Google Sheets, select a cell in your data, choose Insert > Pivot…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.