Excel Tip of the Week #171 - Using #N/A in charts

Hello all and welcome back to the Excel Tip of the Week.  This week, we have a Creator post in which we are looking at using #N/A in Excel charts to exclude items we don’t want to plot.

Charts are a great way to visually represent data, but it can often be tricky trying to get all your data in the right place, particularly if there are zero values or particular dates you don’t want to plot on the chart. However, by using #N/A, Excel will ignore the data and not include it in your graph.  #N/A is the error value that means "no value is available." You can use #N/A to mark empty cells and by entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations and graphs. This is particularly powerful when the charts need to be flexible for timing.  You can create a #N/A error value by using the syntax =NA().

Let’s have a look at this in action. Below we have a basic set up of a loan balance over a 18 year period, although there is only a balance on the loan for 10 of those years.

We could just plot the items that we wanted on a graph, but assuming this would be a dynamic workbook where the balances could change and could go on any number of years before being repaid, we would want to make sure all 18 years were plotted as part of our graph.

When plotting the whole period, we can see that the zeros also get plotted because Excel recognises there is something to plot. This can be quite frustrating if you have a long time series with only a small amount of time where the data is relevant and can lead to graphs with lots of zeros plotted and a small spike in the middle.

Charts are purely visual representations of the data and creating the best chart for the situation can really improve its value. So what if we wanted to only plot the loan balance over the course of the loan?

To remove the zeros from being plotted, we actually need to change how the axis is plotted rather than the data itself. We can create an IF formula that returns the correct date when the balance is not zero, and when the balance is zero an #N/A error is returned.

Start by creating a new line of data for the x-axis - for us this would be the year end date.

Simply plot the new chart, using the new Year end date as the x-axis:

Now the balance is only plotted over the dates which do not display #N/A values, therefore ignoring all the zero balance items.

Using #N/A to manipulate the information you want to show in a chart is a useful way of making graphs more dynamic and relevant. There are some examples in the attached workbook to see what doing so looks like in Excel.

BONUS TIP: If you have a long time series of data and you want to make sure that it looks sensible, you can select the line of data and press F11 to create a Quick Chart in a new tab. This will immediately give you an idea of whether the data is consistent or where there may be a problem with the formula or a particular month etc. You can then just delete this new tab and correct the problem or continue knowing that your data makes sense.

It looks like something could be wrong here, we also know that it is just over halfway through the data so this makes it easy to investigate. Quickcharts are a useful way to check for inconsistencies without having to scan through all the data or having to create a proper chart.

Previous post - Spell checking
TOTW index 
Next post - Rotating between values

Thanks to Ross Macaskill for his help with this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.