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.
CHAPTER 5 n CHARTING IN EXCEL 2007 196
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?
CHAPTER 5 n CHARTING IN EXCEL 2007 197
Looking at the Code
Let??™s take a look at the code we??™ve generated so far. The MakeBeverageSalesChart macro created
a 3-D bar chart for us using a data range we selected. The ChartByRow macro switched
the data orientation of the chart from the default, column, to row.
Sub MakeBeverageSalesChart()
'
' MakeBeverageSalesChart Macro
'
'
Range("A1:E7").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'Monthly Total Sales Amount'!$A$1:$E$7")
ActiveChart.
Pages:
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200