How-to Add a Line to an Excel Chart Data Table and Not to the Excel Graph

Many people making Excel charts love to add a Chart Data Table to their graph.  However, the Excel Data Tables are not very flexible and most people stop using them.  But in this tutorial, I will show you a cool trick.  The trick is on how to add more chart data table rows of data without showing any more series in the chart.

This topic was spawned on the Mr. Excel forums in a user question and I thought of way to solve this problem.  Here was the question:

“I’m doing my chart using Chart Layout 5 where the chart is on top and the table containing series’ data below.   Could I add one or more series and make it to ONLY appear in the table. I only want to show the data in the table but I don’t want it to be included in my chart.”

Here is what my solution looked like:image

So how can you add more chart data table rows to an Excel Chart without showing more lines of data in the chart?  Well it is a simple trick.  Below is a step-by-step tutorial and video if you need to see it in action.

 

The Breakdown

1) Setup Your Chart Data Range with an Additional Series

2) Create A Chart Including the Additional Series

3) Add Chart Data Table to the Graph

4) Change Additional Series Fill Option to No Fill or Line Color to No Line

5) Update Vertical Axis to be in line with the Main Series

6) Clean up Chart Junk

 

Step-by-Step

1) Setup Your Chart Data Range with an Additional Series

In order to add another row to the chart data table, we will actually need to add more data series to the chart, but the trick is that we will hide these additional series from our viewers.  My example is of a line chart for monthly sales with a chart data table of the monthly sales as well as the cumulative year to date sales that only appears in the chart data table.  Here is how you should create your chart data range:image

2) Create A Chart Including the Additional Series

Now lets create our line chart in Excel.  First, highlight the chart data range from A1:C13 and then choose the Insert Ribbon and create a Line Chart from the Chart group.image

Your chart should look like this:image

3) Add Chart Data Table to the Graph

Now we need to add a Data Table to the Excel Chart.  Do this by selecting the chart and then select the Layout Ribbon and choose the “Show Data Table” from the Data Table button in the Labels group.image

Your Excel Chart should now look like this:image

4) Change Additional Series Fill Option to No Fill or Line Color to No Line

Here is the Excel Tip & Trick to making this work.  We need to hide the Cumulative Sales series from the chart.  We do this by selecting the Cumm. Sales series and then either pressing Ctrl+1 or by right clicking on the Cumm. Sales series and choose Format Data Series… from the pop-up menu.image

Then choose the Line Color Options from the left options menu and choose No Line from the right Line Color radio buttons.SNAGHTMLbed1810

Your Excel Chart should now look like this:image

5) Update Vertical Axis to be in line with the Main Series

Now our Excel Chart doesn’t yet look like we had hoped because the Vertical Axis Options Minimum and Maximum are set to include the hidden Cumulative Sales series.  So we need to collapse the Vertical Axis by right clicking on the Vertical Axis and choosing Format Axis… from the pop-up menu or simply pressing Ctrl+1.image

Then from the Format Axis dialog box, change the Axis Options Minimum to Zero and the Maximum to 5000SNAGHTMLbf1816d

Your chart should now look like this:image

6) Clean up Chart Junk

We are almost there.  We just need to clean up the chart.  You definitely need to delete the legend.  Then you may need to either widen your chart area or shrink the font on the Excel Chart Data Table so that the numbers show in the Data Table instead of being cut off like you see above.  Your final chart will now look like this:image

 

Video Tutorial

http://youtu.be/KPRO3nkEb-s

Steve=True