Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards
Direct links to file:
START FILE: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/E%20W%20T%20M%20Accounting%20Excel%20Seminar-StartFileSoYouCanFollowAlong.xlsx
FINISHED FILE: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/E%20W%20T%20M%20Accounting%20Excel%20SeminarAllFinishedExamplesAfterVideoDone.xlsm
Keyboards 0:01:47
Jump: Ctrl + Arrow 0:02:20
Go To Cell A1: Ctrl + Home 0:02:36
Format Cells dialog box or in a chart Format Chart dialog box: Ctrl + 1 0:03:40
Currency Number Format: Ctrl + Shift + 4 0:04:08
Highlight column: Ctrl + Shift + Arrow0:04:20
Currency Vs Accounting Number Format 0:05:00
Alt keys: 0:05:47
PivotTable: Alt, N, V, T 0:05:47
PivotTable 2003: Alt, D, P 0:05:47
Page Setup: Alt, P, S, P 0:05:47
SUM: Alt + = 0:08:38
"Put thing in cell and move selected cell up": Shift + Enter 0:09:35
"Put thing in cell and keep cell selected": Ctrl + Enter 0:09:35
Select sheet to right: Ctrl + PageDown 0:11:11
Select sheet to left: Ctrl + PageUp 0:11:11
Number Formatting As Façade: 0:11:52
Decimal Number Format 0:12:38
Date Number Format 0:13:56
Keyboard for today's hard caded date: 0:13:56
Time Number Format 0:17:10
Percentage Number Format 0:21:25
Efficient Formula Creation 0:25:01
Excel's Golden Rule: If a formula input can vary, put it in a cell and refer to it in the formula with a cell reference 0:25:01
Formula elements, types of formulas, types of data 0:25:01
Monthly Allocation Formula: illustrate formula input that can be hard coded into formula 0:27:00
Tax (inefficient formula): illustrate formula input that can should NOT hard coded into formula 0:27:35
Tax (efficient formula): illustrate Golden Rule 0:27:35
Net Cash In formula: illustrate Golden Rule 0:27:35
Net Income formula: illustrate formula with built-in function within a larger formula 0:30:00
In Balance? formula: illustrate Logical formula 0:30:40
First & Last Name Join Formula: illustrate Text formula 0:31:54
COUNTIF formula: illustrate counting with criteria 0:33:29
COUNTIF & Label formula: illustrate counting with criteria and how the join symbol is used with criteria 0:34:19
Clear Formatting 0:35:50
SUMIFS to add with two criteria 0:36:21
SUMIFS to add between 2 dates 0:38:08
Count workdays formula to illustrate new Excel 2010 function NETWORKDAYS.INTL 0:41:46
Relative and Absolute Cell References 0:41:46
Mixed cell references in budget formula 0:43:58
Lookup Formulas 0:49:50
VLOOKUP to lookup product price: illustrate Exact Match lookup 0:49:50
Data Validation List: 0:53:10
VLOOKUP and IFERROR 0:55:13
VLOOKUP to lookup commission rate: illustrate Approximate Match lookup 0:56:45
Retrieve record (2-way lookup) with VLOOKUP and MATCH 0:59:53
MATCH, ISNA and ISNUMBER functions to compare 2 lists 1:03:38
INDEX and MATCH to lookup Left 1:07:00
Pivot Tables 1:10:02
Proper Data Set 1:10:02
PivotTables Pivot Tables are Easy 1:12:00
Visualize Table First 1:12:00
Adding with One or Two Conditions (Criteria) 1:12:00
Report Layout 1:16:58
Number Formatting 1:17:44
Style Formatting, Create Your Own 1:19:05
Pivoting 1:21:21
Listing Two Fields in Row Labels 1:21:21
Collapsing Pivot Table Row 1:21:59
Changing Calculation: SUM to AVERAGE 1:22:42
Adding with Three Criteria 1:23:29
Filtering a Row (Show Top Two Regions), Clear Filter 1:24:46
Filter whole report with Report Filter 1:25:55
Show Report Filter Pages (30 PivotTables with 1 click) 1:27:55
Filter whole report with Report Filter or Slicer 1:28:30
Compare Formulas and PivotTables 1:30:13
Grouping Dates in PivotTables 1:33:42
Difference between Grouping Integers and Grouping Decimals 1:35:41
Pivot Chart 1:39:00
Show Values As: 1:40:45
Copy PivotTable 1:41:43
Running Totals & % Running Totals 1:42:00
% of Grand Totals, % of Column Total, % of Row Total 1:43:01
Difference From, % Difference From 1:44:17
Multiple Calculation in one PivotTable 1:46:26
Creating Second PivotTable from Second Cache of data using Excel 2003 keyboard shortcut for 3-step Wizard 1:47:54
Blank in number field causes PivotTable to Count by Default 1:49:30
Text in Date field prohibits Grouping of Dates 1:50:09
Recorded Macros For Reports:
Basic Recorded Macro 1:51:05
Absolute References and save in Personal Workbook 1:55:10
How to trick the Macro Recorder into seeing a variable height report using Relative References 2:00:51
Rearrange records from vertical orientation to proper table using Relative References 2:09:01
Charts 2:13:35
Chart Types 2:13:35
Column Chart and How The Chart Wizard Interprets Data From Cells 2:17:48
Chart keyboards (Create Default Chart) 2:18:10
Select Data Source Dialog Box 2:20:30
Linking Chart Title to Cells 2:23:09
Saving Chart Templates 2:25:43
Setting Default Charts 2:26:48
Copy Charts 2:27:45
Bar and Stacked Bar charts 2:27:57
Line Chart & Change Source Data 2:31:39
Number Formatting to show "K" or "M" 2:34:52
Line Chart vs. X Y Chart 2:35:53
X Y Scatter 2:36:45
X Y Scatter Line Break Even Analysis Chart 2:41:28
Multiple chart types 2:42:42
Видео Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards канала ExcelIsFun
START FILE: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/E%20W%20T%20M%20Accounting%20Excel%20Seminar-StartFileSoYouCanFollowAlong.xlsx
FINISHED FILE: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/E%20W%20T%20M%20Accounting%20Excel%20SeminarAllFinishedExamplesAfterVideoDone.xlsm
Keyboards 0:01:47
Jump: Ctrl + Arrow 0:02:20
Go To Cell A1: Ctrl + Home 0:02:36
Format Cells dialog box or in a chart Format Chart dialog box: Ctrl + 1 0:03:40
Currency Number Format: Ctrl + Shift + 4 0:04:08
Highlight column: Ctrl + Shift + Arrow0:04:20
Currency Vs Accounting Number Format 0:05:00
Alt keys: 0:05:47
PivotTable: Alt, N, V, T 0:05:47
PivotTable 2003: Alt, D, P 0:05:47
Page Setup: Alt, P, S, P 0:05:47
SUM: Alt + = 0:08:38
"Put thing in cell and move selected cell up": Shift + Enter 0:09:35
"Put thing in cell and keep cell selected": Ctrl + Enter 0:09:35
Select sheet to right: Ctrl + PageDown 0:11:11
Select sheet to left: Ctrl + PageUp 0:11:11
Number Formatting As Façade: 0:11:52
Decimal Number Format 0:12:38
Date Number Format 0:13:56
Keyboard for today's hard caded date: 0:13:56
Time Number Format 0:17:10
Percentage Number Format 0:21:25
Efficient Formula Creation 0:25:01
Excel's Golden Rule: If a formula input can vary, put it in a cell and refer to it in the formula with a cell reference 0:25:01
Formula elements, types of formulas, types of data 0:25:01
Monthly Allocation Formula: illustrate formula input that can be hard coded into formula 0:27:00
Tax (inefficient formula): illustrate formula input that can should NOT hard coded into formula 0:27:35
Tax (efficient formula): illustrate Golden Rule 0:27:35
Net Cash In formula: illustrate Golden Rule 0:27:35
Net Income formula: illustrate formula with built-in function within a larger formula 0:30:00
In Balance? formula: illustrate Logical formula 0:30:40
First & Last Name Join Formula: illustrate Text formula 0:31:54
COUNTIF formula: illustrate counting with criteria 0:33:29
COUNTIF & Label formula: illustrate counting with criteria and how the join symbol is used with criteria 0:34:19
Clear Formatting 0:35:50
SUMIFS to add with two criteria 0:36:21
SUMIFS to add between 2 dates 0:38:08
Count workdays formula to illustrate new Excel 2010 function NETWORKDAYS.INTL 0:41:46
Relative and Absolute Cell References 0:41:46
Mixed cell references in budget formula 0:43:58
Lookup Formulas 0:49:50
VLOOKUP to lookup product price: illustrate Exact Match lookup 0:49:50
Data Validation List: 0:53:10
VLOOKUP and IFERROR 0:55:13
VLOOKUP to lookup commission rate: illustrate Approximate Match lookup 0:56:45
Retrieve record (2-way lookup) with VLOOKUP and MATCH 0:59:53
MATCH, ISNA and ISNUMBER functions to compare 2 lists 1:03:38
INDEX and MATCH to lookup Left 1:07:00
Pivot Tables 1:10:02
Proper Data Set 1:10:02
PivotTables Pivot Tables are Easy 1:12:00
Visualize Table First 1:12:00
Adding with One or Two Conditions (Criteria) 1:12:00
Report Layout 1:16:58
Number Formatting 1:17:44
Style Formatting, Create Your Own 1:19:05
Pivoting 1:21:21
Listing Two Fields in Row Labels 1:21:21
Collapsing Pivot Table Row 1:21:59
Changing Calculation: SUM to AVERAGE 1:22:42
Adding with Three Criteria 1:23:29
Filtering a Row (Show Top Two Regions), Clear Filter 1:24:46
Filter whole report with Report Filter 1:25:55
Show Report Filter Pages (30 PivotTables with 1 click) 1:27:55
Filter whole report with Report Filter or Slicer 1:28:30
Compare Formulas and PivotTables 1:30:13
Grouping Dates in PivotTables 1:33:42
Difference between Grouping Integers and Grouping Decimals 1:35:41
Pivot Chart 1:39:00
Show Values As: 1:40:45
Copy PivotTable 1:41:43
Running Totals & % Running Totals 1:42:00
% of Grand Totals, % of Column Total, % of Row Total 1:43:01
Difference From, % Difference From 1:44:17
Multiple Calculation in one PivotTable 1:46:26
Creating Second PivotTable from Second Cache of data using Excel 2003 keyboard shortcut for 3-step Wizard 1:47:54
Blank in number field causes PivotTable to Count by Default 1:49:30
Text in Date field prohibits Grouping of Dates 1:50:09
Recorded Macros For Reports:
Basic Recorded Macro 1:51:05
Absolute References and save in Personal Workbook 1:55:10
How to trick the Macro Recorder into seeing a variable height report using Relative References 2:00:51
Rearrange records from vertical orientation to proper table using Relative References 2:09:01
Charts 2:13:35
Chart Types 2:13:35
Column Chart and How The Chart Wizard Interprets Data From Cells 2:17:48
Chart keyboards (Create Default Chart) 2:18:10
Select Data Source Dialog Box 2:20:30
Linking Chart Title to Cells 2:23:09
Saving Chart Templates 2:25:43
Setting Default Charts 2:26:48
Copy Charts 2:27:45
Bar and Stacked Bar charts 2:27:57
Line Chart & Change Source Data 2:31:39
Number Formatting to show "K" or "M" 2:34:52
Line Chart vs. X Y Chart 2:35:53
X Y Scatter 2:36:45
X Y Scatter Line Break Even Analysis Chart 2:41:28
Multiple chart types 2:42:42
Видео Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards канала ExcelIsFun
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Excel for Accountants: Formulas, Functions & Formatting to Build Account Templates (37 Examples)Excel for Accountants: PivotTables, Power Query, IF, SUMIFS, VLOOKUP, Flash Fill, Charts…CWU SeminarHow to Create a Data Entry Form in Excel With Add, Modify, Delete and Reset (Step-by-step Guide)Excel 2016 Advanced50 Ultimate Excel Tips and Tricks for 2020How To Pass Advanced Excel Test For Job InterviewExcel Formula to Lookup Nth Value. FILTER, AGGREGATE or SMALL Function? Excel Magic Trick 1727.Top 10 Most Important Excel Formulas - Made Easy!Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)VLOOKUP Tutorial for Excel - Everything You Need To KnowExcel For Accountants: VLOOKUP & PivotTables Complete Lessons: Basic to Advanced: CWU SeminarTop 15 Advanced Excel 2016 Tips and TricksHow to use Excel Index Match (the right way)Accounting Spreadsheet [Excel Template] Create it in 15 minutesThe Beginner's Guide to Excel - Excel Basics TutorialExcel Crash Course for Finance Professionals - FREE | Corporate Finance InstituteMicrosoft Excel 2016 - Learn Excel 2016 Beginners Tutorial VideoTop 20 Microsoft Excel Tips & Tricks☑️ Top 25 Advanced Pivot Table Tips & Tricks For Microsoft ExcelExcel VLOOKUP Function - All You Need To Know