# I Gave ChatGPT a Messy Excel File. Here's What Happened Channel: Leila Gharani Video: https://www.youtube.com/watch?v=0PzmVXPdkuk Duration: 11 min Language: English Words: 1686 Transcript page: https://viewrankai.com/tools/youtube-transcript/0PzmVXPdkuk --- [0:00] Most people open ChatGPT, drop in an Excel file, and type two words: "Analyze this." And what comes back might look right, but would you put it in front of your manager? I want to test it out and see if the answers are actually correct, and I'm not going to give it an easy case. I have a sales file with multiple sheets that are all related. It will need a lot [0:23] of calculations that don't exist in the file, so GPT has to figure those out. I'll also share my suggested workflow and prompts so you know whether GPT is on the right track or if it's completely messing up. Here's our sales workbook. The main sheet is our Transactions, where everything happens. But look at this. It's all codes. On its own, this tells us nothing. [0:46] The actual meaning is scattered across these other sheets. So, if you take a look at ProductNum, for example, this column only has the product number. To get more information about the product, we have to look up this product number from the ProductCode column. So, notice the column is also called differently here in the Product sheet. This way, we can find more information about the product, like which department it belongs to, [1:14] what's the category, subcategory, and the cost of this product. And the same applies for customer number. We have to look this up on the Customer sheet, so we know which city or state and country the customer lives in. And then we have Transaction Type Number. This information is sitting on another sheet. It tells us whether the purchase was a store purchase or an online purchase. Now, on top of this, we are just tracking quantity and sales price. So, [1:47] if I want to know total sales, GPT has to know that it has to multiply quantity by sales price. If I want to get the total cost of a purchase, GPT has to know to multiply quantity by looking up the product from here and then grabbing the cost. Okay, so to answer anything useful, [2:07] ChatGPT has to connect these on its own, the same way that you would. Okay, so let's start. I'm going to close the file. I'll drag and drop the file in the chat. And I'm not going to type "Analyze this" because, with a file like this, I don't want ChatGPT jumping straight to insight. First, I want to make sure it understands the file. [2:27] So, I'm going to give it a short set of rules, just guardrails. Answer in chat only. Don't create or export files unless I ask. Use the master sheets to decode codes where needed. Briefly explain your method before each main analysis. Reconcile grouped totals back to the transaction-level data. This gives GPT a way to check its answers. [2:51] If the workbook can't answer something, tell me instead of guessing. Now, inspect the workbook and tell me what sheets are included, how they connect, and any issues I should know before analysis, like missing values, dates are wrong, or unmatched codes, or anything else. Now, while this is thinking, I just want to say: be careful what you upload. [3:12] Don't put sensitive company data into ChatGPT, right? If it's not something that you share with an outsider, don't upload it unless you anonymize it first. Basically, stick to your company's AI guidelines, and also check your hidden sheets as well. Don't forget about the hidden sheets. Okay, so let's see what we get. So, it ran for a little over two minutes. It inspected the workbook, [3:34] treated Sales as the transaction-level table, and the master sheets are these ones. Okay, it found all the sheets. It's giving me the number of rows on each. This looks good. And it's added some notes over here. There are no formulas in the workbook. Now, how do sheets connect? Yeah, it's picked it up correctly. [3:56] The Sales column ProductNum matches to ProductCode in the Product sheet. It looks like the rest are correct as well. It's done some reconciliation checks down here and listed a bunch of issues to know before analysis. One missing customer last name. Okay, that's good to know. And we also have a duplicate transaction number. That's also good, something we might want to follow up on. [4:22] There are no unmatched codes, which is good news. And some master records are unused, which is fine. Bottom line, the workbook is usable for analysis. Now, before I dive into any business questions, I just want to make sure that GPT can read the basics correctly. It looks like it understands the file, but I want to be sure because if ChatGPT [4:44] and Excel don't agree on the simple stuff, I'm definitely not trusting it with the hard stuff. So, I'm going to ask it for a few check numbers, things I can easily verify myself in Excel. I'll just use this prompt. Basically, I want it to give me three useful check numbers from the workbook that I can quickly verify in Excel. [5:05] Pick checks that confirm the data was read correctly. For each one, tell me which sheet and column you used and how I can verify it in Excel. So, let's send this off. A quick note while this runs: your screen might look a little different depending on your ChatGPT plan. The free version works. You'll just hit your limits faster. I'm using Pro, and I'm on Thinking Mode here because, [5:29] for a file like this, I want it careful, not fast. Okay. So, here's the first check. We should do the transaction row count. The result should be this. And the way I can check it is to use this formula. Well, that's pretty cool. The second check is for dates. The earliest date is this, and the latest date is this. [5:50] Here's a way I can check it. And the last check is the total sales amount. I need to multiply quantity by sales price. And a way I can do it quickly is through this formula. The total should be this. So, it agrees with me on the basics. Now, let's move on to the hard stuff. [6:08] This is the first real test. For 2026, show total sales for in-store versus online, broken down by state/region. Sort by total sales, highest first. Okay, let's send this off and see what we get. Now, remember, the transaction sheet only has customer numbers and type numbers. To answer this, ChatGPT has to connect to the Customer master [6:34] and to the Transaction Type master on its own. If it can't join those, it falls apart right there. And also, it has to filter the data to 2026 only. So, did it do it correctly? Well, this looks pretty neat. We have the different regions, store purchase, online purchase, total sales. It's sorted properly. These are the totals. [6:57] And down here, GPT has done a reconciliation check as well. Okay, so what do we have? The first region is California. Well, let's cross-check with Excel. So, I've built a data model in Excel and used Power Pivot to create the same report. What do we get? Everything matches perfectly. Now, that's pretty impressive because GPT joined these sheets completely on its own. Okay, last business question, and this is the one [7:31] I'm not sure if it's going to get right because it's really tricky. It's a profitability question for 2026. Are there any products with a negative profit margin? If yes, show the five worst products by profit margin and briefly explain the margin formula you used. Here, the calculation is a bit trickier because cost is sitting in the Product table. [7:55] It has to pull the pieces together and do all the math. Let's see if it manages. Notice I'm asking for the margin formula it used because I don't just want the answer. I want to see how it got there. Okay, so here we have the formulas it used. They all look good. We have 12 products that had a negative profit margin in 2026. [8:18] And these are the top five. So, here we have sales, cost, profit, and profit margin. And down here, it's done a reconciliation. Now, do these match if I create the report from scratch with my data model? Here's my report. In total, we have 12 products with a negative profit margin. The first five are these. [8:46] ChatGPT did a great job here. Now, one last thing I want to ask it is a question it shouldn't answer because the answer is not in the file. Which sales rep had the highest total sales in 2026? It seems like a logical question because we're dealing with a sales file, but we don't have any information about sales reps in the file. [9:09] So, what does GPT do? Does it invent an answer, or does it say this information doesn't exist? It took a little bit of time, and it tells me that the workbook cannot answer the question as it stands because there is no sales rep field in any sheet. That's great. Okay, so can ChatGPT read a complex Excel file? [9:32] Yes, it can. And it can do a really good job if you stick to a logical workflow. You set the rules first, make it inspect the file, check the basic numbers before you trust anything, and make it reconcile back to the source every time. This helps reduce errors. Now, personally, for important things, I will cross-check myself, [9:53] and I can do it because I know how to do it in Excel. For Excel, this was a simple data model. Now, you can build really complex ones, too. If you want to learn this skill, that's exactly what we teach in our Power Pivot and Data Modeling course. Links in the description if you're interested. [10:10] Thank you for being here and for watching all the way through. I know it was quite a long video. I hope you find it useful. I'll catch you in the next video. --- About this transcript Read from YouTube's own caption track and laid out by ViewRank AI (https://viewrankai.com). ViewRank AI finds the videos already beating a creator's own average on Instagram, TikTok and YouTube Shorts, transcribes them from the audio itself in more than 60 languages, and turns what worked into new ideas and scripts. Free transcript tools, no account needed: https://viewrankai.com/tools How to read any video this way: https://viewrankai.com/llms.txt