Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yLURpp
Download Full Written Notes: http://ift.tt/2maLU35
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:15) Example 1: Excel Table feature for VLOOKUP & Data Validation List
3. (07:10) Example 2: Excel Table feature for Chart
4. (09:45) Example 3: Excel Table feature for PivotTable
5. (13:10) Example 4: Excel Table feature for Other Formulas
6. (16:50) Example 5: Add New Column to an Excel Table AND Create Approximate Match Lookup Table from a written statement.
7. (17:47) Create Approximate Match Lookup Table from Written Instructions
8. (24:35) Change Settings to Allow Columns and Rows to be added to Excel Table as you work: Excel File, Options, Proofing, AutoCorrect, Auto Format As You Type, “Apply As You Work” – “Include New Rows and Columns in Table”
9. (26:10) Add columns to Excel Tables with Table Formula Nomenclature with Relative and Absolute References.
10. (29:30) Formula for Discounts when we need to calculate Net Revenue based on a Discount.
11. (34:14) Add new records to bottom of Excel Table and Formulas will automatically populate down
12. (35:05) Summary – Conclusion
Tag: How to
Excel Basics 14: Excel VLOOKUP Function Made Easy! 9 Examples to Make Your Job Easier!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zHeXRV
Download Full Written Notes: http://ift.tt/2zuiTol
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:10) Should we use IF or VLOOKUP?
3. (02:12) What does VLOOKUP do?
4. (03:22) Examples of Lookup Tables
5. (06:14) What does “V” in VLOOKUP mean?
6. (06:41) Formula 1: Exact Match VLOOKUP to get Product Price & Data Validation Dropdown List: =VLOOKUP(E23,A23:C27,3,0)
7. (09:50) VLOOKUP can do 2 different tyoes of Lookup
8. (11:29) Formula 2: Exact Match VLOOKUP to get Product Price for an Invoice: =VLOOKUP(A41,$A$23:$C$27,3,0)
9. (13:04) Add Data Validation List to a cell to create a drop-down list of valid lookup values for VLOOKUP Function
10. (18:03) Formula 3: Approximate Match VLOOKUP to get a Bonus Commission %: =VLOOKUP(B21,$F$21:$G$27,2)
11. (24:11) Formula 4: Approximate Match VLOOKUP to get a Bonus Commission % & Calculate Bonus Amount: =VLOOKUP(B33,$F$21:$G$27,2)*B33
12. (28:15) What VLOOKUP Does: Puts 1 of multiple things into a cell or formula.
13. (29:01) Formula 5: Exact Match VLOOKUP to get Employee E-mail and Phone Number: =VLOOKUP(A16,A2:I11,6,0)
14. (31:56) Formula 6: Exact Match VLOOKUP to List of Employee Salaries: =VLOOKUP(A23,$A$2:$I$11,9,0)
15. (32:48) Formula 7: Approximate Match VLOOKUP to get Tax Amount: =VLOOKUP(D4,A4:B8,2)
16. (34:23) Formula 8: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E13,$A$13:$B$17,2)
17. (36:31) Formula 9: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E4,$H$4:$I$9,2)
18. (38:08) Summary – Conclusion
Excel Basics 13: IF Function Made Easy! IS Functions Too! 14 Epic Examples!!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zkj4lL
Download Full Written Notes: http://ift.tt/2zx9ZqF
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Detailed Introduction to the IF Function & Comparative Operators and Logical Tests
2. (08:53) IF Example #01: Your Bonus: =IF(C6 Greater Than =C11,C10,0)
3. (13:39) IF Example #02: In Balance?: =IF(B22=C22,”In Balance”,”NOT In Balance”)
4. (17:16) IF Example #03: Bonus? TRUE or FALSE?: =C4 Greater Than =$F$4
5. (17:54) IF Example #04: Bonus? “Bonus” or “No Bonus”: =IF(C11 Greater Than =$F$11,”Bonus”,”No Bonus”)
6. (18:54) IF Example #05: Bonus? 0 or $750: =IF(C18 Greater Than =$F$18,$G$18,0)
7. (20:20) IF Example #06: Bonus Commission? 5% or 1%: =IF(C25 Greater Than $F$25,$G$25,$H$25)
8. (23:34) IF Example #07: Calculate Commission Paid: =IF(C32 Greater Than $F$32,$G$32,$H$32)*C32
9. (27:46) IF Example #08: “Under” or “Over”: =IF(C39 Less Than $F$39,$H$39,$G$39)
10. (31:25) IF Example #09: “No Balance” or Show Nothing: =IF(C46=0,$H$46,””)
11. (35:03) IF Example #10: Is it a Number?: =ISNUMBER(C7)
12. (38:26) IF Example #11: Is cell empty?: =ISBLANK(C7)
13. (39:09) IF Example #12: Is it Text?: =ISTEXT(C7)
14. (40:23) IF Example #13: Checkbook Balance: =IF(ISBLANK(B5),””,G4-F5+E5)
15. (48:22) IF Example #14: Sales Category: =IF(L7 Greater Than $O$7,”Large”,”Small”) and then make a PivotTable
16. (52:50) Summary – Conclusion
Mr Excel & excelisfun Trick 188: Sales Team Region Report: 5 Different Amazing Methods
Download workbook: : http://ift.tt/2zdkYFt
and
http://ift.tt/2hcNeB0
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:11) Introduction
2. (00:31) Method 1: Power Pivot Data Model & Create PivotTable dialog box with Data Model PivotTable
3. (02:34) Method 2: Array Formula with SUMIFS and SUMPRODUCT
4. (06:42) Method 3: VLOOKUP and Standard PivotTable
5. (08:10) Method 4: Power Pivot Data Model & Relationships with Data Model PivotTable
6. (11:05) Method 5: Power Query with Pivot, Merge and Group By features
7. (14:37) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 188
Excel Basics 12: Complete Formula Lesson of Formula Types & Formula Elements 12 Examples
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2iZNLH6
Download Full Written Notes: http://ift.tt/2z9Fzrs
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (00:30) Different Types of Formulas
3. (02:07) Formula Elements – what you can out into a formula.
4. (04:55) Formula 01: Goal: Add calls made a service center last week.: =SUM(C23:C29) . Number Formula.
5. (05:53) Formula 02: Goal: Calculate Monthly Insurance Expense.: =C37/12. Number Formula.
6. (06:25) Formula 03: Goal: Calculate Deduction for Each Employee.: =ROUND(C46*$F$46,2) . Number Formula.
7. (09:31) Formula 04: Goal: Calculate Cost of Goods Sold (COGS) in Accounting: =(C58-D58)*E58. Number Formula.
8. (13:17) Formula 05: Goal: Join “Text ” and an Item Number into One Cell.: =”Item # “&C70. Text Formula.
9. (17:02) Formula 06: Goal: Join First & Last Names into One Cell.: =C82&”, “&B82. Text Formula.
10. (20:43) Formula 07: Goal: Extract State Abbreviation using the RIGHT Function: =RIGHT(B96,2) . Text Formula.
11. (24:01) Formula 08: Goal: Extract Product Number using the LEFT Function: =LEFT(B108,6) . Text Formula.
12. (26:00) Formula 09: Goal: Determine If Debits = Credits: =B127=C127. Logical Formula.
13. (30:00) Formula 10: Goal: Determine If Employee Gets a Bonus: =C135 Greater Than =$F$135. Logical Formula.
14. (33:35) Formula 11: Goal: Count how many of each product we sold: =COUNTIFS($B$148:$B$153,B156). Number Formula.
15. (35:15) Formula 12: Goal: Formula to determine whether we need to reorder?: =$D$173 Greater Than B166-C166. Logical Formula.
16. (41:00) Formula 13: Goal: Calculate Net Income: =C181-SUM(C182:C185) . Number Formula.
17. (42:30) Summary – Conclusion
Excel Basics 10: Search for Excel Functions & get help with Function Arguments
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2z7DmPp
Download Full Written Notes: http://ift.tt/2hqSqy5
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:19) Search for Excel Functions with Function Library
3. (03:05) Insert Function Dialog Box, Search for Excel Functions with Insert Function button or with Keyboard: Shift F3
4. (03:46) PMT Function for Loan Payment
5. (04:00) Function Argument dialog box
6. (04:54) Formulas to calculate Month Interest rate, Total Periods and Loan Amount
7. (11:29) Excel’s Golden Rule for analyzing Different Loans
8. (13:29) MEDIAN to Calculate Median
9. (16:15) Summary – Conclusion
Alternative Title:
Excel Basics 10: PMT Function for Loan Payment & MEDIAN to Calculate Median
Excel Basics 10: Insert Function Dialog Box to Search for Functions & get help with Function Arguments
Excel Basics 10: Date & Time Number Formatting, Formulas, Functions & Calculations
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2zXShcU
Download Full Written Notes: http://ift.tt/2xx9GYe
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:25) Keyboard to toggle Ribbon Tabs on and off
3. (01:46) Date Number Formatting
4. (04:55) Number Days Invoice Late Calculation
5. (05:30) Keyboard to hard code today’s date in a cell
6. (08:30) TODAY Date Function
7. (10:38) Loan Maturity Date Calculation
8. (11:36) Number Days in Project Calculation
9. (12:51) EDATE Function to calculate Invoice Due Date 2 months in the future
10. (15:06) EOMONTH Function to calculate Invoice Due Date at the End Of The Month
11. (16:53) Number of Days Late for Invoice Template
12. (18:25) Time Number Formatting
13. (18:58) Date and Time Data Entry Errors that result in Text Values
14. (21:26) Keyboard to Apply General Number Formatting (Eraser)
15. (24:41) Hours Worked Calculation
16. (28:44) Hours Worked or Hours Studied Before and After Lunch Calculation
17. (30:16) SUM Function for Hours Worked Before and After Lunch Calculation
18. (32:34) Formula Evaluator (Evaluate Formula feature) to see how formula is calculated by Excel
19. (35:09) Full Payroll Time Sheet Net Pay Template
20. (38:16) Summary – Conclusion
Related Videos:
Excel Basics #15: Date & Time Format & Calculations
Excel Magic Trick 783: Date Functions & Formulas (17 Examples)
Alternative Title: Excel Basics 10: What is Under Date & Time Number Formatting? And How to Make Date & Time Formulas
Mr Excel & excelisfun Trick 187: SUMIFS to add only Filtered Columns, NOT Hidden Rows
SUMIFS for Filtered Rows!?! Add Sales for Visible “Yes” Rows, Not Hidden Rows
Download workbook: http://ift.tt/2xtzKUd
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:01) Dueling Song
2. (00:12) Mr Excel tries AGGREGATE function
3. (02:23) Excelisfun creates formula with SUMPRODUCT, SUBTOTAL, ROW and OFFSET.
4. (09:38) Mr Excel uses AGGREGATE in helper column and SUMIFS in adding formula
5. (11:39) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 187
Related videos:
Excel Magic Trick 1010: SUMPRODUCT To Multiply Filtered Columns or Columns With Hidden Rows
Excel Basics 9: Number Formatting as Façade & the ROUND Function
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yCGQtg
Download Full Written Notes: http://ift.tt/2gAVevo
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (02:10) What does Number Formatting do?
3. (03:20) Formulas do NOT see Number Formatting
4. (04:20) Number Formatting can save a lot of time for Data Entry
5. (07:03) Compare General, Currency and Accounting Number Formatting
6. (09:43) Percentage Number Formatting
7. (14:55) Rounding
8. (16:15) ROUND Function Basics
9. (20:34) When you MUST Round
10. (21:36) ROUND Function for Payroll
11. (24:00) ROUND Function for Invoicing
12. (26:30) ROUND Function for Income Taxxes when you need to round to the dollar
13. (27:22) Summary – Conclusion
Excel Basics 8: Default Data Alignment In Excel & How it Can Help
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2y4I6Gm
Download Full Written Notes: http://ift.tt/2lgXWaO
Class web page link: http://ift.tt/2kPyedk
Learn about Default alignment in Excel and how it an help you with Data Entry, formulas and tracking down errors.
Excel Basics 6: Customize Quick Access Toolbar (QAT) and Show New Ribbon Tabs
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2y4I6Gm
Download Full Written Notes: http://ift.tt/2lgXWaO
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:04) Introduction
2. (00:40) Customize QAT
3. (07:36) Show New Ribbon Tabs
4. (09:33) Summary – Conclusion
Excel Basics 7: Keyboard Shortcuts Are Fast!
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2y4I6Gm
Download Full Written Notes: http://ift.tt/2lgXWaO
Class web page link: http://ift.tt/2kPyedk
In this video learn these amazing Excel Keyboard Shortcuts:
1. Ctrl + Arrow Key == Jump to end. If all empty, jump to last empty
2. Ctrl + Home == Go To A1
3. Ctrl + Shift + Arrow Key == Select column or row (go until it sees an empty cell). Works in cells, formulas, dialog boxes.
4. Ctrl + Backspace == Jump Back to Active Cell
5. Alt, 1 == get first command in QAT
6. Ctrl + * == Select Current Region (go in all directions until it sees an empty cells)
7. Alt + = == SUM function
8. Tab = Insert “Blue” highlighted function from function drop-down list
9. F4 == puts dollar signs in cell references. And it jumps screen back in view.
10. Arrow == Put Cell Reference into Formula
11. Ctrl + Enter = to put item in cell and keep cell selected
12. F2 == Put Cell in Edit Mode
13. Alt, N, V == PivotTable dialog box
14. Alt, P, S, P == Page Setup