How to Speed up Excel Auto Filter

Excel filtering columns can get extremely slow if you have a large amount of data.  However sometimes excel filtering is slow even if you don’t have a lot of rows of data.  There are usually 2 causes for this.

Problem

  1. There are empty cells towards the bottom of the sheet that excel thinks has values so the filter tries to work on the large data set even though most of the rows are actually empty.
  2. There are lots of formulas in the table causing excel to have to recalculate all the values everytime you apply the filter.

To fix these problems basically looking to decrease the amount of data and calculations.  We can do this by copying only the cells with data into a new sheet and getting rid of all formulas in the table.

Solution

  1. Copy only the cells that actually have values and paste them in a new sheet or workbook.  This gets rid of all the false blank cells.
  2. Copy and paste values to get rid of all formulas in sheet.  This means excel doesn’t have to use memory to calculate the values every time you apply a filter.
  3. Save file as .csv which is just a text file and should run faster than .xls or .xlsx.