You might have seen in a previous moviehow we could, using the regular filter,come up with the results that showwhen both conditions are true,but were looking for when either condition is true.So, for example, the person in Row 4 heredoesnt have 15 years of service,in fact, has only three,but certainly has more than 80,000 in terms of compensation.So we would want that person to appear in the list.And looking the opposite way.

But with the data management and analysis features in Excel 2016, you can keep the largest spreadsheets under control.

In this course, Dennis Taylor shares easy-to-use commands, features, and functions for maintaining large lists of data in Excel.

He covers sorting, adding subtotals, filtering, eliminating duplicate data, and using the programs Advanced Filter feature and specialized database functions to isolate and analyze data.

With these techniques, youll be able to extract the most important information from your data, in the shortest amount of time.

If you have assigned the list or Excel Table a range name, enter the range name.

As a consultant or analyst looking through data for answers you are going to need how to filter.

With the advanced filter in Excel you can filter and extract data using complex AND and OR criteria impossible with AutoFilter.

The Excel advanced filter can also extract filtered data into columnar reports.

Clicking one or more of the filter arrows displays a dialog box you can use to filter your list or Table.

You can turn the AutoFilter arrows off in an Excel Table when you need to.

If the list has filled cells touching the list border, then select the list headers and all data cells.

In this figure, the Region column contains text data, so only Text Filters are available in a side menu.

The conditional criteria, such as Equals, Contains, etc., change to match the type of filter.

The AutoFilters in all columns must be TRUE for data to display.

If you need to work around this limit, use the Excel advanced filter described starting at tip 11.).

Select checkboxes of the data you want to be displayed and clear checkboxes of data you want filtered.

Although Excel calls this the Top 10 AutoFilter, it does much more.

If you dont see this AutoFilter, check to make sure the column contains only numbers.

For example, to find cut but exclude instances that also contain Lite, click And, and select Does Not Contain, and type Lite.

If the column contains text, other than the header, the filter will not work.

Select start and end dates for the range by clicking the calendar icons at the right and selecting start and end dates.

You can use wildcards as substitutes for an individual character or string of characters in text filters.

They contain buttons that display a list of unique contents for the column you want as a filter.

Click one of the buttons and the list or Excel Table is filtered.

With the Excel Advanced Filter you can create very complex filters as well as extract filtered data into a new list.

This figure shows the list or Table on the left, a Criteria range in green, and the optional Extract range on the right.

For example, RegionWest OR RegionEast requires two rows with one Region header label.

Advanced Filter Excel For Mac