Advance 🔥 Filter in EXCEL | Excel Interview | MS Excel [Hindi]

Summary

This tutorial explains how to use the Advanced Filter in Excel, highlighting its advantages over basic filtering. The video covers the process of filtering data by one or multiple criteria, such as department or state, and demonstrates how to copy filtered data directly to another location. Viewers learn to apply the advanced filter on multiple sheets, and how it simplifies tasks that would typically require manual copying and pasting. The tutorial also addresses common issues, like filtering across different sheets, ensuring a seamless and efficient workflow.

Takeaways

• 😀 Advanced filters are an important topic in Excel, especially for job interviews where the difference between basic filters and advanced filters is often questioned.
• 😀 Basic filters are useful for displaying and summarizing data, while advanced filters allow users to extract data directly to a different location without manual copying.
• 😀 The main advantage of advanced filters over regular filters is the ability to directly extract and paste filtered data to a separate area or even another sheet, saving time and effort.
• 😀 When applying an advanced filter, you need to define the 'list range' (the data to filter) and 'criteria range' (the filter conditions).
• 😀 The 'list range' should be automatically selected, but you can manually adjust it if necessary. Similarly, the 'criteria range' needs to be set to match your filtering needs.
• 😀 Once the criteria and list ranges are set, the filtered data can be copied to a new location by selecting the 'Copy to' option in the advanced filter dialog.
• 😀 If filtering by multiple conditions (e.g., state and department), you can apply multiple criteria in the advanced filter by using additional criteria columns.
• 😀 To filter data on multiple sheets, the selection for the filter must be made from the sheet where the data is located, and you must ensure that your selection is active on the target sheet before applying the filter.
• 😀 When you try to filter across sheets, remember that you must select the target sheet and define where the filtered data will be copied—this ensures proper functionality.
• 😀 The tutorial emphasizes the importance of understanding advanced filters, as they can save time and simplify complex data extraction tasks in Excel, particularly for data analysis and reporting.

Q & A

What is the main topic discussed in this video?

The video focuses on explaining the concept of advanced filters in Excel, how to use them, and their advantages over basic filters.

What is the primary difference between a simple filter and an advanced filter in Excel?

A simple filter allows you to view and summarize data, but requires you to manually copy and paste the filtered data. In contrast, an advanced filter automatically extracts filtered data to another location, streamlining the process.

How do you apply a basic filter in Excel?

To apply a basic filter, select the data range, press Control + Shift + Down Arrow and Right Arrow to select the entire range, then go to the 'Data' tab and click on 'Filter'. You can then filter by specific criteria, like state or department.

How does advanced filter help in managing filtered data?

Advanced filter allows you to not only filter the data but also copy it to another location, like a new sheet or a specific area, without manually copying and pasting the data.

Can you filter data across multiple sheets using advanced filter?

Yes, advanced filter can be used across multiple sheets. However, when filtering to a different sheet, you need to ensure the selection is done properly, starting in the target sheet for the filter to work.

What is the 'List Range' in the advanced filter options?

The 'List Range' refers to the entire data set that you want to apply the filter to. This range should include all the columns and rows that contain the relevant data.

How do you define the 'Criteria Range' in advanced filter?

The 'Criteria Range' is the range where you define the conditions you want to filter by. This could include columns like 'State' and 'Department' with specific values that the filter will look for in the data.

How does the 'Copy to' option work in the advanced filter?

The 'Copy to' option in the advanced filter lets you specify where the filtered data should be placed. You can copy the filtered data to a new location, even on a separate sheet, without altering the original data.

What happens if you try to copy filtered data to an inactive sheet?

If you try to copy filtered data to an inactive sheet, you will receive an error saying that you can only copy filtered data to the active sheet. To resolve this, ensure the target sheet is active when you apply the filter.

Can you apply multiple conditions in the criteria for advanced filtering?

Yes, you can apply multiple conditions in the criteria range. For example, you could filter for data from Uttar Pradesh and the 'Electronics' department at the same time by setting both conditions in the criteria range.