CTRL + SHIFT + L: Insert a Filter in Excel

Excel filters allow you to organise data without changing the original dataset. The quickest way to insert filters is to use keyboard shortcuts. In this article, you will learn how to insert a filter in Excel.

Key Takeaways:

• Press Ctrl + Shift + L to add or remove filters.
• If you click anywhere on a cell in the dataset, filters are applied to the entire dataset.
• Turning off filters with the shortcut doesn’t delete your data.
• Once filters are on, you can sort, filter blanks, or find duplicates with just a few clicks.

 

Introduction to Filters

What are filters?

The filter allows you to display specific rows based on selected criteria. Instead of manually searching the data row by row, you can use a filter to view data that meets your requirements.

For example, if you have a sales report containing data from multiple regions, you can filter the spreadsheet to display only sales from a particular region.

Benefits of Filters

• Helps to quickly search information.
• Large datasets are easier to manage using filters.
• Improves reporting accuracy.
• Saves time when working with large data.

 

How to Insert a Filter in Excel

• Select any cell in the dataset.
• Press Ctrl + Shift + L
• Excel will add filters to the column headers.

If filters are already applied, pressing Ctrl + Shift + L again removes them.

 

Use the Filter Menu After Applying Filters

Open a Filter Menu

• Select a column header that contains a filter.
• Press Alt + Down Arrow.

Navigate the Filter Menu

• Up Arrow / Down Arrow – Move through filter options.
• Spacebar – Select or deselect a checkbox.
• Tab – Move between menu sections.
• Enter – Apply the selected filter.
• Esc – Close the filter menu.

Remove Filters

To remove all filters from the dataset, press:

• Ctrl + Shift + L

 

FAQs

What does CTRL + SHIFT + L do in Excel?

It applies or removes filters to your table headers. It allows you to quickly access sorting and filtering options.

Do I need to select the whole table before using the shortcut?

No, just place your cursor anywhere inside your data range. Excel will automatically detect and apply filters to the entire dataset.

What if I accidentally remove the filters?

You can press CTRL + SHIFT + L again to reapply them.

Can I use this shortcut on a table that already has Excel Table formatting?

Yes, Excel Tables have built-in filters, but if they’ve been turned off, this shortcut will bring them back.

Will this shortcut work on blank rows or non-contiguous data?

No, it only works properly on data that is in a continuous block with headers. Blank rows may cause unexpected behavior.