Home
Videos uploaded by user “ExcelIsFun”
Excel Magic Trick #244: Advanced Filter Extract To New Sheet (Word Criteria)
 
02:31
See how to use Advanced Filter to extract records from a Database (Table or List) based on 1 criterion (criteria) and place reesults on a new sheet worksheet.
Views: 66943 ExcelIsFun
Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Dependent DV
 
06:39
Downlod file: http://people.highline.edu/mgirvin/ExcelIsFun.htm 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: 218527 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: 309654 ExcelIsFun
Excel Name Trick #7: INDIRECT function, Names and LOOKUP
 
05:20
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: 227944 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: 113626 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: 41732 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: 85617 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: 121805 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: 60674 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: 113752 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: 291930 ExcelIsFun
Excel Magic Trick 1142: Get Every Other Row with Formula: INDEX and ROWS*2
 
02:52
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to create a formula to Get Every Other Row with Formula using INDEX function and ROWS function. See how to increment the numbers 2, 4, 6, 8… or create sequential numbers like 2, 4, 6…
Views: 78083 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: 339896 ExcelIsFun
Excel Magic Trick #217: IF Function Text Logical Test
 
02:50
See how to create a simple If function formula that uses a logical test that involves Text (instead of a number).
Views: 179168 ExcelIsFun
Excel Magic Trick 1282: Lookup And Return Multiple Items to a Single Cell: TEXTJOIN Function
 
05:12
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the amazing new Excel 2016 function TEXTJOIN to Find Multiple Lookup Values In First Colum AND return Multiple Items From Next Column To a Single Cell. This trick is thanks to DRS at YouTube. Purchase "Office 2016 for Office 365" https://products.office.com/en-us/buy/office Go to Office Insider site to download "Office Insider Version" https://products.office.com/en-US/office-insider Office Insider Version: 16.0.6568.1011 or 16.0.6568.2025 Return Multiple Items to Single Cell with a delimiter DRS at YouTube Lookup Multiple Items Return Multiple Lookup Items to Single Cell DRS at YouTube http://www.mrexcel.com/forum/excel-questions/647235-vlookup-return-multiple-values-same-cell-2.html Excel Magic Trick 1281: TEXTJOIN & CONCAT New Excel 2016 functions (14 Examples of Joining Items) https://www.youtube.com/watch?v=qALXjobSAMA Excel Magic Trick 1280: How To Get Latest Excel Updates for Insider Program: Office 365 https://www.youtube.com/watch?v=9yu8nxy6-Pw
Views: 17246 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: 160014 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: 20007 ExcelIsFun
Excel 2013 Statistical Analysis #03: Download & Install Power Query for Data Transformation Tools
 
03:39
Download files: http://people.highline.edu/mgirvin/excelisfun.htm See how to Download & Install Power Query in Excel 2013
Views: 47741 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: 56244 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: 291258 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: 79588 ExcelIsFun
Excel Lookup Series #1: VLOOKUP Function 1st Example
 
07:58
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm . It is in the very bottom section "Other Section", and the fie name is: WorkbookExcelLookupFunctionsSeriesStartFile" See how to use the VLOOKUP function to deliver a value to cell: 1) Find approximate value from column 2 of lookup table. 2) Find exact value from column 2 of lookup table. Use Named Range, Data Validation and VLOOKUP to make your lookup more accurate. In This Series learn 15 amazing ways to look things up in Excel. We will look at the functions VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, CHOOSE, and the non-function lookup formula using the intersector operator. We will look at simple lookups all the way to complicated, yet efficient methods to look things up in Excel. This is a logical (beginning to end) story about most of the lookup situations you may encounter in Excel. Excel Magic Trick 167p2 IF function formula: 12 Examples! Excel Magic Trick 167p1 IF function formula: 12 Examples!
Views: 512682 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: 17422 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: 455931 ExcelIsFun
Excel Magic Trick #218: Pivot Table Macro w Dynamic Range #1
 
04:28
See how to use an Excel 2007 Table to create a Dynamic Range so a Macro to Create a Pivot Table will work even when new records are added.
Views: 45691 ExcelIsFun
Excel Magic Trick #17: Randomly Generate Whole Numbers
 
04:12
Download Excel workbook: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the RANDBETWEEN function to randomly generate whole numbers. See how to randomly assign numbers to employees.
Views: 36912 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: 191646 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: 251966 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: 617104 ExcelIsFun
Excel 2013 Statistical Analysis #00: Excel Workbooks For This Class
 
02:08
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Learn about the special features of the Excel workbooks we will use for this class.
Views: 55239 ExcelIsFun
Excel Magic Trick 1436 Excel 2016: Combine 2 BIG Tables into 1 for PivotTable Report, No Power Pivot
 
07:05
Download File: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1436Start.xlsx Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1436Finished.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm See how to use Excel 2016 to take a 900,000 row table and a 1,000,000 roe table and combine them into one in order to make a PivotTable Report. See how to use the Power Query (Get and Transform) Append feature to combine the two tables and the load it to the Data Model in order to create a PivotTable report.
Views: 12974 ExcelIsFun
Excel Magic Trick #12: Date Math!
 
03:45
Download Excel workbook: http://people.highline.edu/mgirvin/excelisfun.htm Excel! Learn how to calculate the time between 2 dates like invoices past due. Learn how to calculate a loan due date or how many days you have been alive! Also, learn the lightening fast keyboard shortcuts for removing Number format.
Views: 99696 ExcelIsFun
Excel Magic Trick 1337: No X-Y Scatter Chart From PivotTable!?!? Use Power Query Instead!!!
 
07:59
Download File: http://people.highline.edu/mgirvin/excelisfun.htm If you try to make X-Y Scatter Chart from PivotTable is will NOT work!!!!! See how to use Power Query to create the same output as a PivotTable in order to make an X-Y Scatter Chart: 1. (00:15) Introduction 2. (00:47) PivotTable to Adding Units by Day. But when we try to create an X Y Scatter Chart from PivotTable, we will get the error “You can’t create this chart type with data inside a PivotTable. Please select a different chart type, or copy the data outside the PivotTable”. 3. (02:35) Use Power Query to build PivotTable output, and then the X Y Scatter Chart will work perfectly!! 4. (05:30) Build X-Y Scatter Chart with Proper Labels. 5. (07:14) Summary
Views: 19363 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: 29416 ExcelIsFun
Excel Finance Class 63: Stock Valuation with Dividend Growth Model
 
04:52
Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn about valuing a stock that has predictable dividends using the Dividend Growth Model.
Views: 60851 ExcelIsFun
Excel Magic Trick 460: Transpose Range with INDEX, COLUMNS and ROWS functions
 
03:11
See how to transpose a range (switch rows and columns) using INDEX, COLUMNS and ROWS function. If you dont want to use TRANSPOSE function, this is a good substitute.
Views: 40959 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: 96084 ExcelIsFun
Excel 2010 Statistics 02: Excel 2010 Data Analysis Add-in
 
01:38
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to add the Data Analysis Add-in in Excel 2010. 2. Add-in for Statistics This is for the Highline Community College Busn 210 Statistical Analysis for Business and Economics taught by Michael Girvin.
Views: 104901 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: 212579 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: 492881 ExcelIsFun
Excel Magic Trick 1315: Create A Unique Sorted List with Power Query
 
04:16
Download File: http://people.highline.edu/mgirvin/excelisfun.htm See how to create a unique sorted list with Power Query.
Views: 7631 ExcelIsFun
Excel Magic Trick 1335: Power Query We Couldn’t Refresh The Connection Data Source Not Found Error
 
01:43
Download File: http://people.highline.edu/mgirvin/excelisfun.htm In Power Query see how to use deal with the Refresh Error Message: We couldn’t refresh the connection. Here is the error message we got: [DataSource.NotFound] File or Folder: We couldn’t find the folder.
Views: 10594 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: 39014 ExcelIsFun
Excel 2013 PowerPivot Basics #05: Import External Excel Tables, Build Data Model with SUMX function
 
16:53
Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to Import 3 Excel Tables from an External Excel Wordbook File, Build Relationships, Build Calculated Column, Build Calculated Field (Measure) using SUMX function, and compared Calculated Columns and calculated Fields 1. (00:21 min mark) See how to Import 3 Excel Tables from an External Excel Wordbook File into Data Model 2. (05:39 min mark) Build Relationships 3. (07:06 min mark) Format Tables in data Model 4. (07:37 min mark) Build Calculated Column for Net Revenue that uses ROUND and RELATED functions. Calculated Columns use “Row Context” 5. (09:27 min mark) Build Calculated Field (Measure) using SUMX function to allow “Row Context” to occur in a Calculated Field. SUMX is an Iterator function that will allow a formula to iterate through a table and simulate “Row Context” in a Calculated Field (Measure). Even though a Calcualted Field normally only sees Filter Context, you can force the Calculated Field (Measure) to perform Row Context with the Iterative Function SUMX. 6. (12:32 min mark) Compare Calculated Columns and Calculated Fields (Measures) 7. (14:22 min mark) Build PivotTable based on data Model.
Views: 91216 ExcelIsFun
Excel Magic Trick 1108: Compare 2 Lists and Extract Records: Filter Method
 
05:12
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to Compare Two Lists and Extract Records: 1. ISNA and MATCH function with Filter to extract items in list 2 that are NOT in List 1. 2. ISNUMBER and MATCH function with Filter to extract items that are in both lists.
Views: 16484 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: 69060 ExcelIsFun
excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks.
 
06:08
See how to use the excelisfun YouTube channel: 1) Search For Videos 2) Download Workbooks 3) Find Playlists With The Videos You Want! Excel How To Videos Excel Videos Learn How To Use Excel Videos Excel Tutorial Videos Excel Formula Videos Excel Functions Videos Excel Pivot Tables PivotTables Videos Excel VLOOKUP Videos Videos About Excel
Views: 79757 ExcelIsFun
Excel Finance Class 79: Investment Criteria: NPV, IRR, Payback, AAR, Profitability Index
 
08:50
Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm Investment Criteria: NPV, IRR, Payback, AAR, Profitability Index Comprehensive Example. See the Calculations for all, including NPV and IRR functions.
Views: 120300 ExcelIsFun
Excel Basics #18: Data Analysis Sort
 
05:31
The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #18 Video topics: 1)How to Sort a column of data 2)Sort button 3)Sort dialog box 4)Sort Left to Right 5)Excel List Table Database format is required for sorting 6)Sort A to Z 7)Sort Z to A 8)Sort Ascending 9)Sort Descending
Views: 145523 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: 111914 ExcelIsFun