What is the best way to filter a large list in Excel?

19,762

Solution 1

You really should put that data in an Access table and use Excel's Database Query to do the job. Since it can also filter retrieved data based on a cell value, it's a great combination.
Storing the data in a database brings you another interesting option (depending on what you want to do): to query your database using PowerPivot.

Solution 2

As already mentioned by everyone, excel 2007 will take you to a million rows, but its slower than the excel 2003 that I presume you're using at the moment so filtering using it wouldn't be advisable.

Along with mysql, ms access is also an option.

Share:
19,762

Related videos on Youtube

Marc L
Author by

Marc L

Updated on September 15, 2022

Comments

  • Marc L
    Marc L about 1 year

    I have a table in Excel that I want to filter. It will have a maximum of 1 million rows and 80 columns. All the calculations etc are done programatically in arrays to cut dwn processing time. However, I want to also filter the results to display only certain results based on one column value, followed by a top 5% based on another filter value.

    When I first did the sheet, it was limited to 65000 results so there were no problems with the size of the data set. I just invoked the worksheet filter functions from code and did it that way. Can I do it that way with a larger data set or is there a way to filter an array the way you d a dataset on a sheet?

    Thanks

    • Alan K
      Alan K almost 11 years
      Excel 2007 onwards does indeed allow > 1 million rows; 1,048,576 to be precise. However using it to filter data sets that size is not, IMHO, a great idea unless you have a ridiculously awesome PC. Once you get into more than a few thousand records (rule of thumb) I think it's time to move the records into an SQL based database (such as SQL Server or My SQL) and run the queries in that. Handling very large volumes of data is what they live for. Excel... less so. You can do it, but the experience may not be pleasant.
    • brettdj
      brettdj almost 11 years
      There is a string function Filter that you can using with Transpose to work on a column (see stackoverflow.com/questions/12921350/…) but this won't handle a filter such as top 5%. You should just try using your current approach,if it takes too long (I'd be surprised), then look outside Excel
  • Marc L
    Marc L almost 11 years
    Yep, a solution I have used elsewhere, sadly here they are insisting on an excel only solution
  • lanartri
    lanartri almost 11 years
    I tend to think that when "clients" (or boss) ask for something that's obviously not the right approach, if you come with a clear and gentle explanation of why they will hit a wall, they do listen...
  • Marc L
    Marc L almost 11 years
    Yeah, ut here the to include a way for them to edit/add their own functionality. Since he is ignorant of Access/SQL everything has to be in Excel. Yep, some people will hobble themselves to maintain a sense of control.
  • Marc L
    Marc L almost 11 years
    Heh, I actually use 2010, client is usng excel 2007 so I just make sure not to use any functionality not supported by 2007.
  • Marc L
    Marc L almost 11 years
    Yep, manual calculation in most of the application, just turning on automatic calc at the end to get the results across the sheets and trigger some macros