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

Download file (ALL THE WAY AT BOTTOM OF PAGE): http://people.highline.edu/mgirvin/excelisfun.htm

Keyboards 0:01:47
Jump: Ctrl + Arrow 0:02:20
Go To Cell A1: Ctrl + Home 0:02:36
Highlight column: Click, Shift, Click 0:02:50
Toggle Ribbon tabs On/Off: Ctrl + F1 0:03:18
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
Format Report using 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
Selecting Chart Elements 2:23:55
Keyboard for Format Chart Element: Ctrl + 1 2:24: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

Washington Accounting Association Excel 2013

Comments

Randy Jones says:

WOW THANK YOU

S Privett says:

This is amazing. I start a new analyst role this weekend and this is definitely helping knock the Excel cobwebs off. Thank you!

Maverick Elias says:

19:38! he makes a time sucking noise! LMAO.

dxc says:

Bravo.. Bravo!

Adiba Muminova says:

Thank you for the great job, I really appreciate the work you have done!

John David says:

i came back to watch the video many times, and I have to say it is very useful and you have done a good job. Thank you so much for sharing.

Jojo The Boss says:

Thank You!

joshua Paul says:

Relative Reference is no longer available for Excel 2016, it seems. Should I just learn to code Macro the hard way? And where do you recommend I learn the hard way?

Param Hegde says:

Great Work and Thank You.

wenqi chen says:

Thank you very much for the Video, it is Amazing that your video is helping people around the world!

Jealous Lawn Care says:

Very nice organization…love the times in the desc!

thres34 says:

Really? For free? Wow there are still good people out there

PissMenn says:

9:45 using =sum(B:B) is faster i think

Analyn Mayo says:

WHoAA!!!!! YOUR AMAZING INTRUCTOR! Lot of people are timid when it comes to excel, you made is FUN!!! Love it. Thank you!!! Highly appreciate your help. Your so awesome!!!!!!!!

Anjali Nair says:

Thank you so much for sharing this video. Made pivot tables seem so easy.I can now enjoy working in excel rather than thinking it as a nightmare !

cheese chocolate says:

Is there a way I can donate as an appreciation of your effort?

Michael John Field says:

An extremely good video – I don’t think I could take it in all at once, but so much information and clearly presented. Very nicely done! I enjoyed some of the ‘little’ history lessons on how code was created as well – this helped in some cases for me to remember the details!

Marilyn Gagnon says:

Wow amazing work!! Do you have the latest file completed with all the formulas (the same as the video) ? would be greatly appreciated

Alfredo Lopez says:

Dude. Thank you, the steps you took to make this fun and easy are greatly appreciated.
Total newb too by the way.

ying qi says:

This video is awesome! Many many thanks!

Kuba O says:

GOOOOD JOB!

Carlos Montano says:

Thank you!!!!

Junaid Sayed says:

Thanks a lot sir your are best teacher. you explain so good I have understood every single bit of the video. thanks again

Hemsagar Kosriya says:

thanks

Sudarshan Chatterjee says:

Thanks For the Master Class of Excel, This really is an Epic Video I ever seen……

Mourad Carlo says:

Thanks .

karan kumar says:

Hoe many formulas are there in ms excel?
Are they enough in the video?
Please tell me…

Carlos Averos says:

Best video in youtube!!!

Marie Allen says:

Excellent work!
Thank you

Jose Morales says:

Can anyone help me? I am trying to make an excel sheet that will generate a number when data is entered. Example, column headings will be , number of total surveys, number of total bad surveys, % of total good surveys, *how many surveys needed to get to 90%.

I can’t figure out how to create the formula that will give me the last heading *

southamericanvision says:

You are AWESOME! God!

Luis Joel Vazquez, CPA says:

Amazing you are simply the best!

Adrian Swaby says:

Excel
i am so over being a loser barry loser noses drawn by jim smith

Mary Ann Reformado says:

Simply Amazing Video..Well done!!!

Nick Santangelo says:

I absolutely love your videos and am a subscriber and have you saved to my favorites. Question: I am transitioning from the Military to the Civilian workforce and will be assuming a management role and was wondering if you had any saved spreadsheets that would be useful for tracking, employees, payroll, inventory, purchase orders, etc… I appreciate any help

Julie Tykoezinski says:

Hello 🙂 thanks very much !!! Could you please share the link for the playlist with every cook trick i know (90examples) thank you ver much ☺️

Raju Ar says:

Sir Can you tell me what questions will be asked if you take interview

ivor worrell says:

Great tutorial.

Ly Lai says:

You are an excellent instructor. WOW, I could literally listen to you all day. You really make it really easy to follow along, you’re great with explaining and I really do enjoy your personality  making it so enjoyable! That takes passion and talent! Continue with the great work and thank you for making these tutorials for all of us to enjoy. thank you thank you thank you!

Sabahudin Cergic says:

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.

Louie Jul Dio says:

OMG Dude. Thank you so much!

María Salazar says:

Thank you so much, It´s really amazing this work =D

L C says:

Feel compelled to write this… you are exceptional! Your lessons are quick and to the point, very thorough, and complete. I am subscribed and expect at least an extra view or two, or.. twelve on all your videos in due time. Thank you!

Ahmad Habis says:

thanks, excel boss

mrmhjr7 says:

Hi!

I didn’t manage to dowload the file
the link seem not to work anymore

Could please help?

Eugene Mobuogwu says:

Excellent video. You are awesome. Many thanks

 Write a comment

*

Do you like our videos?
Do you want to see more like that?

Please click below to support us on Facebook!