Charts Plotting 0

Tama Wagner

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". 

Andrée Starå

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

Paul Newcome

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.

Tama Wagner

Thanks! I will give that a try. 

Paul Newcome

Happy to help! 

Sameer Khanna

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?

Paul Newcome

@Sameer Khanna

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.

Sameer Khanna

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

Paul Newcome

Happy to help. 👍️

Mathijs Muller

@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.

Paul Newcome

@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.

https://community.smartsheet.com/discussion/comment/229281#Comment_229281

Mathijs Muller

@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?

Paul Newcome

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

Jason P

@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"))

Paul Newcome

@Jason P Both COUNTIFS should be identical.

Josh Reed

@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