Home
Videos uploaded by user “ExcelIsFun”
Excel Magic Trick 398: VLOOKUP with Two Lookup Values (IFERROR function also) 2 lookup values
 
04:54
See how to use the VLOOKUP function when you have two lookup values for each record. Learn how to use the IFERROR function also, and how to join two cells to add a new first column to the lookup table for the VLOOKUP function. Ampersand symbol to join cell content. VLOOKUP with Two Lookup Values VLOOKUP with 2 Lookup Values VLOOKUP with Two Lookup_Value VLOOKUP with 2 Lookup_Value
Views: 219318 ExcelIsFun
Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Dependent DV
 
06:39
Downlod file: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT539-550.xls Dependent Data Validation List. See how to create a Drop Down List that is based on another drop down list using there features: 1)Data Validation List 2)Defined Names 3)Create Names From Selection 4)INDIRECT function 5)SUBSTITUTE function Drop down based on Drop down list Data Validation based on another data validation Dependent Data Validation
Views: 235550 ExcelIsFun
Excel Power Query #06: Import Multiple Excel Files From Folder Into One Excel Table or Data Model
 
06:32
Download file: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query to import multiple Excel Files into an Excel Table or Data Model. Then see how easy it is to update the Table when new files are dropped into the Folder. 1. (00:10 minute mark) Problem Setup 2. (01:03 minute mark) Import Excel workbook files from Folder. 3. (01:40 minute mark) Add new column using Excel.Workbook 4. (02:36 minute mark) Add new column using Table.PromoteHeaders 5. (04:00 minute mark) Edit Query after it is loaded. Change Data Types for columns. 6. (04:37 minute mark) Create PivotTable with Slicer 7. (05:30 minute mark) Add new files to folder. 8. (05:45 minute mark) Refresh to see that new file data is included in PivotTable. 9. ( minute mark) 10. ( minute mark) Miguel Escobar and Ken Puls are doing online workshops on Power Query www.powerquery.training . This training is not by excelisfun.
Views: 141805 ExcelIsFun
Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH and Concatenated Ranges & Cells
 
07:23
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to do a three way lookup when there are two row header variables (criteria) and one column header (criteria) variable using INDEX and MATCH functions for a normal two way lookup, but with the two row headers joined to create a single row header criteria. In essence, three way lookup is turned into a standard two way lookup. Also see IFERROR function.
Views: 339827 ExcelIsFun
Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function
 
04:55
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do a lookup for Pictures in Excel using: Data Validation Dropdown List INDEX and MATCH Functions Defined Names Also see this video: Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?: Excel Magic Trick 637: Lookup A Chart -- Defined Names, INDIRECT function and Formulas In Pictures Hint for Excel 2007: Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions Hint 2007: Use Bitmap Image instead of picture
Views: 170228 ExcelIsFun
Excel Name Trick #7: INDIRECT function, Names and LOOKUP
 
05:19
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelNamesSeries1-12.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelArrayFormulaSeries1-14Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm THE most amazing Name Trick: See how to use Data Validation, the INDIRECT function, the space operator and Names to look things up! Two 2 Way Lookup YTL Excel #119: Amazing Lookup Solution! Excel Lookup Series #11: INDEX & MATCH functions Two 2 Way Lookup Excel Name Trick #7: INDIRECT function, Names and LOOKUP Mr Excel & excelisfun Excel Trick #1: Two 2 Way Lookup MrExcel's Learn Excel #965 - Two-Way Lookup Excel Magic Trick #136: Two 2 way lookup with VLOOKUP & MATCH Instead of INDEX and MATCH functions for looking up items in a 2-dimenational table, use Names, INDIRECT function, Data Validation and the space intersector operator. See how to create 15 names with a single keyboard shortcut. Create Names From Selection. This is a logical (beginning to end) story about most of the use for Excel Names. In this series you will see 12 amazing uses for Excel Names (14 total Name Tricks).
Views: 233195 ExcelIsFun
Excel Magic Trick 923: Recorded Macro Basics: Click Button To Move Data To Table On Another Sheet
 
13:34
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to: 1. Enter Data On One Sheet and then click Button To Send Data To Table on Second Sheet 2. Recorded Macro: Move Record From Data Entry Sheet To Table On Second Sheet 3. Learn how to Show Developer Ribbon (Excel 2010 = Right-click Ribbon, Customize Ribbon, Checkbox for Developer Ribbon) 4. Keyboard: Alt + F8 = List of Macros 5. Keyboard: Alt + F11 = VBA Editor 6. Learn how to use Absolute and Relative References in Recorded Macro 7. Add Form Button To Sheet that can be clicked to execute recorded Macro. 8. Absolute References = Selecting Cells with Macro Recorder on means Absolute Movement 9. Relative References = Selecting Cells with Macro Recorder on means Relative Movement 10. View Code: Range function for Absolute References, OFFSET Function for Relative References
Views: 365673 ExcelIsFun
Excel Magic Trick 167p1 IF function formula: 12 Examples!
 
10:31
Part 1. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how the IF function can be used in formulas. Also see these functions: AND, OR, SUM, AVEARAGE, STDEVP, and VLOOKUP. See how to use the IF function to put: 1 of 2 numbers in a cell or formula. 1 of 2 words in a cell or formula. 1 of 2 text strings in a cell or formula. 1 of 2 formulas in a cell or formula. 1 of 2 functions in a cell or formula. 1 of 2 cell ranges in a cell or formula. How to have more than one IF (Nested IF Ifs). When to switch to the VLOOKUP function. How to build multiple criteria formulas with the IF function. How to use the AND function with the IF function. How to use the OR function with the IF function. Excel If statement Excel Magic Trick 165p2 MACRO Advanced Filter Extract B-days Other amazing videos at YouTube: Excel Trick, Making a Gantt Chart with Excel, Recording Macros - Excel 2003, VLOOKUP in Excel 2007.
Views: 297180 ExcelIsFun
Excel Magic Trick 1412: Power Query to Merge Two Tables Into One Table for PivotTable Report
 
06:56
Download File: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1412Start.xlsx Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1412Finished.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query to Merge Two Tables into a Single Table, Load as a Connection Only and then build a PivotTable based on the New Merged Table saved as a connection only.
Views: 38581 ExcelIsFun
Excel Magic Trick 807:Protect Sheet To Allow Data Enter Only In Cells With Raw Data
 
03:02
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to use the Protect Sheet feature to allow people to only select cells that have been unlocked. 2. Learn how to unlock cells using Format Cells Dialog Box 3. Perfect for a Form where people enter raw data.
Views: 69710 ExcelIsFun
Excel Magic Trick 326: Hyperlink Worksheet To Worksheet
 
03:00
See how to create a hyperlink from one sheet to another sheet in the same workbook.
Views: 142501 ExcelIsFun
Excel Magic Trick 780: 3-D Cell References For SUMIF Function - Adding with Criteria Across Sheets
 
09:43
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. Great Trick From jeffreybrown at Mr Excel Message Board 2. See how to add with one condition across sheets 3. See Two ways to create a 3-D Cell References when you are summing with one condition. 1) INDIRECT, SUMIF and SUMPRODUCT functions to create a 3-D Range Of Cell References 2) SUMIF on each sheet (Calculate Locally) and SUM function with 3-D Cell Reference (Reference Globally) -- To Avoid the Volatile Function INDIRECT.
Views: 18755 ExcelIsFun
Excel Magic Trick 610: Pivot Table Year Over Year Sales Calculation From Daily Transactional Data
 
05:38
Create a Pivot Table to calculate Year Over Year Sales showing Percentage Change using the Show Values As % Of Value Field Setting.
Views: 67277 ExcelIsFun
Excel 2013 PowerPivot Basics #03: Replace VLOOKUP with Excel 2013 Data Model & Relationships
 
07:24
Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to replace VLOOKUP with Excel 2013 Data Model and Relationships. See how to get two or more tables into a PivotTable Field List: 1. (00:11 min mark) Get Two Tables into an Excel 2013 PivotTable Field List without using PowerPivot. 2. (00:54 min mark) How to indirectly access Data Model and Relationships in Excel 2013 PivotTable Field List without using PowerPivot. 3. (01:10 min mark) Convert Excel Proper Data set to Excel Table (Ctrl + T) and then name Table (Alt, J, T, A) 4. (01:30 min mark) When you invoke the Insert PivotTable dialog box (Alt, N, V) the “Add this data to Data Model” check box appears. By adding the Table from this dialog, you can add Table to Data Model. Even though we do not have PowerPivot, the Columnar Database / XVelocity engine is still in Excel 2013 by default. 5. (02:27 min mark) ALL tab in PivotTable Field List: if you have Two Excel Tables in an Excel 2013 workbook, when you invoke the Insert PivotTable, the two tables will appear. 6. (02:43 min mark) Pulling Fields from both tables into the PivotTable when there is no established Relationship between the two tables invokes the “Yellow Relationship” button. 7. (03:54) When you create a Relationship between the two tables with the “Yellow Relationship” button, the second Table is added to the Data Model and a Relationship is established. 8. (04:24 min mark) Installing COM PowerPivot Add-in. 9. (05:10 min mark) Add Excel Tables To Data Model (Alt, B, Y) 10. (05:30 min mark) Creating a Relationship between two tables using Diagram View in PowerPivot. 11. (05:30 min mark) Creating a Relationship between two tables using Diagram View in PowerPivot. 12. (06:13 min mark0 Create PivotTable based on PowerPivot Data Model. Add two or more tables to PivotTable Field List.
Views: 91506 ExcelIsFun
Excel 2010 Preview #4: Pivot Table Slicer (Report/Page Filter Slicer)
 
02:49
The new Slicer feature for the Pivot Table Report / Page Filter is beautiful and efficient. If you like Pivot Tables with lots of criteria, the Pivot Table Slicer is for you! Pivot Table PivotTable.
Views: 116002 ExcelIsFun
Excel Magic Trick 1141: Link Access Table To Excel Data Source
 
03:48
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to Link Access Table To Excel Data Source.
Views: 45735 ExcelIsFun
Excel 2013 PowerPivot Basics #04: DAX Formulas Can Be Easier & Faster Than Array Formulas
 
09:20
Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See two examples of Array Formulas and two examples of PowerPivot DAX formulas that count unique values in a column (Distinct Count) and compare and contrast: 1. (00:09 min mark) Array Formula For counting unique values using FREQUENCY function and other formula elements. This is more complicated than equivalent DAX function DISTINCTCOUNT. 2. (01:12 min mark) Array Formula For counting unique values using SUMPRODUCT and COUNTIF functions. This is very slow calculating in comparison to FREQUNENCY Array Formula and DAX function DISTINCTCOUNT. 3. (02:07 min mark) Access DAX function DISTINCTCOUNT without PowerPivot using Excel Table feature and Create PivotTable dialog box “Add data to Data Model” check box. 4. (02:32 min mark) Convert proper data set to Excel Table feature using Ctrl + T 5. (02:36 min mark) Name Excel Table using Alt, J, T, A 6. (02:42 min mark) Add Table to Data Model using Create PivotTable dialog box “Add data to Data Model” check box 7. (03;33 min mark) Use PivotTable function Distinct Count 8. (03:55 min mark) Open “Manage Data Model” window with Alt, B, M 9. (04:17 min mark) Create Calculated Field (Measure) called Unique Count using the DAX function DISTINCTCOUNT 10. (04:52 min mark) Assignment Operator for Calculated Fields is “Colon and Equal Signs”. 11. (05:49 min mark) Why use Calculated Field (Explicit Formula) to get DISTINCTCOUNT rather than Distinct Count (Implicit Formula) from PivotTable? 12. (06:27 min mark) Create PivotTable that uses Calculated Field to get a unique count. 13. (07:31 min mark) See very complicated Array Formula to simulate Array Formula Helper Columns And/Or Relationships between table in PowerPivot. This formula is very complicated and it would be easier to just use PowerPivot!
Views: 87675 ExcelIsFun
Excel Magic Trick 1113: Extract All Characters In Cell To Separate Cells: PPPP to P, P, P, P
 
04:31
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to Extract All Characters In Cell To Separate Cells: "PPPP" to "P", "P", "P", "P": 1. Text To Columns using Fixed Width, One Time Event 2. Formula that will update when data changes. Formula uses MID and COLUMNS functions and Mixed Cell References.
Views: 30851 ExcelIsFun
Excel Basics 5: PivotTable Power to Create Reports with Various Calculations & Layouts
 
20:20
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start & Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB05-PivotTableCalculationsLayout.xlsx Download Full Written Notes: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/Office2016Class17-ExcelBasics05PivotTableCalculationsLayout.pdf Class web page link: https://people.highline.edu/mgirvin/AllClasses/216v2016/Content/04Excel/Excel2016.htm Topics in this video: 1. (00:01) Introduction 2. (01:46) Keyboard for PivotTable 3. (04:00) Why a PivotTable is called a PivotTable 4. (04:25) Cross Tabulated Report (Adding with 2 Criteria) 5. (05:15) Calculations with Conditions or Criteria 6. (07:40) Change Calculation in Values area 7. (09:18) Drop Number or Text fields in Values area 8. (11:43) PivotTables on Large Data Set is Easy!! 9. (12:00) Keyboard to jump to bottom of column 10. (13:00) Keyboard for PivotTable on New Sheet 11. (15:30) Format PivotTable with Style 12. (16:05) Slicer to Filter who PivotTable 13. (19:24) Summary - Conclusion PivotTable Basics, Pivot Table Basics, Learn PivotTables, Learn Pivot Tables, Lesson in Pivot Tables, How to create a PivotTable, PivotTables Are Easy! Pivot Tables Are Easy! How to Create a PivotTable Report, Pivot Table Reports
Views: 51617 ExcelIsFun
Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet
 
08:17
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to: 1. Select From Drop Down and Pull Data From Different Sheets using Data Validation List, the INDIRECT Function, and the join symbol ampersand & 2. Learn about syntax for Sheet References 3. INDIRECT Function 4. Data Validation Drop Down List
Views: 505161 ExcelIsFun
Excel Magic Trick #394: IF Function and Conditional Formatting
 
05:04
See how to use the IF function and Conditional Formatting to show the words Warning with a red format when a cell value is too low. Learn about how the Logical_test in the IF function relates to the TRUE / FALSE formula in the Conditional Formatting dialog box.
Views: 177526 ExcelIsFun
Excel Magic Trick 296: Conditional Format Intersection Row & Column
 
06:11
See how to add Conditional Formatting to a row, a column and the intersection of the two with three different colors. See three TRUE FALSE formulas for highlighting the whole row, the whole column and the intersection of the two. Mixed Cell References are the key the three TRUE FASLE formulas
Views: 43915 ExcelIsFun
Excel Magic Trick #5: Data Validation List & VLOOKUP
 
04:16
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTricks01-10Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm Excel! See how to name a cell range, use data validation to add a drop-down list, and how to use the VLOOKUP function to look up values. See how to do this lightening fast with keyboard short cuts! Excel Magic Trick 167p1 IF function formula: 12 Examples! Excel Magic Trick 167p2 IF function formula: 12 Examples!
Views: 289319 ExcelIsFun
Excel Magic Trick 577: Find Duplicates, Then Extract Unique Records
 
06:28
See how to use SUMPRODUCT and the Join Symbol (& Ampersand) to group duplicates and then see how to use Advanced Filter Unique Records to extract unique list from duplicates.
Views: 308234 ExcelIsFun
Excel Basics 3: Count & Add with COUNT, COUNTA, SUM, COUNTIFS, SUMIFS Functions (Intro Excel #3)
 
21:07
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start File: Download File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB03-CountingAddingWithCriteria.xlsx Download Excel Finished File: Download File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB03-CountingAddingWithCriteriaFinished.xlsx Download Full Written Notes: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/Office2016Class15-ExcelBasics03-IntroductionToExcel03.pdf Class web page link: https://people.highline.edu/mgirvin/AllClasses/216v2016/Content/04Excel/Excel2016.htm Topics in this video: 1. (00:01) Introduction 2. (00:41) Different kinds of counting and adding 3. (02:28) Function Drop-down and screen tips to learn which function to use 4. (04:05) COUNT Function 5. (05:04) COUNTA Function 6. (06:16) SUM Function 7. (06:55) COUNTIFS Function 8. (10:14) Screen Click Trick to highlight an entire argument in a function. 9. (11:04) Be Careful of Spaces!!!!! 10. (11:48) SUMIFS Functions 11. (15:05) Use Ctrl Key to check your answers 12. (15:53) More Examples 13. (19:14) Homework you can do to practice 14. (20:06) Summary - Conclusion
Views: 58070 ExcelIsFun
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
 
12:43
Duplicate Lookup or Lookup 1 value, return many for a table Part 1. Return winning bets for week from a football betting database. See a formula that will return multiple items when there are two criteria for the data extraction. See an INDEX and MATCH functions formula that uses SUMPRODUCT, COUNTIFS, IF, ROWS, INDEX, MATCH, SMALL, IF, and ROW functions. This is a data extract with 2 criteria using a formula solution. Return Multiple Items From One Lookup Value. Related Videos: Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row List Winning Bets With Array Formula
Views: 308215 ExcelIsFun
VLOOKUP To Get Complete Record: ROWS, COLUMNS or SEQUENCE Function? EMT 1532
 
08:30
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1532.xlsx In this video see how to use VLOOKUP to retrieve an entire record using three methods: 1) COLUMNS, 2) ROWS, 3) SEQUENCE. 1. (00:05) Introduction 2. (00:35) Do we show record horizontally, or vertically? Discussion of sequential numbers that represent the column numbers needed by the VLOOKUP Function. 3. (01:33) COLUMNS & VLOOKUP 4. (03:51) ROWS & VLOOKUP 5. (04:48) SEQUENCE & VLOOKUP (requires that you have Office 365) 6. (07:57) Summary Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) https://www.youtube.com/watch?v=7jJMDGZpjSk
Views: 23684 ExcelIsFun
Basic Excel Business Analytics #01: Basic Business Analytics using Excel
 
05:04
Basic Excel Business Analytics Download file from “Highline BI 348 Class” section: https://people.highline.edu/mgirvin/excelisfun.htm Introduction to Highline BI 348 Class and Basic Business Analytics using Excel. Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video. Prerequisite classes: Busn 216: https://people.highline.edu/mgirvin/AllClasses/216_2003/216/busn216.htm Busn 218: https://people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm Busn 210: https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj
Views: 76323 ExcelIsFun
Highline Excel Class 18: Subtotals
 
08:36
Download Excel file: http://people.highline.edu/mgirvin/excelisfun.htm See these 5 Subtotal examples: 1. Basics of the Subtotal Feature including Sorting before Subtotaling 2. Adding two Subtotals 3. Adding Subtotals with the SUM function 4. Adding Subtotals with the AVERAGE, COUNT AND MAX FUNCTIONS 5. See how to copy and paste subtotals with the keyboard shortcut for Select Visible Cells Only: Alt + ; This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109
Views: 62867 ExcelIsFun
Excel Magic Trick 1107: VLOOKUP To Different Sheet: Sheet Reference, Defined Name, Table Formula?
 
09:02
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do a VLOOKUP to a different Sheet using: 1. Sheet References 2. Defined Names 3. Table Formula Nomenclature (Structured References)
Views: 721213 ExcelIsFun
Excel Magic Trick 729: VLOOKUP When Multiple Lookup Items In Cell, LOOKUP & SEARCH functions
 
06:28
Download file: http://people.highline.edu/mgirvin/ Learn how to lookup an item when multiple lookup items are in a single cell with the LOOKUP and SEARCH functions.
Views: 34325 ExcelIsFun
Excel Magic Trick #18: Randomly Generate Letters
 
04:35
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the ROW, CHAR and RANDBETWEEN functions to randomly generate letters. See how to randomly create letter sequences.
Views: 31629 ExcelIsFun
Excel Magic Trick 1066: PivotTable: Month Over Month Sales Calculations From Transactional Data Set
 
02:23
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See MOM calculations: 1. (00:18 min) Use PivotTable to calculate Month Over Month Sales Calculations From Transactional Data Set 2. (00:50 min) PivotTable to group by month 3. (00:50 min) PivotTable to add month sales 4. (01:11 min) Show in Tabular Format 5. (01:18 min) Number Formatting, Not Format Cells 6. (01:36 min) PivotTable to Calculate Percentage Change for each month (% Difference From Calculations: Show Values As Calculation).
Views: 20795 ExcelIsFun
Excel Magic Trick 333: #DIV/0! Error IF & ISERROR or IFERROR functions
 
04:46
IF & ISERROR or IFERROR functions for errors? See how to fix errors with the Excel 2007 IFERROR and the Excel 2003 IF & ISERROR functions. See how to fix a divide by zero error. See the SUMPRODUCT function used to calculate monthly costs per contacts. IS functions, IF functions, Logical Test. Formula Errors.
Views: 101052 ExcelIsFun
Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016
 
03:47
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to: create Automatic Calendar Table in Data Model, New in Excel 2016. This ability was not in earlier versions of Excel.
Views: 38366 ExcelIsFun
Excel Magic Trick 167p2 IF function formula: 12 Examples!
 
10:34
Part 2. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how the IF function can be used in formulas. Also see these functions: AND, OR, SUM, AVEARAGE, STDEVP, and VLOOKUP. See how to use the IF function to put: 1 of 2 numbers in a cell or formula. 1 of 2 words in a cell or formula. 1 of 2 text strings in a cell or formula. 1 of 2 formulas in a cell or formula. 1 of 2 functions in a cell or formula. 1 of 2 cell ranges in a cell or formula. How to have more than one IF (Nested IF Ifs). When to switch to the VLOOKUP function. How to build multiple criteria formulas with the IF function. How to use the AND function with the IF function. How to use the OR function with the IF function. Excel Magic Trick 167p1 IF function formula: 12 Examples!
Views: 66361 ExcelIsFun
Excel Magic Trick 715: Take Number with Spaces and Convert to Number
 
03:37
See how to: 1. Take a number with spaces, remove spaces and convert text to number using the SUBSTITUE function and plus zero 2. Take a number with spaces and separate the numbers into individual cells using Text To Column. 3. Keyboard shortcut for Text To Column: Alt + D + E + F or Alt + A + E + F 4. Download file: http://people.highline.edu/mgirvin/
Views: 45798 ExcelIsFun
Excel Table NOT Automatically Copy Formula Down Column? Option Setting to Fix This. Magic Trick 1548
 
01:40
Download Excel Start Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1547-1548.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to change the default setting for copying formulas in Excel Tables down Calculated Columns. Options setting: File, Options, Proofing, Autocorrect Options, AutoFormat As You Type, check the textbox named "Fill formulas in tables to create calculated columns". Download File: https://drive.google.com/open?id=1fifq6PmglyyKBL-rwfEe5iL-Qptu1zXr
Views: 5171 ExcelIsFun
Excel Magic Trick 382: Match Two Lists, Extract Column Data From Each List
 
06:26
Match date in List 1 with List 2, if there is a match take second column from each table and put them in new table. See VLOOKUP and Filter Method and VLOOKUP-SUMPRODUCT-INDEX-MATCH Method.
Views: 245017 ExcelIsFun
Excel Magic Trick 348: Formula for Unit Inventory Total SUMIF function Trick
 
02:59
See a formula that will take invoice information for inventory quantity sold and update Inventory records to show correct amount on hand (in stock).
Views: 236162 ExcelIsFun
Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520
 
09:16
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1518-1523.xlsx In this video learn about how to create a Fully Dynamic Cross Tabulated Report that can update when formula inputs are changed for Row Header Conditions or Column Headers Conditions and will instantly update when new data is added to the source data. This is all done with Formulas, NOT PivotTables, so everything updates instantly when source data or formula inputs change. See the New Array Functions SORT and UNIQUE, and also the older Array Function TRANSPOSE, but most fundamentally, we will see that the INDEX and MATCH Function can lookup columns of data based on formula inputs, and will be the secret ingredient that we use inside the SUMIFS function and also inside the UNIQUE Function. Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) , https://www.youtube.com/watch?v=7jJMDGZpjSk Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm If you want totals for your dynamic Croos Tab report, check out this video: Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports With Totals!!!!! EMT 1528https://www.youtube.com/watch?v=wHeXjYQGvX8
Views: 10061 ExcelIsFun
Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions
 
04:30
Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do a lookup for Pictures in Excel 2007 using: Data Validation Dropdown List INDEX and MATCH Functions Defined Names Excel 2007 Hint (from Joe): Insert Bitmap Image (Insert Ribbon Tab, Text group, Object) instead of a picture and then put formula in Bitmap Image Also see this video: Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?: Excel Magic Trick 637: Lookup A Chart -- Defined Names, INDIRECT function and Formulas In Pictures
Views: 59674 ExcelIsFun
Excel Magic Trick #114: GetPivotData ISERROR and IF function
 
04:42
See how to use the GetPivotTableData function, as well as the ISERROR and IF functions.
Views: 25135 ExcelIsFun
Excel Basics 6: Customize Quick Access Toolbar (QAT) and Show New Ribbon Tabs
 
10:18
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start & Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB06-08QATKeyboardsDefaultAlign.xlsx Download Full Written Notes: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/Office2016Class18-20-ExcelBasics06-08QATKeyboardDefaultAlign.pdf Class web page link: https://people.highline.edu/mgirvin/AllClasses/216v2016/Content/04Excel/Excel2016.htm Topics in this video: 1. (00:04) Introduction 2. (00:40) Customize QAT 3. (07:36) Show New Ribbon Tabs 4. (09:33) Summary - Conclusion
Views: 26116 ExcelIsFun
Excel Magic Trick 302: Randomly Select Names No Repeats
 
03:31
See how to use the INDEX & MRAND functions to randomly select names without repeats. Learn about MoreFun (More Functions) add-in. Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions Excel Magic Trick 306: MCONCAT LOOKUP functions
Views: 122794 ExcelIsFun
Excel Magic Trick 865: Data From Many Columns Into One: Recorded Macro & Paste All Clipboard
 
06:15
Download from: http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn About: 1. Keyboard shortcuts to select columns of data 2. Clipboard to copy multiple columns and collect on Clipboard 3. Paste All button in Clipboard to paste many columns into one column 4. Recorded Marco to copy one column and move to next. 5. Learn about Relative References in Recorded Macro 6. Assign keyboard to Macro 7. Add Developer Ribbon in Excel 2007 and 2010 8. View Macro Code including VBA OFFSET function
Views: 268459 ExcelIsFun
Excel Magic Trick 1065: SUMIFS: Month Over Month Sales Calculations From Transactional Data Set
 
06:51
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See How to calculate Month Over Month Sales Calculations From Transactional Data Set using: 1. (00:40 min) Mouse Drag and Smart Tag to calculate the first of each month 2. (01:06 min) EDATE function to calculate the first of each month 3. (01:55 min) Custom Number Format to show serial Number Dates as Month Abbreviated Names 4. (02:49 min) EOMONTH to calculate the end of the month 5. (03:40 min) SUMIFS to calculate Month Sales 6. (06:00 min) Formula For Percentage Change 7. (06:28 min) Formula For Rate
Views: 34439 ExcelIsFun
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
 
08:24
Duplicate Lookup or Lookup 1 value, return many for a row Part 3. See a formula that looks up one value and returns multiple items. One To Many Relationship. See an array formula that uses the functions: COUNTIF, IF, COLUMNS, INDEX, SMALL and ROW. Related Videos: Excel Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table
Views: 74296 ExcelIsFun
MSPTDA 13: Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report
 
15:24
Download Excel START File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-MSPTDA-RelationshipsRatherThanVLOOKUPStart.xlsx Download Excel FINISHED File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-MSPTDA-RelationshipsRatherThanVLOOKUPFinished.xlsx Download Example of Large File Size using VLOOKUP: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-PivotTablesVLOOKUPFinished.xlsm Download pdf Notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-MSPTDA-RelationshipsRatherThanVLOOKUP.pdf Assigned Homework: Download Excel file and complete the homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-HomeworkStart.xlsx Examples of Finished Homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerPivot/013-HomeworkFinished.xlsx This video introduces Excel Power Pivot and shows how to use the Relationship feature rather than VLOOKUP Function to reduce file size and allow user to pull fields for a PivotTable Report from Multiple Tables. Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor. Topics: 1. (00:15) Introduction 2. (01:43) Look at VLOOKUP solution 3. (02:49) Introduction to What Is Power Pivot? Discuss what a Data Model is. 4. (05:46) Relationship feature 5. (09:16) Create PivotTable Report 6. (11:45) First Look at Implicit Measures 7. (13:02) Enable the Power Pivot Ribbon Tab 8. (13:37) First Look at Power Pivot Window and Data Model 9. (20:30) Summary
Views: 21526 ExcelIsFun
Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number
 
05:49
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1501-1502.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.
Views: 7212 ExcelIsFun