Page 546 - Excel 2013 All-in-One For Dummies

528 Worksheet Charting 101
2. Click the Switch Row/Column button in the Select Data Source dialog box to place the row headings (CDs, Rock, Jazz, Classical, and Other) in the Legend Entries (Series) list box.
3. Click CDs at the top of the Legend Entries (Series) list box and then click the Remove button.
Excel removes the empty CDs data series from the clustered column chart as well as removing the CDs label from the Legend Entries (Series) list box in the Select Data Source dialog box.
4. Click the Switch Row/Column button in the Select Data Source dialog box again to exchange the row and column headings in the chart and then click the Close button to close the Select Data Source dialog box.
After you close the Select Data Source dialog box, you will notice that the various colored outlines in the chart data range no longer include row 3 with the CD Sales row heading (A3) and its empty cells (E3:Q3).
Instead of going through all those steps in the Select Data Source dialog box to remove the empty CD Sales data series from the example clustered column chart, you can simply remove the CD Sales series from the chart on the Chart Filters button pop-up menu. When the chart’s selected, click the Chart Filters button in the upper-right corner of the chart (with the cone filter icon) and then deselect the CD Sales check box that appears under the SERIES heading on the pop-up menu before clicking the Apply button. As soon as you click the Chart Filters button to close its menu, you see that Excel has removed the empty data series from the redrawn clustered column chart.
Adding hidden rows and columns of data to a chart
The sales data graphed in the sample clustered column chart shown in Figures 1-6 through 1-8 only includes the quarterly CD sales totals in each music category. To do this, I outlined the sales data in this entire table and then collapsed the outlined columns down to their second level so that only the quarterly subtotals and yearly grand totals were displayed (see Book II, Chapter 4 for details) before selecting the range A2:Q7 as the clustered column chart’s data range. Because all the columns with the monthly sales data in each quarter were hidden at the time I originally created the chart (as a result of collapsing the outlined columns to the second level), Excel didn’t include their data as part of it.
If I decide that I do want to see the monthly sales represented in the clustered column chart, to accomplish this, all I have to do is open the Select Data Source dialog box (by clicking the Select Data button on the Chart Tools Design tab) and then click its Hidden and Empty Cells command button. Excel then opens a Hidden and Empty Cell Settings dialog box, where I click the Show Data in Hidden Rows and Columns check box and then click OK. Excel then immediately redraws the chart adding columns representing the monthly sales to those for the quarterly subtotals in all four of its clusters.