MrExcel Excel Videos

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

Accountants love the subtotals feature in Excel. We love to see details but add a little summary information. I have a lot of friends who think Subtotals should be deprecated and that anytime you use subtotals, you should be using a pivot table instead. That's rubbish. There are times when subtotals are the perfect solution. What do you think? Do you love them or loathe them?

Candace is using a third-party Bingo app to generate numbers for a bingo game. Some people are complaining that the app is not fair and that it is calling some numbers too frequently. Candace manually keyed the numbers into Excel and wants to analyze to see if the numbers are uniformly distributed. This episode will show you how to test to see the distribution between the five columns and between the 75 numbers. Note that while this could have been done with the FREQUENCY function, I chose a pivot table instead.

Check out the finals of the Financial Modeling World Cup: I was a host for an Excel Competition that aired live on YouTube. Besides being one of the nerdiest & geekiest things I've been involved in, it was great to watch other people use Excel. Poland's Gabriela Stroj kept making fast edits to formulas using a method that I've wrote about in my books but have never seen anyone actually use. Am I the only one who does not do this regularly? Is this something you knew about? Do you use this technique?