6.3 Pareto Charts

A Pareto chart is a bar graph with the bars arranged from the tallest bar to the shortest bar. Because of that, we need first to put the data in descending order.

6.3.1 Sorting Data

  1. Highlight the cells A1:B6 again.
  2. Copy (CTRL + C) the data, click on cell E1 (or any empty cells of the worksheet), and Paste (CTRL + V) the data.
  3. Highlight the cells E1:F6 (or the cells that contain the copy of the data). Select Custom Sort in the Sort & Filter ribbon in the Home tab in the menu bar.
  4. In the Sort dialog window that appears. Select the following options:
    • Sort by -> Number (in thousands)
    • Sort on -> Values
    • Order -> Largest to Smallest

This will sort both highlighted columns concerning the highest frequencies.

  1. Check the box to the left on My list has headers. Click OK.
A screenshot of the custom sort options.

Figure 6.4: Custom sort options..

6.3.2 Building the Pareto Chart

  1. Select the cells E1:F6 (or the cells containing the copy of the data) that have been sorted.
  2. Go to Insert > Column chart and choose the leftmost top type.
  3. Change the title of the graph to Earned Degrees conferred in 2000.
  4. With the graph selected, go to the Chart Design tab.
  5. To add axis titles, click Add Chart Element > Axis Titles > Primary Horizontal and then Add Chart Element > Axis Titles > Primary Vertical.
  6. Replace the horizontal axis title with the Type of Degree.
  7. Replace the vertical axis title with In Thousands.

6.3.3 Practice 2

Use Excel to build a Pareto chart for the data containing Country in the Lab 6 Netflix Sample.xlsx file.