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.
Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to import three tables from three different source files (Access database, Excel File, Text File) and merge them into one table using Power Query. Merge tables into the PowerPivot Data Model or an Excel Table 1. (01:38 minute mark) Import Access File. Remove column, Format Data Type as Date 2. (02:33 minute mark) Import Excel File 3. (03:05 minute mark) Import Text File 4. (03:28 minute mark) Append Table: merge Three Tables 5. (04:12 minute mark) Load to Data Model or Excel Table Miguel Escobar and Ken Puls are doing online workshops on Power Query www.powerquery.training . This training is not by excelisfun.
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
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.
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.
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.
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).
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
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.
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.