Remove Space on Bar Charts for Missing Data

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

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!
 
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.

(It’s useful when you post if you state the version of the software in which
you are working).
 
You could filter the data to show only non-blanks. By default, charts do
not plot data from hidden cells.

- Jon
 
Back
Top