Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in chart
Hi,
I am trying to plot data in a chart, and to not want the "blank" cells to be visible. In the chart options, I have selected "Show empty cells as gaps". However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in chart
Change your formula. Replace "" with NA().
The N/A# error tells charts to ignore the data point. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "GJS001" wrote: Hi, I am trying to plot data in a chart, and to not want the "blank" cells to be visible. In the chart options, I have selected "Show empty cells as gaps". However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in c
Thanks - that worked!
"Luke M" wrote: Change your formula. Replace "" with NA(). The N/A# error tells charts to ignore the data point. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "GJS001" wrote: Hi, I am trying to plot data in a chart, and to not want the "blank" cells to be visible. In the chart options, I have selected "Show empty cells as gaps". However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted inchart
You probably have something like this:
=IF(formula = 0,"",formula) in those cells. Replace it with this: =IF(formula = 0,NA(),formula) This will return #NA to the cell, which will not be plotted. If you want the cell to look blank, then you can apply conditional formatting, such that if the cell contains an error then use a white foreground colour. Hope this helps. Pete On Dec 15, 6:29*pm, GJS001 wrote: Hi, I am trying to plot data in a chart, and to not want the "blank" cells to be visible. *In the chart options, I have selected "Show empty cells as gaps". * However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). *Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. *If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. *Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in c
That is exactly what I had, and yes, your method works! If only Excel help
had this information (if it does, I couldn't find it). "Pete_UK" wrote: You probably have something like this: =IF(formula = 0,"",formula) in those cells. Replace it with this: =IF(formula = 0,NA(),formula) This will return #NA to the cell, which will not be plotted. If you want the cell to look blank, then you can apply conditional formatting, such that if the cell contains an error then use a white foreground colour. Hope this helps. Pete On Dec 15, 6:29 pm, GJS001 wrote: Hi, I am trying to plot data in a chart, and to not want the "blank" cells to be visible. In the chart options, I have selected "Show empty cells as gaps". However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in c
Glad to hear that it worked for you - thanks for feeding back.
Keep reading the newsgroup postings for other tips. Pete On Dec 15, 7:53*pm, GJS001 wrote: That is exactly what I had, and yes, your method works! *If only Excel help had this information (if it does, I couldn't find it). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
create blank/empty cell - don't want a zero to be plotted in chart
Hi,
You can also filter the data range and then go to the filter drop down. Now uncheck the box for "Blanks". Since the blank will not be visible in the range, it will not be plotted. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "GJS001" wrote in message ... Hi, I am trying to plot data in a chart, and to not want the "blank" cells to be visible. In the chart options, I have selected "Show empty cells as gaps". However, setting the result of an if statement to "" which creates a blank cell does not do it, as the cell is not truly empty (it contains a formula whose result is a blank). Even copying the cell to another location using paste special, and pasting only "values", with or without "skip Blanks" does not do it. In all these cases, Excel insists on plotting the results as zeroes. If I manually go i and delete the contents of the supposedly empty cells, then the gaps I am looking for show up in my chart. Unfortunately, I have thousands of data points, and hundreds are empty - doing this manually would be a chore. Am I overlooking something? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I indicate a empty or blank cell in an IF function? | New Users to Excel | |||
How to auto count data in an empty cell to be 0. Blank cell=0 | Excel Discussion (Misc queries) | |||
Return an empty or blank cell value? | Excel Worksheet Functions | |||
Blank (empty) cell always equal to 0?? | Excel Discussion (Misc queries) | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) |