How to Use Excel Filters for Quick Data Analysis

TL;DR

Press Ctrl+Shift+L anywhere inside a data set to switch Excel filters on, and press it again to clear them. Filters hide rows rather than delete them, so the status bar shows counts like 6 out of 26 records. Copying a filtered range with Ctrl+A then Ctrl+C pastes only the visible rows.

Transcript

in today's video we're going to take a look at the basics of Excel filter options Excel filter options can come in really handy when you work with large data tables now there is a lot of options there so I'm gonna split this into two videos in this video we're going to cover the basics and in the next video we're gonna take a look at more advanced ... Read More

Key Insights

• Ctrl+Shift+L is the fastest way to toggle Excel filters. Clicking anywhere inside the data set and pressing it adds filter icons to the header row, and pressing it a second time clears the filter completely.
• Right mouse clicking a cell and choosing Filter then filter by selected cell's value is the quickest path to a single-value filter. Selecting a shirt blue cell this way returns only the rows containing shirt blue.
• Filtering hides rows instead of deleting them. The row numbers turn blue to signal hidden data, and the status bar at the bottom reports how many records are showing, for example 6 out of 26 records.
• Wildcards work directly inside the filter search box. Typing a star sign before and after a word, such as star shirt star, tick-marks every entry containing shirt anywhere in the text, so shirt does not have to be the first word.
• Clearing filters column by column is slow, so pressing Ctrl+Shift+L twice removes every filter and re-applies a clean one. The Home tab's Sort and Filter menu also has a Clear command that does the same job.
• Filter by color picks up the fill colors already used in the column. After highlighting cells in a color, the filter arrow's Filter by Color option lists those colors so a single click isolates them.
• Copying filtered results copies only the visible rows. Pressing Ctrl+A to highlight the area then Ctrl+C and pasting into a new tab produces a block with no hidden rows in the middle.
• The SUBTOTAL formula excludes hidden cells by default, unlike SUM which totals the entire range including filtered-out rows. Choosing function number 9 for sum and selecting the range gives a total that matches what is visible on screen.

Questions & Answers

Q: What is the keyboard shortcut to apply a filter in Excel?

Ctrl+Shift+L is the shortcut. Click anywhere inside your data set and press Ctrl+Shift+L, and filter icons appear on the header row. Pressing Ctrl+Shift+L again clears the filter. The same result can be achieved by going to the Home tab, opening Sort and Filter, and clicking Filter. Because the shortcut toggles, pressing it twice in a row is a fast way to remove all existing filter selections and start again with a clean, freshly applied filter.

Q: How do you filter by a specific cell value without opening the filter dropdown?

Right mouse click on the cell that holds the value you want, go to Filter, and choose filter by selected cell's value. Excel then shows only the rows containing that value. In the demonstration, right clicking a cell containing shirt blue filtered the table down to the six rows with shirt blue in them, out of 26 total records. This works even before a filter has been applied manually, making it the quickest route to a single-value filter.

Q: Does filtering in Excel delete the rows that are not shown?

No. The rows are hidden, not deleted. Two visual cues confirm this: the row numbers turn blue when a filter is active, and the status bar at the bottom of the window reports how many records are visible, such as 6 out of 26 records. The data is still present in the sheet and returns as soon as the filter is cleared, which is why a normal SUM formula over the range still includes the values in those hidden rows.

Q: How do you use wildcards in an Excel filter?

Type the star sign in the filter search box around your search term. Entering star shirt star tick-marks every item that contains the word shirt anywhere in the text, so blue shirt and white shirt are both matched. The star before the word means shirt does not have to be the first word, and the star after means other text can follow. The same trick works on other columns, for example typing site to catch both affiliate site and website. Alternatively, Text Filters has a Contains option that finds the same matches without typing wildcards.

Q: How do you filter Excel data by cell color?

Highlight the cells you care about with a fill color, then click the filter arrow on that column and choose Filter by Color. Excel automatically detects the colors already present in the column and lists them underneath the menu option, so no setup is required. Clicking one of those colors returns only the rows with cells in that color. In the video, cells were highlighted in two colors and then filtered down to just the green ones with a single click.

Q: Why does SUM give the wrong total on filtered data, and what should you use instead?

A normal SUM function totals every cell in the range it references, including the rows that the filter has hidden, so the result covers the whole data set rather than the visible subset. Use the SUBTOTAL formula instead. SUBTOTAL asks you to pick a function type first, where number 9 means sum, and then the range you want totaled. By default it excludes any hidden cells, so the answer matches what you see on screen. The AGGREGATE formula can also be used for this.

Q: How do you copy only the visible rows from a filtered list?

Apply your filter, press Ctrl+A to highlight the filtered area, then press Ctrl+C. Only the visible lines are copied, which you can see from the marching selection covering just those rows. Move to a new tab and press Ctrl+V, and the pasted block contains only the filtered rows with nothing hidden in the middle. Press Escape afterwards to clear the copy marquee. This avoids the common problem of pasting rows you deliberately filtered out.

Q: Why should you convert a filtered data set into an Excel table?

Press Ctrl+T, confirm the range and that your table has headers, and Excel converts the data into a table with a Table Tools tab. Tables keep all the filtering options plus more. You can add a total row and pick sum or count per column, and those totals respond to the filter, removing the need to remember the SUBTOTAL formula. Formulas that reference the table update when you add data, and charts and pivot tables referencing the table have their data ranges updated automatically. If you dislike the default table styling, you can clear it and keep your original look.

Summary & Key Takeaways

• Filters can be applied three ways: click inside the data set and press Ctrl+Shift+L, go to Home then Sort and Filter and click Filter, or right mouse click a cell and choose Filter then filter by selected cell's value. The last method instantly narrows the list to rows matching that cell, such as shirt blue.

• Filtered-out rows are hidden, not deleted, which is why row numbers turn blue and the status bar reports 6 out of 26 records. Individual values can be ticked or unticked, and wildcards typed into the search box, such as star shirt star, auto-select every entry containing that word anywhere in the text.

• Because hidden rows still count in a normal SUM, the SUBTOTAL formula with function number 9 sums only visible cells. Converting the range into an Excel table with Ctrl+T removes the need to remember that formula, adds a total row, and keeps referencing formulas, charts, and pivot tables updated as data grows.