To use the Advanced Filter, first set up a Criteria Range, preferably using empty rows inserted above your list. Copy field names to the top row of the empty cells and then, using different rows below the copied titles, insert what you want from each field. Using different rows allows constructing "OR" type filtering.
- [Voiceover] On this worksheet called Complex OR…we're about to try a filter, but it's not going to work.…The standard filter in Excel is a powerful tool…no question about that.…Let's go to the Data tab here,…click Filter, we got our filter arrows in place.…But what I'm about to do will not work…with a standard filter.…Here's what I would like to be able to do.…I'd like to get a list here comprised of any row,…any record where the Year entry here is more than 15,…or the Salary is over 80,000.…
Now I could start by choosing Year here,…Number Filters, Greater Than Or Equal To, or Greater Than…depending on how you want to set up the boundary.…How about greater than or equal to 15,…so if it is 15 or more I want to see it.…Click OK.…Now I've already eliminated some people…who have salaries over 80,000…because maybe they've only been here 12 years,…or three years, or eight years.…They're gone from the list.…
So if I am about to say I want a list of people…who've been either over 15 years or whose salary…is over 80,000, I can't get to it.…
- Prepping data for analysis
- Multiple-key sorting
- Sorting by rows or by columns
- Setting single- and multi-level subtotals
- Using text, numeric, and date filters
- Creating custom filters
- Filtering tables using slicers
- Using Advanced Filter
- Eliminating duplicate data
- Using SUMIF and COUNTIF functions for quick data analysis
- Working with the database functions such as DSUM and DMAX
Skill Level Intermediate
Excel Workshop: Working with Real-Time Datawith Chris Dutton1h 18m Intermediate
Data-Analysis Fundamentals with Excelwith Curt Frye2h 24m Beginner
Learn Microsoft Power BI Desktop: The Basicswith Gini von Courter2h 55m Intermediate
1. Data Preparation
2. Sorting Data
3. Creating Automatic Subtotals in Sorted Lists
4. Filtering Data
5. Advanced Filter
6. Eliminating Duplicate Data
7. Data Analysis Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.