Summarizing with Pie Charts

In Chart01.xlsm, select the Sales By Category worksheet. Here you'll see a list of product categories with sales quantities by month, as in Figure 5-9.

Figure 5-9. The Sales By Category worksheet

This data provides us with a great format to display each category in a pie chart to see how overall sales looked by month for each product line. Before you begin charting data like this, it's a good idea to make sure the data is sorted correctly to make your selections for charting easier.

1. Put the cursor anywhere in the data table on the Sales By Category worksheet.

2. On the Data ribbon, choose the Sort command, as shown in Figure 5-10.

Figure 5-10. The Sort command on the Data ribbon

The Sort dialog box appears, as shown in Figure 5-11.

The Sort dialog box appears, as shown in Figure 5-11.

Figure 5-11. The Sort dialog box

In this case, Excel made a guess that we want to sort by the Month column (and we are going to override this).We want to sort by Category first, and then by Month.

Note As I was testing this code, I had various results in what Excel decided would be the "Sort by" column. These results ranged from Month, as shown in Figure 5-11, to Category, to a blank value. Your results may vary.

3. Choose Category from the "Sort by" list under the Column listing, as shown in Figure 5-12.

% Add Level >< Delete Level ] [ ^ Copy Level |

-lï ^ J Options... J 0 My data has headers

Column 1 Sort On I Order

Sort by

Month (V

1 Values [ggj]| J Jan, Feb, Mar, Apr, May, Jun, Ju[^j|

Month Quantity

OK j [ Cancel

Figure 5-12. Choosing Category as the first sort field 4. Choose A-Z from the Order drop-down list, as shown in Figure 5-13.

Figure 5-13. Choosing the sort order for Category

5. Click the Add Level button on the Sort dialog box to add a new blank sort item to the sort list, as shown in Figure 5-14.

Figure 5-14. New item added to the sort list 6. In the "Then by" drop-down list, select Month, as shown is Figure 5-15.

% Add Level >< Delete Level ^ Copy Level ] [ ifr ] & [ Options... ] 0 My data has headers

Column | Sort On I Order

Sort by Then by

Category

¡Values Jan, Febr Marr Aprr May, lun, lu v"

S

1 Values Q |AfaZ [v]|

Category

Monte

Quantity Kt

OK ] [ Cancel

Figure 5-15. Adding Month to the sort list

7. Select Custom List from the Order drop-down, as in Figure 5-16. If we choose either alpha sort option, the months will sort alphabetically by name rather than ascending or descending order by month.

Add Level j | X Delete Level 11 t^ Copy Level 4 Options... | 0 My data has headers

Column Sort On \ Order

Sort by j category [v]| ¡Values [v]|

Jan, Feb, Marr Aprr Mayr Jun, lu v"

"Ttienby | g]| |Va|ues g|

A to Z jv]

CustomLlst...

1 OK j 1 Cancel

Figure 5-16. Choosing Custom List

8. The Custom Lists dialog box will appear. Choose the item labeled Jan, Feb, Mar, and so on, as shown in Figure 5-17.

Figure 5-17. Custom Lists dialog box

9. Click OK to return to the Sort dialog box. 10. Click OK to close the Sort dialog box and sort the data. The data should now look like that in Figure 5-9.

0 0

Post a comment