filterpicture1

How to Filter in Excel

by with No Comment beginnerfiltersubtotal

If you’ve got a large data set, you know how important it is to be able to filter that data quickly and easily. Knowing how to filter efficiently in Excel can make it easy to not just pull up the data you need, but to also run calculations. Below, I’ll show you multiple ways that you can filter data.

For this example, I’m going to use a list of all the MLB World Series champions and runners-up.

World Series champions and runners up.

Using the auto filter

A quick way to filter the data is by using Excel’s auto filter. To enable it, click anywhere on the data set and click on Filter button from the Data section:

Filter button on the Data tab.

Clicking the button will create drop-down buttons that you can now use to filter the data with:

Data table with filters enabled in the headers.

Suppose I wanted to see all the times that the New York Yankees have won. To do that, I would click on the drop-down button in the Winner column, where I’d now see all these options:

Filter options available after clicking on a drop-down.

Filtering in Excel can be as easy or complicated as you want it to be. You’ll notice there’s even an option to Filter by Color that’s been greyed out since I don’t have any colors in my data set. If I click on the Text Filter option, I’ll have more options to choose from. I can look for partial matches and I can select an exact match as well:

Selecting equals as the text filter option.

In the following screen, I’ll enter in New York Yankees as my search term:

Selecting a criteria that matches only New York Yankees.

I can also have multiple criteria. For instance, I could say I want to see any victories by the New York Mets as well. In that case I could just enter another criteria and enter their name. However, there’as an even easier way to do that.

Although in the criteria, equals is selected, I can change that by clicking on the drop-down, and there I’ll have more choices:

Selecting filter criteria where the value just contains New York.

I can select contains and just change the search criteria to New York. So even though I selected equals as my initial match criteria, I don’t have to worry about sticking to it. When I click OK, I now see my list of titles won by any team that had ‘New York’ in their name:

Filtered list showing any time a team from New York has won.

Not only do we have the Mets and Yankees on this list, but there’s also the New York Giants.

You’ll notice that the row numbers on the left are now in blue. This is how you can tell that your data is filtered. And so if you’re looking at your data set one day wondering why you’re missing information, have a look at the row numbers, as they may tell you the data’s filtered. If you want to unfilter the data, go back to the Filter button and click it again. Then your data set will be unfiltered and the drop-downs will be gone.

What you can also do is select the individual drop-down and clear the filter. You can tell which column has a filter in place by looking at the drop-down. In the Winner tab, the button shows a filter icon:

Drop down shows a filter icon, indicating the column has been filtered.

Clicking on that drop-down I’ll have the option to eliminate the filter:

Clearing a filter.

Clicking the Clear Filter From “WINNER” button will now reset the filter and keep the drop-down buttons in place.

Filter using selections

Using the text filter isn’t optimal because on newer version of Excel you see the list of items that you can choose from:

Choosing from the values list.

If I uncheck Select All and then scroll down to the New York teams and select them, I’ll arrive at the same result as if I did the text filter earlier:

Checking off the values to filter.

However, even this isn’t optimal. If I’m looking for a partial match, then I can just type in ‘New York’ in the search field and it’ll get the same selections:

Filtering by using the search field.

There’s another less common way that you can filter data. If you’re just looking for one match, you can right-click on the value that you want to filter. Then, right-click on it and click Filter by Cell’s Value:

Filtering by selection.

With this method, the drop-downs in the headers don’t have to be enabled. Once you run this filter, they will be there, however.

Filtering multiple combinations

It’s easy to filter when the names are similar. But if I wanted to filter the data for anytime a team from New York, Chicago, or Los Angeles has won, then it gets a bit trickier. One method would be to go back to the text filters and run multiple filters. But that’s an antiquated way of doing it. However, if you’re running an older version of Excel, that may be your only option.

If you can search through the filter, then there’s an easier way for you to do this. First, let’s go back to searching for ‘New York’ in the filter. We’ll again have all the New York teams in the list.

Next, let’s go back to the drop-down and search again. This time for ‘Los Angeles’ but before clicking OK, we’ll want to click off the box that says Add current selection to filter:

Add current selection to filter checkbox.

By default, this option isn’t checked. If I were to just click OK, the filter would be a list of Los Angeles teams only. But by checking off that box first and then clicking OK, I now have a list that contains both Los Angeles and New York:

Multiple cities filtered.

After repeating the process for Chicago I’ve now got all three cities in my list without having to go through the text filter:

All three cities filtered on the list.

Use subtotals to make your filters even more useful

If I wanted to run a quick tally to see how many of the teams I’ve filtered have won a championship, I could use a COUNTIF function and enter their names. But if you want something more flexible where you can quickly run the filters as I’ve done above and see how many teams are in that list, you can use the SUBTOTAL function. The function takes in an argument for what type of calculation you want to do.

Arguments listed in the subtotal function.

Now, since this data isn’t numeric, I’m going to use a COUNTA function, which is function number 3. For the range, I can use any column since it doesn’t matter which one I’m counting. But I will use range A2:A1000 because I don’t want to include the header in my total. Here’s how my formula looks like:

Subtotal formula counting all the entries that have been filtered.

I could also use the MAX function to see the last time one of the teams I’ve selected has won. I’ll put that directly below the other formula:

Using multiple subtotals in a filter.

Now, if I were to re-filter this data, this time, only for New York, my subtotals will automatically update:

The benefit of using the SUBTOTAL function is your data updates along with your filters. If you want the data to be static and not change, that’s when a regular COUNTIF function would be more appropriate.

If you liked this post on how to filter in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

sum calculation

Using Summation Formulas (SUM, SUMIF(S), SUBTOTAL)

by with One Comment beginnersubtotalsumsumifsumifs

I have saved this sample file here if you would like to look at the data set and have a closer look at how the formulas work.

In this post I will cover some simple summation formulas and when best to use them. I have a sample data set that has stores, customers, and total sales.

sum calculation

Summing Total Sales


If I just wanted to total all the sales then the SUM function would accommodate this easily. There is even a summation button on the Excel ribbon to easily do this.

sum ribbon button

It is on the right hand side of the Home tab in the Editing group. It will try to determine which cells you want to sum but you can change this range to what you need.

Since my values are in column C my formula would be as follows:

=SUM(C:C)

Pretty straightforward, all you need is the column that you want to sum (or specific range if you don’t want everything in the column). This returns the grand total of $256,129 from my data set. Another way I could find out the total is if I just highlight the entire column.

sum column

The sum will show in the bottom right corner of my screen in the status bar.

sum status bar

If I right click on any of this area I get the following menu:

customize status bar

If you didn’t see the sum in your status bar this is where you can add it. In my example I could select Minimum and Maximum and then those values would also be calculated for any range that I highlight and show up in the status bar. This makes it easier if you quickly want to see an average, total, min or max by just highlighting a range without having to type a formula each time.

Summing Single Store Sales

But now suppose I just wanted to know the sales of Store A. I could filter the data set, select the column, and see the total in the status bar. But that is a bit tedious to repeat each time if I wanted to see sales by each store. In this case, I could use the SUMIF function.

My formula would look like this:

=SUMIF(A:A,”Store A”,C:C)
      

The first argument, column A, specifies what range I want to look at; the second argument, “Store A”, is what criteria I am looking for; and column C is the final argument, where I want to pull the values from. This formula is saying to look in column A for a value of Store A and add only those related amounts in column C.

Instead of a static reference for Store A I could reference a cell instead, and that would make it easier to apply this formula to multiple stores without having to change the name manually each time.

In the above example assume my store name is in cell E6. If I have the store names going from cell E6:I6 then I could use the following formula:

 =SUMIF($A:$A,E6,$C:$C)

You will notice I have frozen some of the cells as well. This will allow me to move my formula across without the ranges changing. Below are what my results look like:

sumif calculation function

Summing Sales by Customer and Store

Now, consider a scenario where I wanted to do a summary of sales by stores and customers. Here I can use the SUMIFS function (only available to Excel 2007 and newer versions). In the formula below I have stores in cells G12:J12 and customers in cells F13:F16:

 =SUMIFS($C:$C,$A:$A,E$12,$B:$B,$F13)

I can copy this formula and it will be updated based on what store and customer intersect at that point. The summary will look as below:

sumifs formula calculation

Unlike with the SUMIF function, the first argument here is the range I want to sum, which is column C. The next argument is the range for my first criteria to look up, column A, followed by the criteria I want to match, which in the first formula is cell E12 (Store A). The next two arguments relate to the next criteria range which is column B for the customers and the customer to be matched which in the formula is cell F13 (Customer A).

With the SUMIFS function you can add more criteria than just two. Simply just add another comma and in the next argument specify the range, followed by the criteria. You can keep adding to it as you need.

How the SUBTOTAL Function Works

Unlike the SUM function, the SUBTOTAL function will perform a calculation based on the filters you have applied (if you have none, it will perform a calculation on all the data in the range). The SUBTOTAL function also has an added argument to tell it what type of calculation it should do:

subtotal functions

In the below formula, I am going to total column C again, as I did in the SUM formula initially. However, the additional argument I need in the SUBTOTAL function is defining the calculation. Since I am going to just do a sum, I will set the argument to 9. My formula looks as below:

 =SUBTOTAL(9,C:C)

Now, if I were to use filters to show only Store A and Customer A, the SUBTOTAL formula would return the same result as the SUMIFS function did for the intersection of Store A and Customer A.

subtotal filters

My formula returns a value of $10,796 with the above filters, which matches the result from the SUMIFS formula. The SUBTOTAL function is useful when you are always using filters since it will take those filters into account as opposed to the SUM function which will ignore them. If you don’t use filters then it won’t make a difference.