MrExcel Excel Videos

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

A column of currency ended up with commas for both the thousands separator and the decimal point! How can you replace the last comma

Salama from YouTube has two columns of numbers. The area in column A is from 1 to 36. The month in column B is from 1 to 12. How many times does each combination of Area and Month happen? Today's video offers three methods. Table of Contents 00:00 Problem and solving with SUMIFS 01:27 Solving with a pivot table 02:25 Solving with three dynamic arrays

Ten really short Excel topics jammed into one 188-second video. Cube Functions spill - article by Chris Webb is at Chris Webb's BI Blog: Excel Cube Functions, Dynamic Arrays And Lambdas, Part 1: Getting All The Items From A Set Read more about the new Power Query book at Master Your Data with Power Query in Excel and Power BI Table of Contents (0:00:00) Welcome (0:00:12) #1 Congratulations Leila for 1 Million Subs (0:00:17) #2 Excel is loading much faster (0:00:25) #3 New way to open Browse (0:00:32) #4 Find dialog shows options automatically (0:00:40) #5 PivotTable Fields now will collapse like other task panes (0:00:48) #6 Chris Webb shows how Cube Functions can now spill (0:01:02) #7 Smooth Scrolling (0:01:26) #8 Data Types...