|
This article discusses the limitations of Excel’s built-in data tables as related to embedded charts and provides a simple alternative solution.
When you create certain types of chart sheets or embedded charts in Excel you have the option of adding a data table. A data table is a grid that appears at the bottom of the chart which reflects the source data for that chart. Figure 1 below provides a typical example of an Excel line chart with a built-in data table:
Unfortunately Excel provides the built-in data table option for only a limited group of charts. Data tables are not provided as an option for pie, XY scatter, radar, bubble, surface, and doughnut charts. In addition, data tables are limited to certain font and background formatting, positioning, and X-axis scaling constraints.
So how can you get around the limitations of data tables? One simple solution is to eliminate the built-in data table and use worksheet cells as the alternative. In Figure 2 below, the data table has been removed and the data source has been positioned directly below the chart. To remove the data table, click once inside of the embedded chart. In the standard toolbar click on Chart option, then Chart Options, then the Data Table tab. Deselect the Show Data Table box.
Each of the interior columns has been equally sized at 4 while the two exterior columns have been sized at 1. The plot area, which is the white area within the embedded chart that contains the colored data series lines, has been positioned to take up as much of the inside of the chart as possible. Note that the vertical positioning of the X axis tick marks in the embedded chart and vertical worksheet gridlines are lined up almost exactly.
Given the revised set-up, we can now apply consistent formatting to make the chart with worksheet cells appear as one chart. The next image below starts by decreasing the chart height by one row to make room for the title. To decrease the chart height click once inside of the embedded chart. Press the ALT key and use your mouse to drag the upper exterior border to the next row. By keeping the ALT key pressed down, Excel clicks the chart into the worksheet cell grid so as to insure proper alignment.
In Figure 4, the title row coloring has been formatted to match the yellow interior of the embedded chart. To format the interior of the title row, select the horizontal area within the new chart area that makes up the title row. In the Standard Toolbar, select Format and Cells. In the Format Cells dialog box, go to the Alignment option and choose the Text Control equals Merged Cells option. Then, go to the Patterns tab and choose the color that matches the interior of the embedded chart.
Likewise, the worksheet cell background that makes up the new data table has also been formatted to match the yellow chart interior. To format the new data table, select the cell range that makes up the data table. In the Standard Toolbar, select Format and then Cells. In the Format Cells dialog box, go to the Patterns tab and choose the color that matches the interior of the embedded chart. Add a dark black border around the exterior of the new chart range by going to the Borders tab and choosing a heavy outline.
Figure 5 continues the formatting changes by centering the month titles and adding a border around each data point in the data table. To center the month titles, select the cell range that makes up range of January through December. In the Standard Toolbar, select Format and Cells. In the Format Cells dialog box, go to the Alignment tab and choose Text Alignment Horizontal equals Center. The month names that make up the X axis labels of the embedded chart have been removed. To remove the month names, double-click on the X axis series of month names. In the Format Axis dialog box under the Patterns tab choose Tick Mark Labels equals None.
Continue to freely format the chart as you wish. Figure 6 reflects a finished product. The color of each series line matches the background color of data in the data table.
As you’ve seen, the use of worksheet cells as an alternative to the standard chart data table opens up all kinds formatting options. In addition to the standard formatting applied above, conditional formatting can also be applied.
|
