I had a table pulling in data from different places in Excel, basically feeding into a chart that would be used on a dashboard.

In order to stop people fooling with the formulae, I added a row where they could enter a Y if they wanted a given day to show in the graph. The formula I used was something similar to this:

This basically created an empty string every time a date was not required, like this:

Great, I thought, until I ran the graph. Excel read my empty strings (represented in the formula by the empty speech marks) as zeros, making it look like we were expecting a massive drop off..

After wracking my brain for an answer I came across the solution. The answer is to tell the formula to show an error instead of a blank cell. The updated formula for this is:

The table doesn’t look quite as nice, but it is still just as obvious which days have data populated, and which do not.. BUT the great thing is that my graph does exactly what I want it to: I get my line where I need it to be and don’t have to worry about my formula being messed with..