SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)

Summary

In this video, the presenter uncovers a hidden feature of Excel's Advanced Filter, shared by Bryon. The video starts with a quick recap of how to use the Advanced Filter to filter and copy data to another tab based on specific criteria (e.g., articles containing 'laptop' and dates after July 1st). The twist comes when the presenter reveals how to filter and return only specific columns, not the entire data set. This simple but powerful trick saves time and streamlines data analysis. Viewers are encouraged to share whether they knew this feature and to explore more Excel tips.

Takeaways

• 😀 You can use Excel's Advanced Filter to quickly filter and copy data based on specific criteria.
• 😀 Wildcards can be used in the criteria to include variations of the text, such as including 'laptop' in any part of the article description.
• 😀 You can set multiple criteria, such as dates being greater than or equal to a specific value and matching specific keywords.
• 😀 The Advanced Filter can copy the filtered data to a different tab, helping you keep your original data intact.
• 😀 Instead of copying the entire data set, Excel allows you to copy only specific columns using the Advanced Filter.
• 😀 By selecting only the columns you want to return, you can create a more focused and efficient filtered result.
• 😀 Using Excel's Advanced Filter is more efficient than manually filtering and copying data from your source sheet.
• 😀 You can define your filter criteria on the same tab or in a separate tab, depending on your preference for organization.
• 😀 The hidden feature discussed allows filtering and copying columns that do not include the filter criteria.
• 😀 This feature can help save time by eliminating the need to manually copy or delete unnecessary columns after filtering.
• 😀 The video encourages viewers to share if they already knew about this Advanced Filter trick in the comments below.

Q & A

What is the main feature discussed in the video?

The video discusses a hidden feature in Excel's advanced filter that allows users to filter data and copy specific columns to another location, rather than the entire dataset.

How is advanced filter usually used in Excel?

Advanced filter is typically used to filter data based on specific criteria, and then either display the filtered results in place or copy them to another location, such as a different tab or worksheet.

What is the new trick revealed in this video about Excel's advanced filter?

The new trick is that when copying filtered data to another location, you can choose to copy only specific columns (such as sales or quantity) instead of the entire dataset, which was not commonly known.

What criteria are being used in the example dataset to filter the data?

The criteria used are: the article description must include the word 'laptop,' and the date must be greater than or equal to July 1st.

How are wildcards used in the advanced filter for the 'laptop' criterion?

Wildcards are used by placing an asterisk (*) before and after the word 'laptop' in the criteria. This ensures that any article description containing 'laptop' at any position (beginning, middle, or end) is included in the results.

Why does the speaker use an asterisk before and after 'laptop' in the criteria?

The asterisk is used to ensure that all instances of 'laptop' are captured, whether it appears at the beginning, middle, or end of the article description. This helps avoid missing any relevant results.

What should you do if you want to copy the filtered data to a different tab?

To copy the filtered data to a different tab, start by selecting the tab where you want the results to appear. Then, when using the advanced filter, choose 'Copy to another location' and specify the destination tab and cell for the results.

How do you specify which columns to return in the advanced filter?

Instead of selecting an empty cell when configuring the advanced filter, you highlight the headers of the columns you want to return. Excel will then filter the data and only return those specific columns.

What is the benefit of selecting specific columns in the advanced filter?

Selecting specific columns allows you to filter the data more efficiently by returning only the necessary information, which can save time and space in your report or analysis.

What is the final outcome after applying the hidden feature of advanced filter?

The final outcome is that Excel returns only the selected columns (e.g., sales document number, quantity, sales in USD) based on the defined criteria, without including the rest of the dataset, making the results more focused and relevant.