Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 106
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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Remove Space on Bar Charts for Missing Data

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 833
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.

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

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 461
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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove space BE 039 to BE039 Bobby Sullivan New Users to Excel 2 March 31st 10 07:59 PM
remove space capreecorn Excel Worksheet Functions 4 March 17th 09 03:45 PM
Line charts with missing data points in Excel 2007 turen Charts and Charting in Excel 2 August 22nd 07 06:08 PM
How to remove a space Kevin Excel Discussion (Misc queries) 5 July 20th 07 01:24 PM
data missing in charts original file 97-2003 opened 2007 and then. Choir10 Charts and Charting in Excel 1 February 10th 07 05:36 PM


All times are GMT +1. The time now is 04:46 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"