Forum Discussion
Line Graph Should Ignore Blank Cells
Greetings,
I have an Excel line graph that doesn't seem to be working properly and I'm not sure why. It's acting more like a bug with the Excel file itself - or perhaps the Excel program - than a problem with my graphs and formulas.
Click here for my test sheet in Google Drive.
My data has a number of blank cells. On the graph, I want any blank cells to just not plot anything.
The file is saved in SharePoint/OneDrive. When I open the file from my desktop computer using the local Excel App - not the web app - the blanks display as zero...
When I open the file on the browser - not the local Excel App - it does the same thing...
When I saved the file as a .xlsx file in Google Drive to share it in this forum post, the file ignores the blanks when it ?opens in Google Sheets?. This is what I want!
Here are my settings for Hidden and Empty Cells...
I can't get it to work in Excel on the Desktop App which is where I'll be using the file 100% of the time.
Any ideas what I'm missing?
This failure to recognise that cells containing an empty string are to be considered as blank is a long-standing 'feature' of Excel. Riny_van_Eekelen 's solution of forcing #N/A errors is a standard for traditional spreadsheets but with dynamic arrays other solutions are possible. In particular
= FILTER(SCAN(, admissions, SUM), admissions) Insider only = SCAN(, TRIMRANGE(admissions), SUM)will both create truncated output datasets that work well with charts and create an improved tabular presentation.
This failure to recognise that cells containing an empty string are to be considered as blank is a long-standing 'feature' of Excel. Riny_van_Eekelen 's solution of forcing #N/A errors is a standard for traditional spreadsheets but with dynamic arrays other solutions are possible. In particular
= FILTER(SCAN(, admissions, SUM), admissions) Insider only = SCAN(, TRIMRANGE(admissions), SUM)will both create truncated output datasets that work well with charts and create an improved tabular presentation.
Your formulas filled the blank cells with empty strings. These are not the same as empty cells. Therefore, setting empty cells as gaps doesn't work. The cells must be truly empty or contain #N/A. And why enter spaces in columns D:E for months with no data? Just leave them empty. Fixed all of it in the attached file, and changed some number formatting as well. Would that work for you?
Thank you so much PeterBartholomew1 and Riny_van_Eekelen I learn something new each time! I can appreciate long-standing 'features' and have a great workaround going forward. Thank you both, again!
