I am trying to create a bar chart that shows student achievement results
across a number of years. For some of the schools, I do not have data in
certain years. I do not want the chart to leave a big space for this missing
data - I just want it to display the data that I have without leaving this
huge gap for the empty cells in the table it is linked to. I tried putting
=NA() in the empty cells but the chart is still leaving gaps. Is there any
way to have the chart wizard ignore the empty spaces so that it will just
display the data that I have?
Thanks!

Yes, there is a way to remove the space on bar charts for missing data. Here are the steps:

1. Select the data range that you want to create a chart for, including the empty cells.
2. Click on the "Insert" tab on the ribbon and select "Bar" chart from the "Charts" section.
3. The chart will be created with empty spaces for the missing data. Right-click on the chart and select "Select Data" from the context menu.
4. In the "Select Data Source" dialog box, click on the "Hidden and Empty Cells" button at the bottom.
5. In the "Hidden and Empty Cell Settings" dialog box, select "Show data in hidden rows and columns" and "Show empty cells as: Gaps" options.
6. Click "OK" to close all the dialog boxes.
7. The chart will now display only the data that you have without leaving any gaps for the missing data.

EXCEL 2007

I hope the following helps:-

1. Please refer to:-

http://www.pierrefondes.com/

Item number 98.

2. When I started this chart all I had completed was A1 to B10 of the Chart
Worksheet.

What follows is what I did to try and give you what you want.

3. Highlight cells A1 to B10.

4. Insert / Tables group / Pivot Table / the Create PivotTable window
launched / select the Exising Worksheet Location radio button / clicked in
cell E1 and hit OK.

A PivotTable now got added to the Worksheet.

5. Click in Year in the PivotTable Field List on the far right hand side.

6. Drag Result into the Values section on the lower right hand side.

I did a small amount of cosmetic work here centreing the cells in the Pivot
Table (not important; just pleasing to my eye).

7. Clicked in F1 (where it said Count of Result) / right hand click / select
Value Field Settings / Value Field Settings window popped up / Sunmarize by
tab / selected Sum and hit OK

Saved the file.

8. At this point I could see Pivot Table looking very similar to cells A1 to
B10.

9. Clicked somehwere in the Pivot Table (I used cell F7 but this is
immaterial) / Insert / Charts group / clicked on arrow in lower right hand
corner / Insert Chart pane launched / Bar / Clustered Bar / OK

Chart now launched.

10. Dragged chart down to lower right hand side of screen and saved file.

11. Now in cell E1 (where it says Row Labels) just clicked on the years that
you want to see in your chart (1990, 1992, 1993, 1995, 1996, 1998).

You can turn these onn / off at will.

12. Saved the file.

I think that this gives you what you want.

If you agree please hit Yes.

(Its useful when you post if you state the version of the software in which
you are working).




"Rebecca" wrote:

I am trying to create a bar chart that shows student achievement results
across a number of years. For some of the schools, I do not have data in
certain years. I do not want the chart to leave a big space for this missing
data - I just want it to display the data that I have without leaving this
huge gap for the empty cells in the table it is linked to. I tried putting
=NA() in the empty cells but the chart is still leaving gaps. Is there any
way to have the chart wizard ignore the empty spaces so that it will just
display the data that I have?
Thanks!

You could filter the data to show only non-blanks. By default, charts do
not plot data from hidden cells.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/23/2010 11:34 AM, Rebecca wrote:
I am trying to create a bar chart that shows student achievement results
across a number of years. For some of the schools, I do not have data in
certain years. I do not want the chart to leave a big space for this missing
data - I just want it to display the data that I have without leaving this
huge gap for the empty cells in the table it is linked to. I tried putting
=NA() in the empty cells but the chart is still leaving gaps. Is there any
way to have the chart wizard ignore the empty spaces so that it will just
display the data that I have?
Thanks!