In this blog, we'll guide you through the frequently asked question of how to suppress zero values in Excel Reports. These steps are demonstrated by Solution 7’s Co-founder, Simon Miles, if you'd like to view this demonstration, you can watch the video here.
For more quick guides, you can explore the full playlist here: Ask the Expert Webinar Series.
Firstly, in your report we need to look to wee where the zeroes are and instruct Excel to suppress within those areas. This is demonstrated in the video by using a simple example report.
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.
Apply the zero suppression.
Click on this option to apply.
Alternatively, you can also 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 demonstration video, to overcome this issue, you need to define your exclude range correctly. For example, if your 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.
In this blog, we’ve covered how to suppress zero values in reports, and define an exclude range in Excel.
For the full walkthrough watch, Simon, the co-founder of Solution 7, demonstrate how to suppress zero values in Excel Reports, in a step-by-step guide.
If you’re interested in more hints and tips – click here to see our full Expert Q&A playlist.
To see how Solution 7 enhanced financial reporting and budgeting in Excel for a business like Certified Collision, read our case study.
Comments