MrExcel Excel Videos

How to get a unique combination of all values in Excel columns B & D? Normally, use Advanced Filter or, Remove Duplicates. But today, how to use UNIQUE and CHOOSE to solve the problem. Table of Contents: (0:00) Intro to Getting a UNIQUE from two adjacent columns in Excel (0:35) Advanced Filter for Unique Combinations (1:19) Remove Duplicates for Unique Combinations (2:36) Download the Dynamic Arrays book (2:45) The concept of Lifting in Excel (3:10) How CHOOSE normally works (3:41) Lifting CHOOSE for two columns (4:25) Wrapping in the UNIQUE function (4:47) Testing Our Knowledge: Returning 3 columns (5:43) Array constants and Broadcasting (5:53) Sorting Results using SORT and pairwise lifting (6:38) Testing our Knowledge: Sorting by...

John Michaloudis and Bill Jelen discuss new features in Excel on John's podcast. Thanks to John for allowing me to cross-post this here. All the links John Mentions in the Show Notes are here: https://www.myexcelonline.com/podcast/022-new-microsoft-office-365-excel-features-with-mr-excel-bill-jelen/ It is a long episode. Use this Table of Contents to jump to any point in the video: (0:00) Introduction & Theme Song (2:10) Difference between Office 365 & Excel 2019 (6:44) Insider, Monthly, Semi-Annual Office 365 (7:40) Insider broke Total Visible (10:25) Intro to New Features (10:45) Geography and Stock Data Types (15:40) Stock Data Types (19:56) Artificial Intelligence with Ideas (28:24) Dynamic Arrays (38:10) Why SEQUENCE is not as...

Office 365 customers will soon be seeing a new Data Types gallery in the Data tab in Excel. Convert stock symbols to a Stock Data Type. You can easily return price, CEO, Volume, and other statistics.

Mark a cell as a geography data type in Excel and you can use formulas to return fields such as: State, County, Population, Latitude, Longitude, or Mayor. This feature is new in Office 365. The article for today's video is here: Geography Data Types in Excel Specific Sections: (0:00) Intro to Geography Data Types in Excel (0:22) Picture Transparency in Excel (0:55) List of Twitter handles of who helped (1:54) Declaring data as geography (2:05) Searching for unknown data (2:28) Ambiguous city names - Sutter Illinois (2:50) Displaying a Data Card with more info (3:11) New =A2.Population formula nomenclature (3:45) Get County or mayor for each city (4:15) Using IFERROR around the formula (4:30) Format as Table for extra fields (4:40)...

Can you set up Excel Data Validation so that the choices in a second drop-down menu are dependent on the choice in the first menu? For example, if you choose Bagels, the answers might be Raisin, Tomato, or Everything. If you choose Pancakes, then the answers would be Buckwheat, Blueberry, or Pumpkin. This has been solved twice before on my channel using Names or using OFFSET. Today, with the introduction of new Dynamic Array formulas, there could be an easier way.

A common question: Is it possible in Excel for VLOOKUP to return all results. When someone asks me this question, there are several different things they might be looking for. This video shows four different ways to solve the problem. (0:00) The question: how to return all matches from VLOOKUP ? (0:50) Total All Results (1:36) Concatenate All Results (2:53) Do All Lookups and Sum (4:01) Use Dynamic Arrays

Using the new UNIQUE dynamic array function in Excel to return a list of distinct or unique values. This episode looks at the third and second arguments in the UNIQUE function. Also - using UNIQUE inside of SORT and TEXTJOIN.

One of the 7 new dynamic array functions is RANDARRAY. In today's episode, I re-visit the model that I built for Excel Hash. That model used 200K cells with VLOOKUP, RAND, and NORM.INV. In today's video, replacing the formulas with a single formula. Recalc times are cut in half.

In today's video: replacing a pivot table with three dynamic array formulas. (0:00) How to create a crosstab report in Excel from 3 array formulas (0:13) The official name for the new arrays are Dynamic Arrays, not Modern Arrays (0:23) I've written a 60-page e-book documenting 30 ways to use them. For more information: Excel Dynamic Arrays Straight to the Point (0:36) The roll-out is going to be super-slow, as the Excel team tries to figure out if they will break anything. (1:12) =E3# is an Array-Range Reference Notation (1:44) Creating a cross-tab report to replace a pivot table with three formulas (2:00) SORT/UNIQUE for ROWS (2:22) TRANSPOSE/SORT/UNIQUE for COLUMNS (2:39) SUMIFS in the Values area (3:22) This is an example of...