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

View on YouTube

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

View on YouTube

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

View on YouTube

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

View on YouTube

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.

View on YouTube

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

View on YouTube

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

View on YouTube

Excel Basics 5: PivotTable Power to Create Reports with Various Calculations & Layouts

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: http://ift.tt/2yLoyFx
Download Full Written Notes: http://ift.tt/2y3kPPW
Class web page link: http://ift.tt/2kPyedk
Topics in this video:
1. (00:01) Introduction
2. (01:46) Keyboard for PivotTable
3. (04:00) Why a PivotTable is called a PivotTable
4. (04:25) Cross Tabulated Report (Adding with 2 Criteria)
5. (05:15) Calculations with Conditions or Criteria
6. (07:40) Change Calculation in Values area
7. (09:18) Drop Number or Text fields in Values area
8. (11:43) PivotTables on Large Data Set is Easy!!
9. (12:00) Keyboard to jump to bottom of column
10. (13:00) Keyboard for PivotTable on New Sheet
11. (15:30) Format PivotTable with Style
12. (16:05) Slicer to Filter who PivotTable
13. (19:24) Summary – Conclusion

PivotTable Basics, Pivot Table Basics, Learn PivotTables, Learn Pivot Tables, Lesson in Pivot Tables, How to create a PivotTable, PivotTables Are Easy! Pivot Tables Are Easy! How to Create a PivotTable Report, Pivot Table Reports

View on YouTube

Excel Basics 4: PivotTables & SUMIFS Function to Create Summary Reports (Intro Excel #4)

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. (01:22) What Excel can do: 1) Calculations, Data Analysis
3. (01:58 and 03:22) What is Data Analysis?
4. (04:00) What is a Proper Data Set?
5. (06:26) SUMIFS for Regional Sales Report, Daily Sales Report and Sales Rep Report
6. (18:32) PivotTable for Regional Sales Report, Daily Sales Report and Sales Rep Report
7. (21:31) Format PivotTable with Tabular Layout
8. (22:18) Add Number Formatting to Values area
9. (30:00) Compare SUMIFS and PivotTable
10. (32:40) Summary – Conclusion
PivotTable Basics, PivotTables 101, PivotTable Lessons, Free PivotTable Lessons, Learn PivotTables Quickly, Efficient PivotTables, PivotTables Are Easy!

View on YouTube

Mr Excel & excelisfun Trick 186: Does Text Contain Number Digits? Flash Fill, Formula or VBA?

Download workbook: : http://ift.tt/2xTElyv
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use Flash Fill, Formula or VBA to detect when Text cell value contains number digits. Meni Porat also has a great formula that uses COUNT, SEARCH and IF.
Topics:
2) (00:01) Dueling Song
3) (00:28) Introduction to Topic
4) (00:55) Flash Fill example (Mr Excel)
5) (02:17) Formula example using SUBSTITUTE, AND and IF Functions with an Array Constant (excelisfun)
6) (06:39) Meni Porat also has a great formula that uses COUNT, SEARCH and IF (Meni Porat)
7) (10:52) VBA Code to create UDF function (Mr Excel)
8) (11:40) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 186

View on YouTube