Category: Power Pivot

Excel – Unlock the Secrets of Excel’s New Compatibility Version! – Episode 2663

Posted on by Annie D

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 Continue reading “Excel – Unlock the Secrets of Excel’s New Compatibility Version! – Episode 2663” →

Excel Assign Thanksgiving Dinner Sides Using Excel – Episode 2662

Posted on by Annie D

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 Continue reading “Excel Assign Thanksgiving Dinner Sides Using Excel – Episode 2662” →

Excel Which Products Contain Both Of These Items – Episode 2661

Posted on by Annie D

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 Continue reading “Excel Which Products Contain Both Of These Items – Episode 2661” →

Excel Easy Fake Data From Python In Excel – Episode 2660

Posted on by Annie D

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 Continue reading “Excel Easy Fake Data From Python In Excel – Episode 2660” →

Excel Draw Perfect Shapes With Draw And Hold Gesture – Episode 2659

Posted on by Annie D

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 Continue reading “Excel Draw Perfect Shapes With Draw And Hold Gesture – Episode 2659” →

Excel Introduces TRANSLATE and DETECTLANGUAGE Functions – Episode 2645

Posted on by Annie D

Microsoft Excel Tutorial: Excel Introduces TRANSLATE and DETECTLANGUAGE Functions.

Unlock the Power of Excel’s New Functions: TRANSLATE and DETECTLANGUAGE

To download the workbook from today: https://www.mrexcel.com/youtube/rOQraANkokQ/

Hey Excel enthusiasts! If you found this video helpful, don’t forget to click Like down below. It helps more people discover this content. In this video, we’re diving into two amazing new Excel functions: TRANSLATE and DETECTLANGUAGE. These functions are game-changers, making it easier than ever to work with different languages in Excel.

First up, the TRANSLATE function. This powerful tool allows you to translate any text from one language to another effortlessly. Just enter Continue reading “Excel Introduces TRANSLATE and DETECTLANGUAGE Functions – Episode 2645” →

Learn Power Query M Code From Mike Girvin New Book

Posted on by Annie D

Every time I use the Power Query interface, I am aware that I am writing code that is probably as inefficient as recorded VBA code.

Today, a new book from Mike Girvin will teach you how to write M code in Power Query. This is for Power Query in Excel, Power BI, or Power Automate.

The book will be shipping from Amazon on October 1, 2024. However, by the third week in August, I am going to have 200 advance copies. For $29.95, you can buy one of these books with free shipping to the USA. While you wait, you Continue reading “Learn Power Query M Code From Mike Girvin New Book” →

Excel Adds Support For RegEx In XLOOKUP And XMATCH – Episode 2647

Posted on by Annie D

Microsoft Excel Tutorial: Using a RegEx match in Excel XLOOKUP
To download the workbook from today: https://www.mrexcel.com/youtube/NZybVpM_dI4/

Welcome to another exciting update from MrExcel! In today’s video, we’re diving into a major breakthrough: RegEx support in XLOOKUP and XMATCH, which was just rolled out on August 14th, 2024. This new feature has been made available to 50% of insiders and promises to revolutionize how you work with data in Excel. If you enjoy the content, don’t forget to hit that Like button below to help more people discover this video!

We’ll start by exploring the new RegEx functionality in XLOOKUP Continue reading “Excel Adds Support For RegEx In XLOOKUP And XMATCH – Episode 2647” →

Microsoft Excel Where Did They Move 3D Map Feature – 2646

Posted on by Annie D

Microsoft Excel Tutorial: Where is the 3D Map feature in Excel?

I am a fan of 3D Maps, formerly called Power Map, formerly called GeoFlow. But today, the feature is no longer on the Insert tab. Where did they move it?

Go to the Data Tab. Look towards the right to the Data Tools group. In that group, find the Data Model drop-down. Open that drop-down and there you will find 3D Map.

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

Excel Which Invoices Add Up To This Check Amount – 2658

Posted on by Annie D

Microsoft Excel Tutorial: Which Invoices Add Up To This Check Amount – Faster method using Python in Excel.

To download the workbook from today: https://www.mrexcel.com/youtube/6IohCo5KP9Q/

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 Continue reading “Excel Which Invoices Add Up To This Check Amount – 2658” →

Excel Copilot First Look August 2024 – Episode 2648

Posted on by Annie D

Microsoft Excel Tutorial: First Look at Copilot in Excel, recorded late August 2024

In today’s video, I dive into my very first experience with the new Copilot feature in Excel on Windows. This is exciting stuff, folks! Imagine Bobby Moynihan’s SNL characters bringing some sass to Excel—because that’s exactly what it felt like working with this tool! 😄 Copilot has some cool new tricks up its sleeve, like no longer needing your data to be in a table format. It can now work with regular ranges, making things so much easier for all of us Excellers.

We’re talking about features like Suggest Continue reading “Excel Copilot First Look August 2024 – Episode 2648” →

Mind-Blowing! Using Copilot Outside of Excel to Analyze Financial Statements! Episode 2652

Posted on by Annie D

Microsoft Excel Tutorial: Using Copilot Outside of Excel to Analyze Financial Statements!

In today’s video, we dive into something that completely blew me away – analyzing financial statements using Microsoft Copilot but not in Excel! You won’t believe what Copilot can do with financial data when working outside of Excel, like in OneDrive or Word.

We’re taking three financial statements (Income Statement, Balance Sheet, and Cash Flow) and letting Copilot generate summaries, calculate financial ratios, and even provide a financial outlook. It’s a game changer for anyone working with complex datasets!

A shoutout to Dr. Nitin, who first showed me this Continue reading “Mind-Blowing! Using Copilot Outside of Excel to Analyze Financial Statements! Episode 2652” →