Charts Plotting 0
I recently contacted support to find out if there was an option within dashboard charts to keep the charts from plotting 0. I received the following response:
Currently we don't have a feature in place that will prevent specific values from showing on the chart. At this time the only solutions that we can provide would be to remove the zeros from the cells, which would prevent our system from detecting data and plotting data points, or modify the chart data to not include the future months.
Unfortunately, I do not have the ability to remove the 0's since my data comes from calculations, or modify the chart to not show future months, since we want to see future months compared to past year data.
I would like to vote to have the option to turn off values to be plotted in a chart, such as 0, so that my charts appear "cleaner".
Comments

Hi Tama,
Great idea! That would be a great addition to Smartsheet features.
Please submit an Enhancement Request when you have a moment.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

I "hide" zeros in calculations quite frequently. Here is a basic example of how.
Original formula:
=COUNTIFS({Sheet A Range 1}, "In Progress")
Gives me a count of how many "In Progress" statuses I have, but I want that cell to be blank if the count is zero or in other words: populate the number if it is greater than zero.
So I use an IF statement to accomplish this.
.
=IF(COUNTIFS({Sheet A Range 1}, "In Progress") > 0, COUNTIFS({Sheet A Range 1}, "In Progress"))
.
It basically says "if original formula is greater than zero, run original formula. Leaving the "else" portion of the IF off means it will default to blank.
I use it A LOT in my reporting metrics that feed dashboards.

Thanks! I will give that a try.


Hi @Paul Newcome  Your suggestion above for hiding "0s" using formulas works well. However, the charts still show the labels for which the values are blank. Is there a way to hide the labels from the axis as well?

Insert another column and use a formula such as
=IF([Column With Count]@row <> "", [Column With Label]@row)
Then you can reference this column in your chart.

Thanks @Paul Newcome. This helps. Sorry, somehow missed this message earlier.

Happy to help. 👍️

@Paul Newcome what if the data is imported from an Excel sheet formula? We import excel data with formula's. In excel we can hide zero values, but when we import into Smartsheet the 0 does show. How can we remove them? We use the imported data to generate PDF documents, and now all the 0's show on the documents.

@Mathijs Muller You would want to insert a helper column and use the formula outlined in my comment above where you say IF the value is greater than zero then display the value.

@Paul Newcome Hi Paul, thanks for that. However, still not clear to me where I add the formula? I import data from excel, which creates a new sheet. From the new sheet, I copy the row to my existing sheet. In this sheet where I copy to, the "zero" should be hidden/ taken out. Can I put the formula to cover the complete sheet?

You would insert the new column in the existing sheet. and put the formula into every row.

@Paul Newcome Hi Paul. I've used the formula above but where the cell count was 7, on executing the formula the cell returns 0, what am I missing?
=IF(COUNTIFS({Design  Request Register Range 3}, ($Category@row), {Request Tracker Range 2  Status}, "In Progress") > 0, COUNTIFS({Design  Request Register Range 3}, "In Progress"))
Cheers.

@Jason P Both COUNTIFS should be identical.

@Paul Newcome Beautifully simple and effective. Thank you! Seems obvious but I don't know if I'd ever have thought of it. The only downside is the formula doesn't work unless you type it all out again when used for longer formulas. I tried to copy/paste and no dice. Not sure why this happens, it seems like the Formula assist doesn't register each piece unless it is entered manually... Oddly enough if I write it out once I can then auto fill the other cells in rows/columns and edit the criteria accordingly and that works.
*Shrugs
The really negative part is not so much having to type it out as it is thinking the formula was somehow incorrect when I copy/pasted. I wonder if I just needed to refresh or something but I was getting the unparceable error
Cheers,
Josh
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives