In large Excel spreadsheets, auto filters might work slowly and periodically crash. Here are 7 tips on how to speed up filtering in Excel and sometimes work with filters in general. When you have multiple filtering criteria or complex calculations that are involved typical Excel auto filter usage, it is not the best tool for the job.
Only a fool blames his tools. Sometimes you have to adjust what you are doing. Here are the 7 tips that fill help filter large Excel file content faster and significantly speed up this process.
1. Speed up filtering in Excel by using advanced filters
I think that the advanced filter in Excel is underrated. By using that, you will significantly increase the speed of the filtering process by multiple criteria that you cannot use in the auto filter at the same time.
If you are creating a list of filtering criteria, you can reuse it later or send it to someone else. Sometimes there is a question on how the data is filtered. If you have all the filtering parameters as below, it is a simple question.
Excel advanced filter location is in an Excel Data tab right to the auto filter.
2. Use by selected cells value to filter to speed up filtering in Excel
Sometimes you have a single parameter in a column that is necessary to filter your data. You might spend your time waiting for the auto filter to respond and repeat if it is crashing.
By using this simple trick, you will eliminate waste of time instantly.
1. Select a column where you want to filter.
2. Use shortcut Ctrl + F to search for one of the possible values that you want to filter.
3. Right-click on the cell that contains the filtering parameter. Choose to filter by selected cells value.
3. Clear column filter without opening the auto filter
If you want to clear the filter in a large table, it might cause the auto filter to freeze up. Don’t do that.
Try to right-click on the column that is filtered. Choose Filter section and clear filter for that column.
If you want to clear the filter for all the columns, use the Clear button in the Data tab.
4. Use the FILTER function to collect necessary data
Here is a simple instruction on how to use the FILTER function in Excel. It is a relatively new function but will speed up filtering in Excel.
5. Turn off automatic calculations to speed up filtering in Excel
Sometimes the use of filters might initiate recalculations in Excel.
If you want to stop recalculations when it is not necessary, then turn them off. Go to the Formulas tab, and in the Calculation group, click the Calculation Options button. There will be an option to set them manually.
Do not forget what you did. Turn calculations back to automatic when you are done with filtering. That is the default behavior, and usually, that is what you want.
6. Use Power Query to transform your data
Power Query works with a sample of your data, and that makes it very efficient in handling large datasets. It is also worth mentioning that it is available in Power BI, and knowing how to use that in Excel might be useful in Power BI. Here is a list of inspirational posts about Power Query.
You can find Power Query in the Excel Data tab. Get & Transform group contains tools that allow to get data and transform it by using a filter or other tools.
7. Try other tools for the filtering data
Excel is not a tool for every possible data-related task. If you are getting a feeling that you are stuck, consider learning new tools.
Here is a post that will explain to you my approach to switching from Excel to R programming language. The same principles might apply if you consider another tool.
Leave a Reply