MrExcel Excel Videos

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

Microsoft Excel Tutorial: VLOOKUP in Python for Excel How to do a VLOOKUP or XLOOKUP in Python. To download the examples in this workbook: Excel Python XLOOKUP 2619 Sample Files - MrExcel Publishing Along the way, you will see: 101: Doing a VLOOKUP Not specifying the key field! What if a customer is missing from lookup table? How to IFERROR() Limiting which fields are returned. What if headings don't match? What if a customer is duplicated? Lookup on two fields. Table of Contents (0:00) Python lookup overview (0:25) Comment indicator in Python (1:00) VLOOKUP 101 in Python using pd.merge (2:36) Leaving off the On field (2:50) IFERROR when customer missing with .FillNA (3:23) Limiting lookup table to needed fields (4:04) Headings...

Microsoft Excel Tutorial: Hiding part of Social Security Number Download this workbook from: Excel Display Only Last 4 Of Social Security Number - Episode 2618 Sample Files - MrExcel Publishing Someone from the Veterans Administration is getting data downloaded that includes the entire social security number (SSN). They only want to display the last four of the SSN. But, sometimes, they need to be able to go back and see the entire SSN. I have two solutions today, but I bet you have something better. First, it would be nice if Excel offered a custom number formatting code that said "There is a digit here, but we don't want to display it". My first solution is a pair of VBA macros that embed the original SSN in the N() function in...

Microsoft Excel Tutorial: Using a function in Python for Excel calculations. The problem today: Count how many times a word occurs in a cell in Excel. To download this workbook: Excel Python Custom Function - 2617 Sample Files - MrExcel Publishing The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel? After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA. Other topics here: Saving Python function in a cell Adding Text as line line of a Python script to appear in the...

Microsoft Excel Tutorial: Using Python in Excel to create Excel-like pivot tables. I love pivot tables in Excel. In fact, I've written an entire book on Pivot Tables. So when I saw that Python has a function to generate "Excel-like" Pivot Tables in a new data frame, I wanted to try it out. The Python Pivot Table is missing a few things: 1. Row Fields are called Index 2. Defaults to Average instead of Sum 3. Empty cells show as errors. Use fill_value 4. No Grand Totals by default! Turn on with margins=True 5. When you add Grand Totals, they are called "All" Unless you change them with Margins_Name 6. When you group by dates, you can't have grand totals 7. Does not sort by Custom Lists 8. Odd arrangement of headings when 2 row fields...

Microsoft Excel Tutorial: Python in Excel After using Python in Excel for 2 days, I am gaining confidence. Topics today: • Python Tips from Day 2 • Slow rollout of Python • Paying for Anaconda • Python Libraries to explore • How to write Python results back to Excel grid • Real-life K-Means clustering with 18K customers, 30K transactions, 300 products To download the first workbook: Gaining Confidence With Python In Excel - 2615 Sample Files - MrExcel Publishing The second workbook contains actual customer data and I am not sharing it at this time. Table of Contents (0:00) Welcome (0:45) Keyboard shortcuts for Python (1:22) Leila tip DF.Customer eliminates square bracket notation (2:14) Seaborn Library of Charts as shown by Mynda...

Microsoft Excel Tutorial - Using Python in Excel. Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me... I've been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working. To download this workbook: Python In Excel - Getting Started - 2614 Sample Files - MrExcel Publishing In this video: a getting started with Python in Excel tutorial. How to open the Python editor in Excel Ctrl+Enter versus Enter in the Python Editor in Excel Returning a Value or a Python Object Referring to an Excel range in Python Using Variables in Python in Excel Plotting Data Using Python in Excel Which Python Libraries are loaded by default...