Claude in Excel: Reproducible Charts and Quick Analyses (Full Transcript)

[00:00:00] Speaker 1: So I had a researcher at one of our workshops asked me, Hey, can I use this graph that I made with chat GPT in my paper? And the answer is no, no. Why, why are you making your graph with chat GPT? Make it the right way, make it in Excel with cloud. And look, this feature just blows my mind. This is another AI game changer that just is just beautiful. So the problem with AI making figures is it's not reproducible. You can try to get the prompt and even get engineer chat GPT. Tell me the prompt that you use to make this figure so I can reproduce it. Never gets it right. It's always going to be subtly different. It's going to be frustrating. If you need a figure made based on data, not just like a graphical illustration. I think nano banana and Gemini is a Google's one does that quite well. But if you need something based on data, you need to make it an Excel for journal quality figures. For the vast majority of you, there's some exceptions. There's some biological and other software for physicists that you might use. But for most data scientists, for example, you're going to want to use Excel. And now what I'm gonna show you today with the way cloud can live inside your Excel means you can make these AI driven figures, but you can also step in and intervene yourself and it's done in a reproducible correct way where you have the data right in front of you and you haven't taken this big black box. Like what happened? How did that magic transform this data into this figure? Um, you can see that yourself. You can retrace the steps and get a whole lot more transparency, and that's what you always need with AI. You need to check it. You need to not just rely on the AI black box. You need to lift the curtain, get in there yourself, figure out what's happening and figuring out if you're happy with it. So let's go into this quick demonstration of AI cloud integration. One thing to say before diving in, not all universities or other places will allow this. Um, some for privacy reasons are blocking it. So if you're banging your head, trying to get that to work, you might have a block on your side. So here's what it is all about. Head to Claude and, uh, ask Claude about any cell formula tab, uh, update your assumptions, how to use it. And this is exactly it. It is going to live right there. Inside Excel and this pop-up tab, as I'm gonna show you in a second, can, uh, help you interpret your data, run analysis models and create figures. So you're going to go through this process. I'm not going to go through it live here, but you're going to click install. Now it is going to ask you to get it now. And you need to have a Microsoft account. You can go create this for free on Microsoft live. So if you don't have this, just go get yourself a free account. And that will also give you Excel on line where you'll be able to merge this in together, or you can do this from your desktop version of Excel. I'm going to do it on the online one, since that's going to be easier for all of you to use yourself. Okay. So once you've done that step of integration, you've got your online Excel pulled up, what you're going to do is come here to cloud. You're going to see an ad in cloud by Anthropic. You're going to pull up this little box that is going to live inside your Excel. So then you're going to want to log into your cloud account. And now it will give you options of how you want to work with cloud, all this, just put in data science, whatever you're going to do, you have different connectors in your sources. I won't get through all that, but it can help you pull in data more easily, which is also cool for certain applications. I'll turn this on and a control C gives you a hot key for it. Okay. Now let's pull up an actual sheet and let me show you the power of what it can do. Quick interruption to today's video from our sponsor. Mate, if you want to work with a real person and not AI, I would encourage you to check out our elite mentorship and support programs where we work directly together in a one-to-one capacity to help you develop a personalized plan. That's going to get you from where you are to defining a winning topic all the way through to successful publication. We go so far as to offer a publication guarantee that if you show up, you do the work, we don't do it for you. That would be unethical. Just like AI doing it for you is unethical and just not a good idea. We work together each step of the way. So you never feel lost. You never feel stuck so that you optimize your chances for success. And we guarantee we're not going to leave you hanging. If you show up and do the work, we are going to see you through to the finish line. That's a big ask. That's a big offer. And no one we know of can match this level of commitment with the real results that our students have had. If you're interested, book a call with a link below and see if you could be a good fit for us working together in an intimate way. Okay. So here I've copied in a dataset I've been working on of data breaches in healthcare and the U.S. from hacking. And suppose I want to say here I've got by year and the date, I just want to look over maybe how the number of breaches has increased over time. So let's say create a line graph depicting the rise in data breaches over time based on the year of breach. And just say each row corresponds to one breach. Give me also ideas for summarizing this data as the size of the breach is in column D. Okay. Let's see what it comes up with. And what you're going to see that's cool is it will make the graph in Excel as if you made it yourself. And so you can go back in and manipulate it. So normally you'd come here and you go insert, and I'm going to choose the graph type and which series of data. This is going to do all this for you. So it's going to ask some questions. And it wants to build the breach count by year. Yeah. So we want to, I'll always allow in this case. Done. Okay. So it's got it here. So we'll go to that. I want to see 12. We'll see where it put it. And it says it can add these for summarizing column D. So I can superimpose something on the chart. So let's see what this looks like. Okay, cool. It just made the chart here. So let's see where it took the data from. So this is the year five, the number of breaches and the individuals affected. So that's very cool. That is very, very cool to see. Now, as ever, like I say, it put it in a new sheet. You got to check it. What is this? I don't know what this is. So, you know, it's still going to get some quirky things. 11. Okay. So this looks like the number of data breaches here based on D and those are the individuals effect. So some of the individuals, but it did something that wasn't quite right. So I'm going to check it. So here we got column two down to 2025. I can see there should be, yeah, there should be 142 or so. Let's see what it got. Yeah. So that's right. So, so it did get the number of breaches. Correct. So that's nice. This is looking good. I can't figure out what this is. Data breaches by year. I think this is the year and it intended this year to be the label for the axis. So I'm going to tell it to fix that. Like I said, this is saving you time. Now, if you're very good with Excel, you can do this faster, but say, please, instead of, I don't know why I'm saying please to the AI, but Hey, AI be a good friend, um, instead of the year as a series, make the year data points like 2001, 2002, et cetera, the label for year. Currently the year is one, two, three, four, et cetera. Okay. Let's see if it'll fix that up. And I see, I like that. I put it in a new sheet. Now let's see what his ideas were here. And it's like having a colleague that you can chat with. Delete. Yeah. So we need to, uh, not dangerously. I'll always allow, we're going to allow it here. It's going to redo that. Oh, great. Look, check that out. It fixed that for me. Now I like to have my grid lines, uh, shaded, make the grid lines two thirds transparency and dashed. All right. See what it does. Awesome. Now let's make it transparency. 67 transparency. I don't think that's 67. Make it more transparent. I softer. Let's see what, if it does that. Bump to 87. I, so that didn't quite work, but what I can do is I can go into this graph and I can format this and see, I can still manipulate it myself. I can still see where the data are coming from. And then here I can go, no, you're not going to have as, you know, as much control here on this case, so it may as in the desktop, so it may not be able to do it quite as well as one, but, uh, at any rate, this is fine. This is already looking pretty good. I want to go back into my add in to cloud. And again, this is, I'm just intentionally doing this. Using the free version of all these tools so that you can do the same thing. So let's come back to Claude and I'll say, just remove outer order of the figure. And, uh, this is going to help you get your figures to publication quality. You don't need to ask chat GPT to do this for you anymore. When, why do that? When you could have cloud living in here, look at this outer border now removed. I mean, this is close to a journal quality, uh, figure. I could actually run with something like this in a paper. It's looking not bad. I mean, I'd probably do some further manipulation myself with the grid lines. I might even get rid of them to make it a bit cleaner. Um, but there you are. The other thing it can begin to do is you can start doing analysis. So if you want to do say, Hey, do a regression analysis to see, you know, if the numbers of individual affected have increased, so let's try this. So do a regression analysis to see if the numbers of individual affected have increased over time. Let's see if it gets this right. There's just something quick and dirty. And then we could also see if business associate present is a determinant of the, uh, breach incidents. Um, I don't recommend this as kind of a weaker area. I recommend going into our, we're doing any kind of heavy lifting. If you just want to do something quick and dirty, a quick correlation analysis, this can be fine. I I'm pushing this here to some analytical limitations. And to illustrate the potential power that you have here before, I, I might write my own macros in Excel and now you have cloud just doing all that, that coding for you, potential data. Okay. So this is where things get a little bit riskier. So always make a backup of your file. I like to save like this might be book two and then give it a real name. And then my app is going to be a little bit more secure. So I'm not going to do that. Like this might be book two and then give it a real name and then my version one version two. So let's just allow it here for the sake of example. And this will also show you what's going to happen. I don't, I worry about it overwriting data. So I, I think the AI is going to hate me. Now I've given it a little bit of a headache asking it to do something much harder, but while it's still thinking, ah, okay, here is starting to, um, build this up here and here's the regression. All right. Look here. Cool. I want to know though. So this is a little bit less transparent. Oh, it gives you the code. You have your code of what it did to calculate the R squared, how calculated the intercept, how calculated the slope. So this is nice. So this is a very simple univariate regression. Yeah. Not going to make a whole lot out of this, but this seems to suggest that the breach size has been increasing over time. And, uh, yeah, here, here it shows, I see it even added this white over it. It added it to the graph. So it kept my main sheet intact and, uh, it added the trend line for me from having done this. Again, this graph looks terrible. Don't make anything out of this. Obviously 2025 data are incomplete, but guys, what do you think? Uh, I'm just blown away by this functionality. This is the future. The cliche is really true. If AI is not going to replace you, but a researcher who knows how to use AI will, and AI is enabling researchers who already have mastered the fundamentals to produce considerably more than ever before with less time and energy in, but as important, you get this fundamentals, right? AI is like the, the accelerator. If you don't have that right, AI will accelerate you into driving off a cliff. If you want to learn those fundamentals, guys, I've got something great for you. Click the link below. See if how we work resonates, join our community AI enhanced workflows, 200 members around the world, all working together to be the best researchers they can be many getting their manuscripts out and half or even a fraction of the time, we, we did a quick glance and we had 40% of our researchers say they would have never even finished, uh, if it weren't for our help. So look forward to seeing you on the inside and, uh, yeah, send me your requests for what AI videos and demonstrations you'd like me to make as I'll be producing more of this content coming up, see you in the next video.