Charts are terrific. Many people are visually oriented and can pick up what you want to communicate about numbers more easily from a chart.
This could lead them to also want to see the number, now that they get the picture.
You could have the values put into the body of the chart, but why clutter a good picture?
That's why in later versions of Excel, they offer the option of show the data in a neat table at the end of the chart. Depending on the number of data series you have in the chart, this could be a nice way of giving your reader everything on one page while still appealing visually.
Let's start with the chart that we had last time.
To add the data table to the chart, right click on the chart and select Chart Options.
If you have a later version of Excel, you will have Data Tables as a tab. Click on it and this what you see.
Notice that the legend option cannot be selected- it is grayed out above.
Next click the Show data table box. In the process, the show legend keys option is now available.
Check out the bottom section of the thumbnail sketch of the chart.
First, don't worry, that is not quite what the chart will look like in full view. They compress the view in the thumbnail sketch so you can see all the sections.
Notice that the legend will automatically appear in the data table section.
You have the option of showing the legend keys in the data table. To see what it looks like without, uncheck the legend keys option and click OK.
It tells your reader much more by having the data in the chart. They still get a visually appealing, clean chart in the top section and then can move down to the data section.
Back to the legend keys in the data table section. Here is what we prefer to do. Right click on the chart, select Chart Options, then the Data Table tab and click the show legend keys box.
One more quick step. Since we will have the legend in the data table section, no need to double up on the legends. Click on the Legend tab and then unselect the legend in that section by unchecking Show Legend.
Here is the updated chart.
Now you have the legend keys and the data table together, to make more room for the body of the chart.
Here are some more thoughts on making the best use out of data tables in charts:
The longer the names of your data series, the wider the legend section will be on the left of the data table. If you have long names, then consider cutting down the size of the names or showing the names in a separate legend section.
If you have a large number of data series, then it might be too many to show in a data table. What happens is that one or more of your bottom data series will be there, but will not show up in the chart. The fix is to shrink the body of the chart (by left clicking on the chart body, then finding the little tab at the bottom of the body and dragging it up). If you have a lot of data, this may not even work and you may have to forget about showing the data table.
The data table also depends on how entries you have in each data series. For example, if we had 12 regions above, there would be too many to use a data table. It also depends on the next bullet point below.
Another factor is how wide are your numbers. If the above numbers were in the trillions, it could be too wide for the data table. The fix then is to round the numbers, for example, to thousands or millions.
Data tables in charts can be a great way to accomplish two things for your readers- show them the picture and give them the numbers.Return To Excel Tips Index