Home
Videos uploaded by user “ExcelIsFun”
Excel 2013 PowerPivot Basics #02: Import Large Non-Normalized Data Set To Reduce File Size
 
05:03
Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how importing large data sets into PowerPivot can dramatically reduce file size. 1. (00:03 min mark) 500,000 row non-Normalized Data Set in Excel 2. (01:31 min mark) Example of Normalized Data 3. (02:02 min mark) Import data from External Excel workbook into PowerPivot Columnar Database 4. (04:10 min mark) File size is much smaller for Non-Normal data Set example and for Normalized Data Set example.
Views: 104865 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: 235820 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: 142238 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: 38900 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: 340451 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: 233277 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: 365939 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: 297239 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: 18782 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: 67383 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: 69982 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: 142940 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: 91590 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: 45802 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: 87802 ExcelIsFun
Excel 2013 Statistical Analysis #03: Download & Install Power Query for Data Transformation Tools
 
03:39
Download files: https://people.highline.edu/mgirvin/AllClasses/210Excel2013/Ch01/Excel2013StatisticsCh01.xlsm See how to Download & Install Power Query in Excel 2013
Views: 49486 ExcelIsFun
Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria
 
10:12
Create a formula to extract part of a table from a larger table. Similar to a query in Access, but with a array formula in Excel that uses the functions: INDIRECT, IF, ROWS, COUNTIF, INDEX, SMALL, and ROW. This formula will extract records that meet one condition (criteria criterion) and list them in order with no extra rows between records. We will also use the INDIRECT function to save on formula creation time. Related Videos: YTLE#84: A Few Data Validation Tricks Excel Magic Trick #180: COUNTIF function formula 21 Examples 04 Excel Series: Names 1-12 Excel Name Trick #7: INDIRECT function, Names and LOOKUP 05 Excel Series: Array Formulas 1-14 Various INDEX Lookup w TRUE/FALSE criteria "Reverse Lookup" Excel Magic Trick 186 Dynamic Formula Extract Data 2 criteria Excel Magic Trick #187: Unique List w Dynamic Formula Excel Magic Trick #81: Advanced Filter AND OR Data Extract
Views: 148755 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: 52166 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: 62881 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: 506038 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: 177815 ExcelIsFun
Highline Excel 2013 Class Video 45: Combining Chart Types and Secondary Axis in Excel 2013
 
03:59
Download workbook: http://people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. Topics in this video: 1. Add Data To Chart with Select Data Button 2. Add Secondary Axis 3. Change Chart Type For Series Values 4. 1 click Combining Chart Types in Excel 2013 5. Add Data with Mouse and Range Finder Color Coding for Cell References
Views: 87518 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: 308719 ExcelIsFun
Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.
 
12:13
Download Excel File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/Intro/001-MSPTDA-IntroToClass.xlsx Download pdf Notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/Intro/001-MSPTDA-IntroToClass.pdf This video introduces the topics that will be covered in this Highline College BI 348 Class: Name of Class: BI 348 – Microsoft Power Tools for Data Analysis: • Power Query • Power Pivot • DAX • Power BI Desktop • Excel For Creating: • Data Models, Reports, Dashboards and Analytics Taught by Mike excelisfun Girvin, Excel MVP 2013-2018 • A class about connecting to multiple source of data, transforming the data into a refreshable & dynamic data model, and building reports and dashboards to provide insightful and actionable information. Prerequisites for this class: • Busn 216: Excel Basics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k • Busn 218: Advanced Excel, https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw • Busn 210: Business Statistics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj What Version of Excel: • Office 365 (updated each month) What Version of Power BI Desktop: • Free Tool we will download (update each month) Over View of Topics for the class: 1. Data Analysis / Business Intelligence terms and concepts that we will learn in this class: • Proper Data Set • Fact Table • Dimension Tables • Relationships • Star Schema • ETL • Measures • Dashboards • SQL • Data Warehousing   2. Learn how to use Excel Power Query: • Import Data from multiple sources • Clean and Transform Data • Create Data Components for Star Schema Data Models • Load Data To Excel, the Data Model and Connection Only • Replace Complicated Excel Solutions with Power Query Solution • Use the Power query User Interface to create Power Query Solutions • Learn about the Case Sensitive, Function-based M Code Language that is behind the scenes in Power Query 3. Learn how to use Excel Power Pivot: • Excel Power Pivot provides: i. Data Model where we can have multiple tables, formulas and relationships (Star Schema) ii. Columnar Database to hold "Big Data" and process quickly over that "Big Data" iii. New Formula Language called DAX: 1. Many More Calculations than in Standard PivotTable 2. Build One Formula that can work in many reports 3. Add Number Formatting to Formulas • Excel Power Pivot to: i. Replace VLOOKUP Formulas and Single Flat PivotTable Data Source with Multiple Tables, Relationships in the Data Model to create more efficient Reports & Dashboards ii. Use Power Pivot Columnar Database to hold millions of rows of data iii. DAX formulas have more Power than Standard PivotTable Calculations 4. Learn about Building Star Schema Data Models: a. Why they are important in Power Pivot and Power BI Desktop b. How to build them using: i. Power Query ii. Power Pivot iii. DAX iv. Power BI Desktop 5. Learn how to author DAX Formulas for Excel’s Power Pivot & Power BI Desktop: a. Calculated Column Formulas for Data Model b. Measure Formulas for PivotTables c. DAX Functions like SUMX, CALCULATE, RELATED, and Much More… d. Lean why we must create Explicit rather than Implicit formulas e. Learn how Row Context works in formulas f. Learn how Filter Context works in formulas g. Learn about Scalar & Table Functions h. Use DAX Studio to visualize and analyze DAX Formulas 6. Learn how to use Power BI Desktop: a. Power Query to import, clean, transform and create Star Schema Data Models b. Create Relationships c. Create DAX Formulas d. Build Interactive Visualizations e. Build Dashboards   7. Learn how to use Excel: • Spreadsheet Formulas & Functions • Standard PivotTables • Power Query • Power Pivot • Build Data Model PivotTables and the resultant Reports, Dashboards and Analytics 8. Building Refreshable, Insightful Dashboards a. Build Excel Dashboards b. Build Power BI Dashboards 9. Case Studies to practice using Power Pivot & Power BI Desktop for Reporting, Building Dashboards and Building Business Analytics Solutions The Power Query logo used in this video is copyright of and used with the express permission of https://powerquery.training Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!
Views: 41142 ExcelIsFun
Excel Magic Trick 1311: Return Multiple Items from One Lookup Value & Show Total for Invoice Items
 
07:11
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See an Array Formula that can extract (from a proper data set) multiple invoice items for one lookup value & show total. See the functions: COUNTIFS, ROW, AGGREGATE, ROWS, INDEX, IF and SUMIFS. See a number of Array Operations in the formula. Learn how AGGREGATE function can handle array operations without using Ctrl + Shift + Enter
Views: 51451 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: 289463 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: 76435 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: 308517 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: 23819 ExcelIsFun
Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks
 
09:35
Download Files: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Power Query to easily import multiple sheets of data from multiple Excel Workbooks and easily have Power Query Add a new column that is populated with the Sales Rep Name from the Sheet Tab: 1. (00:10 Minute Mark) Problem Setup 2. (01:32 Minute Mark) Import From Folder 3. (01:55 Minute Mark) Remove Other Columns 4. (02:05 Minute Mark) Add Custom Column with =Excel.Workbook([Content]) to get Excel Workbook Content 5. (02:38 Minute Mark) Remove Column 6. (03:02 Minute Mark) Filter by “.Kind” to Filter out Tables and Defined Names 7. (03:58 Minute Mark) Use Filter Feature to Filter out Sheet Tab Names that do not contain a proper Sales Rep Name. 8. (05:20 Minute Mark) Add Custom Column to Promote Headers with =Table.PromoteHeaders(“*.Data”) 9. (06:16 Minute Mark) Expand Tables to create on large table. 10. (06:54 Minute Mark) Add Data Types. 11. (07:33 Minute Mark) Save and Load to Table in Excel Worksheet. 12. (07:57 Minute Mark) Set Query Property to Update When File Is Opened. 13. (08:27 Minute Mark) Add New Sheets to Source Files and see that Power Query Updates Automatically. Extract Sales Rep Name from Sheet Tabs. Include Sheet Tab Names as New Column. Promote Headers. Table Promote Headers. Filter Does Not Contain. Set Query Property. Update When File Is Opened. Remove Other Columns. Miguel Escobar and Ken Puls are doing online workshops on Power Query www.powerquery.training . This training is not by excelisfun.
Views: 57793 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: 58664 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: 723102 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: 10153 ExcelIsFun
Excel Magic Trick 1336: Power Query: Import Big Data Text Files: Connection Only or Data Model?
 
11:35
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Import 10 Text Files and Append (combine) then into a single Proper Data Set before making a PivotTable Report. Compare and Contrast whether we should use Connection Only or Data Model to store the data. 1. (00:18) Introduction & Look at Text Files that Contain 7 Million Transactional Records 2. (01:43) Power Query (Get & Transform) Import From Folder to append (combine) 10 Text Files that contain 7 Millions transactional records. 3. (05:07) Load Data as Connection Only and Make PivotTable 4. (08:17) Load Data into Data Model and Make PivotTable. 5. (10:46) Summary
Views: 35583 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: 101132 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: 66384 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: 5238 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: 236913 ExcelIsFun
Excel Magic Trick 1551: Lookup Discounted Price? 2 VLOOKUPs & ROUND
 
07:46
Download Excel Start Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1551.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn how to to lookup a discounted price based on Product Name and Units Discount. See how to use 2 VLOOKUP Functions & the ROUND Function in a single formula.
Views: 12021 ExcelIsFun
Excel Magic Trick 1269: Excel 2016: Without Power Pivot: DAX Measures, Relationships & Data Model
 
10:09
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Using Excel 2016, learn how to access the Data Model, build Relationships between tables and create DAX Measures (Formulas in Pivot Tables) without having PowerPivot Installed and without having to buy the upper level sku for Excel: Microsoft: “All SKUs (on Win32) should allow analysis. if you need a dedicated, professional, tool for "self service BI", PowerPivot is available in premium SKUs of Excel.” Topics: 1. (00:11) Problem introductions: Learn how to build a report from three tables without VLOOKUP or helper column formulas by accessing the Relationship feature on the Data Tab and utilizing the Data Model and DAX Measures (Formulas in Pivot Tables) indirectly through the Create PivotTable dialog box. This process will not allow us to open the “Manage Data Model window” to look at our Data Model because we do not have PowerPivot installed. For advanced Analytics and advanced features and easier to use user-interface, it is bet to install and use PowerPivot. 2. (01:08) Convert Proper Data Sets to Excel Tables using Ctrl + T (Excel Table Feature) 3. (01:38) Add first Table to the Data Model using the Create PivotTable dialog box and the “Add this to the Data Model” check box. The Data Model contains a Columnar Database that can hold millions of records. 4. (02:36) Look at PivotTable Field List and learn about the “ALL” tab which allows use to see all Excel Tables in the Excel Workbook and the fact that Excel Tables that have been added to the Data Model have a Dark Line above the table name in the PivotTable Field List to indicate that it has already been added to the Data Model. 5. (02:52) Create Relationships between the Tables using the Manage Relationships dialog box from the Data Tools group in the Data Ribbon Tab. When you create Relationships between tables, the Tables are automatically added to the Data Model. 6. (05:19) Create DAX Measure (DAX Formulas, DAX Measures, Calculated Columns, PivotTable Formulas are all synonyms) using the PivotTable Field List. From the PivotTable Field List, right-click the table and point to “Add Measure”. This required that you are familiar with the DAX Formula language. See the DAX Functions: SUMX function, RELATED function, and ROUND function. Learn how to create a DAX Formula that will calculate Net Revenue given Units, a Price that is “looked up”, and a discount. See that Number Formatting can be added directly to the DAX formula and that it will follow the Measure (formula) around into whichever PivotTable you drag it into. 7. (08:57) Drag new DAX Measure (Calculated Field) from the PivotTable Field List to the PivotTable Values area. 8. (09:35) Summary. To learn about how PowerPivot, Relationships and DAX Measures (Calculated Fields or Formulas in PivotTables): Playlist of videos for DAX and PowerPivot: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nGCx21PRFbsJpUIH06LKs- Two Intro Videos for Excel 2013: Basic Excel Business Analytics #40: Introduction to PowerPivot & Data Modeling https://www.youtube.com/watch?v=cqJbVInVyX4 Basic Excel Business Analytics #42: Comprehensive PowerPivot, Data Model, DAX & Reporting Example https://www.youtube.com/watch?v=NHk-g_Echik Intro Video for Excel 2016 PowerPivot: Basic Excel Business Analytics #41: Excel 2016: Introduction to PowerPivot & Data Model https://www.youtube.com/watch?v=xVmbMqRGBA8
Views: 22194 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: 245583 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: 59720 ExcelIsFun
Basic Excel Business Analytics #35: Power Query to Get Data From Web Site & Import into Excel.
 
03:01
Download file from “Highline BI 348 Class” section: https://people.highline.edu/mgirvin/excelisfun.htm Learn about How to use Power Query to Get Data From the Web and Import into 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. Power Query Tab, Get External Data, From Web. Excel 2016: Data Tab, Get and Transform group.
Views: 13677 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: 123080 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: 21723 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: 26420 ExcelIsFun
Excel Magic Trick # 267: Percentage Change Formula & Chart
 
06:41
Download file in "Excel Magic Trick" section: https://people.highline.edu/mgirvin/excelisfun.htm Learn about the universal formula for Percentage Change: (End Value)/Beg Value) - 1 = Percentage Change. The see how to create and format a chart with two data series and two chart types in one chart: Line Chart and Column Chart.
Views: 564068 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: 7240 ExcelIsFun
Excel Magic Trick 1024: SUMIF & SUMIFS function & Mixed Cell References & Dynamic Table Range
 
10:26
Download workbook example from video: http://people.highline.edu/mgirvin/excelisfun.htm Add with one or more conditions: 1. Learn how to add with one or more conditions using the SUMIF and SUMIFS functions. 2. Excel table feature to create dynamic ranges, ranges the expand as new records are added. 3. Learn how to use the Excel Table feature with or without the Table Formula Nomenclature. 4. See how to use regular cell references rather than Table Formula Nomenclature so that you can copy the formula to the side and not have the Table Formula Nomenclature move to different columns. 5. Learn how to create a cross tabulated table to add with two conditions 6. Learn how to use Mixed Cell References to help speed up formula creation time.
Views: 128337 ExcelIsFun
Excel Magic Trick 1268: Excel 2016 Auto Detect Relationships: 2 Tables In PivotTable Field List
 
06:27
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to build a PivotTable report from two tables without using VLOOKUP. Learn that in Excel 2016 when you add two tables to the PivotTable Field list and drag and drop fields, the Auto Detect Feature can build your Relationship for you. See how to create two Excel Tables, Add to Data Model using Create PivotTable Field list, drag and drop field and create Relationship between the two Products Field using the Auto Detect Relationship feature.
Views: 19634 ExcelIsFun