Creating the Pie Chart

In this example, we are going to create a pie chart based on the data for one product category. The chart will show the monthly sales for the category. Then we'll explore options to reuse the code and automate the creation of pie charts for each product line.

1. Select the Sales By Category worksheet.

2. Create a new macro and name it MakePieChart.

3. Select the data range that contains the data for the Baked Goods & Mixes category (A2:C5), as shown in Figure 5-18.

A

B

c

Category

Month

Quantity

Baked Goods & Mixes

Jan

30

Baked Goods & Mixes

Feb

20

Baked Goods & Mixes

Mar

10

Baked Goods & Mixes

Apr

45

Figure 5-18. Selection for pie chart

4. On the Insert ribbon, select Pie from the Charts section, as shown in Figure 5-19.

4. On the Insert ribbon, select Pie from the Charts section, as shown in Figure 5-19.

Figure 5-19. Selecting a 2D pie chart from the ribbon

The pie chart is displayed, but as Figure 5-20 shows, it is not exactly what we might have expected. Excel combined the first two columns of data and created the legend from them. The data itself is fine. With a couple of quick adjustments, we will modify the legend to show the month name only, and we'll add a title to the chart showing the product category.

How Rotate Axis Title Excel
Figure 5-20. The new pie chart as created

5. With the Macro Recorder still running, select the pie chart if it's not already selected.

6. Go to Chart Tools > Design ribbon, and choose the Select Data command, as shown in Figure 5-21.

Figure 5-21. The Select Data command

Figure 5-21. The Select Data command

7. The Select Data Source dialog box will appear, as shown in Figure 5-22.

Figure 5-22. The Select Data Source dialog box

The Select Data Source dialog box contains functions to set the data range for the chart, to switch row/column orientation, to assign a range that contains the data values for the chart series, and to assign a range that contains the legend information.

We see in Figure 5-22 that the Chart data range, ='Sales By Category'!$A$2:$C$5, is correct, and we do not want to switch the row/column orientation. We need to correct the legend information display, and we want to use the category information to add a title to the chart.

8. In the Legend Entries (Series) section, at the bottom left of the Select Data Source dialog box, select Series 1 from the list.

9. Click the Edit button to display the Edit Series dialog box (shown in Figure 5-23).

Figure 5-23. The Edit Series dialog box

10. To add the title, in the Series name text box, type ='Sales By Category'!$A$2 (or use the range selector to navigate to cell A2 and let Excel insert the range reference for you). Figure 5-24 shows the Edit Series dialog box with this value entered.

Edit Series

yrtJ

Series name:

='Sales By Category'ISA52

isl

= Baked Goods ft...

Series values:

='Sales By Category'!SC|$2:5C55

lm\

= 30, 20, 10, 45

E

OK

| Cancel

Figure 5-24. Series name range reference added to the Edit Series dialog box

11. Click OK to store the range reference.

12. In the Horizontal (Category) Axis Labels section, at the bottom right of the Select Data Source dialog box, click the Edit button to show the Axis Labels dialog box, as shown in Figure 5-25 (no selection is necessary).

l^'Sales By Category'!5A52:5B$5 |eS] = Baked Goods ft ...

Figure 5-25. The Axis Labels dialog box

13. In the "Axis label range" text box, type in ='Sales By Category'!$B$2:$B$5 to tell Excel to show only the month names in the legend (or use the range selector to select cells B2:B5 and let Excel insert the range reference for you).

14. Click OK to store the range reference.

The Select Data Source dialog box should look like Figure 5-26.

Figure 5-26. The Select Data Source dialog after edits

15. Click OK to close the Select Data Source dialog box and save the changes to the chart.

16. Stop the Macro Recorder.

Figure 5-27 shows the updated chart with the category as the chart title and the month names for the legend.

Figure 5-27. The updated pie chart
0 0

Responses

Post a comment