MrExcel Excel Videos

An awesome new function from Microsoft 365 debuted this afternoon for the beta channel. Load Daily, Weekly, or Monthly Stock Data into Excel using the new STOCKHISTORY function. This video discusses the new #BUSY! error in Excel. Learn about the awesome new FNV - Formatted Number Value and how XLOOKUP, IF, INDEX, and CHOOSE) can lookup a formatted number. Also - how to do Exchange rates. Table of Contents (0:00) New Names for Microsoft 365 and Beta channel (0:23) STOCKHISTORY function syntax (0:49) Example using Zoom Video Stock (1:50) #BUSY! error in Excel (2:27) Updates a few hours after market close (3:08) Forcing it to a certain stock exchange (3:30) Returning historical exchanges rates in Excel (3:36) NEW! Formatted Number Values...

An awesome trick when you are referring to a data type cell in a formula. I learned this from Chandoo - when he was explaining why another formula was *not* working. Using XLOOKUP (or INDEX/MATCH), if your lookup table contains data types, you can refer to columns that you can't see in the grid. I sort of new something related to this in the past - when you can sort a table by a field that is not in the table if the table contains data types.

If you are in the US and run a small business, the Payroll Protection Loan program went live on Friday April 3, 2020. The SBA and Treasury department clarified their guidance on April 7, 2020. If you haven't applied for the loan yet, the new guidance could generate a larger loan amount. This plan offers an incentive to keep your employees on board for eight weeks. It offers a very-low-interest loan to cover eight weeks of payroll, and the loan can be forgiven if you keep your people employed at the same salary through the eight weeks. Updated April 7 guidance: https://home.treasury.gov/system/files/136/Paycheck-Protection-Program-Frequenty-Asked-Questions.pdf Info from Treasury Department: The CARES Act Provides Assistance to Small...

New for Office 365 (soon to be called Microsoft 365) - a new series of icons, stickers, and cut-out people. That's right, 19 people, each with the background removed, each with dozens of postures and emotions. Some people are holding signs. This feature is in Excel, Word, and Power Point. (0:01) Welcome (0:20) New feature added to Office 365 (0:32) What is a cutout person? (0:49) The dialog box to add icons, stickers, or cutout people (1:10) Meet the cast (1:45) The 52 shades of Randy (2:10) Randy as Superman (2:28) Searching for people by Name or Emotion (3:03) People pointing at a cell (3:17) People facing away from you or to the side (3:30) Searching for doctor (3:45) Distribution by Age of Office Cutout people? (4:02) Microsoft 365...

This started as an April Fools joke from Bill Szysz and Mike Girvin, but it brought back memories of Schoolhouse Rock from 1973 and the story of Little Twelve Toes. In today's video, a secret decoder ring in Excel using BASE and DECIMAL. Also: you can now assign fill color and font colors using Hex color codes in Excel. This video includes alternate numbering systems like Base 12, Hexadecimal, and Base 36. Download the workbook from: https://www.mrexcel.com/download-center/2020/04/Excel_Decoder_Ring.xlsx

Jennifer from Daytona wants to Filter a data set to only show Tomato and then number the visible rows. But you can't do this with the Fill Handle. In today's episode, a couple of formulas to solve the problem.

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.