Getting Started

As with many of the previous features we've explored, we'll manually create a few different charts and record macros to take a look at some of the chart object properties and methods. Then we'll write our own code to create charts for our users.

1. In the Download section for this book on the Apress web site, find the file named Chart01.xlsx and open the workbook.

2. Since we know we'll be inserting code into this workbook, let's save it in the macro-enabled format, as Chart01.xlsm.

3. Activate the Monthly Total Sales Amount worksheet.

The Chart01.xlsm file shown in Figure 5-1 contains three worksheets containing North-wind sales data, including sales by category, sales amounts by product, and total sales for products in the beverage product line.

4. On the Developer ribbon, choose Record Macro from the Code section.

5. Name the Macro MakeBeverageSalesChart, as shown in Figure 5-2.

6. Click OK to run the Macro Recorder.

7. Select the data in cells A1:E7, as in Figure 5-3.

Figure 5-1. Northwind sales data on the Monthly Total Sales Amount worksheet
Figure 5-2. Recording the MakeBeverageSalesChart macro
Figure 5-3. Data selected for charting

Note You'll notice that we did not select the row or column containing the total sales amounts. Excel will include them in the chart, which will throw our vertical (value) axis amounts off.

8. On the Insert Ribbon, go to the Charts section, and click the Column chart type dropdown list to display the many column chart types available to you.

9. In the 3-D Column section, choose the first (leftmost) item, the 3-D Clustered Column chart type, as shown in Figure 5-4.

Column Selectivity Chart
Figure 5-4. Column chart type selection menu

10. Stop the Macro Recorder.

The new chart is inserted in the worksheet. Notice in Figure 5-5 that Excel has highlighted the data ranges associated with each chart element (legend, values, and horizontal axis label ranges).

Figure 5-5. The Beverage sales chart

In addition to inserting the chart, Excel also added a context ribbon. Context ribbons provide commands relative to the currently selected object. In this case it's a chart, but it could be an inserted image or any other object that can be acted upon. Context ribbons are noted by a title bar above the ribbon area. Figure 5-6 shows the Chart Tools context ribbon. It contains three of its own ribbons: Design, Layout, and Format.

Figure 5-6. The Chart Tools context ribbon

Figure 5-6. The Chart Tools context ribbon

Excel's default charting behavior is to display the data values by column (by product in this example). The vertical and horizontal axes may not show the data with the orientation you expected. Assuming that is the case here, let's record a macro so we can see the command Excel applies to switch the chart's data orientation from column to row.

1. On the Developer ribbon, click Record Macro.

2. Name the macro ChartByRow.

3. Select the chart by clicking anywhere inside of it.

4. Select the Design ribbon from Chart Tools.

5. From the Data section of the Design ribbon, select the Switch Row/Column command (Figure 5-7).

Figure 5-7. The Switch Row/Column command on the Data tab

6. Stop the Macro Recorder.

The chart should now look like Figure 5-8.

Figure 5-8. Beverage sales chart with rows and columns switched

The original chart in Figure 5-5 showed us the sales grouped by month, and was helpful in showing which product lines had strong sales in a given month. By choosing the Switch Row/Column command, we can quickly view the monthly sales trend for each product. Is it a coincidence that beer sales went up as summer approached?

0 0

Post a comment