MrExcel Excel Videos

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.

Today's tip from Kristin in Cape Canaveral: a faster way to insert worksheet rows in Excel. I will show you the method that I use, other methods I know about, and Kristin's faster method using the Quick Access Toolbar. Also in this video - the easy keyboard shortcut for the first 9 icons on the Quick Access Toolbar.

Extremely tricky problem today: How can you use UNIQUE to get all unique individual cells from A1:D12? The solutions are all pretty complex. Do you have something better? Vote for a SPLIT function at https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/33712939-add-new-split-formula Vote for changing UNIQUE at Allow UNIQUE() to operate on a rectangular range, returning all unique values Follow Brad Yundt on Quora: Brad Yundt - Quora Information on Fast Excel V4 from Charles Williams: FastExcel V4 SpeedTools Cool stuff in this video: Ingeborg Hawighorst using TEXTJOIN to turn a rectangular range into a single cell. Fairwinds splitting that cell into a row of cells Charles Williams add-in with...

Insomnia in Merritt Island. How would you solve this problem in Excel? I have a solution, but it can't possibly be the best solution. We are playing for Excel Guru patches. Download the data from https://www.mrexcel.com/download-center/2020/02/Podcast2316Data.xlsx and let me know how you would solve it.

Long-time viewer Dave writes in with a potential bug in Excel. Why does RANDARRAY with a RANDBETWEEN inside evaluate to a #SPILL! error about 10 percent of the time? There is nothing in the way that should be causing the spill error. The sad news is that this is against the rules. Here is the support article: #SPILL! - Volatile Size The stranger question: why does it work 90% of the time?

The new Dynamic Array formulas in Excel are awesome. But it is very common to run into a known limitation. It is called an array of arrays and it is happening a lot to people who are trying to learn dynamic arrays.

A recent news story reported that 02/02/2020 was the first time in 900 years that we had a palindrome as a date. While the news story left out a bunch of additional details, I was wondering if you could easily fact-check that in Excel. In today's episode a way to use Dynamic Arrays, SEQUENCE, MID, and CONCAT to reverse the text in a cell. Also: Check out Fast Excel Speed Tools at FastExcel V4 SpeedTools for a REVERSE.TEXT function. Check out my Dynamic Arrays book: Guide to Excel Dynamic Arrays by MrExcel Table of Contents (0:00) Introduction (0:20) Clark Kent of Dynamic Array Functions: SEQUENCE (0:33) Broadcast News Rant (1:08) Formula to test for a Palindrome (2:00) Why the Excel formula works, step by step (2:25) TEXTJOIN versus...

To buy or try WBMerge: https://mrx.cl/2HVj0wU Save 25% on WBMerge through March 21 2020 using coupon code MREXCEL You have an Excel workbook and would like to split it by (customer or product or whatever) with each unique value going to a new worksheet or to a new workbook. The people who made WBMerge have added a WBSplit feature.

John has figured out how to use SUMIF to find all entries less than 10. But his next step is to find everything between 10 & 20. He asks how can he make SUMIF handle two conditions? In this video: How to switch from SUMIF to SUMIFS How to prevent having to edit each formula How to use a Pivot Table to do the analysis quickly And old-school SUMPRODUCT formula.