From the course: Excel: Managing and Analyzing Data

Use the FILTER dynamic array function - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial

Use the FILTER dynamic array function

“

[Instructor] Here is some city data and it is sorted alphabetically by the city name, and now we would like to extract certain records from it. We've been asked to provide a list of these cities that do not have a mayor listed. So I'm going to go down to this cell and use the filter function. Equals, filter, open parenthesis, filter what. We've been asked for this data. The population was not requested. Okay. So, comma, include what? Include where, the mayor column, equals, empty, close parentheses. Enter. Look at that and we can verify Xanthic Lake doesn't have a mayor in cell F16 and Xanthic Lake is listed in our dynamic array result. So look at this another way. Let's make it a little more complex. We want the data that has no mayor listed and a population of less than 100,000. Equals, filter, double click, the array. This time let's grab the entire data set. Comma, include what? Because we want multiple criteria for our filter, we have to put each criteria into parenthesis. Parentheses, where, the mayor column equals, empty, close parentheses. And, so we need the ampersand and then open parentheses. Now the population is less than 100,000. Close parentheses And we're not worried about if empty, so we can close the parentheses for the filter and hit enter. Look at that. All of the records that don't have a mayor listed, and the population is indeed under 100,000 people. Now I'm going to show you one other thing we're going to do an or. Go back here. Delete the formula. Now equals, filter, open parenthesis. This time we would like to grab these cells. Comma, include what? Where, open parenthesis, the County does not equal Barco, and we have to put that in double quotes, closed parentheses, plus. And that's our or operator. Open parenthesis, the founded date is greater than 1950, close, close, enter. So let's look at the data. We wanted the data where the record is either not Barco or the city was founded before 1950. So that's how we have Barco city because it was founded in 1919. But then when we go down to West Farkas, that is on our list because it was founded after 1950. And since we have the data that we want, we can copy this and then paste it as values and then provide it to whomever asks for the data this way. And this is how you can filter your source data very easily and not have to actually filter the data itself. Use the filter function.

Contents