### Lecture Description

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

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}).

### Course Index

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

### Course Description

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.