A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Remove Space on Bar Charts for Missing Data



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 04:34 PM posted to microsoft.public.excel.charting
Rebecca
external usenet poster
 
Posts: 284
Default Remove Space on Bar Charts for Missing Data

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!
  #2  
Old April 23rd, 2010, 06:53 PM posted to microsoft.public.excel.charting
trip_to_tokyo[_3_]
external usenet poster
 
Posts: 932
Default Remove Space on Bar Charts for 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.

(It’s 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!

  #3  
Old April 24th, 2010, 01:41 AM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default Remove Space on Bar Charts for Missing Data

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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.