MrExcel Excel Videos

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

This part 2 on a series on Dynamic Arrays in Excel. These formulas were announced at Ignite on September 24, 2018. This video covers SORT and SORTBY. Topics in the video: Sort Excel data with a formula Use SORT to sort a range Use SORTBY to sort one column by another column Use SORTBY with RANDARRAY to do random drug testing or random without repeats. To use the file from this episode: https://wwww.mrexcel.com/download-center/2018/09/excel-sort-with-a-formula-using-sort-and-sortby.xlsx

The next Excel dynamic array function introduced at Ignite 2018 is the FILTER function. Use =FILTER(array, Include, [value_if_empty]). This is one of the new functions that is entered in a single cell, but the results spill into adjacent ranges. To download the workbook from today: https://wwww.mrexcel.com/download-center/2018/09/filter-function-in-excel.xlsx

This covers the Excel SEQUENCE and RANDARRAY modern array functions. =SEQUENCE(Rows, Columns, StartAt, Step By) =RANDARRAY(Rows,Columns) Do these Excel function because people can't figure out how to have the Fill Handle make 1 drag to 1, 2, 3? No! The SEQUENCE function makes every Excel function into an array function. Using Sequence inside of IPMT function to replace an amortization table. To use the workbook from today's video: https://wwww.mrexcel.com/download-center/2018/09/sequence-and-randarray-functions.xlsx

Build 1810 of Excel introduces Threaded Comments and they are cool. You can now have a conversation about each cell in Excel. Threaded comments will show date, time, author, and the comment. To download the workbook from this video: https://wwww.mrexcel.com/download-center/2018/09/notes-and-threaded-comments.xlsx Jump to these sections in this video: (0:00) Threaded comments and notes in Excel (0:14) Jon Peltier and Excel.Uservoice (0:30) Inserting a Note (1:16) Tricks with the legacy notes keep working (2:01) New Threaded Comments (2:36) Seeing Replies to Excel Threaded Comments (3:05) Purple indicator versus red indicator (3:16) Icons in the Review tab of Ribbon (3:31) Comments Panel with Show Comments (4:09) Recording a...

Will Asteroid Bennu strike the Earth in the 2182-2198 time frame? Using Microsoft Excel to model Bennu's path 10 million times. Even after OSIRIS-REx, we might learn that we are at greater risk than we think now. This is my entry in Oz do Soleil's #ExcelHash competition. Vote for your favorite here: Microsoft Forms The entire playlist is here: Excel Hash 2018 Table of Contents of Video (0:00) Introduction and Welcome (0:08) About Excel Hash game (1:00) Excel Technique: 3D Models in Excel (1:25) Excel Technique: Rotating 3D Models (1:35) Science: Bennu Spinning Wildly (1:51) Science: What does Bennu Eventually hit? (2:15) Joke: JPL Travel Poster (2:25) Science: Bennu Close-Pass Dates (2:50) Science: How small is 1 in 2700...