Excel Guides

How to increase the AutoFilter drop-down limit in Excel

If you’re using a large data set in Microsoft Excel, you may find yourself frustrated when trying to use the AutoFilter feature. This is because the default number of items that appear in the drop-down list is 10,000. While this may be sufficient for smaller data sets, it can be a hindrance when working with larger ones.

Fortunately, there is a way to increase the limit on the number of items that appear in the AutoFilter drop-down list. By making a small change to the Windows Registry, you can increase the limit to 1 million items.

Before proceeding, it’s important to note that making changes to the Windows Registry can be dangerous. If you don’t know what you’re doing, you could potentially damage your computer. For this reason, it’s always a good idea to create a backup of the Registry before making any changes.

With that said, here’s how to increase the AutoFilter drop-down limit in Excel:

  1. Open the Registry Editor by pressing Windows+R, then typing “regedit” and pressing Enter.

  2. Navigate to the following key:

    [HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options]
  3. (If you’re using a different version of Office, replace “14.0” with the appropriate number.)

  4. Create a new DWORD (32-bit) Value and name it “MaxMenuItems”.

  5. Double-click “MaxMenuItems” and set its value to “10000”.

  6. (You can actually set the value to any number between 1 and 1 million.)

  7. Close the Registry Editor and restart Excel.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.