Category: Power Pivot

Excel Pivot Table Number Format Copied From Source Data Episode 2674

Posted on by Annie D

🚀 *Unbelievable Excel Update!* 🚀

Pivot Table numeric formatting is now automatically coming from the grid! 🎉 Microsoft didn’t announce this (that I’ve seen), but it just *worked* this morning. No more manually formatting numbers in your Pivot Tables—Excel is pulling it straight from the source data.

✅ Thousands separator? ✔️
✅ Currency symbols? ✔️
✅ No extra steps? ✔️

Watch as I demo this game-changing update with a quick dataset. This small but mighty improvement is a *huge* win for efficiency!

💡 Have you seen this in your version of Excel yet? Let Continue reading “Excel Pivot Table Number Format Copied From Source Data Episode 2674” →

Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673

Posted on by Annie D

Microsoft Excel Tutorial: Excel Clean Data with Copilot

Microsoft Excel just introduced the Clean Data feature in Win32, bringing AI-powered data cleaning straight to the desktop! This tool, previously available in Excel Online since October 2024, promises to detect and fix inconsistencies in your dataset—like inconsistent text, formatting issues, and more. But does it really work as expected?

In this episode, I put Clean Data to the test with a variety of messy datasets. It does well in some areas, like recognizing spelling variations and fixing number formats. However, it struggles with company name inconsistencies, date issues, and partial data cleaning—sometimes catching Continue reading “Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673” →

Excel Three Bots Versus Three Gurus Episode 2672

Posted on by Annie D

Microsoft Excel Tutorial: Solving a Text Comparison Problem: 3 AI Bots versus 3 Humans.

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

The battle is on! In Episode 2672, we’re pitting three AI bots (Deep Seek, Copilot, and Beta Copilot with Python) against three (and a half) human Excel gurus (Rico, XLLambda, and Geert). Who will come up with the best solution for an Excel text comparison problem?

This challenge comes from Brian, who wanted to compare two text strings and extract the differences. Seems simple, right? Well, not quite! My initial formula had some issues, so I turned to Continue reading “Excel Three Bots Versus Three Gurus Episode 2672” →

Excel Copilot Versus Deep Seek Head to Head – Episode 2671

Posted on by Annie D

Microsoft Excel Tutorial: Deep Seek vs. Copilot: Head-to-Head Excel Formula Challenge!

Welcome to Learn Excel from MrExcel Podcast, Episode 2671! Today, we’re putting two AI-powered assistants to the test—Deep Seek vs. Copilot—to see which one performs better in Excel formula creation and formula explanation. Who will come out on top? Stick around to find out!

Test #1: Formula Creation

I started with a simple yet classic Excel challenge: detecting prime numbers in a column of integers. Deep Seek provided both a helper column approach and a single-cell array formula, while Copilot struggled due to the requirement for Auto Save to be turned on. Continue reading “Excel Copilot Versus Deep Seek Head to Head – Episode 2671” →

Excel The Mysterious Chart Label That Wouldn’t Update – Solved! – Episode 2670

Posted on by Annie D

Microsoft Excel Tutorial: The Mysterious Chart Label That Wouldn’t Update – Solved!

A completely evil Excel problem! A chart label refuses to update, no matter what you do. My friend Kelly sent me this chart, and everything seemed fine—except for one stubborn label that always says “Test 1.” Even after checking that the labels are correctly linked to a range using “Value From Cells,” this one label remains broken. After trying everything, I had to reach out to someone on the Excel team, and they finally gave me the answer.

Turns out, the label became disconnected from the data, and there’s a Continue reading “Excel The Mysterious Chart Label That Wouldn’t Update – Solved! – Episode 2670” →

Excel Change 3D Map From City To State – Episode 2669

Posted on by Annie D

Microsoft Excel Tutorial: Use 3D Maps to summarize city data at the state level.

Got sales data by city but need to display it by state in Excel’s 3D Maps? A.K. asked if there’s a way to sum sales at the state level instead of individual cities—yes, and it’s easy to do! In this tutorial, I’ll walk you through the steps to modify your 3D Map to show sales totals by state.

By default, Excel’s 3D Map plots data at the city level. If you want to switch to state-level mapping, simply adjust the location field to use State instead of Continue reading “Excel Change 3D Map From City To State – Episode 2669” →

How to Show “Discount 10%” in Excel Using Formulas & Custom Formatting – Episode 2668

Posted on by Annie D

Microsoft Excel Tutorial: Formula To Show Word Next To Discount Percentage. How to Show “Discount 10%” in Excel Using Formulas & Custom Formatting.

Today’s question comes from Roo: How can we modify a dropdown list of discount percentages (10%, 20%, 30%) to display as “Discount 10%” on an invoice? This video covers multiple ways to achieve this, whether your percentages are stored as text or numbers.

First, we look at a simple formula approach. By using a basic concatenation formula with an ampersand (`&`), we can combine the word “Discount” with the selected percentage. However, if your percentages are stored Continue reading “How to Show “Discount 10%” in Excel Using Formulas & Custom Formatting – Episode 2668″ →

Excel Kill The Annoying Copilot Floating Icon – Episode 2667

Posted on by Annie D

Microsoft Excel Tutorial: Microsoft has a new Copilot icon that follows the active cell around Excel. It is super annoying. Here is how to get rid of it!

In yesterday’s video, DR Steele asked a great question: How do we remove this annoying Copilot icon that won’t go away? Microsoft doesn’t give us a global setting to turn it off, and every time you open a new workbook, it comes right back. Frustrating, right? Well, I’ve got a workaround!

Thanks to John Acampora, we found a way to manually hide the icon using a keyboard shortcut. But that’s not a permanent fix. Continue reading “Excel Kill The Annoying Copilot Floating Icon – Episode 2667” →

Excel Split Date Time Into Columns For Date And Then Time Episode – 2666

Posted on by Annie D

Microsoft Excel Tutorial: Split Date Time into Columns for Date and then Time.

In this video, we tackle an intriguing Excel challenge from a real-world job interview scenario. Imagine being asked to split a column of date and time into separate date and time fields, build a pivot table, and create a report limited to certain conditions—class equals 1 and days are Tuesday or Thursday. Sounds tricky, right? Let’s break it down step by step and explore multiple approaches!

First, we’ll discuss how to split the date and time using simple formulas like =INT(A2) for the date and =A2-D2 for the time, Continue reading “Excel Split Date Time Into Columns For Date And Then Time Episode – 2666” →

Excel – How to Quickly Convert Text URLs to Clickable Hyperlinks in Excel with VBA – Episode 2665

Posted on by Annie D

Microsoft Excel Tutorial: Jody needs a way to convert a column of text URLS into clickable hyperlinks in Excel.

Here is the macro:
Sub MakeSelectedHyperlinksHot()
For Each Cell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:=Cell.Value, TextToDisplay:=Cell.Value
Next Cell
End Sub

In this video, we tackle a common Excel issue: converting thousands of text strings that look like URLs into clickable hyperlinks. Jody reached out with a situation—5,000 rows of exported data in Excel, all containing text that resembles hyperlinks. The manual method of right-clicking each cell and creating hyperlinks one by one is tedious and inefficient. Is Continue reading “Excel – How to Quickly Convert Text URLs to Clickable Hyperlinks in Excel with VBA – Episode 2665” →

Excel I Need A Once In A Lifetime Formula For 2025 – Episode 2665

Posted on by Annie D

Microsoft Excel Tutorial: Looking for a great formula to format 2025 cells.

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

Start 2025 with a bang by diving into an Excel adventure like no other! In this tutorial, we explore the fascinating concept of “square years”—numbers that are perfect squares of whole numbers—and why 2025 (45 squared) is truly a once-in-a-lifetime event. We’ll walk you through formulas and creative techniques to visualize this unique year in Excel, while tying it back to historical milestones like the California Gold Rush and the completion of the Hoover Dam.

Discover how to create Continue reading “Excel I Need A Once In A Lifetime Formula For 2025 – Episode 2665” →

Conquering Excel’s Formatting Quirks with Creative Hacks – Episode 2664

Posted on by Annie D

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: Continue reading “Conquering Excel’s Formatting Quirks with Creative Hacks – Episode 2664” →