How to Use Claude in Excel to Clean and Analyze Data (Full Transcript)

[00:00:00] Speaker 1: I'll show you how to use Claude directly inside Excel to clean data, fix formulas, and build models automatically. This spreadsheet is a mess. Broken formulas, inconsistent data. Let's use Claude to fix it. This video is sponsored by Anthropic, the team behind Claude. Hi, I'm Kevin, and let's dive in. Let's start by getting Claude set up in Excel. This only takes about a minute. First, open Microsoft Excel, and make sure you're on the Home tab. All the way over on the right-hand side, you'll see an icon for Add-ins. Let's click on that, and here at the top, let's search for Claude. You should see it as the best match. Then, over here, click on Add. Once it's installed, you'll either need to log in or sign up. Claude for Excel requires a paid plan. It does not work with the free version. Right over here, I'll log in. And that's it. You now have Claude in Excel. To open Claude in Excel, you can press the shortcut key Ctrl-Alt-C, and here we get a dialog telling us that that shortcut is currently being used for multiple features. Let's set it to show the Claude sidebar, and then click on OK. Over on the right-hand side, let's close Claude. Now, alternatively, you can also open up Claude by clicking on the icon on the ribbon. So here, I'll click there, and that also opens up the side pane. If, for whatever reason, the shortcut key did not work for you, you can always reset it. Up on the top search field, type in Reset Office Add-in Shortcut Preferences, and then, right here, select this option. When you click on that, now, when we press Ctrl-Alt-C, I get that prompt again, and here, I can set it to the Claude sidebar. I'll click on OK, and there we see it. This opens a sidebar directly inside your spreadsheet, where, down below, you can now type what you want it to do. All right, let's start with this spreadsheet. If you want to follow along, you can download this spreadsheet from the link right down below in the description. Here, I have sales data for the Kevin Cookie Company, and you'll notice that it's pretty messy. If we look here at the Order Date column, you'll notice that we have inconsistent dates. In fact, one of these dates may not even exist. Here, we have a missing customer name. We have different casing for all the different product names and the same goes for the sales rep. And, in fact, if I look closely, I think there may even be a duplicate order entered here, and that could mess up our reporting. We definitely don't want that. Now, normally, this would take quite a bit of time and manual work to clean this up. Instead, let's have Claude take a first pass at fixing everything. Let's highlight this entire table of data, and then let's use that shortcut key that we just learned, Control-Alt-C, and that opens up the Claude pane over on the right-hand side. Now, right down below, you'll see that it's going to work on the data that we just selected. Right down at the bottom, let's type in the first prompt. Clean up this dataset, standardize the date formats, remove duplicates, and fix any obvious issues. Once you type that in, let's send that through. On the right-hand side, you can now see it working through the sheet. If it needs to make any changes, it'll ask for your permission first. It looks like it identified the first issue. Here, it wants to delete range 10, or row number 10. Now, if I look at row number 10, this is the duplicate entry, so I'm okay removing that. Now, the nice thing is Claude asks for permission before it makes any destructive changes to the sheet. I'll click on Allow. All right, let's take a look at what Claude did. Claude has now gone through the sheet. Over here on the right-hand pane, at the very top, we can review all the different issues that it found, and if we scroll down, we can also see the changes it made, and there were quite a few in this dataset. Looking at the sheet now, it's also much cleaner. Here, if we look at the dates, they're all consistent. The customer names are properly formatted, and the same goes for the product names and the sales reps. You'll also notice there are comments in the sheet. These are changes Claude made, but flagged for us to review. Earlier, this date was entered as 8-1, but based on the sequence of dates, it should be 1-8. Claude made that correction and flagged it. The same thing happened here. What I like about this is, instead of guessing silently, it flags anything that needs review, like these invalid dates. Looking at the customer names, this one is still blank, which makes sense, since Claude wouldn't know what belongs here, so I'll go ahead and fill that in. It also flagged this quantity. Earlier, this was blank, and Claude inferred the value based on the unit price and total. Now, if I look at the total column, there's one more nice improvement. Originally, these values were hard-coded, so if I changed the quantity, the total wouldn't update, but now Claude has converted this into a formula, so if I change this to, let's say, a three, you can see the total updates automatically, and I'll change that back to a two. Now that the data is all cleaned up, let's take a look at the formulas. At first glance, everything here looks fine. The totals are all filled in, and there aren't any obvious errors, but just to simulate a real-world scenario, let's go ahead and change this formula here. Now, you'll notice the total is the quantity by the unit price, but let's make a little correction here. Let's change that to the unit price times the unit price. Now, everything still looks correct at a glance, but the math is actually off. Let's have Claude validate everything for us. Here again, I'll highlight this entire table, and right up on top, let's click on Claude, and down below, we can now type in a prompt. I'll say, validate the formulas in this sheet, identify any issues, and fix them. Let's run that. All right, let's take a look. Right here, it looks like it caught the issue, and then it also made the fix, so everything is now calculated properly, and here, if I click into the cell, it looks like it's returned it to the quantity times the unit price. Now, this is a great example of where everything can look correct at a glance, but there may still be some underlying issues. Now, we have clean data, and we can also trust that the calculations are correct. Now that we have clean data, and we can trust the calculations, let's take this a step further and actually build something on top of it. Instead of creating a report from scratch, let's have Claude generate a quick summary for us. Right up on top, let's open Claude, and down below, I'll type in my prompt. Create a summary of total sales by product and region, and add a chart to visualize the results. Let's run that. It created a summary table showing total sales here by product, and right down below, we also have it by region, and to the right of that, we also have associated charts helping me visualize the data. Now, the nice thing is this is all built directly in Excel. You'll notice when I click into the table, this is a formula that ties back to the underlying data on sheet number one, so if I modify any of it, that'll flow through to the table, and then also the chart. Now, personally, I prefer using pivot tables since I think they're more flexible, so let's actually see if we could have Claude convert it to a pivot table. Back over here, let's follow up by saying, can you turn this into a pivot table instead, and then let's run that. Here, it asks me a follow-up question. Let's go with two separate pivot tables for each view. Here, it wants to replace the current data that's on this sheet with the pivot tables. Let's go ahead and allow that, and there we go. We have one pivot table for product and one pivot table for region, and then we also have associated pivot charts. From here, you can keep iterating with Claude until you get exactly what you're looking for. Now that we have a clean data set and a report, let's take this another step further and actually understand what's going on in the data. Instead of manually digging through the numbers, I'm going to have Claude analyze all of this for us. Let's open Claude. I'll press the shortcut key, and right down below, I'll type in my prompt. Analyze this data and highlight the key trends, top-performing products, and any notable patterns. Let's run that. Let's take a look. I'll scroll up, and right here, it summarizes the key insights from the data, including information on the product mix. Right down here, we could also see which products sell the most, chocolate chip, or the least, snickerdoodle. If we scroll up a little bit, we could also click into the summary, and this brings me to the summary sheet, and here, it constructed a table with some of the key metrics. This way, it helps me quickly understand what's happening in the business, and of course, if I want to go deeper, right over here, I can continue asking follow-up questions. This makes it really easy to go from just all of my raw data to actual insights, and this is just scratching the surface. Claude also supports things like custom instructions. Right up here, I'll click on the three dots, and then go into settings, and here, I could type them in. For example, if you always want results formatted in a certain way, like including a thousand separator, you could set that once. Here, I'll go back. Now, down below, you could also click on this plus icon, and you could turn on something called connectors. This allows you to pull in external data from sources like FactSet or Moody's, and you also have built-in skills. Now, these are pre-built workflows that you can trigger with a command. For example, earlier, when we cleaned the data, you could also use a skill that's already optimized for that task. Now, you could add a skill here, or in the prompt field, you can simply enter the forward slash, and here, you get access to all of your different skills, but even just using the simple prompts like we did here can help you save a lot of time. So, in just a few steps, we cleaned up the data, verified the formulas, built a report, and generated insights, all directly inside Excel using Cloud. If you want to try this for yourself, I've included a link in the description and also in the comments. Thanks for watching, and I'll see you in the next video.