MrExcel Excel Videos

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?

This weekend, Fantasy Football teams will be doing their draft. If you are using the ESPN website to manage your draft, the Draft Recap Report could use some formatting. Download our free Excel template from: Transform Your ESPN Fantasy Football Draft Recap Report Watch the video to see how to copy your Draft Recap from ESPN, paste it into Excel, and then use Power Query to clean it up and format it. Thanks to Rob Collie at P3 Adaptive - Leading Consultants on Power BI & the MS Data Platform Thanks to Suat Ozgur from MrExcel.com

Long ago, Excel offered a full screen mode. The Ribbon would completely disappear and you could get the maximum number of rows in the grid visible. The feature was removed in Excel 2013. Today, it is back! At least in beta. Take a look at how Full Screen Mode in Excel works. Link to the old help topic about full screen mode: Switch to full or normal screen view in Excel

I have a problem in Excel. About a week ago, the vertical scroll bar on an Excel worksheet stopped working. I lost the scrolling ability from top to bottom. So when I try to drag the right scroll bar from row 1 down to row 960, it doesn’t work. I have to use the down arrow, which takes a lot longer.

Kartikey has 6800 rows of data. For each product in column A, he needs every combination of Column B with Column C. In this episode, Bill uses Power Query and a Full Outer Join to solve the problem.

Another new LAMBDA helper function in Excel is MAKEARRAY. This video shows a simple example, and then tackles the problem from episode 2317 of how to unwind a rectangular range into a single column so you can pass it to UNIQUE and SORT. The formula used in the video is: =LET(array,B2:G15, rowcount,ROWS(array), colcount,COLUMNS(array), itemcount,rowcount*colcount, MAKEARRAY(itemcount,1, LAMBDA(r,c, INDEX(array, LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)), ROUNDUP(r/rowcount,0)) )))

Subhash wants to make changes in either the source or copied data and have it reflected in the other place. A little bit of VBA makes this possible. The code from the video: Private Sub Worksheet_Change(ByVal Target As Range) MirrorCell = False Select Case Target.Address Case "$D$3" MirrorCell = "B7" Case "$E$3" MirrorCell = "B8" Case "$F$3" MirrorCell = "B9" Case "$G$3" MirrorCell = "B10" Case "$H$3" MirrorCell = "B11" Case "$B$7" MirrorCell = "D3" Case "$B$8" MirrorCell = "E3" Case "$B$9" MirrorCell = "F3" Case "$B$10" MirrorCell = "G3" Case...