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