MrExcel Excel Videos

Microsoft Excel Tutorial: Calculate Average Cost per Year in Excel. Derek has a fleet of vehicles and a list of all repairs made in the last 20 years. He wants to figure out how the annual repair and maintenance costs change as the vehicle ages. In this video, MrExcel discusses how to calculate annual repair costs based on the age of a vehicle. He provides a data set of repairs made on a fleet of vehicles and explains how to determine the first date each vehicle was placed in service. He suggests using an approximation for vehicles without records by subtracting a certain number of days from the first service date. MrExcel then demonstrates how to calculate the age of each repair using the DATEDIF function. He suggests using a pivot...

Microsoft Excel Tutorial: Microsoft Labs in Cambridge releases a great Python editor for Excel. Just 27 days after Python preview appears in Excel, Microsoft has added a dramatically better Python editor. To download the workbook from today: Excel Adds Python Editor 27 Days After Python Debuts - Episode 2625 Sample Files - MrExcel Publishing This new editor in the task pane offers AutoComplete, Intellisense, and automatic code coloring. Take a walkthrough the editor in today's video. Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel Table of Contents (0:00) Python Editor Added to Excel (0:10) Free Silo Photos (0:20) Theories on backstory (0:59) Get Add-Ins moving from Insert to Home tab (1:22) Update Excel Labs...

Microsoft Excel Tutorial: Sorting months in a pivot table when they are alphabetic. I met people who are downloading data from Oracle through Analysis Services. Their dates are coming in as text in the format of Sep-20 for September 2020. When they create pivot tables, the months are alphabetic instead of sequential. To download the workbook from today: Excel - Oracle Sending Dates as Text MMM-YY Episode 2624 Sample Files - MrExcel Publishing There are two ways to solve it: 1) A convolunted method from Sam Radakovitz that you only have to do once 2) A simple method that you will have to do 1000 times a year. In this episode, I show Sam Rad's method of setting up a custom list with the text months in the correct sequence. If you use...

Microsoft Excel Tutorial: How to use Excel's Filter Search Box to remove items from Filter. Melvin from Orlando shared a great Excel trick with me during yesterday's live Power Excel seminar in Daytona Beach. To download the workbook from today: Excel Use Filter Search Box to Remove Items from Filter! - Episode 2623 Sample Files - MrExcel Publishing We all know you can use the Filter drop-down Search Box to find all cells that contain Apple. But what if you want everything that does not contain Apple? Rather than use the old Text Filters for Does Not Contain, you can use this great trick from Melvin: 1. Search for Apple 2. Uncheck Select All Items 3. Check Add Current Selection to Filter This applies a "Negative" filter to the...

Microsoft Excel Tutorial: Sum across worksheets when rows do not line up? Today's question from Rebaz on podcast 1984 - Excel Sum Across Worksheets: "if we have different cell in different sheet, how can I Sum?" Download the workbook from today: Excel Sum Across Sheets When Rows Do Not Line Up - Episode 2622 Sample Files - MrExcel Publishing This video shows you an easy way to build a 3-D reference in Excel, also known as a spearing formula. Excel functions include SUM, XLOOKUP, FILTER, SUMIFS, TEXTJOIN, TEXTSPLIT, SUMPRODUCT, Helper Arrays, LET, Python in Excel, and VSTACK. Table of Contents (0:00) Problem: Adding Across Sheets that are not lined up (0:43) How to build a 3-D Reference (1:19) Does XLOOKUP work with 3-D? (1:50) Does...

Reid would like to list all 6-number combinations of the numbers 1 to 44. For example, 1-2-3-4-5-6, 1-2-3-4-5-7, and so on up to 39-40-41-42-43-44. The first thing to realize is that all lottery combinations are a lot of numbers. Over 7 million possibilities according to the COMBIN function in Excel. (For Power Ball, there are 292 million combinations!). Listing all combinations will be difficult because Excel only includes 1,048,576 rows. In this video, I show how to enable Macros in your version of Excel and then the macro code to list all possible combinations. Here is the code you can copy into your project. Sub ListThemAll() TC = 1 TR = 1 Ctr = 1 MaxRows = Rows.Count EndCell = 7059052...

Microsoft Excel Tutorial: Generating all combinations of N outcomes for K games. In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted "binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5" method. This involves a lot of typing and two different formulas. Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of 1. And then at the end, he uses +1111 to convert the 0 to 5 to 1 to 6. In this video, I show Kyle's method for the Jeopardy Masters problem and then I generalize the steps for any values of N and K. To...

Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in Excel To download today's workbook: Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 Sample Files - MrExcel Publishing Today, a question about creating a Python data frame from multiple Excel sheets. I use the CONCAT function in Python but then realize that the headings are repeated. So I show how to use .tail(-1) to remove the top row from each data frame except the first. Table of Contents (0:00) Problem Statement (0:29) Defining 3 data frames (1:32) Python CONCAT function (2:20) Python Tail Function (3:10) Wrap-up

Microsoft Excel Tutorial - Drawing a 3D Scatterplot using Python in Excel Download the workbook: Excel Python 3D Scatterplot - Episode 2620 Sample Files - MrExcel Publishing How to plot a circle in an Excel XY Chart Converting degrees to radians in Excel Building X, Y, Z values for a series of circles in Excel Using Chat-GPT for Python Code for a 3D Scatter Plot Adapting the copied code Marker choices for Python Charts Table of Contents (0:00) Problem Statement - 3D Scatterplot in Python (0:14) Excel formulas to plot a circle in Excel (0:45) Convert degrees to Radians in Excel (1:15) Formulas to Create a 3D Spiral in Excel (2:45) Finding Python code from Chat-GPT (3:10) Adapting Python code for Excel (4:45) Making a tiny chart larger...