Comprehensive Excel 2013: Basics To Advanced

Video Lectures

Displaying all 50 video lectures.
Lecture 1
Back To Basics: Number Format, Keyboards, PivotTables, Formulas
Play Video
Back To Basics: Number Format, Keyboards, PivotTables, Formulas
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Cells, Worksheets, Sheet Tabs and Workbooks (0:01:14 min)
2. Navigating Sheets in Excel 2013 (0:02:14 min)
3. Keyboards (0:03:30 min)
4. Number Formatting as Façade (0:08:51 min)
5. Types of Data in Excel (0:21:36 min)
6. Alignment (0:23:20 min)
7. Proper Data Sets, Non-Proper Data Sets (0:27:47 min)
8. Raw Data Into Information using Formula (0:29:28 min)
9. PivotTable #1: Raw Data Into Information using PivotTable (0:33:00 min)
10. Bad Data and Tracking Down Formula Errors (0:39:38 min)
11. Excel Tables (0:45:42 min)
12. Style & Number Formatting for cells (0:53:40 min)
13. PivotTable #2: Style & Number Formatting for PivotTable (1:00:39 min)
14. Page Setup (1:04:34 min)
15. Sorting (1:11:04 min)
16. Filtering (1:13:15 min)
17. PivotTable #3: PivotTables (1:17:35 min)
18. Charts (1:19:12 min)
19. PivotTable #4: Recommended Chart (1:25:28 min)
20. Excel's Golden Rule (1:26:42 min)
21. IF function (1:29:53 min)
22. VLOOKUP function (1:33:35 min)
Lecture 2
Comprehensive Excel Formula Types & Elements Video 12 Examples
Play Video
Comprehensive Excel Formula Types & Elements Video 12 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
Overview: (00:39 min)
1. Hours worked formula. Type of Formula: Calculating. Formula Elements: Equal sign, parenthesis, cell reference, math operator, cell reference, parenthesis, math operator, number that does not change. (03:31 min)
2. Gross pay formula. Type of Formula: Calculating. Formula Elements: Equal sign, cell reference, math operator, cell reference. (5:13 min)
3. Number of sales each sales rep made formula. Type of Formula: Calculating. Formula Elements: Equal sign, function, absolute range of cells, relative cell reference. (05:38 min)
4. Text extracting formula. Type of Formula: Text Formula. Formula Elements: Equal sign, RIGHT function, relative cell reference, number that does not change. (07:31 min)
5. Text join formula. Type of Formula: Text Formula. Formula Elements: Equal sign, relative cell reference, join symbol, ampersand ( & ), relative cell reference. (09:05 min)
6. Over Hurdle formula. Type of Formula: Logical Formula. Formula Elements: Equal sign, relative cell reference, comparative operator, absolute cell reference. (10:03 min)
7. IF function to show "over" or "under" text formula. Text Formula that contains the formula elements: equal sign, IF function, relative cell reference, comparative operator, absolute cell reference, "text". (11:19 min)
8. VLOOKUP to retrieve employee record formula. Type of Formula: Lookup Formula. Formula Elements: Equal sign, VLOOKUP function, absolute cell reference, absolute range of cells, function argument element. and Drop-down arrow: Data Ribbon, Data Validation, List. (13:50 min)
9. Robust Net Income Formula. Type of Formula: Calculating. Formula Elements: Equal sign, cell reference, math operator, built-in function, range of cells. (17:54 min)
10. Add Top 3 Sales Formula. Type of Formula: Array Formula. Formula Elements: Equal sign, SUM function, LARGE function, range of cells, array constant. (20:51 min)
11. Add with 2 criteria (conditions) formula. Type of Formula: Calculating formula. Formula Elements: Equal sign, SUMIFS function (add with 2 criteria), range of cells, cell reference. (26:02 min)
12. Count with 2 criteria formula (count numbers between a lower and upper limit) formula. Type of Formula: Calculating formula. Formula Elements: Equal sign, COUNTIFS function (count with 2 criteria), absolute range of cells, relative cell reference. (29:05 min)
Types of formulas:
1. Calculating Formulas that calculate a number answer (like adding)
2. Logical Formulas that give you a logical value, either TRUE or FALSE (like formulas that say whether two accounts are in balance)
3. Text Formulas that deliver a word to a cell or create labels for reports (like a label for a loan payment).
4. Lookup Formulas that look up an item in a table (like looking up a price for a product).
5. Array Formulas are advanced formulas that contain a formula element that operate on arrays instead of individual items and produce an array of answers.
Formula elements:
1. Equal sign (starts all formulas).
2. Cell references (also defined names, sheet references, workbook references, table nomenclature).
3. Math operators.
4. Numbers (if the number will not change; for example, 12 months, 24 hours).
5. Functions (IF, MATCH, INDEX, COUNTIF, and so on).
6. Function argument elements (such as a 0 in the third argument of MATCH to tell function to do an "Exact Match" lookup,or FALSE in the 4th argument of VLOOKUP to do an "Extact Match" lookup).
7. Comparative operators.
8. Join operator: Ampersand (join symbol: &).
9. Text within quotation marks (for example, "For the Month Ended").
10. Array constants (for example, {1,2,3}).
Lecture 3
How Formulas Calculate: Order of Precedence in Excel
Play Video
How Formulas Calculate: Order of Precedence in Excel
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

How Formulas Calculate: Order of Precedence in Excel. Topics in this video:
Parenthesis ( )
Reference Operators: colon, space, comma
Example of colon in range of cells
Example of intersection operator
Example of comma (union
Negation
Converts %
Exponents
Multiplication (*) and Division (/), left to right
Adding (+) and Subtracting (-), left to right
Ampersand (&) (Join operator)
Comparative operators
Lecture 4
Percentage Increase or Decrease Formula
Play Video
Percentage Increase or Decrease Formula
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video: Percentage Increase or Decrease Formula
Lecture 5
Cell References: Relative, Absolute & Mixed (8 Examples)
Play Video
Cell References: Relative, Absolute & Mixed (8 Examples)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Relative Reference Example, Net Income Formula (01:09 min)
Ctrl + Enter to populate a range of cells with a formula (02:02 min)
2. Relative and Absolute Reference Example, Increase Price Formula (02:20 min)
Ctrl + Period ( . ) to jump to each corner of a highlighted range (04:29 min)
3. Relative and Absolute Reference Example, Commission Formula (05:00 min)
4. A. Relative and Absolute Reference Range Example, SUMIFS formula for adding with one condition. Top of sheet CR(4). (05:51 min)
5. Mixed Cell Reference Example, Income Statement Budget Example (07:56 min)
6. Mixed Cell Reference Example, Multiplication Table Example (19:23 min)
4. B. Mixed Cell Reference Example, Cross Tabulated Table with SUMIFS formula for adding with two conditions (criteria). Bottom of sheet CR(4). (22:47 min)
7. Mixed Cell Reference Example, Future Value (Value of pension example) (28:34 min)
Lecture 6
Scenarios To Save Sets Of Formula Inputs, Paste Special Trick
Play Video
Scenarios To Save Sets Of Formula Inputs, Paste Special Trick
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. What If Analysis (00:15 min)
2. Add Formula Inputs As A Scenario using Scenario Manager (01:10 min)
3. Alt keyboard for Scenario Manager: Alt, T, E (02:29 min)
4. Budget Paste Special Trick : Paste Special Operations and Values to increase all percentages in Budget by 10% (03:25 min)
5. Add Scenario button to Quick Access Toolbar (QAT) (04:55 min)
Lecture 7
Worksheet & Workbook References, 3-D Cell References
Play Video
Worksheet & Workbook References, 3-D Cell References
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Worksheet References, Sheet References, (00:20 min)
2. Workbook References (02:08 min)
3. 3-D Cell References (07:16 min)
Lecture 8
Excel Table Formula Nomenclature / Structured References 22 Ex.
Play Video
Excel Table Formula Nomenclature / Structured References 22 Ex.
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Excel Table feature works for Proper Data Sets (00:48 min)
2. Single cell, then keyboard to convert proper data set to Excel Table: Ctrl + T ("List" in Excel 2003 use Ctrl + L) (01:16 min)
3. TableTools, Design Ribbon Tab, Properties group, Table Name: "NoSpacesInName" (01:50 min)
4. Formatting
5. Sorting (02:17 min)
6. Filtering (02:32 min)
7. Total Row (02:36 min) Right-click, Ribbon and Keyboard for Total Row!!!!
8. Keyboard for Total Row (3:33 min)
9. Slicer Formula Tricks (04:20 min)
10. SQUARE BRACKETS are used for Field Names in Table Formula Nomenclature (Structured References) (06:56 min)
11. Dynamic/Expandable ranges for: Formulas (07:33 min)
12. You can create formula first, then convert proper data set to Excel Table (08:01 min)
13. Or use Table Formula Nomenclature (Structured References) (09:20 min)
14. Table names and field names in square brackets (09:20 min)
15. Dynamic/Expandable ranges for: Chart (10:02 min)
16. Dynamic/Expandable ranges for: Data Validation List (12:06 min)
17. Dynamic/Expandable ranges for: VLOOKUP table (13:09 min)
18. Dynamic/Expandable ranges for: PivotTables (14:34 min)
19. Tables Names in Name Manager (16:28 min)
20. Can type formulas on different sheets. When typing formula, list of field names & other elements from table show up in a drop-down list (16:59 min)
21. Easy to read formulas (17:40 min)
22. To lock a field you can use the syntax: [[Field]:[Field]] (20:58 min)

Excel Table feature. Excel table. Excel Table Expandable Ranges, Excel Table Dynamic Ranges. Slicer Magic, Excel 2013 Slicer Table, Excel Table Filter Slicer, Slicer For Totals Row in Excel Table.

Highline Excel 2013 Class Video 08: Excel Table Formula Nomenclature / Structured References 22 Examples
Lecture 9
Defined Names & Aggregate Functions
Play Video
Defined Names & Aggregate Functions
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Create Defined Name with Name Box. (01:03 min)
2. Name Manager. Keyboard = Ctrl + F3 (02:19 min)
3. Create Names From Selection. Keyboard = Ctrl + Shift + F3 (03:23 min)
4. Enter names into aggregate functions. Paste Name in formulas keyboard = F3. (04:12 min)
5. #NAME? error when Defined Name if Deleted. (07:10 min)
6. Create multiple Names From Selection. (08:17 min)
7. AGGREGATE function to add and ignore errors. (10:20 min)
Lecture 10
AND & BETWEEN Criteria: SUMIFS, DSUM, COUNTIFS, AVERAGEIFS
Play Video
AND & BETWEEN Criteria: SUMIFS, DSUM, COUNTIFS, AVERAGEIFS
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. AND Criteria for SUMIFS, COUNTIFS, AVERAGEIFS (00:34 min)
2. DSUM (05:18 min)
3. SUMIFS with 3 criteria (07:53 min)
4. Use COUNTIFS or COUNTIF for 1 condition? (10:36 min)
5. Use SUMIFS or SUMTIF for 1 condition? (12:35 min)
6. Between Criteria (Lower and Upper Limits) with comparative operators in Formula Input Cells (17:03 min)
7. Between Criteria with Text Labels and comparative operators joined to cell references in formula (21:43 min)
8. Quarterly Report: Between Criteria for Dates. Between an upper and lower date for each quarter. Comparative operators joined in formula. (28:44 min)
9. EOMONTH function (30:15 min)
10. Compare COUNTIFS formula and PivotTable for Between Criteria with an upper and lower limit (34:13 min)
Lecture 11
Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM
Play Video
Calculations With OR Criteria: COUNTIFS, DCOUNTA, SUMIFS, DSUM
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. OR Criteria (00:12 min)
2. OR Criteria for counting with 2 COUNTIFS (00:55 min)
3. OR Criteria that does counts and yields incorrect answer (don't double count) (03:40 min)
4. OR Criteria for counting with DCOUNTA (04:30 min)
5. OR Criteria for adding with 2 SUMIFS (06:49 min)
6. OR Criteria for adding with DSUM (08:23 min)
7. Many OR Criteria adding, long method where you helper cells to make intermediate steps (10:10 min)
8. Many OR Criteria adding: DSUM function (10:43 min)
9. Many OR Criteria adding: SUMIFS and a function argument array operation and the SUMPRODUCT function (12:57 min)
10. Many Or Criteria for a proper data set: DCOUNTA for counting and DSUM for adding (17:26 min)
11. OR and AND Criteria for a adding in Cross Tabulated Table: SUMIFS, a function argument array operation, the SUMPRODUCT function and Mixed Cell References (19:06 min)
Lecture 12
AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT
Play Video
AND & OR Criteria Calculations: D Functions like DSUM, DCOUNT
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. D Functions (00:32 min)
2. AND Criteria for DCOUNT, DSUM, DAVERAGE, DSTDEV (01:20 min)
3. OR Criteria for DSUM (05:50 min)
4. AND and OR Criteria for DSUM (07:02 min)
Lecture 13
AND & OR Criteria Calculations: Table Feature
Play Video
AND & OR Criteria Calculations: Table Feature
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Excel Table (00:37 min)
2. Total Row (01:04 min)
3. AND Criteria with Table Feature (01:59 min)
4. OR Criteria with Table Feature (04:07 min)
5. AND and OR Criteria with Table Feature (04:40 min)
Lecture 14
AND, OR, IF Functions For AR Dept (Multiple Logical Tests)
Play Video
AND, OR, IF Functions For AR Dept (Multiple Logical Tests)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video: Analyze customer credit worthiness with logical formulas:
1. Analyze customer credit worthiness (00:23 min)
2. AND functions (01:42 min)
3. IF function and AND function (03:45 min)
4. OR function (06:05 min)
5. Formula Evaluator (Evaluate Formula) = Alt, M, V (07:47 min)
6. AND and OR function together (08:23 min)
Accounting Accounts Receivable Credit Department Evaluate Customer's Credit worthiness.
Lecture 15
IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs
Play Video
IF & IS Functions To Deliver Numbers, Text, Formulas, Nested IFs
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. IF Function to deliver numbers (00:33 min)
2. IF Function to deliver text (01:37 min)
3. IF Function to deliver formulas (03:19 min)
4. IF Function to deliver functions (05:19 min)
5. Nested IFs (07:51 min)
6. Null Text String, LEN function and ISTEXT function (10:24 min)
7. Checkbook example 1: IF functions, Null Text String and equal comparative operator (12:23 min)
8. Checkbook example 2: IF functions, Null Text String and NOT comparative operator (17:27 min)
9. Examples of IS functions in Excel: ISTEXT function, ISNUMBER function, ISLOGICAL function, ISBLANK function, ISERROR function, ISERR function, ISNA function, ISNONTEXT function, ISFORMULA (18:17 min)
10. Checkbook example 4: IF functions, ISBLANK function (19:30 min)
11. Checkbook example 5: IF functions, ISNUMBER function (20:42 min)
12. Checkbook example 6: IF functions, ISTEXT function (20:21 min)
13. Baseball data Set example IF function and COUNTIFS function (22:21 min)
Lecture 16
Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...
Play Video
Round Functions: ROUND, MROUND, ROUNDUP, CEILING, more...
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. ROUND function so Invoices and Payroll spreadsheets are accurate (00:09)
2. ROUND to the penny, dollar or thousands position (03:49 min)
3. ROUND, ROUNDUP, ROUNDDOWN functions round to a digit or position (04:28 min)
4. MROUND, CEILING and FLOOR round to an amount or multiple (05:48 min)
Lecture 17
Introduction To SUMPRODUCT Function
Play Video
Introduction To SUMPRODUCT Function
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Bank Deposit Example: Multiply and then Add in Succession (00:35 min)
2. Grade Book Weighted Average Example: Multiply and then Add in Succession (02:37 min)
3. Accounting Budget Weighted Average Example: Multiply and then Add in Succession (03:30 min)
4. OR Criteria SUMPRODUCT and SUMIFS: Add the results of an array operation (04:56 min)
5. Single Cell Profit Formula: SUMPRODUCT and Math Operation subtraction: Add the results of an array operation (06:49 min)
Lecture 18
VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP
Play Video
VLOOKUP Function 20 Examples, VLOOKUP Formula, Excel VLOOKUP
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Why do we have to know lookup functions? (00:38 min)
2. VLOOKUP, Exact Match (03:44 min). VLOOKUP delivers a value to the cell.
3. What happens if lookup value is not in your table? (08:34 min)
4. What happens if there are duplicates in first column of lookup table? (08:57 min)
5. VLOOKUP and IFERROR functions (10:38 min)
6. VLOOKUP ISNA and IF functions solution for Excel 2003 or earlier (12:22 min)
7. VLOOKUP and IFNA functions (13:19 min)
8. VLOOKUP and Data Validation List (14:37 min)
9. VLOOKUP with Named Range (17:23 min)
10. VLOOKUP as Formula Element (18:46 min)
11. The F2 Key for toggling between the 3 Formula Edit Modes: Edit, Enter, Point (19:42 min)
Edit = Arrow Keys move through formula
Enter = Arrow Keys can put cell references in formula
Point = Arrow Keys are putting cell references in formula (dancing ants are dancing).
12. (Formula Evaluator (Evaluate Formula) to see VLOOKUP in Action (20:48 min)
13. Creating an Invoice: Data Validation List, VLOOKUP & IFNA, ROUND & IFERROR (21:30 min)
14. HLOOKUP function (26:33 min)
15. The Hows and Whys of Approximate Match Compared to Exact Match (27:36 min)
16. VLOOKUP, Approximate Match: Commissions Example (30:13 min)
17. VLOOKUP, Approximate Match: Grading Example (33:10 min)
18. DO NOT GET TRICKED BY NUMBER FORMATTING!!!! VLOOKUP and Percentages!! (35:05 min)
19. VLOOKUP, Approximate Match: Tax Example (36:23 min)
20. Challenge for you!!! (40:31 min)
For answer to challenge see 01:00:18 minute mark of this video:
http://www.youtube.com/watch?v=-hJxIMBbmZY
Lecture 19
Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP
Play Video
Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Text Functions (00:11 min)
2. Partial Text Lookup: RIGHT & VLOOKUP (00:23 min)
3. Partial Text Lookup: LEFT and SEARCH & VLOOKUP (02:17 min)
4. Partial Text Lookup: MID & VLOOKUP (05:56 min)
5. Converting Numbers Stored As Text to Numbers (07:51 min)
Lecture 20
VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples
Play Video
VLOOKUP, DGET & MATCH For Looking Up Records 7 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. VLOOKUP to retrieve whole record change columns manually (00:15 min)
2. VLOOKUP to retrieve whole record column number in cell (02:20 min)
3. VLOOKUP & MATCH to retrieve whole record -- more dynamic formula (03:43 min)
4. DGET function to retrieve one record (10:24 min)
5. VLOOKUP and MATCH and Mixed Cell References when you have many records (11:42 min)
6. VLOOKUP to retrieve whole record using ROWS function when you don't have filed names or don't want to type column numbers (14:24 min). ROWS function to increment numbers in a column.
7. VLOOKUP to retrieve whole record using COLUMNS function when you don't have filed names or don't want to type column numbers (18:53 min). COLUMNS function to increment numbers in a row.
Lecture 21
INDEX and MATCH Functions For Unusual Lookups 4 Examples
Play Video
INDEX and MATCH Functions For Unusual Lookups 4 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. INDEX and MATCH (00:10 min)
2. Lookup Left, 1-way vertical range lookup (00:32 min)
3. Lookup Low Bid, 1-way horizontal range lookup (04:41 min)
4. 2-way lookup to lookup discount rate (08:27 min)
5. Lookup Whole Row or Column to add monthly numbers (13:14 min)
Lecture 22
CHOOSE function to lookup table for VLOOKUP With Multiple Tables
Play Video
CHOOSE function to lookup table for VLOOKUP With Multiple Tables
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video: CHOOSE function to lookup table for VLOOKUP.
3 Tables in VLOOKUP?
Lecture 23
VLOOKUP and IFNA for Commissions
Play Video
VLOOKUP and IFNA for Commissions
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video: VLOOKUP and IFNA for Commissions: VLOOKUP to look up an actual sales rep that has a contractual rate and then the IFNA to assign the flat commission rate when there is no contractual rate.
Lecture 24
Getting Top 5 Values With LARGE And Bottom 5 With SMALL
Play Video
Getting Top 5 Values With LARGE And Bottom 5 With SMALL
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Getting Top five values with LARGE and ROWS
2. Getting Bottom five values with SMALL and ROWS
Lecture 25
Goal Seek: Get Formula To Evaluate To Desired Result
Play Video
Goal Seek: Get Formula To Evaluate To Desired Result
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Goal Seek: Get Formula To Evaluate To Desired Result
2. Goal Seek Keyboard: Alt T, G
3. Set Cell: Contains a formula with a formula input you want to change
4. To value: Result you want formula to be. Must be typed into text box.
5. By changing cell: Must be cell reference for formula input

What IF Analysis Goal Seek
Lecture 26
Excel Compare Two Lists Formula: Item In List? Item Not In List?
Play Video
Excel Compare Two Lists Formula: Item In List? Item Not In List?
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Is Item In List 2 also in List 1? Use MATCH and ISNUMBER. Comparing a prospective customer sales phone list to the companies master customer list (00:27 min)
2. Sort Dialog Box to get records to top (02:43 min)
3. Is Item in List 2 NOT in List 1? Use MATCH and ISNA. Comparing Library database list of books to the list of items found in inventory count. (03:36 min)
4. IF, ISNA and MATCH functions to show "Missing" when book from library shelves is missing (05:20 min)

Compare two columns, Compare 2 columns, Compare 2 lists, compare two lists, compare two lists of items. Match two columns, Match 2 columns.
Lecture 27
How To Track Down Excel Formula Errors (16 Examples)
Play Video
How To Track Down Excel Formula Errors (16 Examples)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. List of possible errors in Excel (00:08 min)
2. 4 Steps to finding formula errors (00:30 min)
3. 3 Examples of how to use the 4 steps (01:44 min)
4. Cleaning Text data with the TRIM function and Paste Special Values (10:50 min)
5. Cleaning Date data (Dates stored as text) with the Text To Columns function (13:43 min)
6. Examples of #DIV/0! #REF! #NAME? #N/A #VALUE! #NULL! #NUM! ####### Circular Cell Reference Errors (16:39 min)
7.

#DIV/0! error
#REF! error
#NAME? error
#N/A error
#VALUE! error
#NULL! error
#NUM! error
Lecture 28
Excel Data Validation: List, Date, Time, Custom (22 Examples)
Play Video
Excel Data Validation: List, Date, Time, Custom (22 Examples)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Data Validation to allow only whole numbers between an upper and lower limit (00:27 min)
2. Create an Input Message for your Data Validation (02:00 min)
3. Error Message with "Stop" (02:18)
4. Circle Invalid Data (03:00 min)
5. Copy and Paste To Wreck Data Validation (03:58 min)
6. Data Validation to allow only decimal numbers below or equal to an upper limit (04:32 min)
7. Error Message with "Warning" (04:56 min)
8. Error Message with "Information" (06:12 min)
9. Data Validation drop down list using a range of cells (07:44 min)
10. Data Validation drop down list using a Defined Name (08:32 min)
11. Ctrl + Shift + F3 = Create Names From Selection for a single column (08:36 min)
12. Data Validation drop down list using a Dynamic Table Name (09:14 min)
13. INDIRECT Function trick for Table Name (10:56)
INDIRECT function takes text that represents a reference and converts it back to a reference.
14. Data Validation List when you type text in source dialog box (13:24 min)
15. Create a Dependent Data Validation List based on a second Data Validation Dropdown List (13:57 min)
16. Ctrl + Shift + F3 = Create Names From Selection for a multiple columns column (14:50 min)
17. INDIRECT Function trick for Dependent Data Validation: to take text that represents a reference and convert it to a reference (16:13)
18. Data Validation to allow only dates between an upper and lower limit (17:06 min)
19. Data Validation to allow only times above or equal to a lower limit (18:04 min)
20. Data Validation to allow a certain text length (18:46 min)
21. Custom Data Validation Logical Formula to allow only prices that are less than or equal to double the cost (19:18 min)
22. Custom Data Validation Logical Formula to prevent duplicates (20:57 min)
23. Summary Comments (23:49 min)
Data Validation List
Lecture 29
Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example)
Play Video
Flash Fill: Extract, Combine, Insert, Reverse Data (25 Example)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Extract First Name (00:54 min)
2. Extract Last Name (01:13 min)
3. Formulas or Flash Fill? (01:24 min)
4. Extract First and Last Initial (01:59 min)
5. Flash Fill Keyboard: Ctrl + E (02:27 min)
6. Reverse Text (02:34 min)
7. Reverse and Insert (02:51 min)
8. Change Case (03:04 min)
9. Combine (Concatenate) (03:44 min)
10. Avoid empty cells (04:05 min)
11. First & Last Initial and avoid empty cell (04:31 min)
12. First & Middle Name in columns here some names don't have a middle and some middle initials have periods (05:19 min)
13. From Serial Date extract Year (06:10 min)
14. From Serial Date extract Day (06:23 min)
15. From Serial Date extract Month (06:27 min)
16. From Serial Date with lead zero extract Day (06:33 min)
17. From Serial Date with lead zero extract Month (06:48 min)
18. Extract Date from number, no-pre-format (06:59 min)
19. Extract Date from number, with-pre-format (07:32 min)
20. Insert text to properly format phone number (08:54 min)
21. Insert text to properly format Student ID (09:19 min)
22. From Money, extract Integer (09:34 min)
23. From Money, extract Decimal (09:45 min)
24. From Money, extract Dollar and add Currency Number Format (09:52 min)
25. Extract Hour from Serial Number Time (10:21 min)
26. Extract Minute from Serial Number Time (11:09 min)
27. Extract Last Choice (11:29 min)
Lecture 30
Custom Number Formatting & Text Functions, (33 Examples)
Play Video
Custom Number Formatting & Text Functions, (33 Examples)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. 23 Examples of Custom Number Formatting (00:18 min)
2. Custom Number Formatting For Unit Price for Invoice (15:33 min)
3. Three TEXT function examples for creating labels (16:26 min)
4. Extract First Name formula using LEFT and SEARCH (18:55 min)
5. LEN function (20:13 min)
6. Extract Last Name formula using REPLACE and SEARCH functions (21:38 min)
7. Extract Last Name formula using SUBSTITUTE function (23:15 min)
8. Create e-mail address from employee names using LOWER, LEFT, SEARCH, REPLACE functions (42:10 min)
9. Flash Fill to create e-mail address from employee names (26:00 min)
10. TEXT function to create time from Text Time Items and the backslash character to insert a character into a Custom Number formatting (Barry Houdini Trick) (27:52 min)
11. Flash Fill to create time from Text Time Items (29:43 min)
Lecture 31
Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More
Play Video
Date Functions: EDATE, EOMONTH, DATE, YEAR, MONTH, DAY, More
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. TODAY function to put in today's date so that it updates each day (00:19 min)
2. NOW to put in today's date and time so that it updates each day (00:38 min)
3. Keyboard for hard coding today's date (Ctrl + ;) and time (Ctrl + Shift + ;) (00:48 min)
4. YEAR and MONTH and DAY functions to add extra columns to data sets so that you can perform data analysis. (01:25 min)
5. SUMIFS and Helper Column to add sales by Year. (02:33 min)
6. EOMONTH function (03:25 min)
7. EDATE function to calculate same day next month (04:31 min)
8. DATE and YEAR and MONTH and DAY functions to calculate same day next month (05:34 min)
9. EDATE function to calculate Vest Date (07:30 min)
10. DATE and YEAR and MONTH and DAY functions to calculate Vest Date (07:57 min)
11. DATE, LEFT, MID, RIGHT functions to create serial number date from database output date (eight consecutive number output) (08:19 min)
12. TEXT functions and Forward Slash to Insert Characters into Number Format to create serial number date from database output date (eight consecutive number output) (09:38 min)
Convert year month date number to a serial number.
Lecture 32
Introduction To Array Formulas and Array Functions
Play Video
Introduction To Array Formulas and Array Functions
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Essential definition of array formula: operation on an array of items rather than single items. (00:30 min)
2. Example of operation on single items: subtracting 2 cells (00:52 min)
3. Example of operation on an array of items: Subtracting 2 ranges of cells (arrays) inside SUMPRODUCT function to calculate total profit (02:08 min)
4. SUM function number1 argument is not automatically programmed to handle array operations (we get a #VALUE! error) (03:14 min)
5. SUMPRODUCT array argument is programmed to handle array operations. SUMPRODUCT can add the resultant array that is produced from the subtraction array operation. Add the individual profit numbers. (03:38 min)
6. Example of operation on single items: 1 criteria entered into the criteria1 argument of the SUMIFS function (04:44 min)
7. Example of operation on an array of items: 2 criteria entered into the criteria1 argument of the SUMIFS function (function argument array operation) (05:19 min)
8. SUMPRODUCT array argument is programmed to handle array operations. SUMPRODUCT can add the resultant array that is produced from the SUMIFS function. Add the individual totals for each team member. (06:35 min)
9. Single cell SUMPRODUCT array formula to add the total Gross Profit (06:55 min)
10. What the array operation looks like (07:40 min)
11. Formula Evaluator to see how the array operation works (08:00 min)
12. Calculate max gross profit. MAX function with an array operation. (09:06 min)
13. Number 1 argument in MAX function will calculate array operation only if you use Ctrl + Shift + Enter (10:55 min)
14. Array Formula Curly Brackets (11:10 min)
15. No Ctrl + Shift + Enter = #VALUE! error (11:17 min)
16. AGGREGATE array argument is programmed to handle array operations. See how to use AGGREGATE for a MAX calculation (12:09 min)
17. AGGREGATE array argument is programmed to handle array operations. See how to use AGGREGATE for a MIN calculation (15:36 min)
18. How to think about filtering values in a formula (16:19 min)
19. MIN and IF functions together to make a conditional minimum calculation (17:38 min)
20. MAX and IF functions together to make a conditional minimum calculation (22:53 min)
21. Implicit Intersection causes incorrect answer, but not a #VALUE! error (23:43 min)
22. MODE.SNGL and IF functions together to make a conditional minimum calculation (24:47 min)
23. STDEV.S and IF functions together to make a conditional minimum calculation (25:36 min)
24. Array Functions (27:15 min)
25. TRANSPOSE Array Function (27:23 min)
26. FREQUENCY Array Function (29:09 min)
27. Summary (32:46 min)
Lecture 33
Sorting In Excel. Excel Sort (13 Examples)
Play Video
Sorting In Excel. Excel Sort (13 Examples)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Most sorting is done on a Proper Data Set (00:16 min)
2. Click in single cell and use Sort Button (00:43 min)
3. Whole data set is sorted, not just selected column (00:43 min)
4. Sort upon more than 1 column (01:35 min)
5. Sorting can be done on a set that does not have a field name (04:30 min)
6. Sort Hierarchy (05:13 min):
1. Numbers
2. Text/words (including Null Text Strings)
3. FALSE
4. TRUE
5. Errors (in the order they occur)
6. Empty Cells (Empty Cells are always sorted to the bottom whether or not you do A to Z or Z to A).
AND
ASCII Characters. Example C = 67 and c = 99.
7. Empty always goes to bottom (A to Z or Z to A) (07:42 min)
8. Errors sort to top if you use Z to A (Descending) (08:30 min)
9. Use extra column with RAND() to sort randomly. (09:54 min)
10. Formula To Extract Random Sorted List of Names (11:08 min)
11. Sort Horizontally (14:25 min)
12. Sort by color (15:00 min)
13. Change Major sort, or change which sort gets applied first (16:01 min)
14. In order to use the Subtotal feature, you must Sort FIRST (16:30 min)
Lecture 34
Excel Subtotals, Add Subtotals based on One or Two Columns
Play Video
Excel Subtotals, Add Subtotals based on One or Two Columns
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Subtotals (00:11 min)
2. Alternatives to Subtotal: SUMIFS Formulas and PivotTables (00:45 and 04:13 min)
3. Sort First!!!!!!!!!!!!!!! (01:30 min)
4. Add Subtotals based on One Column (Field) (01:56 min)
5. Outline (03:12 min)
6. Copy and Paste Subtotal Report Trouble (05:18 min)
7. Select Visible Cells Only, then Copy and Paste Subtotal Report (05:57 min)
8. Go To dialog Box (06:43 min)
9. Select Visible Cells Only Keyboard: Alt + ; (07:11 min)
10. Revert Subtotals back to original data set (07:59 min)
11. Add Subtotals based on Two Columns (Fields) (08:32 min)
12.
Lecture 35
Excel 2013 PivotTables: Basic To Advanced 58 Examples
Play Video
Excel 2013 PivotTables: Basic To Advanced 58 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. 3 Reports with Formulas (00:01:20 min)
2. What is a PivotTable do? (00:01:43 min)
a. Summarize data
b. "Calculation with criteria"
c. Create Reports that contain calculations with conditions/criteria
3. Proper Data Set (00:02:57 min)
4. Visualize (00:03:16 min)
5. Create from Insert Tab (00:04:34 min)
6. Excel 2013 Create PivotTable dialog box keyboard: Alt, N, V (00:04:51 min)
7. Drag and drop from Field List (00:06:16 min)
8. Format to Show Field Names: Design, Report Layout, Show in Tabular Form (Format Tip #1) (00:08:37 min)
9. Number Formatting must be done on Field, not with Format Cells dialog Box (Format Tip #2) (00:09:30 min)
10. Create PivotTable Style (Format Tip #3) (00:10:32 min)
a. Limited Formatting
11. Set PivotTable Style as Default (00:12:34 min)
12. Why Called Pivot? Pivot the report (00:13:16 min)
13. Pivot Data is stored in a cache (00:14:56 min)
14. Change Source Data (00:14:56 min)
a. Formulas Update Automatically
b. PivotTables update after "Refreshing" (00:15:54 min)
i. Analyze PivotTableTools Tab
ii. Alt + F5 (00:16:17 min)
iii. Ctrl + Alt + F5
15. Copy A PivotTable Report (00:17:02 min)
16. Change Function (00:17:30 min)
17. Filter a Field (00:17:58 min)
18. Only 11 functions (00:19:33 min and 00:23:11 min)
a. See list
b. Formulas in cells can make many more calculations
19. One Stop Shopping for Calculations: Value Field Settings (00:18:57 min)
20. Text Data defaults to Count (00:21:18 min)
21. Number Data defaults to SUM (00:21:18 min)
22. Counting counts criteria: Counting records, Numbers or Text (00:22:06 min)
23. Mixed Data Defaults to Count (00:23:50 min)
24. Review: 5 steps to creating PivotTable (00:25:50 min)
25. Report we saw back in Subtotal video (00:25:40 min)
26. Report with three calculations (00:26:50 min)
27. Formulas vs PivotTables (00:28:42 min)
PivotTables: 1) Fast and easy to create calculations with criteria. 2) Requires "Refresh" when Source Data Changes. 3) You can Pivot a report and add and remove whole categories of criteria and calculations update. 4) Change the function easily. 5) Formatting is limited.
Formulas: 1) Usually more complicated to create calculations with criteria. 2) Automatically updates when Source Data Changes. 3) It is extremely difficult if not impossible to Pivot a report and add and remove whole categories of criteria. 4) Changing a function is more difficult with formulas. 5) Formatting is less limited.
28. Collapse and Expand criteria in Row Labels (00:29:48 min)
29. Extracting data to new sheet with Double Click (00:30:15 min)
30. PivotChart (00:31:03 min)
31. Group Dates: Right-click Date in Pivot and point to Group. (00:32:17 min)
32. Group Times: Right-click Time in Pivot and point to Group. (00:34:44 min)
33. Old Excel 2003 dialog box to group or ungroup a field in a second way: (00:36:07 min)
a. Keyboard: Alt, D, P.
b. This creates a separate cache of data.
34. Grouping can't be done with Data Mismatch (00:38:47 min)
35. Group Numbers: Decimal create ambiguous categories (00:40:34 min) (Integers: Lower and Upper Limits are included in Category. Decimals: Lower Limit is included and Upper Limits is not included in Category). Integer creates unambiguous categories
36. Recommended Chart: Amazing because it summarizes (00:43:11 min)
37. Recommended PivotTable (00:45:06 min)
38. Report Filter: Adds new criteria to report (00:46:01 min)
39. Show Report Filter (00:47:45 min): PivotTableTools Analyze Ribbon Tab, PivotTable group, Options, "Show Report Filter Pages".
40. Slicers (00:49:14 min): Fancy Filters: PivotTableTools Analyze Ribbon Tab, Filter group
41. Use Filter and Slicer together to extract data (00:50:14 min)
42. Timelines (00:53:13 min): PivotTableTools Analyze Ribbon Tab, Filter group Show Values As: % of Column
43. Show Values As: % of Grand Total (00:56:11 min)
44. Show Values As: % of Column (00:59:19 min)
45. Show Values As: % of Row (01:00:24 min)
46. Connect One Slicer to many PivotTables (01:01:34 min)
47. Show Values As: Running Total (01:03:21 min)
48. Show Values As: Difference From (01:05:02 min)
49. Show Values As: % Difference From (01:05:50 min)
50. Show Values As: % of Parent (01:07:15 min)
51. Modify PivotTable Style (01:08:47 min)
52. Show Values As: % of Parent Row Total (01:09:36 min)
53. Show Values As: % of Parent Column Total (01:09:36 min)
54. Show Values As: Rank (01:10:16 min)
55. Sort based on a Value Field (01:11:39 min)
56. Create Formula in PivotTable: Sales - Expense (01:12:01 min)
57. Data Model: to get new PivotTable function: Distinct Count. (01:13:53 min)
58. Use Data Model and Relationships to replace VLOOKUP (01:16:46 min)
Pivot Tables
Lecture 36
Excel Filter For Extracting Records with AND or OR Criteria
Play Video
Excel Filter For Extracting Records with AND or OR Criteria
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Sort to Extract Records (00:29 min)
2. Filter for extracting records. (01:02 min)
3. Filter Toggle Keyboard: Ctrl + Shift + L or Alt, D, F, F (01:24 min)
4. Single Criteria Data Extract (02:17 min)
5. Turn Off Filter: Ctrl + Shift + L, L (03:19 min)
6. 2 or more criteria or conditions: (04:00 min)
AND Criteria = All tests must get TRUE
OR Criteria = At least one test must get TRUE
7. Date Between = AND Criteria (04:27 min)
8. Product "Quad" or 'Carlota" = OR Criteria (06:10 min)
9. SalesRep "Gault" in "East" = AND Criteria (06:50 min)
10. Contains or Search "15th Lane" = 1 Condition. (07:49 min)
11. Top 5 = 1 Condition (09:05 min)
12. Filter by color (10:20 min)
13. Don't forget new Slicers for Tables (10:33 min)
14. Slicers great for Extracting records or making calculations with 1 or more conditions (12:04 min)
15. Alt + = adds Total Row (13:20 min)
16. Add New Record with Total Row (14:16 min)
Lecture 37
Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
Play Video
Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

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
Lecture 38
Excel Text To Columns To Split or Convert Data 7 Examples
Play Video
Excel Text To Columns To Split or Convert Data 7 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Split Text with Delimiter = Space. Split First and Last Name and Skip a column (00:38 min)
Text To Columns keyboard: Alt, A, E or Alt, D, E (01:15 min)
2. Split Text with 1st Time: Delimiter = Comma, 2nd Time: Delimiter = Space. Clean Address data (02:31 min)
TRIM Function to removce spaces (03:55 min)
Paste Special Values using Right-Click-Drag Method (04:09 min)
3. Split Text with Delimiter = Dash. Get code from middle of text string (05:12 min)
4. Split Text with Fixed Width to get hour and minutes from integer number (06:02 min)
5. Split Text with Fixed Width to get year, month and day from integer number (06:45 min)
6. Convert integer number that represents Date to Serial Number Date with Text To Columns, Step 3 = YMD Date Type (07:27 min)
7. Convert Text Dates to Serial Number Dates with Text To Columns (Alt, D, E, F) (08:22 min)
8. Convert Numbers stored as text to numbers with Error Checker (09:41 min)
Lecture 39
Excel Import Data: Text File, CSV File, Access, Excel File
Play Video
Excel Import Data: Text File, CSV File, Access, Excel File
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Import Commas Delimited Data From Word Document with Copy and Paste then do Text To Columns (1:00 min)
2. Import CSV File and Txt File (02:13 min)
3. Import Proper Data Set into Excel from Access (05:22 min)
4. Export Data Set as CSV File or Text File File using Save As (07:05 min)
5. Import from another Excel File (08:30 min)
Lecture 40
Conditional Formatting Basic To Advanced 50 Examples
Play Video
Conditional Formatting Basic To Advanced 50 Examples
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Built-in Conditional Formatting. Conditional Formatting to cells: A logical test that comes out TRUE or FALSE. TRUE = get Formatting. FALSE = Not get Formatting. (00:00:44 min)
2. Highlight Cell Rules: Type in textbox: Greater than 1000 (00:02:05 min)
3. Highlight Cell Rules: Type in textbox: Contains "Fun" (00:03:00 min)
4. Highlight Cell Rules: Link textbox to cell: Greater than 1000 (00:03:36 min)
5. Highlight Cell Rules: Link textbox to cell: Contains "Fun" (00:04:13 min)
6. Top Bottom Rules: Top 3 (00:04:46 min)
7. Top Bottom Rules: Above Average (00:05:13 min)
8. Data Bar on top of numbers (00:05:29 min)
9. Data bars in cell next to numbers. Also RANDBETWEEN Conditional Formatting. (00:05:53 min)
10. Manage Rules dialog Box (00:06:15 min)
11. Manage Rule dialog box keyboard: Alt, O, D (00:10:31 min)
12. Color scales = Ranks number by color. (00:07:23 min)
13. Fun with CF, RAND, and Bubble Chart (00:08:12 min)
14. Icon: Arrow Icons on top of numbers (00:09:42 min)
15. Icon: Arrow Icons next to numbers (00:10:12 min)
16. Logical Formulas. Get Formatting = TRUE or Any Non-Zero Number. NOT Get Formatting = FALSE or Zero or Error. (00:11:14 min)
17. Format number less than zero: 3 methods (00:12:35 min)
18. Format Actual when Actual Exceeds Budgeted: 2 methods (00:17:27 min)
19. Keyboard for New Format Rule dialog box: Alt, O, D, Alt + N or Alt, H, L, N (00:19:29 min)
20. Keyboard to open CF Rule Manager to edit or delete a rule: Alt, O, D, D, Enter (00:39:25 min or 01:05:51 min)
21. Clear Content and CF still remains (00:20:02 min)
22. Format cell based on a different cell's value: Format Net Sales based on Date (00:20:47 min)
23. Sort and CF will adjust (00:21:53 min)
24. Format cell based on a different cell's value: Format Net Sales based on Invoice number (00:22:09 min)
25. Format whole row based on date (00:22:36 min)
26. Format whole row based on Invoice number (00:26:13 min)
27. Format Row when Actual Exceeds Budgeted (00:27:32 min)
28. Format whole column (00:29:21 min)
29. Format cell based on OR criteria: OR function. (00:30:51 min)
30. Format cell based on OR criteria: MATCH function (Errors are ignored, Any non-zero number = TRUE). (00:33:10 min)
31. Format whole row based on OR Criteria use MATCH (Errors are ignored, Any non-zero number = TRUE). (00:35:19 min)
32. Format cell based on AND Criteria: Between Upper and Lower Date: AND Function. (00:37:55 min)
33. Format whole row based on between Date Criteria (00:40:01 min)
34. Format whole row based on AND Criteria from different columns (00:41:16 min)
35. Format whole row based on OR and AND Criteria (00:43:09 min)
36. Format entire row when sales are above average (00:45:49 min)
37. Format every other row with MOD function (1 = TRUE, 0 = FALSE) (00:47:18 min)
Copy Conditional Formatting: (00:50:18)
38. Format Row In Checkbook when Reconciled (00:50:54 min)
39. Format Entire Row for the 3 Biggest Values (00:52:24 min)
40. Format lowest Price in Row (00:53:50 min)
41. Format intersecting cell (00:56:50 min)
42. Format row based on Approximate Match based on VLOOKUP (00:59:13 min)
43. Multiple Rules applied to each cell: Format row, column and intersection value (01:02:41 min)
44. Gantt Chart for Day Schedule Cell Chart (01:08:11 min)
45. Format Weekends and Holidays NOT WORKDAY.INTL NETWORKDAYS.INTL function (01:13:31 min)
46. Format Duplicates Built-in Method or Formula Method: 2 methods (01:18:00 min)
47. Format items in List 2 that are NOT in List 1 (01:20:18 min)
48. Format items that are in List 2 that are also in List 1 (Errors are ignored, Any non-zero number = TRUE). (01:21:40 min)
49. Format stocks with a loss (01:22:29 min)
50. Format cells that are not empty, using COUNTA (Any non-zero number = TRUE, Zero = FALSE) (01:23:07 min)
51. MAX For Seattle (Array Formulas work in Conditional Formatting dialog box) (01:24:35 min)
Lecture 41
Review Of Chart Basics For Excel 2013
Play Video
Review Of Chart Basics For Excel 2013
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

This video was previously released at YouTube with the title:
Office 2013 Class #43: Excel 2013 Charts: Column, Bar, Pie, Line, X Y Scatter, Sparklines, Recommended
For the Highline Excel Class, we must review the basics before we can go on to more advanced Charting tricks.

Topics in this video:
1. What do Charts do? (00:50 min)
Visually portray Quantitative data (number data).
Give a quick impression of the number data.
Create a picture that can communicate more quickly than just the numbers alone.
Charts allow you to see patterns or trends that you may not be able to see if you are looking at just the number data.
2. Chart elements (02:09 min)
3. Effective charts (04:09 min):
Number data AND labels for the number data
No "Chart Junk"
4. Column Charts (06:18 min)
5. Change Chart Titles (08:08 min)
6. Link Labels to Cells (08:48 min)
7. Formatting Charts with Icons next to chart and with Task Panes (10:26 min)
8. Copy Charts (16:05 min)
9. Change Chart Type (16:50 min)
10. Bar Charts (17:12 min)
11. Switch axes labels (18:28 min)
12. Stacked Column Charts (18:51 min)
13. Stacked Bar Charts (19:49 min)
14. Pie Charts(20:15 min)
15. Line Charts (22:22 min)
16. BIG KEY: If the chart does not come out right: Select Data button (25:25 min)
17. X-Y Scatter Charts (30:10 min)
18. Sparklines Cell Charts (35:15 min)
19. Get Stock Data From Yahoo and Create Sparkline (36:52 min)
20. Recommended Charts (40:28 min)
21. Summary of all charts (42:52 min)
Lecture 42
Save Chart As Template in Excel 2013
Play Video
Save Chart As Template in Excel 2013
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Save Chart As Template
Lecture 43
Set Default Chart in Excel 2013, Keyboard Shortcuts Charts
Play Video
Set Default Chart in Excel 2013, Keyboard Shortcuts Charts
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Keyboard for chart on Active Sheet = Alt + F1
2. Keyboard for chart on New Sheet = F11
3. Set Default Chart in Excel 2013
Lecture 44
Pie within Pie Chart in Excel 2013
Play Video
Pie within Pie Chart in Excel 2013
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Pie within Pie Chart in Excel 2013
Lecture 45
Combining Chart Types and Secondary Axis in Excel 2013
Play Video
Combining Chart Types and Secondary Axis in Excel 2013
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Add Data To Chart with Select Data Button
2. Add Secondary Axis
3. Change Chart Type For Series Values
4. 1 click Combining Chart Types in Excel 2013
5. Add Data with Mouse and Range Finder Color Coding for Cell References
Lecture 46
Statistics: Histogram Formulas & Chart in Excel 2013
Play Video
Statistics: Histogram Formulas & Chart in Excel 2013
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Formulas for Min and Max values
2. Formulas for Chart Labels
3. COUNTIFS formula to count between upper and lower numbers. BETWEEN Criteria. AND Criteria.
4. Histogram Chart with No Gap Width and Labels linked to cells
5. Change Formulas for Chart Labels with the CHAR(10) formula element to add wrap Text (Line Return) in the formula so that all category labels wrap to three lines
Lecture 47
Statistics: Dot Plot (Cell Chart)
Play Video
Statistics: Dot Plot (Cell Chart)
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Dot Plot (Cell Chart) using COUNTIF function and Cell Formatting

Visualize Data In Excel 2013
Lecture 48
Conditional Formatting: Bar Chart with Data Labels
Play Video
Conditional Formatting: Bar Chart with Data Labels
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Conditional Formatting: Bar Chart with Data Labels
2. Formula to Define length of Bar

Visualize Data In Excel 2013
Lecture 49
Break Even Analysis Formulas & Chart, Plotting Break Even Point
Play Video
Break Even Analysis Formulas & Chart, Plotting Break Even Point
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Formulas for Units, Sales, Variable Costs, Contribution Margin, Fixed Cost, Total Costs and Net Income (01:10 min)
2. X-Y Chart for Break Even Analysis (05:48 min)
3. Formulas and Chart label for Break Even Units (07:41 min)
4. Plot the Break Even Point on chart (09:55 min)
Lecture 50
Recorded Macro Basics, including Absolute & Relative References
Play Video
Recorded Macro Basics, including Absolute & Relative References
Highline Excel 2013 Class Video 51: Recorded Macro Basics, including Absolute & Relative References
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/B...

Topics in this video:
1. Macro Basics (00:46 min)
2. Format Variable Height Report with Absolute and Relative References(10:11 min)
3. Rearrange Records from Vertical Orientation to Proper Data Set with Relative References. (22:47 min)
Copy Code from Internet:
4. Spell Numbers Custom User Defined Function (27:10 min)
5. Add Red Cells Custom User Defined Function (30:10 min)