Excel - Advanced Filter Is Faster than Looping in VBA - Episode 2060

Microsoft Excel Tutorial: Advanced Filter in Excel.

Welcome to MrExcel netcast, where we bring you the latest tips and tricks for mastering Excel. In this episode, we will be discussing the advanced filter feature in Excel and how it can help you quickly and efficiently extract data from large datasets. This topic was inspired by a question from one of our viewers, Mort, who was struggling with processing 100,000 rows of data in VBA using arrays. I suggested using the advanced filter instead, and in this video, we will explore how to use this powerful tool.

First, let's review the basics of the advanced filter. It requires an input range, which is your data, and at least one of the following: a criteria range or an output range. In today's example, we will be using both. The input range must have headings above the data, so we will temporarily insert a row and add some generic headings. The output range is where we will specify which columns we want to extract from the input range. And finally, the criteria range is where we will define the criteria for filtering the data.

Now, let's take a closer look at how to set up the advanced filter. We will select our input range, which in this case is A1:D100000, and then specify our output range, which will be A1:C1. Next, we will define our criteria range, which will be the heading "Year" and the value we want to filter for, such as 2014. It's important to note that in older versions of Excel, the output range cannot be on a different sheet, so you may need to use a named range for the input range in order for the advanced filter to work.

Once we have all the ranges set up, we can go to the Data tab and click on Advanced Filter. We will select the option to copy the filtered data to another location and specify our output range. And just like that, the advanced filter will quickly extract all the data that matches our criteria. This process can be repeated for different criteria, making it a very efficient way to filter large datasets.

But what if you want to use the advanced filter in VBA? Well, it's even easier. With just one line of code, you can perform the entire advanced filter process. We will demonstrate this by writing a simple macro that will ask the user for the year they want to filter for and then create a new sheet with the filtered data. This is a much faster and more efficient method than using arrays or loops in VBA.

In conclusion, the advanced filter is a powerful tool that can save you a lot of time and effort when working with large datasets. It's more advanced than the regular filter because it can copy the filtered data to a new range, and it also allows for complex criteria to be defined. So next time you have a large dataset to filter, remember to use the advanced filter and see how much time and effort it can save you. Thank you for watching this episode of MrExcel netcast, and don't forget to subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153885/

This video answers these common search terms:
how to filter advanced criteria in excel?
how to advanced filter in excel
how to use advance filter on excel
what is an advance filter excel
how to use advanced filter in excel to copy information
how to do an advanced filter excel
how to use the "advanced filter" in excel
how to insert an advanced filter excel
can i remove duplicates in advanced filter excel

Table of Contents:
(00:00) Excel Advanced Filter
(00:23) Explanation of Advanced Filter and its requirements
(00:35) Using Advanced Filter with input, criteria, and output ranges
(00:52) Adding headings to input and output ranges
(02:02) Using Advanced Filter in Excel 2016 and older versions
(02:40) Step-by-step guide to using Advanced Filter
(03:32) Benefits of using Advanced Filter in VBA
(04:05) Writing code for Advanced Filter in VBA
(06:02) Running the code and testing it
(08:18) Recap of the episode and tips for using Advanced Filter
(08:28) Clicking Like really helps the algorithm

Mort is trying to process 100K rows in VBA by looping through records or using an array
It will always be faster to use built-in Excel features than writing your own code.
You need an Input range, and then a Criteria Range and/or an Output Range
For the input range: single row of headings above the data

Видео Excel - Advanced Filter Is Faster than Looping in VBA - Episode 2060 канала MrExcel.com