Please read our Privacy Statement and Standard License Terms. By downloading any of the documents below, you are agreeing to our Standard License Terms.
Suppress Zero Values in Excel Reports
First, find where the zeroes are and instruct Excel to suppress within those areas.
- Identify and select the zeroes in your report.
- Highlight the rows and columns you wish to suppress.
- Go to the "Zero Suppression" Option. Find the zero suppression tool within the Solution 7 tab of the toolbar, this will allow you to define the ‘suppression range’ to the relevant sheet or workbook.
- Click to apply the zero suppression.
As discussed in the video below, you can alternatively use the “Quick Set” , option to cut out some of these steps, but you will receive a prompt from Excel to define the suppression range as part of the task.
The advantage of defining a suppression range in Excel: Storing the suppression range allows you to be anywhere in the workbook while still being able to un suppress or re-suppress those particular cells. This means you don’t need to constantly reselect the range–it will remember the suppression range that’s already been set. To clear it, just go in and remove the suppression range.
How to remove a suppression range in Excel for Trial Balance: If you want to remove the suppression range, go to the suppression settings, click on "OK," and your suppression range should be cleared. The next time you choose to suppress, it will be reset.
How to set up an Exclude Range in Excel: The ‘Exclude Range’ is useful for cells that may contain numbers which you don’t want Excel to take into account when suppressing – e.g., table headings with dates, if Excel is viewing these as numbers it can sometimes interfere with the suppression function.
As explained in the video below, to overcome this issue, you need to define your exclude range correctly. For example, if your dates are in the first three rows, set rows 1 to 3 as you exclude range, Excel will then ignore these rows and hide them without them coming back.