Suppressing 0 or #N/A values in Bar Charts

fowzee

New Member
Joined
Jul 30, 2019
Messages
5
Hi all,

I've read this post already on suppressing zeroes in bar charts but I still can't get this working.

The chart refers to data straight through FY2020 (and this data is fake) - as you can see, we only have actual data through 6/20. I need to suppress the #NAs beyond 6/20 (which represent forecast months) in the easiest way possible, but want new months to simply appear on the chart as new data comes in.

I don't want to create multiple series and would prefer the formatting approach, but it's not working. Can someone tell me step by step what to do? Do I need to format the labels in row 20? the data in rows 24-26? Do I change the formatting of the series in the charts? Etc.

Thank you!

Fowzee



1594064820962.png
 

Attachments

  • 1594064263041.png
    1594064263041.png
    18 KB · Views: 1

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
Hopefully someone will correct me if I'm wrong but I'm not sure if you can suppress N/A's and zeros for bar charts. I know you can for pie charts but for some reason bar charts are different.

Instead, what you can do is make them blanks so they don't show. You can add a simple IFNA formula to your values like this: IFNA(A1,"")
This says that is cell A1 equals #N/A then show as blank otherwise show the value. So now you wont see any N/A's on the chart and if it changes to a value then the value will plot.
 

fowzee

New Member
Joined
Jul 30, 2019
Messages
5

ADVERTISEMENT

yeah, but which row do I turn the values into “” instead of NA?

the labels? the data? both?

then where do I change formatting?
 

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
Both, and you don't need to change the formatting with this approach.

Just look at whatever formula you have in any of the N/A cells and put IFNA( in the front and ,"") in the back. For example if your formula for one of the N/A's is C2+B2 then you change it to IFNA(C2+B2,"") I would do this for every cell you're plotting.
 

fowzee

New Member
Joined
Jul 30, 2019
Messages
5
gotcha, but the chart will appear as though it has empty columns for all those "". There might not be any labels or data, but the size of the graph will basically look like it has empty placeholders rather than resizing to omit those placeholders, right?
 

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
gotcha, but the chart will appear as though it has empty columns for all those "". There might not be any labels or data, but the size of the graph will basically look like it has empty placeholders rather than resizing to omit those placeholders, right?

Right, but you can fix this just by hiding columns where the values are blank. Make sure your chart isn't in the way though and select those columns where the total is blank and hide them. Your chart should expand on its on.

If you're familiar with VBA you can have this done for you.

You can have some VBA in the Auto_Open module that says something like this: for each cell in range("A26:Z26) if cell.value = "" then HideEntireColumn = true Else HideEntireColumn = false. This would Hide or unhide columns that total blank for you when the workbook is opened.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,198
Members
412,706
Latest member
msousa25
Top