Business Tools Blog

Don’t panic. You don’t need to be an econ major to create this simple chart. 

When we ask for a Pareto chart, we are asking you to provide a visual depiction of the most important information. A Pareto chart makes it easy to figure out the 20% that matters.

Example – This is a pareto chart of a Sales Funnel

When your team reviews this chart, everyone knows which opportunities are the biggest. This should provide a clear direction on the most important opportunities.  

How do I create this chart in Excel?

Start by sorting the data from the highest dollar opportunities to the lowest dollar opportunities. Put your cursor somewhere in the data, then,

1. Click the toolbar and select Data
2. Then Sort

3. Select Revenue (this is the column that you want to sort)
4. Descending
5. “OK”

Next, you’ll need to calculate the cumulative percentage.

6. In Cell C2, divide Customer 1’s Revenue (B6) by the Total Revenue (B22)
Trick – by adding a $ sign before the B and before the 22 ($B$22) you will be able to copy this formula and it will always divide by the total in cell (B22).

7a. In cell C3, you will calculate Customer 2’s % of revenue (B3/$B$22) and add it to Cust. 1’s % of revenue (C2) (B3/$B$22+C2)

7b. Then you will copy the formula in cell (C3) to the remaining cells – thru C21. The total in C21 should equal 100%.
Trick: double click on the little black box in the bottom right corner of cell C3 and the formula will copy down to cell C21 in one step.

Now that your data is ready, I will show you how to build the graph.  (check out tomorrow’s post)

If you can’t wait … here is a link to other sites with directions: Quality Toolbook.