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

• published: 29 Aug 2013
• views: 901398

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