Suppress Zeros and Blanks for a Workbook

Row Display and Column Display in Workbook Properties

The "Suppress Zeroes" option in the workbook properties that is referenced in the screenshot defines the default state of zero suppression workbook-wide. If that option is checked, then you can refresh the data in the workbook and click on the "Suppress zero" button on the OfficeConnect ribbon and the suppression will work.

If the "Suppress Zeroes" option in the workbook properties is unchecked, then the "Suppress zero" button on the OfficeConnect ribbon will not work after the data refresh because the default state of the workbook has been changed. This also allows for selecting a different suppression setting to be set on individual rows so you can have parts of your workbook behave according to the workbook properties and then any select rows where you wish zeroes to be suppressed anyway.

In the simplest terms, the “Hide rows with all zeros” in Workbook properties ensures that suppression function is enabled. 

Hide Zeros & Blanks

The “Hide Zeros & Blanks” button on the ribbon is only a toggle to hide or show these rows. 

Example: Suppress Zeros & Blanks - On

Example: Hide Zeros & Blanks - On

Example: Suppress Zeros & Blanks - Off

Example: Hide Zeros & Blanks - On

Here we can see that the toggle no longer works to hide the rows with all zeroes in them (Note: the “Refresh” button needs to be pressed for the above to take effect)