MrExcel Excel Videos

Microsoft Excel Tutorial: Can you mix formatting in an Excel cell that contains a formula? How to Work Around Excel's Formatting Limitations Ever wondered if you could apply different formatting to parts of a cell populated by a formula in Excel? In today’s video, we dive into this intriguing question posed by Paul, a viewer who couldn’t find a solution to this common Excel conundrum. While Excel doesn’t allow partial formatting of a formula’s result, I’ve discovered a clever workaround using the **Paste Linked Picture** feature. Watch as I demonstrate this creative hack step-by-step! We start with a fundamental Excel tip: editing and formatting individual characters in a cell. From using **F2**, the **Home** key, and **Shift**, to...

You have 100 people coming to a conference. There are 11 sessions. Tables seat 10 people each. How can you set up a seating chart for the 11 sessions so everyone has a chance to meet everyone else at least once? Bill tries brute-force Monte Carlo and can't get better than 65% of the way to the goal. Is there a better way? Download my workbook from here: Arrange100People.xlsm

Microsoft Excel Tutorial: Unlock the Secrets of Excel's New Compatibility Version! Are you tired of mismatched results when sharing Excel files between coworkers using different versions? Microsoft has finally addressed this issue. Today we dive into the game-changing **Function Compatibility Version**. This feature offers a solution to inconsistencies caused by updates to five key functions: **LEN, MID, SEARCH, FIND, and REPLACE**. While the updates might seem minor, the implications are massive, especially for teams juggling old and new versions of Excel. Learn how the new **Compatibility Version** setting makes it easier to manage version disparities. Whether you're stuck on Excel 2016 or using the latest features in Excel 365...

Microsoft Excel Tutorial: Randomly Assign Sides for Thanksgiving Dinner In today’s tutorial, we’re tackling a fun holiday challenge: assigning dinner sides for Thanksgiving using Excel! Whether you’re hosting a big feast or coordinating a potluck, Excel makes it easy to assign dishes randomly and fairly. In this video, I’ll walk you through the process step by step, using Excel’s powerful SORTBY and RANDARRAY functions. Have you ever used a spreadsheet to manage your holiday meals? Let me know in the comments! My friend Katie from my morning workout group inspired this video—she needed a way to assign sides for her large Thanksgiving dinner. Together, we built a system in Excel to ensure everyone gets a dish to bring. You’ll...

Microsoft Excel Tutorial: Which Products Contain Both Of These Items? Today, an odd data problem. I have a solution, but I suspect there are much easier ways to do this. I have a list of songs in workouts from Supernatural VR. Over 500 workouts are Quick Hits with just two songs. Each Quick Hits workout should be a subset of a longer workout. Given the two lists, how can you use Excel to find the parent workout? My solution involves an XLOOKUP with a concatenated key and a concatenated lookup vector. This gives me a list of the Workout IDs that contain song 1 and the Workout ID's that contain song 2. Given these two list of workout IDs, how can you find which workout ID is in common between the two cells? I was surprised to find...

Microsoft Excel Tutorial: Excel Fake Data From Python In Excel Creating fake data with Python in Excel has never been easier! In this video, I walk you through generating random data directly in Excel using Python’s powerful capabilities. We start from a completely blank workbook, search for Python, and dive into some exciting new options like the QR code generator and random data generator. This process is amazing for anyone who needs quick, randomized data sets without leaving Excel. Just a few clicks, and you can have it all right at your fingertips! We'll start by setting up Python to generate a sample data set, including names, cities, countries, addresses, or whatever fields you need. I demonstrate how you can adjust the data...

Microsoft Excel Tutorial: Draw Perfect Shapes With Draw And Hold Gesture In this video, I'm diving into a new feature in Microsoft Excel: the Draw and Hold Gesture! If you've ever wanted to create shapes in Excel without struggling with awkward mouse drawing, this update is for you. While the feature has been available in OneNote for a while, it’s finally made its way to Excel and PowerPoint—at least for those on the Insider’s channel. I’ll walk you through how to activate this new tool and show you what it can do! First, if you don’t see the Draw tab in your ribbon, I’ll show you how to get it. It's just a quick right-click and then a trip to the “Customize Ribbon” menu. Once activated, you’ll have access to a variety of freeform...

Microsoft Excel Tutorial: Which Invoices Add Up To This Check Amount - Faster method using Python in Excel. To download the workbook from today: Excel Which Invoices Add Up To This Check Amount - 2658 Sample Files - MrExcel Publishing Today, I'm excited to share a powerful solution to a problem I used to call the "knapsack problem" in accounts receivable. We often face scenarios where customers send a payment without specifying which invoices are covered—leaving us to solve the puzzle! In this video, I'll show you how to identify which invoices match a specific payment amount using Python in Excel, without the need for a Copilot subscription. 🚀 This challenge involves finding a combination of invoices that add up to the exact payment...

Microsoft Excel Tutorial: Create an NBA Shot Chart Using Python & Copilot | LeBron James - 16 Playoffs In this video, I’m excited to show you a demo I saw from Microsoft that's seriously cool! We’re diving into how to use the new Python integration with Excel Copilot to create an NBA basketball shot chart. With data sensors installed in every NBA arena since 2013, we now have detailed info on every shot, including the exact X and Y coordinates and whether the shot was made or missed. This data opens up a whole new world of possibilities for sports analytics in Excel. For this example, I’ve extracted LeBron James' shot data from the 2016 playoffs with the Cleveland Cavaliers – the Cavs' only championship season during my lifetime! With...