MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Charts


Posted by Cathy Moody on February 04, 2002 8:37 AM

I am trying to put a Grand Total row in a chart data section. When I select the area for grand totals from the pivot table and insert it into the chart, the totals are included in the chart figures. I want them independent of the chart and only dependent on the column cells above as a sum. I am having to draw text boxes under each column and type in the totals, is it possible to have row that is not figured into the chart data, and if so how is it done?


Posted by Damon Ostrander on February 04, 2002 1:17 PM

Hi Cathy,

There are several possibilities for not displaying certain rows' data in your chart.

1. You can hide the total rows. Rows that are hidden will not appear on the chart. If you want to be able to hide/unhide a row quickly, you can use Data Grouping (outlining) which gives a button along the side of the page.

2. You can make a particular point on the chart invisible by turning off its marker and line coloring. If the points are connected with lines, you will also need to turn off the line associated with the next point in the sequence. Unfortunately, this will put a gap in the line that appears in the chart.

3. This is probably the best way: define the range of cells that the series is plotting as a disjoint range. For example, you can make the chart plot the range of cells:

=(Sheet1!$B$1:$B$10,Sheet1!$B$12:$B$19)

which explictly leaves out cell B11, by assigning this string to the desired chart series.

Happy charting.

Damon