Ignore Blank Cells In Grpahs

Speed

New Member
Joined
Oct 29, 2002
Messages
8
Can anyone help me out with graphs??
My selection of data has some cells which are blank. Can I ignore those blank cells when plotting a graph using the selection??
Thanks in Advance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
Speed - If I undersand you correctly, you should be able to do this fairly easily. Hold the Cntrl key down when making your data selection and use the mouse to select only the cells with data... skipping over the blank cells. You will get a chart that has no blank data categories.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Click on your chart and choose Tools, Options. On the Chart tab check "Not plotted (leave gaps)".
 

Speed

New Member
Joined
Oct 29, 2002
Messages
8
Hi Andrew,

Thnx for the quick reply.
But I do not want to leave any gaps. That is what prompted my question in the first place.
Any more help will be appreciated.

On 2002-11-07 11:50, Andrew Poulsom wrote:
Click on your chart and choose Tools, Options. On the Chart tab check "Not plotted (leave gaps)".
 

Speed

New Member
Joined
Oct 29, 2002
Messages
8

ADVERTISEMENT

Hi Roger,

Thnx for the fast response. But that was the way I was doing it. I thought there might be an automatic way and I can directly specify the entire range and Excel will automatically ignore the blanks. Hence my query.

Any more assistance will be highly appreciated.

On 2002-11-07 11:49, RogerC wrote:
Speed - If I undersand you correctly, you should be able to do this fairly easily. Hold the Cntrl key down when making your data selection and use the mouse to select only the cells with data... skipping over the blank cells. You will get a chart that has no blank data categories.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
Not sure if these are by formula of just data, but try replacing your blanks with
#n/a

You could modify your formula or, if data, use goto special blanks.

EDIT: If data, highlight the range, find nothing and replace with #n/a.
This message was edited by IML on 2002-11-07 12:06
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

If your series data is in rows you can AutoFilter it for NonBlanks. Then the chart will only plot the visible cells.
 

Speed

New Member
Joined
Oct 29, 2002
Messages
8
Hi Andrew,

I finally got it.

Your first solution helped me.
Instead of selecting Not Plotted (Leave Gaps) what we need to do is select Interpolated. This does the Graph Fine.

Thanks a Lot.

Speed
On 2002-11-07 12:11, Andrew Poulsom wrote:
If your series data is in rows you can AutoFilter it for NonBlanks. Then the chart will only plot the visible cells.
 

justme

Well-known Member
Joined
Aug 26, 2002
Messages
722
I try some of the questions just to learn more during my lunch break. I made a chart with blank cells and tried to choose interpolate, but the option is in gray scale. Why would this happen?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For some reason Interpolated is only available with line charts. Was yours a column chart?
 

Forum statistics

Threads
1,143,614
Messages
5,719,720
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top