Category: Power Pivot

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” →

Excel Copilot With Excel With Python – First Look – Episode 2653

Posted on by Annie D

Microsoft Excel Tutorial: Excel Copilot With Excel With Python
An exciting announcement from Satya Nadella this morning.

In today’s video, we’re diving into something groundbreaking! It’s September 16th, 2024, and right now, Satya Nadella is still talking at Microsoft’s big event, but the exciting news is already here—Copilot in Excel is now generally available, and even better, Copilot with Excel with Python for Advanced Analysis is in preview!

📊 What’s in this Episode? I’ve got 18,000 customer records categorized into four groups—Excel Books, Power BI, Power Query, and VBA—and I’m using Python’s K-means clustering to create six clusters, giving me a clearer picture Continue reading “Excel Copilot With Excel With Python – First Look – Episode 2653” →

Excel New Range Reference For Dynamic Sized Ranges – Episode 2651

Posted on by Annie D

Microsoft Excel Tutorial: Excel New Range Reference For Dynamic Sized Ranges and TRIMRANGE function.

In today’s video, we’re diving into an exciting new feature in the Excel beta that’s bound to change the way we work with dynamic ranges. Microsoft has introduced a groundbreaking method for referencing ranges using a simple dot notation, or by leveraging the new TRIMRANGE function. Say goodbye to inefficient full-column references and hello to cleaner, more efficient formulas. Stick around as we explore how these new features can streamline your Excel workflows!

First up, let’s talk about the TRIMRANGE function. This new function allows you to specify Continue reading “Excel New Range Reference For Dynamic Sized Ranges – Episode 2651” →