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

Keyboards
Jump: Ctrl + Arrow
Go To Cell A1: Ctrl + Home
Format Cells dialog box or in a chart Format Chart dialog box: Ctrl + 1
Currency Number Format: Ctrl + Shift + 4
Highlight column: Ctrl + Shift + Arrow
Currency Vs Accounting Number Format
Alt keys:
PivotTable: Alt, N, V, T
PivotTable 2003: Alt, D, P
Page Setup: Alt, P, S, P
SUM: Alt + =
"Put thing in cell and move selected cell up": Shift + Enter
"Put thing in cell and keep cell selected": Ctrl + Enter
Select sheet to right: Ctrl + PageDown
Select sheet to left: Ctrl + PageUp
Number Formatting As Façade:
Decimal Number Format
Date Number Format
Keyboard for today's hard caded date:
Time Number Format
Percentage Number Format
Efficient Formula Creation
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
Formula elements, types of formulas, types of data
Monthly Allocation Formula: illustrate formula input that can be hard coded into formula
Tax inefficient formula: illustrate formula input that can should NOT hard coded into formula
Tax efficient formula: illustrate Golden Rule
Net Cash In formula: illustrate Golden Rule
Net Income formula: illustrate formula with built-in function within a larger formula
In Balance? formula: illustrate Logical formula
First & Last Name Join Formula: illustrate Text formula
COUNTIF formula: illustrate counting with criteria
COUNTIF & Label formula: illustrate counting with criteria and how the join symbol is used with criteria
Clear Formatting
SUMIFS to add with two criteria
SUMIFS to add between 2 dates
Count workdays formula to illustrate new Excel 2010 function NETWORKDAYS.INTL
Relative and Absolute Cell References
Mixed cell references in budget formula
Lookup Formulas
VLOOKUP to lookup product price: illustrate Exact Match lookup
Data Validation List:
VLOOKUP and IFERROR
VLOOKUP to lookup commission rate: illustrate Approximate Match lookup
Retrieve record 2-way lookup with VLOOKUP and MATCH
MATCH, ISNA and ISNUMBER functions to compare 2 lists
INDEX and MATCH to lookup Left
Pivot Tables
Proper Data Set
PivotTables Pivot Tables are Easy
Visualize Table First
Adding with One or Two Conditions Criteria
Report Layout
Number Formatting
Style Formatting, Create Your Own
Pivoting
Listing Two Fields in Row Labels
Collapsing Pivot Table Row
Changing Calculation: SUM to AVERAGE
Adding with Three Criteria
Filtering a Row Show Top Two Regions, Clear Filter
Filter whole report with Report Filter
Show Report Filter Pages 30 PivotTables with 1 click
Filter whole report with Report Filter or Slicer
Compare Formulas and PivotTables
Grouping Dates in PivotTables
Difference between Grouping Integers and Grouping Decimals
Pivot Chart
Show Values As:
Copy PivotTable
Running Totals & % Running Totals
% of Grand Totals, % of Column Total, % of Row Total
Difference From, % Difference From
Multiple Calculation in one PivotTable
Creating Second PivotTable from Second Cache of data using Excel 2003 keyboard shortcut for 3-step Wizard
Blank in number field causes PivotTable to Count by Default
Text in Date field prohibits Grouping of Dates
Basic Recorded Macro
Absolute References and save in Personal Workbook
How to trick the Macro Recorder into seeing a variable height report using Relative References
Rearrange records from vertical orientation to proper table using Relative References
Charts
Chart Types
Column Chart and How The Chart Wizard Interprets Data From Cells
Chart keyboards Create Default Chart
Select Data Source Dialog Box
Linking Chart Title to Cells
Saving Chart Templates
Setting Default Charts
Copy Charts
Bar and Stacked Bar charts
Line Chart & Change Source Data
Number Formatting to show "K" or "M"
Line Chart vs. X Y Chart
X Y Scatter
X Y Scatter Line Break Even Analysis Chart
Multiple chart types