Download workbook: people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm

Topics in this video:
1. Advanced Filter: Cross Between Filter and Database Functions (00:34 min)
2. Advanced Filter 3 Areas: (01:31 min)
Criteria Area
Data Set
Extract Area: Be sure to keep area below CLEAR!!!! (Example at 19:33 min and 24:14 min and 27:42 min)
3. Advanced Filter is in Sort & Filter group on the Data Ribbon Tab (02:13 min)
4. Advanced Filter Keyboard: Alt, A, Q or Alt, D, F, A (02:23 min)
5. Advanced Filter dialog box: (02:53 min)
Action: In Place or Copy To Another Location
List: Proper Data Set
Copy to location
Unique Records
6. Extract with 1 condition (02:53 min)
7. Extract with AND criteria: Same Row (04:02 min)
8. Extract with OR Criteria: Different Rows (04:50 min)
9. Defined Names Created by Advanced Filter For Criteria Area and Extract Area (05:16 min and 19:10 min)
10. AND and OR Criteria (05:46 min)
11. Don't Need all fields (06:23 min)
12. Between Criteria for numbers: Repeat Field Names (07:03 min)
13. Between Criteria for Dates: Repeat Field Names (07:43 min)
14. Extract to Different Sheet: Start on sheet you want to extract data to (08:17 min)
15. Extract to Different Workbook: Start in workbook you want to extract data to (09:10 min)
16. Unique List (11:14 min)
17. Unique List with Criteria (David Method) (12:33 min)
18. Unique Records (14:38 min)
19. Which to use: Filter or Advanced Filter? (15:43 min)
20. Filter with 4 criteria (AND and OR Criteria) (16:11 min)
21. Advanced Filter with 4 criteria (AND and OR Criteria) (17:42 min)
22. Run Advanced Filter Multiple Times (18:57 min)
23. Advanced Filter Replaces Data Below the Extract Area (19:33 min and 24:14 min and 27:42 min)
24. Helper Column with Logical Formula to Extract every 5TH record (20:35 min)
Extract every nth record
25. Advanced Filter Formula with Logical Formula to Extract every 5TH record (24:49 min)
Formula and empty cell above used as Criteria for Advanced Filter (25:41 min)
26. Advanced Filter Formula to Extract Items In List 2 That Are NOT In List 1 (28:32 min)
Extract items not in list. Compare two lists. Compare two lists extract records not in List


Excel Extract Unique List with Criteria David Method

1. Back To Basics: Number Format, Keyboards, PivotTables, Formulas
2. Comprehensive Excel Formula Types & Elements Video 12 Examples
3. How Formulas Calculate: Order of Precedence in Excel
4. Percentage Increase or Decrease Formula
5. Cell References: Relative, Absolute & Mixed (8 Examples)
6. Scenarios To Save Sets Of Formula Inputs, Paste Special Trick
7. Worksheet & Workbook References, 3-D Cell References
8. Excel Table Formula Nomenclature / Structured References 22 Ex.
9. Defined Names & Aggregate Functions
10. AND & BETWEEN Criteria: SUMIFS, DSUM, COUNTIFS, AVERAGEIFS
11. Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM
12. AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT
13. AND & OR Criteria Calculations: Table Feature
14. AND, OR, IF Functions For AR Dept (Multiple Logical Tests)
15. IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs
16. Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...
17. Introduction To SUMPRODUCT Function
18. VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP
19. Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP
20. VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples
21. INDEX and MATCH Functions For Unusual Lookups 4 Examples
22. CHOOSE function to lookup table for VLOOKUP With Multiple Tables
23. VLOOKUP and IFNA for Commissions
24. Getting Top 5 Values With LARGE And Bottom 5 With SMALL
25. Goal Seek: Get Formula To Evaluate To Desired Result
26. Excel Compare Two Lists Formula: Item In List? Item Not In List?
27. How To Track Down Excel Formula Errors (16 Examples)
28. Excel Data Validation: List, Date, Time, Custom (22 Examples)
29. Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example)
30. Custom Number Formatting & Text Functions, (33 Examples)
31. Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More
32. Introduction To Array Formulas and Array Functions
33. Sorting In Excel. Excel Sort (13 Examples)
34. Excel Subtotals, Add Subtotals based on One or Two Columns
35. Excel 2013 PivotTables: Basic To Advanced 58 Examples
36. Excel Filter For Extracting Records with AND or OR Criteria
37. Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
38. Excel Text To Columns To Split or Convert Data 7 Examples
39. Excel Import Data: Text File, CSV File, Access, Excel File
40. Conditional Formatting Basic To Advanced 50 Examples
41. Review Of Chart Basics For Excel 2013
42. Save Chart As Template in Excel 2013
43. Set Default Chart in Excel 2013, Keyboard Shortcuts Charts
44. Pie within Pie Chart in Excel 2013
45. Combining Chart Types and Secondary Axis in Excel 2013
46. Statistics: Histogram Formulas & Chart in Excel 2013
47. Statistics: Dot Plot (Cell Chart)
48. Conditional Formatting: Bar Chart with Data Labels
49. Break Even Analysis Formulas & Chart, Plotting Break Even Point
50. Recorded Macro Basics, including Absolute & Relative References

This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. It contains 50 video lectures teaching the basics of Excel, as well as advanced concepts.

Be sure to download the workbook to follow along with the examples. They are found on the official course website.