Excel is one of the most common software applications used for reporting on financial data. It’s power, flexibility and ease of use contribute to its use by so many users. Plus, having Excel as the most commonly used spreadsheet software in use on desktop computers around the world also helps make its use so popular.
Since Excel is so popular, it is commonly used for reports – even though it is not designed as a reporting system. It’s a good thing that Excel has the ability to automate so much and has so many built-in features.
The feature that will be shown are the Excel filters, and you will see how to use them to customize a report.
Start with the Report
What will be demonstrated here is how to automate some of the reporting. Let’s assume that you will be reporting monthly on the financial data on sales of fruit at two stores.
Below is the basic report that will be modified. Notice that it shows two stores on the report and the total of the two.
The report shows what the projected forecast was of quantity of fruit sold, and what was actually sold for both of the stores.
Determining the Report Needs
Making the report easier to understand is always good to do. Therefore, you don’t want it to be cluttered with useless information.
Due to that, for this report, you want to only see:
- fruit that were projected to sell and that did end up selling
- fruit that weren’t projected to sell, but did end up selling
You don’t want to see:
- fruit that weren’t projected to sell and that didn’t end up selling
So, for the West Store, you don’t want to see:
- dragon fruit
Also, for the North Store, you don’t want to see:
- dragon fruit
Also, notice that it looks like the North Store is not doing so well, in comparison to the West Store, either.
Manually Customizing the Report
You could manually remove the lines on the report for those items that haven’t sold, and add in lines for any items that sold since the last time you reported on the data.
However, just imagine the extra work this creates each time you want to report on the data.
And if you want to report on new data more frequently than monthly, the amount of work and possible errors increases.
And besides, you’re using Excel, so you might as well use its built-in features to customize the report.
Seems like an excellent opportunity to use filters.
Unintended Results Using Excel Filters
Excel filters can be used to automatically hide the rows in the report to fit the criteria that you want.
Now you may be thinking that all that needs to be done is add the filters and then filter the two columns “Qty Forecast” and “Qty Sold” to remove any that are zero.
Doing that, though, will not produce the results that you want.
Just take a look at the image below.
See how the report now doesn’t show the two stores, the sub totals or the grand total. For this example, filtering this way does not give the results wanted.
How do you correct it so that the report filters out the lines you don’t want to see since they are zero, while still showing the lines that you do want to see?
Using Excel Filters on a Formula
You can get the results you want if you add a column to the right side of the report that has a formula that identifies those rows that we want to keep visible, and then apply a filter to that column.
For this example, I left a blank column between the report and the column that I added to filter the report.
If you do it the same way, just remember to make sure that you highlight all the columns that you want the filter to be applied to before you apply the filter. If you don’t, that column won’t be filtered, and you need it filtered.
You could also not leave a blank column between the report and the filter column. Then Excel will apply the filter to the added column along with the rest of the report. It’s really your preference on how you want to do it.
The image below shows the extra column added to the right of the report.
Now, here’s the crucial part:
The formula needs to be added to all the cells in the column starting from the first row of the report after the titles right down to the end of it.
And what is the formula?
It’s this, in cell I2:
What it is doing is checking if cell A2 is blank AND if the total of C2 and D2 is zero. If the result of both are TRUE, it results in the text “Hide”. If the result of either one is FALSE, it shows the text “Hide”.
Why do we care about cell A2?
Well, for this report, column A has the name of the store, and that row is needed to show on the report. If you didn’t add the ISBLANK function, that row would be hidden when you didn’t want it to be.
Now, once you have that formula copied down to all the rows of the report, you can see that the text in that column changes, depending on the results in each cell, as you can see below.
Because you have done that, it’s easy to filter the report to hide the rows that you don’t want.
Now, all that needs to be done is to click on the filter on the column with the formula.
The image below shows that the Excel filter is applied to only include those that say “Show”.
Now the report is filtered to show only those lines that fit the criteria that you want.
As a result, the report has less data and allows the user to focus on the applicable data. See in the image below.
Excel filters can be used in unique ways to customize how the filtering is done.
As a result, here you saw how adding a column with a formula to determine which rows to include allows the Excel filters to change what is included on the report.
Please let me know if you find this useful, or if you have any suggestions for this method.