Attend FREE Webinar on Data Science for Career Growth Register Now

Data Analytics Blog

Data Analytics Case Studies, WhyTos, HowTos, Interviews, News, Events, Jobs and more...

Auto Filter and Advanced Data Filtering in Excel

5 (100%) 1 vote

Filtering data simply means seeing limited data. The other data that you do not want to see doesn’t get deleted or doesn’t change in anyway, it just gets hidden.

Why would you want to filter data? If you have thousands of rows of data, it’s going to be difficult to locate part of the data directly and understand anything from it. Thus, to look at only a subset of that data, you apply a filter on the entire dataset.

For example, you have a Sales dataset, of which you want to see records only of = Male – Gender AND Product – Pen AND Quantity between 5 & 10 AND Bought from store in Mumbai. Once you apply Excel autofilter, you will see only this data and be able to analyze it better.

To understand all this practically, let’s take a sample sales dataset as below.

Auto Filter

To start the autofilter in Excel, under the Data tab, click on Filter.

Once you start the Excel autofilter, you will see drop-down icons on each column’s header.

In Excel, filter is applied in different ways on Text, Numbers and Dates. Let’s take all 3 one-by-one and understand further then.

Text

When it comes to text, think of columns like Gender, Product, Size, Category, Person Name, City, Country, etc. In the Product column, you could select ‘Pen’ and all the data where the product is Pen only would appear. All other data will be hidden.

In this dataset, under the Drop-down icon in the ‘Store’ column, keep the tick on only S2 and remove all other tick marks. When you press OK, you will see data of only S2 Store.

To identify that your data is filtered, you can see 2 things, a mark on the header of the column and the row numbers’ color has changed to blue. This means that a filter has been applied on this data.

Now, if you want to filter to see data of S2 and S3 both only, simply tick on those 2 stores and remove the other tick marks.

But, if you want to filter to see data of S2 AND Product ‘Pen’, you must repeat the process you did for the Store column. So, first apply the filter on Store column for S2 as earlier and then after doing that, go to the drop-down of Product column and keep only ‘Pen’ ticked and remove other tick marks.

Your will now see only S2 AND Pen data.

Remove filter

Simply click on ‘Clear’ option.

Conditional Text filters

Under any of the text-based columns’ filter drop-downs, you will find an option called ‘Text filters’.

You can apply a conditional filter like Begins With B. when you apply this, all data where the City name is starting with the letter B will only appear.

Like Begins With, there are other options too – Contains, Does not contain, etc. Try out all of them.

Numbers

When you think of Numbers, think of columns like Quantity, Price, Sales Value, Incentive, Commission, Age, etc.

Conditional Number filters

Taking Age as an example, if your dataset has age of people ranging from 18 to 60, and you want to see data of all entries where the age is between 25 and 40, it will be tedious to tick mark on all the numbers from 25 to 40 or remove all other tick marks.

This is when the conditional filter will come in handy.

In our dataset, you want to see all data where the Quantity is between 20 & 40.

Go to the drop-down in Quantity column, then to Number filters, then click on Between.

Put in the values 20 & 40 in each of the boxes and press OK.

You will now see data only where the quantity is between 20 & 40.

This way, try out all the other conditional numbers filters too – greater than, less than, top 10, above average, etc.

Dates

Date changes every day! Today is say 14th Nov 2017, tomorrow will be 14th Nov 2017, obviously!

But, this, Excel understands and let’s you filter based on this concept.

Conditional Date Filters

You have a lot of dynamic options where you don’t have to put in a particular date or a date range, you simply choose ‘today’, ‘tomorrow’, ‘this week’, etc. and Excel autofilter will read what is your computer’s date today and according to that it will apply the filter you want.

Left-To-Right Data

It happens so that you come across data that is from left-to-right. Meaning your headers are row headers and not column headers.

In such cases, excel autofilter will not run on the headers. You will have to first transpose your data using Paste Special and make it normal the way it should be. Only then the filter can be applied.

Filter by Color

If your data has some colors applied for whatever reason, and you want to view only the data that has those colors applied on it, under the filter drop-down, there is an option called Filter by Color. Choose the color that you want to filter on and its done.

Advanced Filter Excel

Under the Data tab, you will find another option called ‘Advanced’ next to the auto filter that we understood above.

Keep your cursor inside the data, click on Advanced.

Advanced filter works on 3 logics – Action, Criteria & Unique records.

Actions

Do you want to filter in the same place itself or do you want to keep this data as it is, and create a new list of data with the filter applied?

Criteria

AND and OR concepts come into picture while building the criteria.

What Advanced Filter does for you is, it allows you to create your criteria outside in some blank cells, and then the filter will pick up the criteria mentioned in those cells and apply it quickly. This is very useful when your criteria are very complex, and you want to apply many different criteria one-by-one to analyze your data better.

How to build the criteria with AND and OR concepts

For AND, you must place the values next to each other, for OR, place the values below each other.

These examples will make you understand how it works.

  • Store = S1 AND Product = Pen
  • Store = S1 OR S2
  • Store = (S1 OR S2) AND Product = Pen
  • Product = Pencil AND Qty > 40
  • Product = Pen OR City = Boston

Let’s apply Example 3 in the dataset

In the Advanced filter box, in Criteria Range field, select the Criteria you made outside in the blank area.

Your data is now filtered to show records of only S1 with Pen and S1 with Pen.

If you want this filtered list to be copied somewhere else, as discussed earlier, use the Copy to Another Location option.

Unique records

In the data, we have a column for Sales Person.

Let’s say you want to make a unique list of Sales Persons, basically a list that shows names with no duplicates. This list you want to make in a separate blank area.

Follow these steps to create this unique records list.

  • Select the column of Sales Person
  • Click on Advanced Filter
  • Choose the Copy of another location option
  • In the ‘Copy to’ field, choose a blank cell on the sheet outside the data
  • Leave the Criteria range blank
  • Tick on ‘unique records only’
  • Click OK

You will get a unique list of Sales Persons’ names.

Conclusion

Filter allows you to do a lot of tasks very quickly without having the use any formulas. But these tasks are very much limited to only viewing data that you really want to see right now. It doesn’t involve any calculations or data manipulation.

Advanced filter in Excel isn’t much known to even many Advanced Excel users. Once you know the power of the ‘Criteria Range’ feature, your filtering tasks become way more simple and fast.

Make sure you explore all conditional filter options in Text, Numbers and Dates to know it in-depth.

Founder & CEO at DLTC.co and Blogger at YouTube.com/ExcelRush. Certified Microsoft Office Specialist in Excel. Trained 2000+ working professionals and management students. Strength is training in Excel’s Advanced features & functions, programming using VBA in Excel, also Power BI & SQL. Multifunctional work experience of 11+ years. Dancer at Heart.

  • Data-Analytics

  • Your Comment

    Your email address will not be published.