3 Incredible easy Filtering Tips and Tricks in Excel: for Biginners

3 Incredible easy Filtering Tips and Tricks in Excel: Unleash Your Data Wizardry!


“Discover 3 amazing Excel filtering tips and tricks that can elevate your data handling to a pro level. Learn how to navigate your data effortlessly. This easy-to-follow guide is perfect for Excel users at all skill levels.”


1. Wild About Wildcards? Asterisk and Question Mark

Ever felt like finding a needle in a haystack while looking for specific text in Excel? Wildcards are your new best friends. They’re like those super flexible yoga instructors who can take any pose.

  • Asterisk (*): Think of this as the ‘anything goes’ wildcard. Type “A*” to filter everything that starts with ‘A.’ Want all the names that start with ‘J’? Just use “J*.” It’s like a magic wand!
  • Question Mark (?): This wildcard is a bit more precise. It’s for times when you know the exact pattern but are missing just a character or two. “A?” will filter all two-letter words starting with ‘A.’

Example! 

Using a data set of product names.

filter product name

Utilizing Wildcards:

If you want to filter all the products that start with “Apple,” you can use the asterisk (*) wildcard:

  • Go to the “Data” tab on the Ribbon at the top of Excel.
  • Click on the “Filter” button in the “Sort & Filter” group. or “Ctrl + Shift + L”

filter appears

  • Click the filter box -> Text Filters -> Custom Filter

custom filter

  • Type “Apple*” Make sure you add * under equals

add

The result will look like this:

result 1

  • using Question mark: do the same thing,
  • Click the filter box -> Text Filters -> Custom Filter
  • This time under contains, enter “banana?” with a question mark ?

add question mark

Here is the result!

banana result

Wildcards aren’t just cool symbols; they’re super handy tools that make text filtering a breeze.

2. A Colorful Way to Filter

If you’ve ever used highlighters to mark important notes (who hasn’t?), you’ll love this one. Excel lets you filter by color. Imagine your data is a beautiful rainbow, and you can pick and choose the shades you want to see. It’s not only visually appealing but super functional, especially if you’ve color-coded your data.

Let’s say you’ve color-coded the “Status” column as follows:

color filter

  • Select the Column: Click on the “Status” column header, where the color-coded cells are.
  • Activate the Filter: Click on the “Data” tab, and then click the “Filter” button.
  • Click the Dropdown Arrow: In the “Status” column, click on the dropdown arrow that appears after activating the filter.
  • Choose the Color: Hover over “Filter by Color” in the dropdown menu. A submenu will appear displaying the colors used in that column. Select the yellow color (or the color you want to filter by).

filter by color

  • View the Filtered Data: Only the rows with the yellow “Completed” status will remain visible. In this case:

yellow reslut

3. Get Custom with Auto Filters

Auto Filters are like those customizable burgers where you choose the toppings. Want to filter numbers between 10 and 20 or dates within the last month? Custom Auto Filters let you define your unique conditions. It’s like having your chef who knows exactly how you like your burger!

Suppose you have a dataset of book sales, including information about the title, author, genre, and sales figures:

auto filter

Let’s say you want to filter books by Jane Smith that have sold more than 1000 copies:

  • Select the Data: Click anywhere within the data range or select the columns you want to filter.
  • Activate the Filter: Go to the “Data” tab and click the “Filter” button. You’ll see dropdown arrows appear in each column header.
  • Filter by Author:
    • Click the dropdown arrow in the “Author” column.
    • Select “Text Filters” > “Equals” and type “Jane Smith” into the box.
    • Click “OK.”

equals jane smith

jane result

  • Filter by Sales:
    • Click the dropdown arrow in the “Sales” column.
    • Select “Number Filters” > “Greater Than” and type “1000” into the box.
    • Click “OK.”

grather than 1

  • View the Filtered Data: The data now shows only the books by Jane Smith that sold more than 1000 copies:

grather than result

If you are interested in how to sort! click here


There you have it, friend – five incredible filtering tips and tricks that can help you become an Excel pro. From wildcards to color filtering, these are more than just features; they’re life-savers for anyone who loves (or sometimes wrestles with) Excel.

These tips are like secret shortcuts on a treasure map, leading you to hidden Excel gems. Next time you’re navigating a sea of data, remember these tricks up your sleeve. Whether you’re an Excel rookie or a seasoned data warrior, these tips will add a spark to your Excel journey.

So why not give them a try? Go on, open that Excel sheet, and let’s get filtering. Who knows, you might just discover a newfound love for data handling.

Trust me; your spreadsheets will thank you!

Leave a Comment