I Gave ChatGPT a Messy Excel File. Here's What Happened — Transcript

Full transcript

1. 0:00Most people open ChatGPT, drop in an Excel file, and type two words: "Analyze this."
2. 0:07And what comes back might look right, but would you put it in front of your manager?
3. 0:12I want to test it out and see if the answers are actually correct,
4. 0:16and I'm not going to give it an easy case.
5. 0:18I have a sales file with multiple sheets that are all related. It will need a lot
6. 0:23of calculations that don't exist in the file, so GPT has to figure those out.
7. 0:27I'll also share my suggested workflow and prompts
8. 0:30so you know whether GPT is on the right track or if it's completely messing up.
9. 0:35Here's our sales workbook. The main sheet is our Transactions, where everything happens.
10. 0:41But look at this. It's all codes. On its own, this tells us nothing.
11. 0:46The actual meaning is scattered across these other sheets.
12. 0:50So, if you take a look at ProductNum, for example,
13. 0:53this column only has the product number. To get more information about the product,
14. 0:59we have to look up this product number from the ProductCode column.
15. 1:05So, notice the column is also called differently here in the Product sheet. This way, we can find
16. 1:11more information about the product, like which department it belongs to,
17. 1:14what's the category, subcategory, and the cost of this product.
18. 1:19And the same applies for customer number. We have to look this up on the Customer sheet,
19. 1:25so we know which city or state and country the customer lives in.
20. 1:30And then we have Transaction Type Number. This information is sitting on another sheet.
21. 1:36It tells us whether the purchase was a store purchase or an online purchase.
22. 1:42Now, on top of this, we are just tracking quantity and sales price. So,
23. 1:47if I want to know total sales, GPT has to know that it has to multiply quantity by sales price.
24. 1:54If I want to get the total cost of a purchase, GPT has to know to multiply
25. 1:59quantity by looking up the product from here and then grabbing the cost.
26. 2:05Okay, so to answer anything useful,
27. 2:07ChatGPT has to connect these on its own, the same way that you would.
28. 2:12Okay, so let's start. I'm going to close the file. I'll drag and drop the file in the chat.
29. 2:18And I'm not going to type "Analyze this" because,
30. 2:20with a file like this, I don't want ChatGPT jumping straight to insight.
31. 2:25First, I want to make sure it understands the file.
32. 2:27So, I'm going to give it a short set of rules, just guardrails.
33. 2:32Answer in chat only.
34. 2:33Don't create or export files unless I ask.
35. 2:36Use the master sheets to decode codes where needed.
36. 2:39Briefly explain your method before each main analysis.
37. 2:43Reconcile grouped totals back to the transaction-level data.
38. 2:48This gives GPT a way to check its answers.
39. 2:51If the workbook can't answer something, tell me instead of guessing.
40. 2:55Now, inspect the workbook and tell me what sheets are included,
41. 2:58how they connect, and any issues I should know before analysis,
42. 3:03like missing values, dates are wrong, or unmatched codes, or anything else.
43. 3:09Now, while this is thinking, I just want to say: be careful what you upload.
44. 3:12Don't put sensitive company data into ChatGPT, right?
45. 3:15If it's not something that you share with an outsider,
46. 3:18don't upload it unless you anonymize it first.
47. 3:21Basically, stick to your company's AI guidelines, and also check your hidden sheets as well.
48. 3:26Don't forget about the hidden sheets.
49. 3:28Okay, so let's see what we get.
50. 3:30So, it ran for a little over two minutes. It inspected the workbook,
51. 3:34treated Sales as the transaction-level table, and the master sheets are these ones.
52. 3:40Okay, it found all the sheets. It's giving me the number of rows on each.
53. 3:46This looks good.
54. 3:47And it's added some notes over here.
55. 3:50There are no formulas in the workbook.
56. 3:53Now, how do sheets connect?
57. 3:55Yeah, it's picked it up correctly.
58. 3:56The Sales column ProductNum matches to ProductCode in the Product sheet.
59. 4:02It looks like the rest are correct as well.
60. 4:05It's done some reconciliation checks down here and listed a bunch of issues to know before analysis.
61. 4:12One missing customer last name.
62. 4:14Okay, that's good to know.
63. 4:16And we also have a duplicate transaction number.
64. 4:19That's also good, something we might want to follow up on.
65. 4:22There are no unmatched codes, which is good news.
66. 4:24And some master records are unused, which is fine.
67. 4:28Bottom line, the workbook is usable for analysis.
68. 4:32Now, before I dive into any business questions,
69. 4:35I just want to make sure that GPT can read the basics correctly.
70. 4:40It looks like it understands the file, but I want to be sure because if ChatGPT
71. 4:44and Excel don't agree on the simple stuff, I'm definitely not trusting it with the hard stuff.
72. 4:50So, I'm going to ask it for a few check numbers, things I can easily verify myself in Excel.
73. 4:56I'll just use this prompt.
74. 4:58Basically, I want it to give me three useful check
75. 5:01numbers from the workbook that I can quickly verify in Excel.
76. 5:05Pick checks that confirm the data was read correctly.
77. 5:08For each one, tell me which sheet and column you used and how I can verify it in Excel.
78. 5:14So, let's send this off.
79. 5:15A quick note while this runs: your screen
80. 5:18might look a little different depending on your ChatGPT plan.
81. 5:22The free version works. You'll just hit your limits faster.
82. 5:25I'm using Pro, and I'm on Thinking Mode here because,
83. 5:29for a file like this, I want it careful, not fast.
84. 5:33Okay.
85. 5:33So, here's the first check.
86. 5:34We should do the transaction row count.
87. 5:37The result should be this.
88. 5:39And the way I can check it is to use this formula.
89. 5:42Well, that's pretty cool.
90. 5:44The second check is for dates.
91. 5:46The earliest date is this, and the latest date is this.
92. 5:50Here's a way I can check it.
93. 5:51And the last check is the total sales amount.
94. 5:55I need to multiply quantity by sales price.
95. 5:58And a way I can do it quickly is through this formula.
96. 6:01The total should be this.
97. 6:03So, it agrees with me on the basics.
98. 6:06Now, let's move on to the hard stuff.
99. 6:08This is the first real test.
100. 6:11For 2026, show total sales for in-store versus online, broken down by state/region.
101. 6:17Sort by total sales, highest first.
102. 6:21Okay, let's send this off and see what we get.
103. 6:25Now, remember, the transaction sheet only has customer numbers and type numbers.
104. 6:30To answer this, ChatGPT has to connect to the Customer master
105. 6:34and to the Transaction Type master on its own.
106. 6:37If it can't join those, it falls apart right there.
107. 6:41And also, it has to filter the data to 2026 only.
108. 6:45So, did it do it correctly?
109. 6:48Well, this looks pretty neat.
110. 6:49We have the different regions, store purchase, online purchase, total sales.
111. 6:54It's sorted properly.
112. 6:55These are the totals.
113. 6:57And down here, GPT has done a reconciliation check as well.
114. 7:04Okay, so what do we have?
115. 7:06The first region is California.
116. 7:09Well, let's cross-check with Excel.
117. 7:11So, I've built a data model in Excel and used Power Pivot to create the same report.
118. 7:17What do we get?
119. 7:19Everything matches perfectly.
120. 7:22Now, that's pretty impressive because GPT joined these sheets completely on its own.
121. 7:28Okay, last business question, and this is the one
122. 7:31I'm not sure if it's going to get right because it's really tricky.
123. 7:35It's a profitability question for 2026.
124. 7:38Are there any products with a negative profit margin?
125. 7:42If yes, show the five worst products by profit margin
126. 7:46and briefly explain the margin formula you used.
127. 7:49Here, the calculation is a bit trickier because cost is sitting in the Product table.
128. 7:55It has to pull the pieces together and do all the math.
129. 7:58Let's see if it manages.
130. 8:00Notice I'm asking for the margin formula it used because I don't just want the answer.
131. 8:06I want to see how it got there.
132. 8:08Okay, so here we have the formulas it used.
133. 8:11They all look good.
134. 8:12We have 12 products that had a negative profit margin in 2026.
135. 8:18And these are the top five.
136. 8:21So, here we have sales, cost, profit, and profit margin.
137. 8:27And down here, it's done a reconciliation.
138. 8:30Now, do these match if I create the report from scratch with my data model?
139. 8:37Here's my report.
140. 8:38In total, we have 12 products with a negative profit margin.
141. 8:43The first five are these.
142. 8:46ChatGPT did a great job here.
143. 8:49Now, one last thing I want to ask it is a question
144. 8:52it shouldn't answer because the answer is not in the file.
145. 8:56Which sales rep had the highest total sales in 2026?
146. 9:00It seems like a logical question because we're dealing with a sales file,
147. 9:04but we don't have any information about sales reps in the file.
148. 9:09So, what does GPT do?
149. 9:11Does it invent an answer, or does it say this information doesn't exist?
150. 9:17It took a little bit of time, and it tells me that the workbook cannot
151. 9:21answer the question as it stands because there is no sales rep field in any sheet.
152. 9:27That's great.
153. 9:28Okay, so can ChatGPT read a complex Excel file?
154. 9:32Yes, it can.
155. 9:33And it can do a really good job if you stick to a logical workflow.
156. 9:37You set the rules first, make it inspect the file, check the basic
157. 9:42numbers before you trust anything, and make it reconcile back to the source every time.
158. 9:47This helps reduce errors.
159. 9:49Now, personally, for important things, I will cross-check myself,
160. 9:53and I can do it because I know how to do it in Excel.
161. 9:56For Excel, this was a simple data model.
162. 9:59Now, you can build really complex ones, too.
163. 10:02If you want to learn this skill,
164. 10:04that's exactly what we teach in our Power Pivot and Data Modeling course.
165. 10:07Links in the description if you're interested.
166. 10:10Thank you for being here and for watching all the way through.
167. 10:13I know it was quite a long video.
168. 10:15I hope you find it useful.
169. 10:17I'll catch you in the next video.