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: 69764 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: 222174 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: 315359 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: 119585 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: 229436 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: 143300 ExcelIsFun
Excel Magic Trick 1094: Wildcards for Sheet References and INDIRECT function
 
03:39
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to lookup all A8 cell references from across many sheets using a wildcards, incomplete sheet names and the INDIRECT function. Amazing trick from Abhay Gadiya from YouTube.
Views: 21514 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: 61990 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: 62152 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: 57061 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: 42869 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: 126315 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: 293223 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: 114531 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: 87066 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: 347680 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: 81517 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: 23982 ExcelIsFun
Excel Magic Trick 684: VLOOKUP 1 item & Add Values from Multiple Rows/Columns - HLOOKUP
 
03:56
See how to do 1 Condition Lookup Adding for both the VLOOKUP and HLOOKUP functions. See how to use an array constant in the row/column num argument for the HLOOKUP and VLOOKUP functions. Download file: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm
Views: 101065 ExcelIsFun
Excel Magic Trick # 3: AVERAGE & Go To Blanks
 
03:30
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! Learn how to use the Ctrl + Enter keyboard Trick to enter formulas or data into many cells simultaneously. Also, learn how the AVERAGE function ignores Blanks, but not zeroes! And finally, learn about the amazing GoTo feature to select all Blanks in a range!
Views: 89923 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: 83057 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: 59048 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: 294971 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: 23936 ExcelIsFun
Excel Array Formula Series #2: TRANSPOSE function
 
02:13
See how to use the Array function TRANSPOSE to transpose a table, switching rows and columns to columns and rows. In this series see how to create array formulas in Excel. Array formulas can take complex series of formula calculations and reduce them down to a single formula that sits in just one cell! You will see how to create array formulas and see how to use Array Functions.
Views: 106169 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: 18125 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: 16806 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: 281558 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: 164458 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: 467431 ExcelIsFun
Excel Lookup Series #1: VLOOKUP Function 1st Example
 
07:58
Lookup Series 1-15 Download Excel Files: Start File https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelLookupFunctionsSeries1-15.xls Finished File https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelLookupFunctionsSeries1-15%20Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm 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: 513760 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: 45971 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: 201790 ExcelIsFun
Excel Statistics 08: Install Excel 2007 Data Analysis Add-in
 
02:44
See how to Install Data Analysis Add-in using Excel Options and the Add-in Feature. This is a beginning to end video series for the Business & Economics Statistics/Excel class, Busn 210 at Highline Community College taught by Michael Gel ExcelIsFun Girvin
Views: 222826 ExcelIsFun
Excel Magic Trick 1296: COUNTIFS with OR Criteria & SUMPRODUCT to Score Myers-Briggs Test
 
04:04
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to Score Myers-Briggs Test from a Rectangular (Improper Lookup Table) with COUNTIFS Function with OR Criteria & SUMPRODUCT function.
Views: 10616 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: 48136 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: 33938 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: 40216 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: 640999 ExcelIsFun
Highline Excel 2013 Class Video 38: Excel Text To Columns To Split or Convert Data 7 Examples
 
10:14
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. Split Text with Delimiter = Space. Split First and Last Name and Skip a column (00:38 min) Text To Columns keyboard: Alt, A, E or Alt, D, E (01:15 min) 2. Split Text with 1st Time: Delimiter = Comma, 2nd Time: Delimiter = Space. Clean Address data (02:31 min) TRIM Function to removce spaces (03:55 min) Paste Special Values using Right-Click-Drag Method (04:09 min) 3. Split Text with Delimiter = Dash. Get code from middle of text string (05:12 min) 4. Split Text with Fixed Width to get hour and minutes from integer number (06:02 min) 5. Split Text with Fixed Width to get year, month and day from integer number (06:45 min) 6. Convert integer number that represents Date to Serial Number Date with Text To Columns, Step 3 = YMD Date Type (07:27 min) 7. Convert Text Dates to Serial Number Dates with Text To Columns (Alt, D, E, F) (08:22 min) 8. Convert Numbers stored as text to numbers with Error Checker (09:41 min)
Views: 21754 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: 109835 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: 12992 ExcelIsFun
Excel Power Query #09: Merge Multiple Worksheets in Workbook To New Table using Append Feature
 
05:06
Download file: http://people.highline.edu/mgirvin/excelisfun.htm See how to Merge Multiple Worksheets in Workbook To New Table using Append Feature: 1. (00:08 minute mark) Problem Setup 2. (00:39 minute mark) Table Feature for getting Tables into Power Query 3. (00:53 minute mark) Use “From Table” feature in Power Query to get Tables into Power Query 4. (00:59 minute mark) Keyboard Differences in Power Query between Excel 2010 and Excel 2013 5. (02:13 minute mark) Discussion of Keyboard Shortcuts as a “Dance” 6. (02:33 minute mark) Merge Multiple Sheets in Workbook To New Table using “Append Feature” 7. (03:51 minute mark) Bug in Power Query November 2014 Update: Number Format not being picked up when Query is refreshed. Miguel Escobar and Ken Puls are doing online workshops on Power Query www.powerquery.training . This training is not by excelisfun.
Views: 60718 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: 97216 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: 21587 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: 93230 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: 14898 ExcelIsFun
excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks and more...
 
05:44
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: 454556 ExcelIsFun
Excel Magic Trick 743: Conditional Formatting To Match Items In List On A Different Sheet
 
04:53
Download file: http://people.highline.edu/mgirvin/ See how to add Conditional Formatting To Match Items In List On A Different Sheet using a true false formula (logical formula) and the MATCH function & Defined Names.
Views: 31021 ExcelIsFun
Excel Lookup Series #13: INDEX & MATCH functions Example 3!
 
10:48
Download Excel Files: Start File https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelLookupFunctionsSeries1-15.xls Finished File https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelLookupFunctionsSeries1-15%20Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the INDEX & 3 MATCH functions in one formula to look up a value from 1 of 3 different tables! Also see how adding Names & a Data Validation Drop-down List can make the INDEX & MATCH functions more accurate. Also see how to use Custom Number Formatting for a formula input number. See how to correct a formula using Formula Evaluator. See how the Amazing INDEX function can lookup values from different tables! See how we can tell the INDEX function a Row #, a Column# & a Table# and it will retrieve a value from 1 of three different tables. See how to make an invoice shipping charge formula when you have a complicated lookup requirement. 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.
Views: 84374 ExcelIsFun

The uk constitution is no longer fit for purpose essay examples
Disadvantages of having a roommate essay
Andhvishwas hindi essay on mahatma
Beschaffenheitsvereinbarung beispiel essay
Benjamin franklin the way to wealth essay