Yes—an Excel Pareto chart can use words such as “Late delivery” or “Damaged item” as category labels, with a numeric count for each category. For summarized data, put the category names in one column and counts in the next, sort counts from largest to smallest, then build a column-and-line combo chart with a cumulative-percentage line; Excel’s built-in Pareto chart is another option for a raw list of category observations.
What “with words” means in a Pareto chart
A Pareto chart is a ranked bar chart combined with a cumulative-percentage line. The words identify the categories along the horizontal axis, the bars show the number of observations in each category, and the line shows how much of the overall total has accumulated as you move from left to right. For example, a support team might compare “Login issue,” “Billing question,” and “Slow response.” The labels are words, but the bar heights still need to be numeric.
People sometimes expect Excel to turn sentences or comments into a meaningful Pareto chart automatically. It does not determine the causes or decide how similar phrases should be grouped. You need to classify the observations consistently and count them first. If one row says “package arrived late” and another says “late shipment,” decide whether both belong in a “Late delivery” category before charting.
Choose raw observations or a summary table
There are two useful starting points. A raw list has one category entry per observation—for example, one column containing each recorded complaint. A summary table has one row per category and a count beside it. The built-in Pareto chart is convenient for raw observations when Excel recognizes the entries as categories. A summary table gives you more control over wording, ordering, and percentage calculations, so it is a dependable choice when preparing a report.
- Raw observations: Use one column of repeated category names, such as “Late delivery,” “Wrong item,” and “Damaged item.”
- Summarized counts: Use one column for category names and another for counts, such as “Late delivery” and 12.
- Free-text comments: Classify the comments into consistent categories before counting them. A chart of every unique sentence is usually difficult to interpret.
Do not include a grand-total row as if it were another category. It would be charted as an extra bar and distort the cumulative percentages.
Create a Pareto chart from a raw list
In Excel for Microsoft 365 on Windows, place a heading above the category list, select the heading and the observations, then choose Insert > Insert Statistic Chart > Pareto. Excel creates a chart with category frequencies and a cumulative-percentage line. The category names become the chart’s labels; repeated entries contribute to the frequency for that category. If Excel interprets the selection unexpectedly, or the chart does not show the word categories as intended, use the summarized-table method below instead.
For a useful raw list, check that categories are spelled and named consistently. “Late delivery,” “Late deliveries,” and “Delivery late” may be counted as three separate labels even if you meant them to represent the same issue. Clean or standardize the source entries first. You can also create a separate cleaned category column rather than changing the original records, which preserves the original wording for reference.
To change the chart title, click it and type a descriptive name, such as “Customer complaints by category.” To make category names visible on the bars as well as the horizontal axis, select the chart, use the chart’s Chart Elements (+) button, and add Data Labels. In the Format Data Labels pane, use Label Options to choose which information to show. If labels become crowded, keep the category words on the axis and show numbers on the bars instead.
Build a controlled chart from category counts
Suppose a service team has these counts: Late delivery 12, Wrong item 8, Damaged item 5, Missing parts 3, and Other 2. Put category names in A2:A6 and counts in B2:B6, with headings in A1 and B1. Sort the rows by count in descending order using Data > Sort, choosing the count column as the sort field and largest to smallest. Keep each name paired with its count when sorting.
Add a cumulative-count heading in C1 and a cumulative-percentage heading in D1. With the first count in B2 and all five counts in B2:B6, enter this in C2 and fill it down through C6:
=SUM($B$2:B2)
In D2, with counts still in B2:B6 and the running count in C2, enter this formula and fill it down through D6:
=C2/SUM($B$2:$B$6)
Format D2:D6 as Percentage using Home > Number. The total is 30. The cumulative counts are 12, 20, 25, 28, and 30, so the cumulative percentages are 40%, 66.7%, 83.3%, 93.3%, and 100%. These results provide a quick check that the formula references and data are behaving as expected.
Select the category names and the count and cumulative-percentage columns. In Windows, hold Ctrl while selecting non-adjacent ranges; alternatively, arrange the columns next to each other and select the full table. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set the count series to Clustered Column, the cumulative-percentage series to Line, and select Secondary Axis for the percentage series. Click OK. The precise labels in the dialog can vary slightly across Excel builds, but the key setup is columns for counts and a line on a secondary axis for percentages.
Make the word labels readable
Category names are often longer than the chart area can comfortably display. Widen the chart, shorten labels without changing their meaning, or rotate the horizontal-axis text. To adjust the axis, click the category labels, open Format Axis, and use its text or alignment options. The location of text-orientation controls can differ slightly with chart selection and Excel updates. Another practical option is to use concise labels on the chart and explain abbreviations in a note below it.
If you want words printed directly on the bars, add data labels and select the category name in Format Data Labels > Label Options, where that option is available for the selected chart. Showing both category names and values on every bar can create clutter, especially with many categories. Often the clearest design is category names on the axis, counts on the bars, and percentages on the right-hand axis.
Give the axes clear titles where useful: the left axis is the number of observations, and the right axis is cumulative percentage. Check that the percentage axis runs from 0% to 100%; an automatic scale can otherwise make the line seem more dramatic than it is. Avoid shrinking the chart text until it is unreadable just to fit every long label.
What the chart can and cannot tell you
The bars rank the categories by frequency, and the cumulative line shows how quickly the total builds. In the example, the first three categories account for 25 of 30 observations, or 83.3%. That helps a team focus investigation on a smaller set of frequent issues. It does not prove that those issues have the largest financial cost, the most severe consequences, or the easiest fixes. A count-based Pareto chart answers a frequency question; a cost- or severity-based analysis needs a different measure.
The familiar “80/20 rule” is a rough observation, not a promise that exactly 80% of outcomes will come from exactly 20% of causes. Your cumulative line may reach 80% after one category, several categories, or not follow a neat split. Use the actual data rather than forcing the chart to fit a rule of thumb.
Common problems and fixes
- Bars are not in descending order: Sort the category-count table by counts, largest to smallest, before making the manual combo chart. Do not sort only the names or only the numbers.
- The words appear as a series or not as labels: Check the selected range and confirm the category column is text and the count column is numeric. For a summary table, use the combo-chart method and verify the category axis range if needed.
- The cumulative line is not a percentage: Confirm that the formula divides each running count by the total count, and format the results as percentages. Plot that percentage column rather than the running-count column.
- The line looks flat or the scale is confusing: Put the cumulative-percentage series on the secondary axis and format the axis as a percentage from 0% to 100%.
- Some categories seem missing: Look for blank cells, inconsistent spelling, accidental spaces, or filters that hide rows. Similar-looking text entries can be separate categories.
- Labels overlap: Shorten the displayed names, enlarge the chart, rotate labels, or show fewer categories and combine the remainder as “Other” when that grouping makes sense for the analysis.
Built-in Pareto versus a manual combo chart
The built-in Pareto chart is quicker when you have a clean raw list and want Excel to count and rank its categories. A manually constructed combo chart takes more preparation, but it is easier to audit and customize when you already have summarized counts, want specific category wording, or need to control how “Other” is handled. It also makes the cumulative-percentage calculation visible in worksheet cells, which can be useful when someone needs to verify the figures.
For a summary table, a regular column chart alone is not a Pareto chart because it lacks the cumulative line. Conversely, a pie chart may show category shares but does not provide the same ranked bars and cumulative progression. Choose the chart that matches the question: ranked frequency and cumulative contribution call for a Pareto-style chart.
Frequently Asked Questions
Can Pareto chart labels be words instead of numbers?
Yes. Use text category names for the horizontal-axis labels and numeric counts for the bar values. The words identify what each bar represents; they do not replace the data values.
Can I make a Pareto chart from a two-column table?
Yes. Put each category name beside its count, sort the rows by count from largest to smallest, calculate cumulative percentages, and create a combo chart with columns and a line on a secondary axis. This manual approach is especially useful for summarized data.
How do I put category names on the bars?
Select the chart, add Data Labels, and look in the Format Data Labels pane under Label Options for the category-name option. If the labels overlap, keep names on the axis and use bar labels for counts.
Why does my cumulative line not end at 100%?
Check that the final cumulative count includes every category and that each percentage divides by the full total. Also confirm that the plotted range includes the last cumulative-percentage cell and that no category rows were omitted.
Is the 80/20 rule required for a Pareto chart?
No. It is a rule of thumb, not a condition for the chart. A valid Pareto chart ranks categories and shows cumulative contribution even when the data does not produce an 80/20 split.
Related
- Merge Columns in Excel with Power Query
- Meal Planning Excel Sheet: Build a Weekly Planner
- Meal Planning Template Excel: Free Options and Setup
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- A-Level Economics Graphs: Key Diagrams and How to Use ThemA-level economics graphs are diagrams that show how economic variables relate, such as how a change in price…
- Excel Trendline Equation Without a ChartYes. In Excel for Microsoft 365 on Windows, calculate a linear trendline without creating a chart by using…
- Excel Histogram: Easy Steps to Create and Format OneTo make an Excel histogram, put your numeric observations in one column, select the data, then choose Insert…
- Excel Waterfall Charts: How to Create and Format ThemIn Microsoft Excel for Microsoft 365, select a table of categories and values, then choose Insert > Insert…
- Gantt Chart Excel Free Template XLS: How to Use OneYes: you can use a free Gantt chart template in Excel, but many current downloads are in the…
- Excel Pivot Table zh: Chinese Terms and SetupIn an Excel search, “zh” usually means Chinese: it is the two-letter language code commonly used for Chinese,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.