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: 3

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
yeah, but which row do I turn the values into “” instead of NA?

the labels? the data? both?

then where do I change formatting?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top