A Pareto graph shows you which problems cause most of your trouble

A Pareto graph is a bar chart combined with a line graph that ranks your data from biggest to smallest, then shows you what percentage of the total each item represents. The name comes from the 80/20 rule — the idea that 80 percent of your results usually come from 20 percent of your causes. In file organization, a Pareto graph can show you that three file types take up 80 percent of your storage space, or that five folders contain most of your documents.

Excel does not have a built-in Pareto chart type in older versions, but newer versions (Excel 2016 and later on Windows, Excel 2015 and later on Mac) include it as a standard option. If you have an older version, you can build one by hand using a bar chart and a line graph layered together. Either way, the steps are straightforward and take about ten minutes once your data is ready.

Key Takeaways

  • A Pareto graph combines bars (showing amounts) with a line (showing cumulative percentage) to reveal which items matter most.
  • Excel 2016 and later have a built-in Pareto chart type under Insert > Charts > All Charts > Pareto.
  • For older Excel versions, you build a Pareto chart by creating a bar chart, calculating cumulative percentages in a helper column, and adding a secondary axis with a line graph.
  • Your data must be sorted from largest to smallest before you create the chart, or the ranking will be wrong.
  • A Pareto graph works best when you have between 5 and 15 categories — too few and the pattern is obvious, too many and the chart becomes hard to read.

Prepare your data in three columns

Before you build the chart, set up your spreadsheet with three columns: Category, Count, and Cumulative Percentage. In the Category column, list the items you are measuring — for example, file types (PDF, DOCX, XLSX, JPG, MP4) or folder names. In the Count column, put the number or size for each category. Leave the Cumulative Percentage column empty for now; you will fill it with a formula.

Sort your data from largest to smallest by the Count column. Click any cell in your data range, then go to Data > Sort. Choose the Count column, select Largest to Smallest, and click OK. This order is essential — if your bars are not ranked from left to right, the Pareto principle will not show up visually.

Once your data is sorted, add a formula in the Cumulative Percentage column. In the first row of that column, enter =B2/SUM($B$2:$B$6) (adjust the range to match your actual data). In the second row, enter =C2+B3/SUM($B$2:$B$6). Copy this second formula down to the rest of the rows. The result will be a decimal that represents the running total as a percentage of all counts — 0.45 means 45 percent, 0.80 means 80 percent, and so on.

Use the built-in Pareto chart in Excel 2016 or later

If you have a recent version of Excel, the fastest route is the built-in Pareto option. Select your data range including headers (Category and Count columns only — do not include Cumulative Percentage yet). Go to Insert > Charts. In the Charts group, click the small arrow next to the chart icons to open the full menu, then choose All Charts. Scroll down to find Pareto and click it. Excel will create the chart automatically, with bars on the left axis and a line on the right axis.

The chart may need adjustment. Right-click the line and choose Format Data Series. Under Series Options, make sure the line is plotted on the Secondary Axis — this keeps the percentages on the right side of the chart while the counts stay on the left. You can also change the chart title by clicking it and typing a new name, such as "File Storage by Type" or "Document Count by Folder".

Build a Pareto chart manually in older Excel versions

If you have Excel 2013 or earlier, you will create the chart in two steps: first a bar chart, then add a line on top. Select your Category and Count columns (not Cumulative Percentage). Go to Insert > Charts > Column Chart and choose the basic column type. Excel will create a bar chart with your categories on the bottom and counts on the left axis.

Now add the line. Right-click the chart and choose Select Data. Click Add. In the Series name field, type "Cumulative %". In the Series values field, select your Cumulative Percentage column (for example, D2:D6). Click OK twice. Your chart now has both bars and a line, but the line is probably scaled wrong — it will look flat because the percentages (0 to 1) are tiny compared to your counts.

Fix the scale by right-clicking the line itself and choosing Format Data Series. Under Series Options, check the box for Secondary Axis. This moves the line to its own axis on the right side of the chart, scaled from 0 to 1 (or 0 to 100 percent if you formatted the Cumulative Percentage column as a percentage). The bars and line will now be readable together.

Interpret the chart to find your 80/20 split

Once your chart is built, look for the point where the line crosses 80 percent on the right axis. Count how many bars are to the left of that point — those are your vital few categories. If three bars account for 80 percent of your file storage, those three folders or file types are where you should focus your cleanup effort. The remaining categories are the trivial many and usually not worth the time to organize.

This is where the Pareto graph earns its place in file management. Instead of spending equal time on every folder or file type, you can see at a glance which ones matter. If you are trying to free up storage space, deleting old videos (the tallest bar) will help far more than deleting old text files (a short bar). If you are trying to organize documents, focusing on the three largest categories will bring order to most of your files.

Adjust colors and labels for clarity

By default, Excel charts are functional but plain. You can make yours easier to read by changing the bar color and adding data labels. Right-click a bar and choose Format Data Series. Under Fill, select a solid color — blue or gray works well. Under Border, choose a thin line in a darker shade so the bars stand out.

To add labels showing the actual counts on each bar, right-click a bar again and choose Add Data Labels. The numbers will appear on top of each bar. You can also right-click the line and choose Add Data Labels to show the cumulative percentage at each point. These labels make it straightforward for someone else to read your chart without guessing at the axis values.

Common mistakes and how to avoid them

The most common error is forgetting to sort your data before creating the chart. If your bars are not in order from tallest to shortest, the chart will not show the Pareto principle and will be useless. Always sort by Count from largest to smallest first.

Another mistake is including too many categories. If you have 30 file types, a Pareto chart becomes cluttered and hard to read. If your data has many small categories, group them into "Other" — add up all the counts below a certain threshold and create one row called "Other" with that total. This keeps your chart focused on the vital few.

A third error is forgetting to put the line on a secondary axis. If both the bars and line use the same left axis, the line will be invisible or flat because percentages are much smaller numbers than counts. Always check that the line is on the Secondary Axis.

Frequently Asked Questions

Can I make a Pareto chart from file sizes instead of file counts?

Yes. Instead of counting how many files of each type you have, use the total size in megabytes or gigabytes. The process is identical — sort by size from largest to smallest, calculate cumulative percentages, and build the chart. This is often more useful for storage cleanup because a few large video files may take up more space than hundreds of small text files.

What if my data has negative numbers?

Pareto charts assume all values are positive. If you are measuring profit and loss by category, convert losses to positive numbers or exclude them from the chart. A Pareto graph is designed to show which items contribute most to a total, not to handle mixed positive and negative values.

How do I update the chart if my data changes?

If you add new rows or change the counts, the chart will update automatically as long as the new data is within the original range you selected. If you add rows below your original data, you will need to edit the chart's data range. Right-click the chart, choose Select Data, and adjust the range to include the new rows.

Is there a way to make the line show percentages instead of decimals?

Yes. Format your Cumulative Percentage column as a percentage before creating the chart. Select the column, right-click, choose Format Cells, and select Percentage. The line will then show 45% instead of 0.45. This makes the chart easier to read at a glance.