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...
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Filters
Show only:
Loading…
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...
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...
