MrExcel Excel Videos

Microsoft Excel Tutorial: To play along, read the trivia questions and download the data set here: Space Trivia 🚀👩🏽‍🚀 In case you have problems solving any of the trivia questions, I've created a video showing the answers. Jump to the section for any of the five questions: Table of Contents (0:00) Welcome (0:22) How many astronauts have orbited the Moon? (4:35) Who had the most hours of spacewalk activity? (5:25) Who has launched the most number of times? (6:27) Woman with the most days in space? (7:41) How many launched on Shuttle and returned on Soyuz? (9:26) Wrap-up

Microsoft Excel Tutorial: Visio Diagrams in Excel. In episode 2375 of the Learn Excel from MrExcel podcast, Bill Jelen discusses the general availability of the Visio Data Visualizer add-in. This add-in is now accessible to a broader audience, offering three types of charts: org charts, flowcharts, and process charts. Bill demonstrates how to create an org chart using the add-in, highlighting the ability to update data in real-time and customize the chart. He also explores the flowchart feature and the unique shape types available. Additionally, he introduces the process chart, also known as a Swim Lane Chart, and explains how it can be used to represent processes with rows and columns. Bill concludes by sharing information on Visio...

Microsoft Excel Tutorial: Expanding two cells with a year range into one row for each year in Excel. In episode 195 of the Dueling Excel podcast, Bill Jelen and Mike Girvin tackle a question from Erno V on YouTube. The challenge is to expand a column of data containing ranges of years, such as "2000-2004," into individual years. Bill approaches this using VBA, while Mike opts for Power Query. Bill's VBA solution involves looping through the data, checking for single years or ranges, and then creating separate rows for each year within the range. He demonstrates the code to achieve this expansion. Mike, on the other hand, employs Power Query to create a dynamic list of years within the cell itself. He starts by converting the data...

Microsoft Excel Tutorial: Three ways to filter a pivot table and two of them you might not have known about. Daniel Bula asks if the trick from episode # 2359 can be used in a pivot table. Here is a great pivot table hack to filter to the south region over $5K. Table of Contents (0:00) Welcome and shout-out to Daniel Bula (0:24) Excel Problem: Filter to South greater than $5K (0:35) Filtering based on Sum of Sales in Excel (0:50) Sorting on South (1:00) Slicer based on the detail data (1:20) Typing criterion below South column (1:33) Customize quick access toolbar (1:47) Filtering from cell below pivot table (2:01) Clearing Filter from South (2:20) Turning off Filter drop-downs in Excel (2:32) Wrap-up

Microsoft Excel Tutorial: Do you need to randomly select someone from your department to win a prize? This awesome free macro from Rob Collie at PowerPivotPro.com will do the trick. Download for free from: Wheel Of Inquisition Files Download | P3 Adaptive Rob had an idea of how to improve participation on his company Zoom calls. He wanted a way to randomly call on a person. I knew it was going to be a fun day when Rob called and said, "do you think you can rotate the Excel Camera Tool?" He explained what he was trying to do and both of us spent way too much time trying to tweak the workbook to have a spinning Wheel of Inquisition. We kept adding new features so the wheel would spin faster at first and then slow down. Rob added the...

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