MrExcel Excel Videos

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...

New Excel Features announced at Ignite 2018 include the Picture Transparency feature. If you have a photo in your Excel spreadsheet and want to see what is behind the picture, it is now easy using the new setting in Office 365.

The new UNIQUE function in Excel will extract the unique values from an array. This replaces the complicated Ctrl+Shift+Enter function in Excel. This is the fourth video in the series on Dynamic Arrays in Excel. To use the workbook from today's video: https://wwww.mrexcel.com/download-center/2018/09/unique-function-in-excel.xlsx

BREAKING NEWS: Just moments ago at the Ignite Conference in Orlando, Microsoft unveiled a new calculation engine for Excel. A single formula can now spill into adjacent cells, essentially returning an array of values without anyone ever pressing Ctrl+Shift+Enter. To go along with the new calc engine are six new functions: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, and RANDARRAY. I will cover all six functions in videos 2233 through 2237. To download the workbook for today's video: https://wwww.mrexcel.com/download-center/2018/09/excel-modern-array-formulas-debut-in-office-365.xlsx