If a Pareto chart is not working in Excel, first check whether the chart is getting the kind of data it expects: Excel’s built-in Pareto chart is best suited to a column of individual observations that it can count. For a table that already contains category names and totals, make a Pareto chart manually with a sorted column series and a cumulative-percentage line; that approach gives you more control and avoids many common charting problems.
What a Pareto chart should show
A Pareto chart combines bars with a line. The bars represent categories, ordered from the largest count to the smallest, and the line shows the running cumulative share of the total. For example, if a team records 40 late deliveries, 25 damaged packages, 20 incorrect items, 10 billing errors, and 5 other complaints, the first bar is 40 and the first point on the line is 40% of all complaints. The line reaches 100% at the final category.
The chart is often used to identify which causes contribute most to a measured problem. It does not prove that a particular cause produced an outcome, and it does not guarantee that a fixed 80% of a problem comes from 20% of causes. The familiar 80/20 idea is a heuristic, not a rule the chart must satisfy. If your bars are not descending or the line does not accumulate toward 100%, check the data setup before interpreting the result.
Check whether your source data is raw or summarized
This distinction explains many charts that look blank, oddly grouped, or unlike a Pareto chart. A raw list records one observation per row—for instance, a column containing the reason for every customer complaint. Excel can count how often each text category appears and arrange those counts. A summarized table already has one row per category and a count beside it. The built-in statistical chart may not treat that table as you intend, because it is designed to analyze the observations rather than simply use a precomputed frequency table.
For a raw list, put a clear header above the category column, keep one recorded item in each row, and avoid selecting unrelated columns or a grand-total row. In Excel for Microsoft 365 on Windows, select the data and choose Insert > Insert Statistic Chart > Pareto. If the chart counts unexpected items, inspect the selected range and the text values. Extra spaces, inconsistent labels such as Late and Late delivery, or blank entries can produce categories that are not meaningful for your analysis.
For a summarized table, use a manual combo chart as described below. It is usually the clearest option when you already have counts, want to combine several data sources, or need to control how categories are grouped. Do not include a total row as though it were another category: that makes the largest bar represent the total and distorts the cumulative line.
Repair the chart with a manual setup
Suppose a worksheet has defect names in A2:A6 and counts in B2:B6: Scratches 40, Missing parts 25, Wrong label 20, Late arrival 10, and Other 5. First sort the five category rows by count, largest to smallest. Then add a cumulative percentage column. The total is 100, so the cumulative values should be 40%, 65%, 85%, 95%, and 100%.
With category names in A2:A6 and counts in B2:B6, enter this formula in C2 and fill it down through C6: =SUM($B$2:B2)/SUM($B$2:$B$6). The first row returns 40%; filling down returns 65%, 85%, 95%, and 100%. Format C2:C6 as Percentage. The dollar signs keep the full count range fixed as the formula is copied, while the ending row of the first SUM expands one row at a time.
- Arrange the worksheet columns as Category, Count, and Cumulative %. Keep the header row, but do not include a separate total row in the chart range.
- Select A1:C6, then choose Insert > Insert Combo Chart > Create Custom Combo Chart. If the chart is already present, select it and choose Chart Design > Change Chart Type > Combo.
- Set Count to a Clustered Column chart and Cumulative % to a Line chart. Select the Secondary Axis option for the cumulative-percentage series, then confirm.
- Check that the left axis displays counts and the right axis displays percentages. If necessary, format the right axis bounds from 0 to 1, which represents 0% to 100% for a percentage-formatted series.
The resulting chart should have descending bars and a line that never falls as it moves from left to right, ending at 100%. If you add or remove categories, extend the chart range and copy the cumulative formula into the new rows. For a changing report, consider converting the source into an Excel table with Home > Format as Table, then verify that the chart’s data range includes the new rows.
Fix bars that are out of order
A Pareto chart’s defining feature is descending bar size. In a manually built chart, Excel does not necessarily reorder categories for you; sort the source table by the Count column from largest to smallest. Select the complete category-and-count rows before sorting, so names stay attached to their values. Choose Data > Sort, select Count as the sort column, and choose Largest to Smallest. The cumulative formula should then recalculate in the new order.
In a native Pareto chart based on raw observations, Excel calculates and sorts category frequencies. If you are looking at a standard column chart instead, it will not acquire Pareto ordering just because the data was selected from a frequency table. Change the chart type or build the manual combo chart. Also confirm that no extra series, such as a target or total, is being mistaken for the frequency bars.
Fix a missing or misleading cumulative line
A line that is invisible or flat is often an axis-scale issue. Counts may range from 5 to 40 while percentages range from 0 to 1; plotting both on the same axis can compress the percentage series near the bottom. Put the cumulative series on a secondary axis. If the right axis shows values such as 0.4 and 1.0, format it as Percentage. If it is scaled in a confusing range, use Format Axis to set suitable bounds, such as 0 and 1 when the underlying values are decimal percentages.
Check the formula if the line stops below 100%. The final cumulative value should use all category counts in the denominator, and the displayed chart range must include the last category. A total row accidentally included in the cumulative series can also produce a wrong line. If the last value exceeds 100% or the line decreases, look for a denominator that changes as the formula is copied, an unsorted or mismatched range, or manually entered percentages that do not match the counts.
Review labels, blanks, and category definitions
Long category names may overlap or become difficult to read. Widen the chart, reduce label font size, or shorten labels in a separate display column while preserving the full descriptions in the source data. Rotating category labels can help, but it is usually better to show fewer, well-defined categories than to squeeze a long list into a small chart.
Decide in advance how to treat missing or rare categories. A blank is not a useful cause label, and several different descriptions of the same issue should be standardized before counting. If you combine small categories into Other, add their counts together before calculating the cumulative percentages. Make that grouping rule visible in the chart or a note; otherwise, readers may assume Other is a single observed cause.
For a fair comparison, categories should represent the same kind of event and the counts should cover the same time period or population. A chart of raw complaint counts may make a high-volume product look worse simply because more units were sold. If the question concerns rates, calculate a suitable rate using an appropriate denominator before comparing categories, and explain what that denominator represents.
Version and platform differences
The built-in statistical Pareto chart is available in modern desktop Excel, including Microsoft 365 for Windows. If the Pareto option is missing, check that you are using a recent desktop version and that your selection is suitable. Excel for the web and different desktop releases may not offer the same chart-creation or formatting controls. When a command or setting is unavailable in the browser, open the workbook in desktop Excel and use the manual combo-chart method if needed.
On Excel for Mac, the ribbon layout and wording can differ from Windows, but the same underlying approach applies: insert a Pareto statistical chart for raw observations, or create a combo chart with count columns and a cumulative-percentage line for summarized data. Keyboard shortcuts also differ between platforms, so use the visible ribbon commands rather than relying on a Windows shortcut when working on a Mac. If a chart behaves differently after opening on another platform, check the selected data range and series types in the chart settings.
Frequently Asked Questions
Why does Excel show a histogram instead of category names?
A statistical Pareto chart is related to Excel’s histogram tools, and it may interpret numeric input as values to group into bins. For named categories with counts already summarized, make a combo chart from the category, count, and cumulative-percentage columns instead.
Why are my Pareto bars not sorted from largest to smallest?
For a manual chart, sort the entire source table by the count column in descending order. For a native chart, make sure you inserted the Pareto chart rather than a regular column chart and selected only the raw observations you meant to count.
Does a Pareto chart have to show an 80/20 split?
No. The chart ranks contributions and shows their cumulative share; it does not force a particular distribution. Your line may reach 80% after one category, many categories, or not in a pattern close to 20% of the causes.
Why does the cumulative percentage line not reach 100%?
Check that the final category is included, the formula denominator covers every count, and the chart includes the last cumulative-percentage cell. Also make sure a total row has not been included as an extra category.
Can I make a Pareto chart from a pivot table?
Yes, but a regular combo chart based on the pivot table’s category counts is often easier to control. Keep the categories sorted by count, calculate or display a running percentage, and confirm that refreshes do not change the chart’s intended data range or ordering.
Related
- Microsoft Excel vs Apple Numbers: Which Should You Use?
- Microsoft Excel Transpose Table: Methods, Limits and Tips
- Microsoft Excel Time Tracking Template: Setup and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline Intercept: Meaning and How to Set ItIn Excel, a trendline intercept is the estimated y-value where the trendline crosses the vertical axis, which is…
- Excel Horizontal Bullet Chart: Build One Step by StepTo make a horizontal bullet chart in Excel for Microsoft 365 on Windows, create a stacked bar chart…
- Financial Dashboard in Excel: Build, Track, and AnalyzeA financial dashboard in Excel is a workbook page that summarizes important financial measures—such as revenue, expenses, profit,…
- How to Flip an Axis in an Excel Chart: Step-by-Step GuideTo flip an axis in an Excel chart, select the axis, right-click it, choose Format Axis, and turn…
- Excel Line Chart in a Cell: Create and Format SparklinesTo put a line chart inside an Excel cell, insert a Line Sparkline: select the destination cell, choose…
- Google Sheets Chart Trendlines: Add and Customize OneTo add a trendline in Google Sheets, open the chart editor by double-clicking your chart, choose Customize >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.