MrExcel Excel Videos

Ground-breaking news in Excel. The partner to the LET function was just released. Check out the new LAMBDA function in Excel. This makes the Excel formula language Turing Complete. Store formula logic in a name and pass parameters to that logic. Link to the Brian Jones article: Announcing LAMBDA: Turn Excel formulas into custom functions Table of Contents (0:00) Introduction to Lambdas and Revenge of the Nerds (0:26) Alonzo Church and Alan Turing (1:52) First LAMBDA: Case Quantity (3:10) Hypotenuse of a Triangle using HYP (3:49) Area of a Pizza (4:55) Using Data Types - Distance Between Two Cities (5:52) Recursion example from Brian Jones (6:24) Wrap-up

You have 100 people coming to a conference. There are 11 sessions. Tables seat 10 people each. How can you set up a seating chart for the 11 sessions so everyone has a chance to meet everyone else at least once? Bill tries brute-force Monte Carlo and can't get better than 65% of the way to the goal. Is there a better way? Download my workbook from here: Arrange100People.xlsm

My former co-worker thought it was a good idea to hide all of the whitespace below our Excel data and managed to hide a million rows and 16000 columns. How can I unhide something at the edge of the worksheet?

"My spreadsheet must be haunted" is a great opening line to an Excel question sent in just before Halloween. You have 1510 rows in Excel. You try to Delete Sheet Rows from A1511 to A1048576, but the rows keep coming back. How can you delete all of the whitespace at the bottom of Excel? How can you delete unused rows at the bottom of Excel? How can you delete unused columns to the right of your Excel data? The solution is to hide the extra rows and columns.

How to use XMATCH with Application.WorksheetFunction in Excel VBA? Today, a question from Hydrogen Peroxide. How can you refer to an array inside of a formula in Excel VBA. This workbook takes a look at how to simulate an Excel function in VBA. We start off with a simple one, just as MID which is already built in to VBA. Then, for a function like VLOOKUP that is not in Excel VBA, you can use Application.WorksheetFunction. However, today's question needs to concatenate two ranges and then pass that to Application.WorksheetFunction and this does not work. The solution, thanks to Brad Yundt, is to do the concatenation inside of Application.Evaluate. Check out Brad Yundt's answers on Quora: Brad Yundt

Can you use the MAX function in Excel to find the largest value across all worksheets? You can use a 3-D Reference or a spearing formula. In this episode, I will show you an easier way to create 3D References in Excel. Also, a trick from Mack Wilk for making your 3D Reference go through a blank sheet at the end. For more information about the book, MrExcel 2021 Unmasking Excel, visit: MrExcel 2021 – Unmasking Excel

Your manager asks if you can add a blank row after each subtotal. Episode 2433 shows you how to do this in Excel. The first way, is clearly cheating: Increase the height of each row. But the second way is the real way, using Ctrl+Enter, Select Visible Cells, Insert Cells Shift Down, and Insert Rows.

Geert asks: "in my experience the datasets are non-uniform in the sense that only a subpart of the data needs subtotals. (categories of data are sequentially listed and only 1 category has a further breakdown that needs subtotals – the other categories do/must not) Is it possible to do that using the buttons on the ribbon? This episode shows how to add subtotals to each customer in the manufacturing region.

A co-worker sent you a file with subtotals. How do you get rid of them? Why are there 3 numbered buttons to the left of column A in Excel? Why are there minus buttons and vertical lines to the left of column A? This video shows you a quick way to get rid of subtotals that were added by a co-worker.