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?
1. Put your cursor on any cell in the data and Insert Chart
2. In the chart wizard, select “Custom Types”
3. Select Line-Column on 2 Axes
Then, click Next >
4. Select the data range (do not select the total in row 22)
5. Select – Series in Columns
Then, click Next >
6. Add the titles
7. Then, click the Legend Tab
8. Delete the legend, by unchecking the box
9. Then, click Data Labels
10. Click Value
11. Then, click Finish
Finally, we’ll make the Pareto Chart pretty. (Neatness counts)
1. Click the MRR data series
Then, click delete
2. Double click on the Cumulative % Series
3. Change the label position to Above
Then, click OK
Hint – if the labels are too big, or too small, go to the font tab and resize the font
Next - change the MRR Scale to thousands
4. Double click on the Y Axis
5. On the Scale tab, click Display units in Thousands
Then, Click OK
Change the Y Axes Scale to maximum of 100%
6. Double click on the second Y axis
7. Change the maximum to 1 (this is equal to 100%)
8. Then, Click Patterns
9. Click None on the Major Tick Mark Type
Then, click OK
Format the plot area (save toner by making the area white)
10. Double click the plot area by clicking on the little black box in the corner
11. Select None in Border and Area
Then, click OK
Change the color of the bars
12. Select all bars by double clicking on the series
13. On the patterns tab, select gray for the area.
Last step, format the chart area:
14. Double click on the chart area
15. On the patterns tab, click None for Border and None for Area
You now have a pareto chart that looks like this:
I saved this template in the BusinessToolsBlog Shared Files Google Group - become a member and download for free today.
Your data is ready, now you can build a Pareto Chart:
1. Put your cursor on any cell in the data and Insert Chart
2. In the chart wizard, select “Custom Types”
3. Select Line-Column on 2 Axes
Then, click Next >
4. Select the data range (do not select the total in row 22)
5. Select – Series in Columns
Then, click Next >
6. Add the titles
7. Then, click the Legend Tab
8. Delete the legend, by unchecking the box
9. Then, click Data Labels
10. Click Value
11. Then, click Finish
Finally, we’ll make the Pareto Chart pretty. (Neatness counts)
1. Click the MRR data series
Then, click delete
2. Double click on the Cumulative % Series
3. Change the label position to Above
Then, click OK
Hint – if the labels are too big, or too small, go to the font tab and resize the font
Next - change the MRR Scale to thousands
4. Double click on the Y Axis
5. On the Scale tab, click Display units in Thousands
Then, Click OK
Change the Y Axes Scale to maximum of 100%
6. Double click on the second Y axis
7. Change the maximum to 1 (this is equal to 100%)
8. Then, Click Patterns
9. Click None on the Major Tick Mark Type
Then, click OK
Format the plot area (save toner by making the area white)
10. Double click the plot area by clicking on the little black box in the corner
11. Select None in Border and Area
Then, click OK
Change the color of the bars
12. Select all bars by double clicking on the series
13. On the patterns tab, select gray for the area.
Last step, format the chart area:
14. Double click on the chart area
15. On the patterns tab, click None for Border and None for Area
You now have a pareto chart that looks like this:
I saved this template in the BusinessToolsBlog Shared Files Google Group - become a member and download for free today.
If you liked my post, feel free to subscribe to my rss feeds
BlogoSquare
2 Comments so far (Add 1 more)