Hello - I'm creating basic bar charts and right now they look cluttered. How do I show the data values EXCEPT when that value is zero? I tried using the 0 formula on the source sheet, but it still showed up on the chart.
You could change the formula in the sheet to insert NULL in place of a 0. This will remove the item from the chart.
Sean
Thanks!
I generally use something along the lines of
=IF([Number Generating Formula] > 0, [Number Generating Formula])
This will leave the cell blank until the number is greater than zero.
You could even change the > to <> to get it to show negative numbers, but leave blank if zero.
Hi @Paul Newcome
Regarding the formula above to discount the zeros, can you help a little more with the construction. For example I already have a countifs formula as follows:
=COUNTIFS({Interdependencies and impacts register Range 2}, "CFCD", {Interdependencies and impacts register Range 1}, "funding")
How is this combined with your suggestion above to keep the zeros out of the chart on the dashboard? If you could write the full formula it would be a big help.
Thanks
Phil
@Phil Wightman Wherever you see "[Number Generating Formula]" in my formula above, just drop in your COUNTIFS without the leading "=".
Hi @Paul Newcome , thanks.
Can you spot why my attempt s not working?
=IF([COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"]), > 0, [COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"])
Hi @Phil Wightman
At a glance, you have square brackets that shouldn't be in the formula.
Try this.
=IF(COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started"), > 0, COUNTIFS({New Inter-dependency Register Range 2}, "CSOD", {New Inter-dependency Register Range 1}, "Not started")
Did that work?
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
@Andrée Starå Is correct. Sorry about that. I should have also mentioned to drop the square brackets as well. My apologies.
Hi @Andrée Starå
I used your suggested version without square brackets and I get '#INVALID REF'
Any further suggestions gratefully received.
Rgs
@Phil Wightman How exactly are you creating your cross sheet references? Are you following the steps of clicking on the "Reference Another Sheet" link in the little helper box that pops up when entering a formula, selecting the appropriate sheet, then selecting the range and inserting the reference that way?
@Phil Wightman
Did you get it working?
I'm at a complete loss, I've look at a number of posts on this issue and have tried all by I still get a 0 in the cell, and like others it makes the dashboard to busy. Any assistance you could offer?
=if(COUNTIFS({Design - Request Register Range 2}, ($Category$86), {Request Tracker Range 4 - Request Type}, ($Category87), {Request Tracker Range 2 - Status}, "Not Started")),>0, COUNTIFS({Design - Request Register Range 2}, ($Category$86), {Request Tracker Range 4 - Request Type}, ($Category87), {Request Tracker Range 2 - Status}, "Not Started")))
Would it no be easier is 'SS added the option to not display Zero's like in Excel? these seem overly complicated formula's just to remove a 0
Cheers.
@Jason P It looks like you have some extra parenthesis and commas tucked in...
=if(COUNTIFS({Design - Request Register Range 2}, $Category$86, {Request Tracker Range 4 - Request Type}, $Category87, {Request Tracker Range 2 - Status}, "Not Started") > 0, COUNTIFS({Design - Request Register Range 2}, $Category$86, {Request Tracker Range 4 - Request Type}, $Category87, {Request Tracker Range 2 - Status}, "Not Started"))
The concept itself isn't too complicated. You just have to make sure you are dropping the right pieces into the right places.
=IF(Number_Generating_Formula > 0, Number_Generating_Formula)
@Jason P
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Andrée
Ugh. No answer for what should be such a fundamental and easy solution? Crazy.
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…
I need to be able to pull data manually from the source sheet into a report to send to another person for approval. I do not want to give the person approving access to view other sensitive information on the source sheet. Is this possible?
hello, How can I set our account to reflect real time and dates. for example, when viewing the cell history, It usually shows the day before. The correct time stamp for this edit should be 9/3/25 5:50pm. Any way to change this to our correct time zone? We are in Guam.
