Garreth Hanley:
This is Excel Tips podcast for accounting and finance professionals brought to you by CPA Australia.
1. Using formatted tables instead of macros for hiding rows
Neale Blackwood:
Welcome to Excel Tips. My name is Neil Blackwood and in this episode, I will explain how we can combine a number of excels features and functions to create a system that easily hides blank and zero rows in your report. Now stay tuned to learn about a superpower of the subtotal function, which can help us identify if the filter needs to be reapplied.
Hiding blank or zero rows and reports used to be done with macros, but now we can use formatted tables with a few formulas and a slicer and a text box to provide a workable solution.
Now this can be applied to any report where you need to hide certain rows and columns if conditions are met. In this case, we're going to be hiding blank or zero rows. The companion video goes through in a lot more detail all of the formulas that are used, so I highly recommend you check out the companion video. So that's on the INTHEBLACK website, I use an example from the April 24 podcast, an article where we had a dynamic array report and that report had quite a number of blank rows in there. It also had a zero row, which we also wanted to hide.
I've also included an override feature in this solution, so when you're using just formulas, formulas can hide all of the blank rows.
Now in reports you might need a few blank rows between headings and things like that, so there is an override feature in this solution so that you can force a row to display even if it is blank. There's also an error capture, because when you're using filters, which we're going to use to hide the rows, filters don't update automatically if values change. So, there is the possibility that when the data is updated that a row could be hidden that needs to be displayed, and on the other side there could be a row that's displayed that needs to be hidden. And so I've created an error capture system that will display a message if the filter needs to be reapplied.
2. About the example and tutorial file
So in the example, I've inserted a formatted table on the left of the report. The left side of the report is used for all of the helper cells, and it's easily hidden using some grouping buttons. There's four columns in the formatted table, column A determines whether the row should be displayed or not, column B identifies if the row is a blank row or has all zero values, column C is the filter check and column D is the override column.
So that's where you can enter a letter, in this case D, to ensure that that particular row is displayed, which as I mentioned is useful just for the layout of the report if you need to insert blank rows between sections in your report.
So column B is the one that identifies if a particular row has zeroes or is blank. It uses two of the count functions, so count A is used to identify all of the entries in the range and count IF is used to count all of the zero entries in the range. Now, if those two counts equal each other, it means the row is either blank or it's zero, and so it needs to be hidden. And so, in column B it displays true if that particular row should be hidden.
Now column D as I mentioned is the override column. So, if we just used column B to determine whether to hide the row or not, then it would hide all of the blank rows. And so, column D allows you to force a row to be displayed simply by entering a D in that column.
Column A uses an IF function and an and function to figure out whether to display the row or not. So, if the row is a blank row and there's no override, then it will display the word ‘hide’ in the cell otherwise it will display the word ‘display’. Column A is used to figure out whether to display or hide the row and because it's a formatted table, we can use a slicer to filter it, and so when we filter the formatted table on the left that's going to flow through to the report on the right.
3. Applying the filter and slicer
So the slicer that's been applied has a few setting changes, so again I recommend checking out the video to see those. Basically, it's got two columns and it doesn't have a heading and so it's a very cut down slicer, it just has two buttons on it one for display and one for hide. And basically, you just click on the display button and that hides all of the rows that you need to hide.
As I mentioned, the filter system has a problem in that it doesn't automatically update if an underlying value changes.
So in the video, I demonstrated a couple of times where there was a zero row and I actually put some values into that particular account number for that row and it demonstrated that the filter doesn't update when the underlying value changes. So we need a way to identify if a filter needs to be applied.
To make sure you know if you need to reapply the filter. I've included an error check, so I use the subtotal function to identify if the filter that's applied is correct. So if there is a problem, then a little message pops up in a text box saying that the filter is required.
4. The subtotal function superpower
So the subtotal function has a superpower, it can perform calculations on visible cells. Now it does this inside a filtered table and what that allows us to do is to use, in this case the count A function within subtotal, which is the number 3 which is the first argument within the subtotal function.
So basically, we can use the subtotal function on the row we're on, and we can count in column A whether there is a value there.
So if it's hidden, then the value will come up as zero, if it's visible it will come up as one. So we can compare the subtotal result to any function that returns one if the word display is in column A or zero if there is a hide in column A. And so, by comparing those two results, we can return true if the filter is correct or false if there's something wrong with the filter.
Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
Neale Blackwood:
We can then add up all of the false entries using count IF find out if there are any errors in that particular column and then we use an IF function to then display a certain message either OK, or filter required and then that message can be linked to a text box. So right next to the slicer is the text box which then says either OK or filter required.
So it's as simple as clicking on the display button again to fix any problems with the filter.
So this solution can really be applied to any report where you have to conditionally hide certain rows. Now in this case that condition related to either being a blank row or having all zeros in the values. As I mentioned, it could be applied to any time you need to change the rows that are visible in a report. Thanks for listening. Hope you found that useful.
Garreth Hanley:
Thanks for listening to Excel Tips as CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel Tips and tricks.
To find out more about our other podcasts and CPA Australia, check out the show. Notes in this episode. And we hope you can join us next time for another episode of Excel Tips.
