Is it possible to have a custom data table visible underneath a chart in MS Excel?

Question

I have a column chart with a single series of data to be shown on the chart.

However, I also have another column of data that corresponds to the one being visualized, but instead of charting it, I want to show it as a data table beneath the chart.

Right now, every time I add a data-table to the chart and poke around its options, I can only ever get the data for the series that are actually being charted, and not additional columnar data. Ideally, I could exclude the data that is already charted (to avoid redundancy) and add the other column for use only in the data table. But it seems the data table is inexorably tied to what you actually see in the cart.

Is this possible? I know there are lots of tricks for massaging Excel into doing what you want, but so far it has eluded me. (Excel 2007)

Answer 1

You can set the ChartArea and PlotArea properties to NoFill to make them transparent. Then place to chart on top of a range containig the table

Answer 2

I'm not sure at this point whether the options are the same in 2007, but in 2010, you can simply Format the Series that you are wanting to show in the table, but not the graph and change the Marker Options to None and the Line Color to None as well.

The only issue is if it is outside of the standard bounds of the axis that you are displaying, you may need to move it to secondary axis for scale reasons. That or just manually adjust your scale.

Answer 3

1. If you want to display columns, and hide some series, but show all values in the data table - change the chart type to combo.
2. change the the series that you don't want to see to lines.
3. For the series that you don't want to make visible on the chart area, select the line on the chart area, format data series, and change the transparency of the line to 100%

Answer 4

Not sure if this is the direction you want, but, you could copy your desired cell range as a picture, then paste the picture anywhere you like.

In Excel 2007 you'd select the desired cell range, copy it, then on the "Home" tab, choose "Paste", "As Picture", "Copy as Picture", and accept the defaults. This copies the selected range as a picture to your clipboard, and now you can paste it anywhere. You can further manipulate the picture properties at this point if needed.

You can "paste as picture" a lot of things -- including pictures of graphs.

You can automate it with a macro as well.

Answer 5

In Excel 2010, you can copy the table cells you want and paste them into a section of the spreadsheet as a picture. Then the picture can be cut and pasted into the chart. Don't know why you can't paste the cells as a picture directly into the chart without first pasting them somewhere on the spreadsheet.