MrExcel Excel Videos

Rico S introduced me to ProgramKey+K to Pick from a Dropdown List. This gives you a drop-down menu without data validation. I can see that this feature could be pretty cool, but I need something easier than Program Key, K, Down, Down, Down, Enter, Enter.

Quick update on the Excel LET function. Two questions: Will it work with arrays and how is the formula speed? This video shows that it does work with arrays and the calc speed is faster.

The Excel team introduced an amazing new LET function to Office 365 Insiders Fast on March 16, 2020. After the initial introduction video yesterday, today, I tackle a massive 340-character formula to find the second-to-last word in a phrase (aka the penultimate word). And Yes, this can be done easily with VBA, but today I am taking a 300+ character formula from the MrExcel Message Board circa March 2010 and simplifying it dramatically.

Brand new in Excel for Office 365 (currently in Insiders Fast channel only)... a LET function. Any time that you have a formula that repeats the same calculation over and over again, the LET function will let you assign that intermediate result to variables!

You are collaborating in a workbook and a co-worker filters or sorts their data and it changes your view as well. Microsoft has introduced Sheet View to Excel Online. This allows a person using Excel online to Filter to just their data without screwing up your view of the data. To read more: Sheet Views in Excel

Links to the 16 articles: Bill's "How Would You Clean This Data" Challenge Back in episode 2316, I had a clunky solution to a data shaping problem from Beth. I knew there had to be a better way and I asked you for your solutions. I learned so much from the 27 solutions and many comments. Thanks to Peter Bartholomew, brandon brimberry, Jonathan Cooper, Bohdan Duda, Geert Demulle, Fowmy, ExcelisFun, Roger Govier, Wyn Hopkins, Josh Johnson, Michael Karpfen, Hussein Korish, Kevin Lehrbass, John MacDougall, Ondřej Malinský, Rene Martin, Chris McNeil, Jason M, Christian Neuberger, Ken Puls, Jamie Rogers, Prashanth Sambaraju, Oz Du Soleil, Rico S, Bill Szysx, Frank Tonsen, Charles Williams, MF Wong, and Matthew Wykle.

For 16 years, people asked me if Excel VBA will be around forever. I always said, "Don't worry - they can't kill it until there is a viable replacement with a macro recorder." Today, in Public Preview, Office Scripts offer a macro recorder that records TypeScript. This will be the product that eventually kills Excel VBA. Listen as deal with the stages of grief as I try to figure out if I can figure out typescript. This is a first look at the Macro Recorder and trying to understand TypeScript. Microsoft Tutorial: Sample scripts for Office Scripts in Excel on the web - Office Scripts

Creating a Frequency Distribution in Office 365 Excel is easier thanks to dynamic arrays. First: make sure you have a function called UNIQUE. You don't need UNIQUE in this method, but if you don't have UNIQUE, then you don't have spillable arrays and you need to watch this instead: Included in this video: 1. Creating a frequency distribution from random data using a bins array and the FREQUENCY spilling function. 2. An odd bug with the Histogram chart. Why you could choose Pareto first and then switch back to Histogram to avoid the bug. 3. Using NORM.INV instead of RANDARRAY or RANDBETWEEN to generate data that has a standard normal distribution.

I really love this new feature from the Excel team. Do you know those big task panes that take up space on the right side of your Excel screen? You can now collapse (most of) them into a single tab strip on the far right side. Show one pane at a time, or no panes. The only pain point is getting trapped with focus on the tab strip. If you regularly use the arrow keys to navigate, you might find your keystrokes directed to the task pane instead of the grid.