MrExcel Excel Videos

Microsoft Excel Tutorial: How to add page breaks so you print five rows per page of a 5000 page report? Shoaib wants to do this without VBA! There are four methods in this video, three of which do not use VBA. Table of Contents (0:00) Problem is how to print five rows per page without VBA (0:22) Solution 1: Change bottom margins (1:18) Solution 2: VBA Macro (1:47) Selecting top row of every page with Go To Special and a formula (2:10) Solution 3: Insert one page break, then Tab / F4 998 times (2:46) Solution 4: Use Subtotals in hidden columns (5:25) Wrap-up

Microsoft Excel Tutorial: The Action Pen has been in PowerPoint and Word, but it just came to Excel for Office Insiders. With the Action Pen, you can use a tablet or a mouse to hand-write data in Excel cells and the Action Pen will convert your handwriting to text. Also in this video: a recap of some other inking features in Office. Table of Contents (0:00) Intro to video (0:20) Writing values to Excel cells with the Action Pen (1:15) Summing cells with Alt+Equals (1:25) Selecting multiple cells by circling them (1:40) Overwriting a value in a cell (2:00) Deleting a cell by crossing out. (2:30) Writing text in a cell (2:50) New pen colors (3:05) Creating perfect shapes with ink using Ink To Shape (3:35) Ink to Math (3:40) Point eraser...

Microsoft Excel Tutorial: Sean is calculating scores for a golf league. The Differential is Score minus Rating times 113 divided by the Slope. Given a course name and set of pins, he needs to lookup into a table to get the rating and slope. Download the workbook from: Golf_Differential_Two_Way_Lookup_3_Ways_2368.xlsx This video shows three easy ways to do the two way lookup: FILTER function in Excel or Google Sheets XLOOKUP function in Excel Power Query in Excel (the best and easiest way) Table of Contents (0:00) Intro to video (0:40) Calculating a Differential (1:07) Solving with a FILTER function in Excel (4:30) Solving with a two-key XLOOKUP (5:20) Solving with a merge in Power Query (7:48) Wrap-up

You have two lists of data in Excel. If something from List2 is found in List1, put the address of the matching cell next to the item in List2. Mike and Bill try to find a one-cell formula using either CELL or ADDRESS in this week's episode. Table of Contents (0:00) Description of problem (0:30) Does ADDRESS or CELL return a spillable array? (2:02) Can XLOOKUP return an array of references? (3:33) Bill's answer: a single ADDRESS formula that spills (5:47) Mike's first answer: using old-school CELL function and copying (8:07) Mike's second answer: using LET and XLOOKUP (10:05) Wrap-up

Microsoft Excel Tutorial: Fast way to fill blank cells with value from above in Excel. In Podcast 2367, Bill Jelen demonstrates a quick method to fill down blank cells in Excel with the value from the cell above in just 11 keystrokes. Deepak's data consists of 50 rows and four columns with numerous blank cells that need to be populated with values from the cells above. Here are the 11 keystrokes to accomplish this: 1. Select the data. 2. Press Ctrl+G or F5 to open the Go To dialog. 3. In the Go To dialog, press Alt+S to select "Special." 4. Inside "Go To Special," press Alt+K to select "Blanks." 5. Press Enter to confirm the selection. This will highlight all the blank cells in the selected range. 6. Type an equals sign (=) to...

Microsoft Excel Tutorial: Copy and paste this VBA code: Sub ChangeSheetNameAllWorkbooks() Dim WB As Workbook For Each cell In Selection Set WB = Workbooks.Open(cell.Value) WB.Worksheets(1).Name = Left(WB.Name, Len(WB.Name) - 5) WB.Close SaveChanges:=True Next End Sub Today's question from Lakshmi. She wants to bulk change all workbooks in a folder so that the Sheet name is the name of the workbook. In this special case, there is only one worksheet per workbook. I talked to Lakshmi and asked if the workbook name is C:\Customers\Apple.xlsm then the sheet name would be Apple. This workbook shows off three skills Table of Contents (0:00) Welcome and Question (0:33) Use Power Query to bring a list of files into Excel (1:36) Launch Excel...

A strange question today: What is the "correct" way to select consecutive worksheets in Excel? Along the way, you will also see: How to select non-consecutive worksheets in Excel How to have the page number for Sheet2 continue after the last page number for Sheet1. Link to the original question on Quora: Bill Jelen's answer to Which is the correct process for selecting consecutive worksheets? - Quora Table of Contents (0:00) Introducing the question (0:19) Using Ctrl+PageDown or Page Up to move between sheets (0:34) Using Ctrl+Shift+PageDown to select consecutive sheets (0:44) Using the mouse to select consecutive sheets (1:01) How to select non-consecutive sheets in Excel (1:10) Changing data or formatting on several Excel worksheets...

25kasa1 has numbers with the decimal points missing. How can you quickly change 5000 to 50.00 in an entire column. In less than 2 minutes and 40 seconds, this video shows 6 methods PLUS a way to prevent the problem in the first place. Includes Formulas, Power Query, VBA, and Paste Special.

Microsoft Excel Tutorial: Using Excel to randomly draw a winner. In this episode of the MrExcel netcast, Bill Jelen announces a giveaway of three pairs of Excel-themed socks and shares some insights about his YouTube subscribers. Bill initially encountered difficulty in obtaining subscriber data from YouTube due to privacy settings. However, he managed to gather data from 37,000 subscribers who had set their subscriptions to public. Bill explores this subscriber data, revealing fascinating trends. He analyzes when his current subscribers first subscribed, noticing some who joined even before he began posting videos on YouTube in 2009. Bill also looks into how many subscribers his subscribers have, discovering that a significant...