Accounting Excel
Topics
1 Keyboards
2 Number Formatting As Façade
3 Efficient Formula Creation
4 Lookup Formulas
5 Pivot Tables
6 Recorded Macros For Reports
7 Charts
8 Search the excelisfun YouTube channel for topics
9 Books and DVDs by Mike "excelisfun" Girvin
Topics
1
3
4
5
6
7
8
9
Topics
Keyboards
Slaying Excel Dragon: 1) Pages 57 - 78 in book and 2) video #6 on DVD.
SUM ==> Alt + =
Highlight Column ==> Ctrl + Shift + Arrow
Alt keyboards
Number Formatting As Façade
Slaying Excel Dragon: 1) Pages 2 - 248 in book and 2) video #2 on DVD.
Decimal
Date
Time
Percentage
Efficient Formula Creation
Slaying Excel Dragon: 1) Pages 119 - 340 in book and 2) videos #11 - 36 on DVD and 3) Video #1 on
Excel's Golden Rule: If a formula input can vary, put it in a cell and refer to it in the formula with
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 c
SUMIFS to add with two criteria.
SUMIFS to add between 2 dates.
Count workdays formula to illustrate new Excel 2010 function [Link].
Mixed cell references in budget formula
Lookup Formulas
LOOKUP DVD
VLOOKUP to lookup product price: illustrate Exact Match lookup.
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 MACTH to lookup Left.
Pivot Tables
Slaying Excel Dragon: 1) Pages 374 - 419 in book and 2) videos #39 - 43 on DVD.
Proper Data Set
PivotTables Pivot Tables are Easy
Visualize Table First
Adding with One or Two Conditions (Criteria)
Report Layout
Style Formatting
Number Formatting
Pivoting
Listing Two Fields in Row Labels
Collapsing Pivot Table Row
Changing Calculation: SUM to AVERAGE
Adding with Three Criteria
Filtering a Row
Clear Filter
Filter whole report with Report Filter or Slicer
Show Report Filter Pages
Compare Formulas and PivotTables
Grouping Dates in PivotTables
Grouping Integers
Grouping Decimals
Pivot Chart
Running 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-
Blank in number field causes PivotTable to Count by Default.
Text in Date field prohibits Grouping of Dates.
Recorded Macros For Reports
Slaying Excel Dragon: 1) Videos #52 on DVD.
Basic Recorded Macro
Format Report using Absolute References.
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
Slaying Excel Dragon: 1) Pages 465 - 506 in book and 2) video #49 on DVD.
Chart Types
Column Chart
Select Data Source Dialog Box
Chart keyboards
Linking Chart Title to Cells
Keyboard: Ctrl + 1
Format Chart Elements
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"
X Y Scatter
Line Chart vs. X Y Chart
X Y Scatter Line Break Even Analysis Chart
Multiple chart types
Search the excelisfun YouTube channel for topics
[Link]
Books and DVDs by Mike "excelisfun" Girvin:
Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun:
Slaying Excel Dragons DVD: 53 Lessons to Make Excel Fun: $25
Ctr l+ Shift + Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formu
Problems: $20
Ctr l+ Shift + Enter: Mastering Excel Array Formulas DVD: $25
Excel Lookup DVD: $20