4
Turning Excel from a Chore into a Strategy Tool 7:52 Lena: Okay Miles, Excel. This is where I usually hit a wall. I know how to make a basic table, but as soon as someone mentions "Vlookup" or "Pivot Tables," my brain starts to fog over.
8:05 Miles: You’re definitely not alone. But the cool thing about the 2026 version of Claude in Excel is that it’s no longer just giving you a formula to copy-paste. It’s actually building the models.
8:16 Lena: So, Use Case Number Seven?
8:19 Miles: Use Case Number Seven is Dynamic Formula Generation and Refactoring. Imagine you have a messy sheet with three different "Sales" tabs. You can highlight a range and tell Claude, "Create a rolling 12-month moving average for the 'North Region' category, and make sure it handles the gaps in the data." Claude will write a complex `LET` or `LAMBDA` formula—things that are a nightmare to get right manually—and apply it directly to the cells.
8:43 Lena: I remember reading that Claude is actually better at this because it can iterate. If the formula throws an error, it sees it and fixes it?
0:46 Miles: Exactly. It doesn't just "guess." It uses a "Plan, Execute, Validate" loop. That brings us to Use Case Number Eight: Automated Pivot Table Creation and Analysis. You can just say, "Create a pivot table that shows revenue by salesperson for the last quarter, and tell me who had the highest growth compared to last year." Claude builds the table and then *explains* the trends it sees.
9:14 Lena: That’s the "Judgment vs. Mechanics" split we saw in the sources. I delegate the building to Claude, but I keep the judgment on whether that growth is actually sustainable.
9:23 Miles: Spot on. Use Case Number Nine is Macro Modernization. If your company is still running on old, clunky VBA macros from 2012, you can paste that code into Claude and ask it to "Rewrite this as a modern Office Script using TypeScript." It makes the automation faster, more secure, and it works in Excel for the Web.
9:43 Lena: Oh, that’s huge for legacy businesses. What about the people who aren't "data people"?
9:49 Miles: For them, Use Case Number Ten is Natural Language Data Cleaning. You know when you get a CSV where the dates are all in different formats and the names are sometimes "Last, First" and sometimes "First Last"? You can just tell Claude, "Clean this up. Standardize all dates to ISO format and make sure all names follow the 'First Last' pattern."
10:07 Lena: That alone would save me hours of "Find and Replace."
10:10 Miles: It’s a total lifesaver. Use Case Number Eleven is Scenario Modeling. You can ask, "Based on this budget, show me what happens if our shipping costs increase by 15% but our sales volume drops by 5%." Claude will create the new scenario columns and even generate a chart to visualize the impact.
10:26 Lena: And Use Case Number Twelve?
10:29 Miles: Cross-Workbook Data Consolidation. This is where the M365 Connector really shines. You can tell Claude, "I have five different regional sales workbooks in the '2026 Sales' folder on SharePoint. Pull the totals from each and create a master summary sheet here."
10:45 Lena: Wow. So it’s navigating SharePoint, opening the files, extracting the data, and bringing it back to your active sheet.
4:17 Miles: Precisely. It’s performing the role of a junior analyst, but it does it in seconds.
10:58 Lena: It’s interesting how these use cases are starting to blur the lines between the apps. We’re seeing data move from SharePoint to Excel seamlessly.
11:07 Miles: And that’s the goal—making M365 feel like one unified workspace instead of a collection of silos. But wait until you see what it does for presentations. That’s where the "visual" side of Claude really comes out.