Home
Videos uploaded by user “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: 230937 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: 329419 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: 295753 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: 133582 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: 231970 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: 22124 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: 89995 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: 146648 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: 136162 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: 360183 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: 115633 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: 65194 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: 91322 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: 85451 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: 65196 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: 32908 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: 107164 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: 172758 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: 44679 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: 48961 ExcelIsFun
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
 
08:24
Duplicate Lookup or Lookup 1 value, return many for a row Part 3. See a formula that looks up one value and returns multiple items. One To Many Relationship. See an array formula that uses the functions: COUNTIF, IF, COLUMNS, INDEX, SMALL and ROW. Related Videos: Excel Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table
Views: 73751 ExcelIsFun
Excel 2010 Magic Trick 813: Compare Two Lists Extract Items In List 2 That Are Not In List 1
 
06:18
Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm 1. See how to compare two lists and extract items from List 2 that are not in List 1 2. Use a Helper Column with a formula that uses the formulas SUM, ISNA, and MATCH functions 3. Use a lookup formula for the extracted list that uses VLOOKUP and IFERROR functions. 4. Compare Two Lists 5. Compare Two Tables
Views: 41510 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: 91835 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: 44562 ExcelIsFun
Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)
 
07:04
Two formulas: VLOOKUP function with wild card criteria for the lookup_value and LOOKUP / SEARCH functions formula with 2^15 number. See what happens when there are duplicates: the two formulas behave differently (VLOOKUP returns first and LOOKUP SEARCH returns last). From Mr Excel Message Board Fuzzy Match
Views: 160003 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: 62628 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: 42727 ExcelIsFun
Excel Array Formula Series #2: TRANSPOSE function
 
02:13
Download Excel Files: Start and Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelArrayFormulaSeries1-14.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 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: 107506 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: 303178 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: 489739 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: 286806 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: 21447 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: 28013 ExcelIsFun
Excel Magic Trick 1258: Add Spaces Between Digits: Custom Number Format & TEXT Function
 
02:19
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Learn how to use Custom Number Format in Excel to display numbers with spaces and to convert numbers to text with the TEXT Function. 1. Custom Number Format in Excel to display numbers with spaces 2. Convert numbers to text with the TEXT Function and Custom Number Format
Views: 66201 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: 299533 ExcelIsFun
Excel Array Formula Series #6: Statistics Bayes' Theorem
 
03:51
Download Excel Files: Start and Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelArrayFormulaSeries1-14.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 Create an array formula for Bayes' Theorem in Statistics. See the Root (Prior) probability, Conditional Probability, Joint Probability and Posterior Probability. 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: 26972 ExcelIsFun
Excel Magic Trick #221: LEN function Counts Spaces
 
03:24
See how to use the LEN function to count charters including spaces. Then see how to use the LEN, SUBSTITUTE, and TRIM function to count characters but not count unwanted spaces.
Views: 15892 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: 690692 ExcelIsFun
Ctrl + Shift + Enter: Excel Array Formulas #03: Comparative Array Operations, & Alternatives
 
14:50
Download files here: http://people.highline.edu/mgirvin/excelisfun.htm EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!! This video covers: 1. (00:28 min) Array Formula that makes "comparative" operation on an array. 2. (00:32 min) Formula goal: Calculate smallest sample time for each city in sample. 3. (01:57 min) Arrays in formula: =MIN(IF($B$7:$B$12=E7,$C$7:$C$12)). 4. (04:42min) Why it is convenient that the IF function puts FALSE into the array. 5. (08:20 min) Could we use the DMIN function? 6. (09:16 min) Could we use a PivotTable? 7. (09:37 min) Which is better: MIN/IF Array Formula, DMIN or PivotTable? 8. (09:50 min) Calculating notes about Array Formula =MIN(IF($B$7:$B$12=E7,$C$7:$C$12)). 9. (09:54 min)Comparative Operators. 10. (10:12 min) Standard Deviation IF: PivotTable, DSTDEV, or STDEV & IF? THIS VIDEO SERIES AT YOUTUBE IS THE SAME AS THE DVD FROM EXCELISFUN. THESE VIDEOS ARE BEING GIVEN AWAY FOR FREE AT YOUTUBE. SUPPORT THE CAUSE BY GOING TO AMAZON AND BUYING THE BOOK. EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!! Buy Ctrl + Shift + Enter: Mastering Excel Array Formulas DVD at A: http://www.amazon.com/Ctrl-shift-enter-Mastering-Formulas/dp/1615470085 Ctrl + Shift + Enter: Mastering Excel Array Formulas Book: http://www.amazon.com/Ctrl-Shift-Enter-Efficient-Calculating/dp/1615470077 Ctrl+Shift+Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formulas for Data Analysis and Calculating Problems Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots. Full playlist of videos at YouTube: http://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci
Views: 28586 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: 43869 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: 118305 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: 30242 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: 74412 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: 262564 ExcelIsFun
Excel Magic Trick 977: Lookup First Non Empty Cell In A Range, Lookup Last Number In A Range
 
06:50
Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm Two Formulas for Lookup First Non Empty Cell: 1. {=INDEX(E2:L2,MATCH(TRUE,E2:L2 not "",0))} 2. {=INDEX(E2:L2,MATCH(FALSE,ISBLANK(E2:L2),0))} Formula to lookup last number: 3. =LOOKUP(9.99E+307,E2:L2) Busn 214 Joyce April Method
Views: 23295 ExcelIsFun
Excel Magic Trick 729: VLOOKUP When Multiple Lookup Items In Cell, LOOKUP & SEARCH functions
 
06:29
Download file: http://people.highline.edu/mgirvin/ Learn how to lookup an item when multiple lookup items are in a single cell with the LOOKUP and SEARCH functions.
Views: 33863 ExcelIsFun
Excel Magic Trick #14: Amazing Find & Go To Trick!
 
03:12
Download Excel Files: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22.xls Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MikeGelGirvinYouTubeExcelMagicTrick11-22Finished.xls Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to use the Find command to go to all cells with a certain formatting, or even a certain function! Then it is easy to make universal changes after you have all the cells with a certain condition highlighted! This trick can speed up work and help you find hidden items that would be hard to find otherwise! Find, Go To, GoTo, Replace.
Views: 53255 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: 99580 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: 223278 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: 235429 ExcelIsFun

Animated love hearts free download
Senators of illinois
Umatilla high school florida
Reginellis baton rouge
Doebels clyde ohio