2 posts

Excel Filters For Custom Reports

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.

Excel Report to Filter

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
  • strawberries
  • cherries

Also, for the North Store, you don’t want to see:

  • apples
  • oranges
  • dragon fruit
  • lemons
  • bananas
  • watermelon

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.

Unintended Filter Results

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.

Column Added to Report For Filtering

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:

=IF(AND(ISBLANK(A2),SUM(C2:D2)=0),”Hide”, “Show”).

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.

The Column Can Now be Filtered

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”.

Filter Applied to Column

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 Filtered Report Result


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.

Using SUM, SUMIF and SUMIFS in Excel

How many ways can Excel add thee, let me count the ways. Butchered Elizabeth Barrett Browning quotes aside, Excel has many functions for summing numbers. Last time I counted the different SUMs in Excel 2013, there were eight different SUM functions. There are SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. I had never even heard of the last four functions until I started to write this blog.

I would say, though, that the most common ones are SUM, SUMIF and SUMIFS.

How do you know which one of these three functions to use?

Let’s take a look at each function and see what they do. That should help us decide when to use each function.

Use the Lowly SUM

SUM(number1, number2)

  • number1 = first number, cell, or cell range to sum
  • number2 = second number, cell, or cell range to sum
  • = next number(s) to sum, up to a total of 255

SUM is the most basic of the SUM functions. It adds the numbers entered between the brackets. This can include hard-entered numbers, cells, cell ranges, or a mix of them. Text values are ignored.

The formula =SUM(E1:E5) in the image below will sum the values in cells E1:E5, which for this example results in 19.

Example of SUM in a Formula

Expand Your Spreadsheets With SUMIF

SUMIF(range, criteria, [sum_range])

  • range = the cells that you want Excel to look in for a match to your criteria. It can be the same range as the range of cells that you are summing, or a different range of cells
  • criteria = what you want to find in the range. Wildcard characters can be include
  • [sum_range ] = If you want to sum a range of cells different from the first range, you enter the sum range here. This is optional

Like SUM, SUMIF also adds the numbers that are entered between the brackets, however you can specify one criteria that has to be matched in the range for it to included in the SUM.

For example, if you have a column with color names and a column of values, you can specify what matching color to sum. The formula =SUMIF(D1:D5,”Blue”,E1:E5) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), which for this example results in 7 (2+5).

Example of SUMIF in a Formula

Supercharge Your Formulas With SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1,)

  • sum_rangethe cells to sum
  • criteria_range1the cells that you want Excel to look in for a match to your criteria
  • criteria1 = what you want to find in the range
  • = additional range and criteria, up to 127

Like SUMIF, SUMIFS sums a range. However, it allows multiple criteria to be specified.

Notice the difference between SUMIF and SUMIFS in the order of the arguments.

Expanding on the example above, let’s say you have a column with color names, column of values, and another column of items. You can specify what matching color and item to sum.

In this example, we will sum the value of all of the pencils that are blue.

The formula =SUMIFS(E1:E5,D1:D5,”Blue”, F1:F5,”Pencil”) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), and a matching “Pencil” in the third column (F1:F5), which for this example results in 5, since there is only one row that has both “Blue” and “Pencil”.

Example of SUMIFS in a Formula


One beneficial and practical use of both SUMIF and SUMIFS is the ability to create a grand total or summary of a large amounts of cells.

Following the examples above, say you have hundreds of rows of data of colors and want a summary of the total value for each color. SUMIF and SUMIFS are excellent functions to simply  create that summary.

Hopefully, now  you have found that SUM, SUMIF and SUMIFS are some really great functions in Excel.

Perhaps you have thought of some creative and interesting ways to use these functions. If so, please comment on how you have used them.