Q. I have used the filter feature in Excel, but what does the Advanced Filter feature do?
A. There is a simple filter feature available in Excel, which is very useful. However, sometimes you may need to filter your data in more complicated ways, such as having multiple criteria or using wild card criteria. Wild card criteria find values that share some of the same characters but not all, such as all customers whose last names start with “Su.” In these cases, using Advanced Filter is much easier. Let’s look at a few examples of how to use Advanced Filter. See the screenshot below for a snippet of the dataset we will be using.
In the first example, say you want to filter the dataset to show account types that start with the number six, occurred in the month of March, and have a budgeted value of at least 20,000. Start by creating a “criteria range” specifying these three criteria. To the side of your data or on a new spreadsheet, copy and paste the three headings of the columns you are filtering: ACCT_TYPE, MON, and BUDGET. Under each heading, specify the criteria for your filter. In order to find account types that start with the number six, the criteria will be 6*. The * is a wild card and instructs Excel to include anything that starts with the number six, no matter what comes after. The criteria for the month is Mar since that is how the month of March is described in the dataset. The criteria for budgeted values of at least 20,000 is >=20000. If you do not include the equal sign, the filter will not capture budgeted values that are exactly 20,000. See the screenshot below for the completed criteria range for the first example.
Assume that you want your filtered data to appear on a different worksheet. Start by creating a blank worksheet and naming it “Filtered Results.” Note that you should always start your Advanced Filter from the sheet where you want your results to appear. Click the Data tab on the Ribbon. Then choose Advanced from the Sort & Filter group, and the Advanced Filter window will open. Under Action, select Copy to another location since you want our filtered results on a new page. Click the box next to List range: and go back to the original worksheet that contains the data. Click A1, hold down Ctrl+Shift+→, then hold down Ctrl+Shift+↓ to select the entire dataset, including the headings. Click the box next to Criteria range: and go back to the original worksheet and select cells J1:L2, which contain your three criteria, including the headings. Click the box next to Copy to:, go to the Filtered Results tab, and click cell A1. Click OK in the Advanced Filter window. See the screenshots below for the completed Advanced Filter window and the filtered results.
Let’s look at another example. This time, say you not only have different criteria in multiple columns, but you also have multiple criteria within the same column. You want to filter the dataset to show account types that start with the number six, occurred in the month of March and April, and have a budgeted value of at least 20,000. First, update the criteria range for the additional filter. You still want just account types that start with six and budgeted values of at least 20,000, but now you also want both March and April. Below each of the headings in your criteria range, you will add an additional row that contains Apr under month (MON). Because you want the same account types and budgeted values for the month of April as you did for the month of March, you will include the same criteria for ACCT_TYPE and BUDGET on the new row. See the screenshot below for the updated criteria range for this second example.
You will again place the filtered data on a separate worksheet, so go to that worksheet and open the Advanced Filter window using the steps described above. The List range: will remain the same, but have the Criteria range: now include the third row and have Copy to: reflect the new location of the filtered data. See the screenshots below for the completed Advanced Filter window and the filtered results. (In this example, I included my new filtered data on the same spreadsheet as the filtered data from the first example, just on different rows.)
Let’s look at another example. This time, say you have multiple criteria within multiple columns. You would like to filter the dataset to show account types that start with the number six, occurred in the month of March with a budgeted value of at least 15,000, or in the month of April with a budgeted value of at least 20,000. See the screenshot below for the updated criteria range for this third example.
Open the Advanced Filter window using the steps described above. The List range: and the Criteria range: will remain the same. Change Copy to: to reflect the new location of the filtered data. See the two screenshots below for the completed Advanced Filter window and the filtered results.
Let’s look at one more example. We will use the same criteria we used in our previous example, but this time I’ll show you how to filter your data and have only specific columns returned instead of the whole dataset. The specific columns don’t even have to be columns that contain your criteria. We will filter the dataset on account types that start with the number six and occurred in the month of March with a budgeted value of at least 15,000 or in the month of April with a budgeted value of at least 20,000, but in this instance we only want filtered results for account code, department, and cost center.
On the worksheet where you want your filtered results, paste the desired headings. In this case, you want the headings ACCT_CODE, DEPT, and COST_CENTER. Paste those in A33:C33 of the Filtered Results tab. The Advanced Filter window will look the same as it did in the third example except for List range:. Instead of clicking in a blank cell for List range:, select the cell or range of cells that contain the headings you created for the filtered results. See the two screenshots below for the completed Advanced Filter window and the filtered results.
You can download the Excel workbook used in these examples and access a video demonstration of how to use Advanced Filter at the bottom of this page.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
