Need to make a Pareto chart in Excel the easy way? The built-in Pareto chart type does nearly all the work, including sorting the bars and drawing the cumulative percentage line. Two columns of data and a few clicks are all it takes.
Key Takeaways
- Excel has a built-in Pareto chart type under Insert Statistic Chart, so no manual sorting or helper formulas are needed.
- Only two columns are required: a category column and a count or value column.
- Excel sorts the bars from largest to smallest and adds the cumulative percentage line automatically.
- The chart type is available in Excel 2016 and later, including Microsoft 365, Excel 2019, 2021 and 2024.
- Titles, labels, colors and bar spacing can be adjusted in a few clicks to make the chart presentation-ready.
Step-by-Step Guide to Make a Pareto Chart in Excel the Easy Way
Prepare the Data
- Step 1: Open the workbook and enter the data in two adjacent columns. Put the category names in the first column (for example, Late delivery, Wrong item, Damaged package) and the matching counts in the second column (for example, 48, 31, 12).
- Step 2: Add a header to each column, such as Issue and Count. Headers make the chart labels clearer later.
- Step 3: Leave the rows in any order. There is no need to sort them from largest to smallest, because the Pareto chart does the sorting on its own.
Tip: Keep the table free of blank rows and total rows. A grand total included in the selection will show up as its own giant bar and throw the whole chart off.
Insert the Pareto Chart
- Step 4: Click any cell inside the data range, or drag to select both columns including the headers.
- Step 5: Go to the Insert tab on the ribbon.
- Step 6: In the Charts group, click the Insert Statistic Chart icon. It looks like a small histogram and sits near the Waterfall and Box and Whisker options.
- Step 7: Under Histogram, choose Pareto.
- Step 8: The chart appears on the sheet with bars sorted from tallest to shortest and a line showing the cumulative percentage on a secondary axis.
Clean Up and Format the Chart
- Step 9: Click the chart title and type a descriptive name, such as Customer Complaints by Type.
- Step 10: With the chart selected, click the plus icon at the top right of the chart (Chart Elements) and tick Data Labels to display the count above each bar.
- Step 11: Right-click any bar and choose Format Data Series. In the Series Options pane, drag the Gap Width slider to the left to make the bars thicker and closer together, which is the classic Pareto look.
- Step 12: Use the Chart Design tab to switch color schemes or apply a different chart style, then click Add Chart Element to add axis titles if needed.
Tip: The first two or three bars usually tell the story. Read across to the cumulative percentage line to see how much of the total those top categories account for, which is the whole point of the 80/20 view.
Use a Single Column of Raw Entries
Sometimes the data is a long list of text entries rather than a summary table, such as a log with one defect name per row. In that case, select just that single column and insert the Pareto chart the same way. Excel counts how many times each entry appears and builds the bars from those counts.
Common Problems and Fixes
The Pareto Option Is Missing from the Ribbon
The Pareto chart type is not available in Excel 2013 or earlier. Update to Excel 2016 or later, or Microsoft 365, to see it under Insert Statistic Chart.
The Bars Are Not Sorted
The built-in Pareto chart sorts automatically. If the bars look unsorted, the chart is usually a regular column chart. Delete it and insert the chart again through Insert Statistic Chart and Pareto.
The Cumulative Percentage Line Is Not Showing
Click the chart and check that the line is not hidden behind a large chart style or formatting change. Resetting the chart style on the Chart Design tab usually brings it back. Inserting the chart again from the Pareto option also restores it.
The Chart Shows One Very Tall Bar for Everything
This happens when a total row was included in the selection. Select only the category rows and their counts, then insert the chart again.
Categories Are Counted Instead of Summed
When only a single text column is selected, Excel counts occurrences. To chart existing totals, make sure the selection includes both the category column and the number column.
Frequently Asked Questions
What is the easiest way to make a Pareto chart in Excel?
Enter the categories and their counts in two columns, select the data, then go to Insert, click Insert Statistic Chart and choose Pareto. Excel sorts the bars and adds the cumulative percentage line automatically.
Which versions of Excel have the built-in Pareto chart?
The Pareto chart type is included in Excel 2016 and later, including Excel 2019, Excel 2021, Excel 2024 and Microsoft 365. It is not available in Excel 2013 or earlier.
Do the data rows need to be sorted before creating the chart?
No. The Pareto chart sorts the categories from largest to smallest on its own, so the source data can stay in any order.
Can a Pareto chart be made from a single column of data?
Yes. If a single column of repeated text entries is selected, Excel counts how often each entry appears and plots those counts as the bars.
How can the cumulative percentage line be seen clearly?
The line is plotted on a secondary axis on the right side of the chart. Adding data labels and keeping the chart large enough on the sheet makes the line and its values easier to read.
How do the bars get thicker or thinner?
Right-click any bar, choose Format Data Series, and adjust the Gap Width slider in the Series Options pane. A smaller gap width makes the bars wider.
How can data labels be added to the bars?
Select the chart, click the plus icon at the top right (Chart Elements), and tick Data Labels. The count then appears above each bar.
Why is the Pareto chart not showing the right categories?
The selection probably included a total row, a blank row or the wrong columns. Reselect only the category and value columns, then insert the chart again.
Can the chart title and colors be changed?
Yes. Click the chart title to rename it, and use the Chart Design tab to apply a different color scheme or chart style.
Can the finished chart be copied into Word or PowerPoint?
Yes. Click the chart border, press Ctrl+C, then paste it into a Word document or PowerPoint slide with Ctrl+V.
