MrExcel Excel Videos

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

Build a new tab in the ribbon with your favorite commands Use lots of Groups with less than 6 icons each Make sure your tab is above Home so it is default Thanks to Ronnie W in Frisco for these tips, To download this workbook: https://www.mrexcel.com/download-center/2018/08/help-tab-added-to-ribbon.xlsx List of upcoming seminars: Excel Seminar Schedule

Help now has its own tab in the Excel Ribbon. Today, a short tour of the icons available there.

Excel now debuts a new Home screen in the File menu - it is a combination of the existing New and Open screens. Also - the icons on the Ribbon are changing for those using Office 365. To download this workbook: https://www.mrexcel.com/download-center/2018/08/new-ribbon-and-help-screen.xlsx List of upcoming seminars: Excel Seminar Schedule

New in Office 365: The ability to format individual cells in a pivot table and have the formatting stick. Added to Office 365, Version 9029.2xxxx or later Right-click the cell and Format Cell or simply apply the formatting. The cell formatting will stick, even after pivoting or filtering. List of upcoming seminars: Excel Seminar Schedule To download the workbook from today's video, visit the URL in the YouTube description