Excel for Accounting: Formulas, VLOOKUP & INDEX, PivotTables, Recorded Macros, Charts, Keyboards

Direct links to file:

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

[geraldchungu]

Funny this is over 8 years ago. Best excel video ever. Thank you so so much.

[hienhsaiyasith]

my wife has been watching your video for almost 4 days and taking notes. She needs to know formulas for work in the accounting world, Excel is the boom! All the things being talked about in this video are excellent!!! The best share secrets is here, thanks for sharing!!!

[vikaasb]

This is a great repository 🙏you have done a favour to all of us

[jah]

8 years on and I don’t think I’ve ever found an excel tut this helpful.

[excelisfun]

Most Epic and Comprehensive Excel Accounting Video Ever (2 hours 44 minutes): Formulas, PivotTables, Recorded Macros, Charts, Number Formatting and Keyboards:

[ManishSinghinfosys]

Dude, I don't think you understand the kind of legend you are. This is some serious learning of a tool we think we know enough. Thank you for such a clear and concise explanation.

[sahidulhaque]

Hi, I don't usually write comments but I thought I should for this. You have taken a lot of time to create this video, so I just wanted to say thank you. It is very detailed and simple to understand! 

[chandrashekarchannaveerapp]

Excellent - Significant chunk of excel in a single video .. Amazing, Thank you very much.

[Marinef]

Simply the BEST!!! THANK YOU!!!-))) This is the absolutely the best Excel class I have ever taken. I have been studying spreadsheets since VisiCalc. I have spent thousands of dollars and many hours in books, classes and instructional videos. This FREE online course is absolutely the best course I have ever had the pleasure of taking. You are an amazing instructor!!! Your enthusiastic video had me captivated the entire time. I thought my skill set in Excel was like a 9.99 before taking this class. Now I know I had not even scratched the surface. I can now do a report (pivot table) that used to take me 2+ hours in less than a minute. My efficiency in Excel is exponentially better so it truly is Fun now. I only wish I had watch this the day you posted it. I have spent way toooo many hours thinking Excel is not Fun and doing things the most inefficient ways possible. Please keep up the excellent work my friend! Thank You so much!

[MrLEEMI]

hi I am 48 years old and has some limited use of excel but since changing my job I seem to be getting more and more involved with it I have just started a excel training course and up to the point of watching your video I was getting lost in what they were saying now I am watching your video on YouTube and the way you are explaining it is a lot clearer to me you are a very inspirational person from the bottom of my heart thank you it might have just saved my job but not just that it has given my the confidence to sit with managers at work and know exactly what they are taking about and also offer some input myself thank you  mick

[thuantritran]

I've never found anything this amazing and generous on Youtube like your works. Thanks so much for sharing knowledge and improve people's lives.

[sammia]

Why would some people dislike his hard work who is trying to help people to be efficient in their work through excel? these people must be idiots who don't know the value of excel. Keep up the hard work Sir, you are a genius.

[christiandemerona]

I've never seen so many useful Excel tips in one place. And being able to skip to whatever tip you want to look at is just great !! Thanks for this.

[olawalesolomonegbewole]

I am so fascinated by this excellent and kind gesture of sharing this much knowledge for free. God bless you.

[nymanPoliceRadio]

Thank-you! Thank-you! Thank-you! I am so glad that I stumbled across your page. I thought I would only spend a few minutes scrolling through until I found the topic I wanted to see; but I watched an hour straight without knowing it! You are a fabulous teacher! I really enjoyed your style of explaining and showing exactly what you were doing without making me go bored out of mind. Your explanation on Pivot Tables alone was incredible and I'm looking forward to going to work tomorrow and be able to catch up on all the things I need to do since now that I understand Pivot Tables and just can shave 2days a month out of my routine by using what I just learned. Thanks again so much for taking the time to explain this the way you did!

[sabahudin]

I do not write comments online very often. Just in a speacial case. And this is the one. Many thanks sir!! Amazing, powerful, clear, well done.

[kaulillian]

WOW. This is FREE education?!?!?! It has really helped me. I've turned your channel into school for me. THANK YOU!

[FromHereWhere]

Dear Mike, I'm a late comer, but I want to voice what most already know - YOU are an amazing person!!! The way you give to others is invaluable not only to us but to you as a human being the way God made us to be. I am sure you are "highly favored" by God. I love your passion and generosity of Spirit. So I not only want to give you sincere praises but willing to back it up with donations!!! Please, please keep being who you are in the world! Thanks for all you do!!!!

[nadishak]

This tutorial video is awesome.  I learned every trick in the book.  Most importantly the use of VLOOKUP and Pivot Tables.

[gennadysudat]

O! M! G! It's merely miracles! I've been using Excel for 19 years now. How many times I was stuck with this relative-absolute mixed issue and was wondering if there's some simple solution for this but never made it to just look for it and here it is! THANK YOU!