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

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 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 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 Buy excelisfun products: https://teespring.com/stores/excelisfun-store

VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function

Download Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/VLOOKUP-SHARK-WEEK-DoawnloadableWorkbook01-Start.xlsx Download Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/VLOOKUP-SHARK-WEEK-DoawnloadableWorkbook01-FINISHED.xlsx Entire Page with All Files for All Videos: https://people.highline.edu/mgirvin/excelisfun.htm Then scroll down to this section: "VLOOKUP "Shark" Week Downloadable Workbooks" Look for the file named: VLOOKUP-SHARK-WEEK-DownloadableWorkbook01-Start.xlsx Buy excelisfun products: https://teespring.com/stores/excelisfun-store Excel VLOOKUP is one of the most important functions because everyone needs to look stuff up. Not only that, but if you want to get a job, you must know VLOOKUP. In this video learn everything you need to know about VLOOKUP in 1 video. Here are the topics (minute marks jump you to that part of the video): 1. VLOOKUP = V = Vertical ( min) 2. Why so common? Because everyone needs to look stuff up ( 00:43 min) 3. VLOOKUP Delivers a value to a cell: Extract Match ( 2:37 min) 4. VLOOKUP #N/A and IFERROR or IF ( 5:25 min) 5. VLOOKUP and Data Validation ( 8:10 min) 6. VLOOKUP with Named Range ( 10:34 min) 7. VLOOKUP as Formula Element ( 17:24 min) 8. HLOOKUP = H = Horizontal ( 18:25 min) 9. VLOOKUP Delivers a value to a cell: Approximate Match ( 19:40 min) 10. VLOOKUP with table on different sheet ( 24:00 min) 11. VLOOKUP with table in different workbook ( 25:56 min) 12. Show Full Record: Numbers above column header ( 27:15 min) 13. Show Full Record horizontally: COLUMNS Function ( 30:20 min) 14. Show Full Record vertically: ROWS Function ( 32:28 min) 15. Show Full Record: MATCH Functions ( 33:40 min) 16. Two Lookup Values: Add join column as first column in table ( 35:40 min) 17. Two Lookup Values: Join columns in formula and use CHOOSE function ( 38:50 min) 18. Partial Text Lookup: LEFT and SEARCH Functions ( 43:22 min) 19. Partial Text Lookup: MID Function ( 45:37 min) 20. Partial Text Lookup: RIGHT Function ( 48:08 min) 21. TRIM and VLOOKUP: Space in lookup value ( 48:57 min) 22. TRIM and VLOOKUP: Space in 1st column of table ( 51:03 min) 23. VLOOKUP with 3 lookup tables: CHOOSE Function ( 53:20 min) 24. VLOOKUP: Two Way Lookup with VLOOKUP and MATCH ( 58:36 min) 25. VLOOKUP: Variable Tax Rate, 3 VLOOKUPS in one formula ( 1:00:18 min) 26. VLOOKUP and Table feature for Dynamic ranges ( 1:05:17 min) VLOOKUP Shark WEEK How To Use Excel VLOOKUP Function Learn Excel VLOOKUP Function VLOOKUP made easy Excel VLOOKUP Excel VLOOKUP Function

Tour Of Excel: Formulas, Formatting, Sort, Filter, PivotTables, Charts, Keyboards

Tour Of Excel: Formulas, Formatting, Sort, Filter, PivotTables, Charts, Keyboards

Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm (all the way at bottom in "Other Excel Series" section) Highline Community College Science Seminar: The Wonders Of Excel, May 20, 2011 Learn about the wonders of what excel can do with a tour of the different things that Excel can do: 1 Copy & Paste Special (2 Examples) (00:48 minute mark) 2 Keyboard Magic (3 Examples) (03:45 minute mark) 3 Number Format (06:03 minute mark) 4 Decimal (6:11 minute mark) 5 Date (5 Examples) (07:32 minute mark) 6 Percentage (15:02 minute mark) 7 Formulas and Relative Cell References (18:26 minute mark) 8 Formulas and Absolute Cell References (2 Examples) (21:47 minute mark) 9 Sorting (4 Examples) (27:27 minute mark) 10 Filtering (32:27 minute mark) 11 PivotTables (2 Examples) (35:19 minute mark) 12 Charts (6 Examples) (43:31 minute mark) Buy excelisfun products: https://teespring.com/stores/excelisfun-store

Excel Data Analysis: Sort, Filter, PivotTable, Formulas (25 Examples): HCC Professional Day 2012

Excel Data Analysis: Sort, Filter, PivotTable, Formulas (25 Examples): HCC Professional Day 2012

Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn the basics of Data Analysis at Highline Community College Professional Development Day 2012: Topics in Video: 1. What is Data Analysis? ( 00:53 min mark) 2. How Data Must Be Setup ( 02:53 min mark) Sort: 3. Sort with 1 criteria ( 04:35 min mark) 4. Sort with 2 criteria or more ( 06:27 min mark) 5. Sort by color ( 10:01 min mark) Filter: 6. Filter with 1 criteria ( 11:26 min mark) 7. Filter with 2 criteria or more ( 15:14 min mark) 8. Filter by color ( 16:28 min mark) 9. Filter Text, Numbers, Dates ( 16:50 min mark) 10. Filter by Partial Text ( 20:16 min mark) Pivot Tables: 11. What is a PivotTable? ( 21:05 min mark) 12. Easy 3 step method, Cross Tabulation ( 23:07 min mark) 13. Change the calculation ( 26:52 min mark) 14. More than one calculation ( 28:45 min mark) 15. Value Field Settings (32:36 min mark) 16. Grouping Numbers ( 33:24 min mark) 17. Filter in a Pivot Table ( 35:45 min mark) 18. Slicers ( 37:09 min mark) Charts: 19. Column Charts from Pivot Tables ( 38:37 min mark) Formulas: 20. SUMIFS ( 42:17 min mark) 21. Data Analysis Formula or PivotTables? ( 45:11 min mark) 22. COUNTIF ( 46:12 min mark) 23. Formula to Compare Two Lists: ISNA and MATCH functions ( 47:00 min mark) Getting Data Into Excel 24. Import from CSV file ( 51:21 min mark) 25. Import from Access ( 54:00 min mark) Highline Community College Professional Development Day 2012 Buy excelisfun products: https://teespring.com/stores/excelisfun-store

Excel Magic Trick #184: Setup Database in Excel

Excel Magic Trick #184: Setup Database in Excel

See how to create a simple database in Excel using the List or Table feature. A simple database can be created in Excel using the Excel 2003 "List" feature or the Excel 2007 "Table" feature. Fields names must be in first row (no blanks). Records are in rows (no blanks). Other data in the sheet cannot be next to the Table/List (at least one blank row or column between other data and the Table/List Keyboard shortcuts: Excel 2003 List: Ctrl + L. Excel 2007 Table: Ctrl + T. The ranges are dynamic: formulas, pivot tables, charts will all automatically update

Excel Magic Trick 783: Date Functions & Formulas (17 Examples)

Excel Magic Trick 783: Date Functions & Formulas (17 Examples)

Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. DAY function 2. TEXT Function to get Day spelled out, like "Monday" 3. TEXT Function to get Month spelled out, like "January" 4. MONTH Function 5. YEAR function 6. Last Day in Month: EOMONTH function 7. Last Day in Month: DATE, YEAR, DAY, MONTH functions 8. First Day Next Month: EOMONTH function 9. First Day Next Month: DATE, YEAR, DAY, MONTH Functions 10. Vest Date: EDATE function 11. List of First Day in Month: EOMONTH 12. WORKDAY function -- find workday in future given a certain number of days. 13. WEEKDAY function 14. Last Monday: DATE, YEAR, MONTH, DAY and WEEKDAY functions 15. Formula for Days Between Two Dates 16. Formula for Days Between Two Dates, Inclusive 17. Net Working Days with NETWORKINGDAYS Function Buy excelisfun products: https://teespring.com/stores/excelisfun-store

Excel Basics #22: IF function formula made easy!

Excel Basics #22: IF function formula made easy!

The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #22 Video topics: 1)Formula for bonus 2)TRUE FALSE formula 3)IF function formula for bonus 4)Put one of two numbers in a cell 5)Formula to check whether two columns are equal 6)IF function formula to check if two columns are in balance 7)Put one of two words (Text Strings) in a cell Also see these videos: Excel Magic Trick 167p1 IF function formula: 12 Examples! Excel Magic Trick 167p2 IF function formula: 12 Examples!

Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)

Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)

Download file: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT778.xlsx There are really about 30 example in this long video. INDEX and MATCH How To: 1 MATCH Functions Basics (about 0:46 minute mark) 2 INDEX Basics: Two way lookup (about 9:05 minute mark) 3 One way lookup vertical (lookup Left) (about 13:07 minute mark) 4 One way lookup horizontal (about 14:50 minute mark) 5 One way lookup with lookup column and match column orientated differently (about 17:51 minute mark) 6 INDEX & MATCH to do approximate lookup on table sorted Descending (about 19:15 minute mark) 7 Lookup whole row (about 21:41 minute mark) 8 Lookup whole column (about 24:49 minute mark) 9 Lookup Cell Reference (about 26:00 minute mark) 10 Create Dynamic Range For Chart (about 26:00 minute mark) 11 Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark) 12 Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark) 13 INDEX and ROWS to Filp a Table (about 47:20 minute mark) 14 Get Amount in Next Bracket (about 50:34 minute mark) 15 Select a Random Value (about 53:18 minute mark) 16 Lookup first non-blank value (about 54:24 minute mark) 17 Handle arrays that require Ctrl + Shift + Enter CSE (about 54:24 minute mark) 18 Basic lookup tool used in complex array formulas for extracting data (about 59:59 minute mark, 1 hour 1 minute 10 seconds)

Excel Basics #1: What Is Excel?

Excel Basics #1: What Is Excel?

Download file (Excel Basics): http://people.highline.edu/mgirvin/ExcelIsFun.htm The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #1 Video topics: 1)What is Excel? Calculating formulas and Data Analysis 2)Rows, Columns, Cells, Worksheets, Sheet Tab Names, Workbook 3)File extensions (file types): .xlsx, .xlsm, .xls, .xlsb 4)Excel 2007 Ribbons 5)Excel 2007 Quick Access Tool Bar (QAT) 6)How to find features in Excel 2007 7)How to add buttons to the QAT Quick Access Toolbar Excel Tutorial Excel How To Excel Lessons Learn Excel Beginning Excel Excel Basics

INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)

INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)

Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm This video is a part of VLookup Week: http://vlookupweek.wordpress.com There are really about 30 example in this long video. Excel INDEX and MATCH functions How To: 1 MATCH Functions Basics (about 0:46 minute mark) 2 INDEX Basics: Two way lookup (about 9:05 minute mark) 3 One way lookup vertical (lookup Left) (about 13:07 minute mark) 4 One way lookup horizontal (about 14:50 minute mark) 5 One way lookup with lookup column and match column orientated differently (about 17:51 minute mark) 6 INDEX & MATCH to do approximate lookup on table sorted Descending (about 19:15 minute mark) 7 Lookup whole row (about 21:41 minute mark) 8 Lookup whole column (about 24:49 minute mark) 9 Lookup Cell Reference (about 26:00 minute mark) 10 Create Dynamic Range For Chart (about 26:00 minute mark) 11 Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark) 12 Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark) 13 INDEX and ROWS to Filp a Table (about 47:20 minute mark) 14 Get Amount in Next Bracket (about 50:34 minute mark) 15 Select a Random Value (about 53:18 minute mark) 16 Lookup first non-blank value (about 54:24 minute mark) 17 Handle arrays that require Ctrl + Shift + Enter CSE (about 54:24 minute mark) 18 Basic lookup tool used in complex array formulas for extracting data (about 59:59 minute mark, 1 hour 1 minute 10 seconds) VLOOKUP Shark Week Buy excelisfun products: https://teespring.com/stores/excelisfun-store

Top Videa -  loading... Změnit krajinu
Načíst dalších 10 videí
 
 
Sorry, You can't play this video
00:00/00:00
  •  
  •  
  •  
  •  
  •  
  •  
  •  
CLOSE
CLOSE
CLOSE