Excel Copilot for Data Cleaning & Analytics

Hello everyone and welcome to today's session. My name is Ree and I'll be your moderator. We are going to get started in a couple of minutes. We're just waiting so everyone has a chance to join before we formally get started. Uh if you haven't done so already, please do register for this session. that will mean we can send you the recording and the resources as soon as it's available in our resource center. This will also stay live on our YouTube as well if that's your uh preferred rewatch uh platform. If you would like to join in live, uh we do have a few uh bits of setup for you. Obviously uh with the title of this session, we you are going to need access to uh Microsoft 365 CPI. Um we do have a spreadsheet that we're going to be working on. The link to that is in the session resources. You can find that uh as the pinned link in the chat. You can also find it in the uh video description as well. If you don't see it in the video description, just hit refresh and it should be in there. Uh but yeah, it's pinned in the chat. For everything else, if you have any questions, comments, or notes, let us know in the chat. We're going to be making use of polls today, so make sure you're ready to engage with those. And yeah, if you have any reactions or anything else, let us know in the chat as well. David's David who is our uh presenter for today's session is going to be asking your question uh sorry answering your questions as we go through. So yeah let us know if you have any questions in the chat if you're trying to follow along as well. Uh let us know in the chat too. We are going to save a few minutes dedicated for Q& A at the end. But yeah let us know in the chat whether you have any questions or comments. Cool. And for anyone that's just joined welcome to the session. My name is Ree. I'll be your moderator for the session today. We're going to get started in about a minute or so. Everything you need if you want to join in live today is in the session resources. There is a link in the video description. There is also uh pinned in the chat. So, that's the best place to look for what we're going to cover today. That's got a link to the slides we're going to cover. That's also got a link to the spreadsheet that we're going to be working with. And obviously if you do want to join in with the session live today, you are going to need a Microsoft 365 co-pilot license. Uh we'll be covering a little bit of info on the different licenses at the beginning. But yeah, if you do want to join in live, you've got to have access to that. And if you don't have access to that, just watch along, catch up with the recording, and then uh join in live there. And that'll give you the option to pause it and go at your own pace as well. Brilliant. If you have any questions or comments or anything else, let us know in the chat. Hopefully the chat's going to be nice and busy today. We've got a few polls uh to yeah see what your reaction is to today uh as well as interact with you so we can make the session uh more engaging for yourselves. So yeah, keep your eyes peeled in the chat and yeah once again if you want to interact with us and if you want to join in with the session uh have a look at the session resources link pinned in the chat and in the video description that's got the spreadsheet that we're going to be working with today. Brilliant. I think that's enough from me. So now I'll hand you over to your host for today's session, Gabrielle. Gabrielle, take it away. — Hello everyone. Welcome to Excel C-pilot for data cleaning and analytics. Excel sits underneath a huge slice of real world analytics. Uh and Copilot has changed tremendously in 2026 compared to uh earlier versions. If you tried it before and bounced off, today is your time uh to jump back in. you'll see what's possible now with both the free and the paid versions and most importantly where Copilot can uh actually help save you hours um versus uh where it gets in the way. So your presenter today is David Benim. He is a data visualization consultant with Excel Consulting Asia and by his own admission he is Cambodia's greatest Excel consultant. Uh David runs uh training and consultancy um on the Microsoft Analytics stack and he also hosts uh an Excel YouTube channel with over 20,000 subscribers. Uh and most importantly, he moonlights as a stand-up comedian with soldout Excel themed shows uh in both Adelaide and Ed Edinburgh uh fringe festivals. So I promise you will not be bored. Uh today David will walk through using co-pilot uh in Excel to clean messy data, generate formulas, and run real uh analytic tasks uh and plus um teaching

more about ways to update your existing spreadsheet workflows and how to take advantage of the AI assistance. So again, check out the uh session resources to get access to the spreadsheets and the slides. Drop questions in the chat as you go. Over to you, David. — Thank you. Thank you. All right. So really excited to be here everyone and this is going to be a code along session. So please get the resources and let's get going. So I'm also a Microsoft MVP which means the Microsoft considers me to be amongst the top 100 or so Excel people worldwide and I get to do fun things like this. I actually got this um session got informed by this by Microsoft. So, my YouTube channel is over 20,000 subscribers. Um, and yeah, I'm actually streaming this on my YouTube channel as well. Ree, if you can drop when you can a link to my socials. I do love comedy. I'm probably the only person in the world that's fusing Excel with spreadsheets and with comedy, standup comedy. Edinburgh Fringe Festival, the largest performing arts festival in the world, had a soldout season last year. This year I'm going back with a room twice as big. So really excited about that. If you happen to be in Scotland in the month of August, come see my show. Great. And we have a poll question to kick off. Uh if you can launch that. Okay, we I see I've got some qu I've got a question before I've even started. Give me a little bit of time, guys. Yes, I am on this year in Edinburgh. Yeah. Yeah. From the 6th to the 20th. Um, okay. So, I'm curious about when you last used Copilot in Excel, but uh let's keep going for now. AI and Python. AI in Excel. So, here I'm going to go into demo. It's going to be mostly demo that we're going to use. And over here we have some data like this. I'm going to start with very early instances of AI that was going on inside Excel. So this first tab is called flash fill. And here we're going to look at some AI stuff, machine learning stuff that Excel developed in 2013. If you want to extract the given name, you can give it an example, you can select the column and here I've done EVA. Select the column. And in the data tab, you have this thing called flash fill. It's got the lightning, which we often use to mean AI. And there you go. So that is how you can drag it down. So yes, I can do that again. You just start typing the example and then it will fill it from the examples like this. And now you get the first name that's appearing there. To go a little bit further, you can do the family name, but the family name has some added issues. So if I go to flashfill after typing it in, I get this going on. Let me know in the chat is there anything from this that you would like to change from my customer name to my family name. Let me know in the chat. Welcome everyone uh from all over the world, USA a lot. Um and from Scotland, Canada, great to see you. But yeah, let me know in the chat if you can think of any other way to do it. Can't hear my audio, someone says. Um I think that's okay for other people though. So yeah. So here we've got yeah casing very good John top capitalization JR is not nice. Yeah you want to get the doublebarreled last name. So the thing about flashfill is that it uses machine learning which is very prominent in AI. So that means that if you give the machine a little bit of information, it will try and guess. But you give it more information, it will try and guess further. So if I do that and press enter, check out what happens here. It will change it for Holland and Jones. And here, if I change it to go Dowi Jr., Roberto Dowi Jr., I'm trying not to get sued by some very, very famous people, but check out what happens here with Katherine Zeba Jones. It will actually do that. So yeah, if you like that then uh then give me a give me a call in the chat. Let me know. And now we're going to go with something even more sophisticated where I can do the description. So here with the description, I can say something like ever buys games for 21K. And if we think about what I'm doing, I'm actually going into this one but taking only the thing before the space. And then I'm adding in buys which is a

custom word to this one. Taking information from this one and then adding K for thousands. Now over here if I do the same thing I do flash fill that will fill it down like this. But can anyone spot anything that's gone wrong? Let me know. All right. A few calls in there. Good. Okay, Chris is in capsules. That's because I didn't change it in the first one, but I could have changed it like I did the family name. I was just going step by step with examples. All right. So, there is one issue here. Can anyone spot the issue? Usually, I've got four, but in this case, I've got M because Excel thinks that I'm getting the F from over here. Yeah, more. Very good. So if you immediately change it, then you get it to do that. Now flashfill is live uh which for as long as you see this. But it's not like a formula because once you get rid of it, if I change this to my own name, if only I was as famous as these people, it does not do that. As opposed to a formula, that will update automatically. But the problem is to get a formula like this, the formula is really complicated. So if you want to avoid formulas, you can use flashfill available in every single version of Excel since 2013. But we're going to go a little bit further. So the issue with flashfill being that it's not live was uh something that Copilot, as we'll come to in a little bit, can do a lot better. But to start off, let's look at how to create these formulas using Copilot. At this point, I'm just going to use Copilot in a browser because I realize that not everyone has the Excel version. So the browser over here, I have my Copilot experience. I'm going to go through my work account and I can ask Copilot for things. So, I'm going to put this on the side with my Excel and I'm going to say the copilot chat thing. So, write an Excel formula to extract the first word from cell C9. Enter. Key things. Say it's an Excel formula. Number one. Number two, what do you want it to do? Number three, give it a cell reference. C9 is this one. And then it's going to give it to you correctly. Then after that, you've got this. And you can copy this one. This only works in the newer versions of Excel. That's what it's saying. And if you paste it like that and press enter, it will show you that. And you can drag it down. And this will work. This one will work in earlier versions of Excel and will also work if you don't have a space. Whereas with this one, space, it will give you an error. So, annoyingly, Copilot doesn't always give you a clean way to one-click copy. Sometimes it does uh if you use it on the web browser, but sometimes it doesn't either. Yeah, that's how you can do it. So, let's do the same with a family name and let's do a different approach to how we can use C-pilot. So that is not a video I want to watch. That guy looks incredibly creepy. Anyway, so screenshots. Screenshots are a really useful thing that you can do. I can press print screen and I can draw around it, paste it. And if I do that, I don't have to say it's an Excel formula because it will be able to tell that. So I can say extract the last word from the customer column. I don't have to say it's an Excel formula because it knows that I don't have to give it the cell reference as long as I capture the cell like that. And this should work. Of course, sometimes Copilot hallucinates as some people have already pointed out, but in this case it works. Here's a trick that you can't do with AI yet, but if you want to fill this formula down, if you move your mouse so it's a black cross, instead of dragging, you can double click it and that will fill it down like this. So, give me a wow if you like that tip. Double click it instead of dragging down. So, Excel and Google Sheets as well. All right. Now, we're going to go through something a lot more complicated. So here we have some cells where we've got an email address, but the email is sometimes in

the middle of text, sometimes it's at the beginning, end, sometimes there isn't even an email. And I want to extract the email. So this is something that Excel recently added this function called equals regx extract. reg X is a complicated coding language that allows it to do loads of really cool things with text really sophisticated but it will not do that are there any privacy issues with print screen so if you do if you enter the way that I did if you enter your co-pilot through work and you are on the work tab there is nothing that's going to be sent back to Microsoft it's not going to be at risk chat GBT on the other hand or other systems might send it back. But if you're using this with the work tab, then it is doing it through a corporate account. There is no risk, no privacy issues. All right. So, regax extract. So, this is a really cool new formula. Because it's new, Copilot might not know it exists yet. So I'm going to say extract the email from cell B9 using Excel formula. This is something that was impossible to do even with the most sophisticated of co-pilots until Excel got the reg X. So yes, it's not doing that. Here we go. Yay. Okay, this only works with Excel 365. copy it and then double click it there, paste it and press enter and you get it showing like this. And you could I could say um do it better with shielding from if there's an error, but we're not going to do that in this example. All right. So, this is how you can use Excel separately. As you can see, I've got a sidebar, but Copilot has now its own sidebar. You can open it directly inside Excel. And this is what we're going to discuss a little bit more recently. But if you click on this dropdown and choose chat only, you'll get basically the same experience that I just got. Also, this is use this is pretty robust. You don't have to do a screenshot and it works. Uh copilot web version is better than copilot in Excel. No, I was just trying to show co-pilot online because some people might not have access to it and it's essentially the same as this chat only one. So over here, yeah, you can follow me on socials. Uh the host Reese just dropped that in there. If you want to get my Excel comedy channel with Instagram or YouTube, you can follow me on there. So this is another thing that Excel has added in the last year that is AI which is pretty cool. You now have the ability to use the translate function. So I can write equals translate and I can click on any one of these and I can close my brackets and I get it to translate like that. So yeah, give me a wow if you like that one. That is just the translate function. You can get it to translate if you leave the target language blank. It translates to the default language on your computer, but you can also translate to anything else. So, give me a language. Who wants a language uh that you understand? And we're going to do a little bit of translation. I'm going to translate all of these things. So, equals translate. And I'm going to say this text translate that or let's just say F3. Yeah. Give me some idea of what you want it to translate. I'm following the chat along. Erdo, we can try. Okay. Source language. Always leave this blank. Excel uses AI tool to detect that. Erdo, yes, it does work. Okay. So, this is Erdo. Those my brackets. And you can tell me if that works. Um, yeah, I can't read Erdo. I'll go with So, I'll do the same thing. And let's do French. Someone said French. I speak. So, I can translate that. Oh, no. I need to do it like that. There we go. numero. Yeah. And you can drag across and then you get these that appear there. So yeah, give me a reaction if you like that one. Aramaic. Wow. Okay. We've got some ambitious

ambitious ones. Um I'm not going anymore, but essentially this is how you can do it. Now Excel can now translate across languages, but the translation that Excel has never been able to do is the ability to translate numbers into text. Uh so let me show you what I mean. So, Excel has never been able to, if I write equals translate, translate this to -78. And you might be thinking, I've never actually wanted to do that. Well, you probably remember that we had checks going around every business around 10 years ago, and this was really, really vital. Excel has never been able to do it, and it was one of the most used VBA codes in the world, except that Excel has always been able to do this. Can anyone tell me what that is? Any idea what I'm showing right now? Okay. Someone said, "I don't have the translate formula. You do you need copilot? " No, you don't. If you don't have translate formula, it's probably because you're using Excel 2024 or your Excel hasn't updated. If you go to account and update, this should have it because this came out about a year ago. Um, all right. So, here we have, believe it or not, Excel has never been able to translate numbers into words in any of the thousands of languages in the world except for Thai. This is a true story. Excel can translate numbers in the Thai language. Yes, one person got it right. Yeah. Yeah. So you can drag that down. However, this is kind of fun. Now that you have translate, you can do this equals translate and bar text and you can click on a number, close your brackets two times, and you get that. So give me a wow if you like that one. This is a pretty random thing. Yeah. minus 78 8 B 63 192. Yep. Yep. That's pretty cool. All right. So, some other stuff. And this is available to everyone. You don't need co-pilot in order to do this kind of AI in Excel. You can take something like this and you can copy it. And but don't worry, I'm going to spend a lot of this talking about co-pilot and the awesome stuff it can do. So we will get there. So you can copy an image and then you can click in a cell in the data tab you have from picture from clipboard because I copied it and this uses AI to convert this into data. I wish I knew the backstory on the Thai stuff. Yeah, I do as well. I actually have no idea. What does bot mean? Bot is just the name of the currency used in Thailand. It's like pounds in the UK and dollars in the US. And this crashes. Yay. Okay. I can't show it to you right now because it's crashed. Uh but usually that would convert this into the cell. Um yeah, I'm not going to troubleshoot it right now because it's just crashing, but it would work. Data from picture. You can also get data from a PDF file and convert this into Excel. So these can work quite well. But what I will say is if you are doing major stuff with PDF or with from picture then throwing it into purpose-built third-party tools will be better. I'm not going to go through what these are but there are third party tools you can find online that are more built for it. If you need something simple this is great. more sophisticated then it is good to uh look up third party tools. Now, let's go into co-pilot. All right, let's go into a bit of a wow factor. So, co-pilot clean data for 2026. So, edit with Copilot. This is the brand new change that's happened to C-Pilot in the last couple of months. So I will say about copilot inside Excel is if you have used it for a while if you used it in 2025 then it wasn't that great co-pilot inside the Excel application but from February this year they have put in a massive update and it keeps updating. In fact I just got an update through yesterday. I was speaking to the organizers yesterday and I said I just

had an update in my co-pilot yesterday. So I've got to include this and show them to it and I will show it to you. But edit with copilot can do four major things. Clean up data, create a dashboard, create a table or a data model and check and fix formulas. So let me show you how this works. So launching copilot in Excel, click on that. I will explain how the licensing works in a little bit. So I'll just show you the demos first. So here I have some data and you have this toggle allow editing plan. This is the new thing that just came out yesterday for me and chat only. Chat only is great for asking for formulas but it won't do any edits in your data. Allow editing is the one I will show you. Check this out. I'm going to duplicate this tab so we have a backup. I can say three words. clean this data. That's it. Just these three words. Let's look at the data first and where it's kind of a bit broken. So in the item column, I've got bracelets that is misspelled. It's got double A. This is space in necklaces. Three bracelets. That should be the same as the others. In the sales numbers, sometimes I have spaces. Sometimes I have a the letter O instead of a zero. This is 28. This probably should be eight instead of B. But of course, Excel does not recognize these as numbers, which means if I try to do formulas, these will break. It'll give me a value error. Here I have capitalization errors. Eh, not that much is there. Here I have the data in French. And gender. But sometimes I have male and female. Other times I have just F or man. So, this is not going to be very good for my filtering. The notes column is fine. There's nothing the issue with that. Now, uh before I get to using this one, I first want to show you that Excel has a paid version of Copilot and a free version of Copilot. Now, the paid version of Copilot gives you in the data tab this thing called clean data. And let's look at that first because that is able to pick up that bracelets was misspelled with a double A and that necklaces was misspelled as well. Germany capitalization and capitalization here. So it has picked up four things. So I can choose to apply it and this is part of co-pilot. This got launched last year. But as I just said, Copilot pre2026, not that great, but now it's really had a big change because this has spotted four things. But let's go back to this one. Clean this data. Make sure you're in allow editing. Click on that and let's see it work. So it's going to think about it. This is using an AI agent. An AI agent will mean that it will interpret clean this data and it will try and use another large language model to reason whether it has done the right thing. So it's sometimes a little bit unpredictable how long this takes. Um in previous demos I've done this has taken about 30 seconds but here it's taking a little bit longer. There we go. Okay. So it's now identified it and it's going to Okay. It's added a new column in this case. version of the table. Look at that. How cool is that? It has translated this. It has made the gender into one thing. And yeah, so give me a reaction if you like that one. Give me a wow if you think that's useful. Look at everything that it did here. And it did this through formulas like that, which is pretty awesome. Now, I'm not going to lie. I did not think it was going to do formulas because uh when I did this couple of weeks ago, it didn't do formulas. It replaced it in the source data. And it hasn't used the translate function. It's only translated. It's done a switch, which is if it finds this, replace it with that. But it hasn't actually done the translate function or done anything through that. So, we can go a little bit further. Um, I did copy it. I'm going to copy it a couple more times because in this one, I'm going to say, clean this data in place. Okay, I'm getting some questions coming through. How do you control the uses of tokens? Um, so the uses of tokens isn't something that particularly comes up in Copilot like it does in the other applications. Note that a lot of people are talking about chat GBT and Claude. With Copilot, it uses both engines. So in the drop down here, you can choose between GPT

made by OpenAI or Claude. So you can choose these ones and it will do anything else. Can you do this with a browser version? Yes, I believe you can. I can activate the dropown for chats. Um, I'm only seeing for chat only. Okay. So, there you go. Now, it's done it in place. Very, very good. And now I can ask it for some audit trails. So, usually for me, I like to get it out of the chat of what it actually did. So, here it has the notes of what it did. So, I'm going to say, write out your steps in column H. I always advise doing this that way you have an audit trail of some documentation. And I'll show you another one. Looks very interesting, but if it makes mistakes, you have to manually check it anyway. What if we have 50,000 rows? Well, Richard, I'm going to say if you have 50,000 rows and you had to manually do this, then you would probably not see the outside world outside your office for the next hundred years. But if you have co-pilot then you might see them after a couple of days. So yes Richard it is not perfect but the moment it becomes perfect then um we've all seen Terminator too. So let's not go into that regard. There you go. It puts out the data cleaning steps. Really good for an audit trail. Or another thing that you can do uh and I realized this by trial and error is you can say write out notes in every cell which you've changed. You mentioned it's perform the same task two different ways. Do you see any ways to predict its behavior? Um uh well if you're more specific in your prompt like I said originally clean this data in place instead of clean this data um or you can do it clean this through formulas but I do recommend doing this and it can continue to go okay total 28 notes added. I don't see the notes. Where has it done that? Um, lies. Try again, buddy. I'm being more polite here. You Sometimes I would say nasty things to my co-pilot. I don't always recommend it, but sometimes it works. Could be slower to manipulate. Yes, I personally haven't run into issues of it having that problem for the moment. Um, but yeah, I would do it. Okay, there we go. Now we got comments. Yay. Okay, it's done that. So, yeah, this is a really good way to get an audit trail in the actual cells. So, if you're working with someone else and you need to explain to them what you've changed, then this is a good way of doing these ones. Okay, so let's go through another one. Badly exported data. I'm sure you guys always have this. You have systems that export data really badly. And here there's submerged cells. Excel doesn't know that this relates to green mangoes. You want you want this to appear. This is how you want your data the more optimal way of getting data. And if you have it like this, then you can do analytics and filters with things like pivot tables. So let's go and delete this one. And all right. And then I can write this one and I can say clean this data. And in the meantime, I'll try and address the questions about versioning. So with versioning, if you have Excel with Microsoft 365, even if you don't pay for an extra license, um you should be able to see this whether it's on the web or whether it's physical. Now you should see the popup on the bottom right. This is new. Uh It used to be up here. So some versions of you might still see copilot over there on the web. I can do this as well. And I can click on this one. that. And that will show it to me like this. So over here you will also see M365

Copilot Premium or you will see M365 Copilot Basic. Now, um, two things matter whether you see this or not. Number one, how big your organization is, and number two, whether you're paying an additional premium. Oh, look at that. Look how much it's built that and re cleaned it up. Oh, I love this feature. I would have used Power Query to do this. And don't get me wrong, I love Power Query and I'm never going to give up my love for Power Query, but um but yeah, but this is pretty cool. And this one you can do without understanding Power Query that well. Uh available for Mac, absolutely no difference from the Mac version. So yeah, going back to versioning, if you see premium, then that means you're paying for a C-pilot license. You have to pay extra for it with your Microsoft 365 subscription. However, if you see basic, you might still be able to choose allow editing if your organization has less than 2,000 people. And this is literally right. If it's less than 2,000 people in your org, then you will see this one. If you have more than 2,000 people, you will not see allow editing. That's the official word from Microsoft. Some people are saying they don't see it anyway. I can't be in control of that. Maybe your IT department has delayed a release for some reason. I am not sure why. Um, yeah. Okay. So, here is I'm going to go to stop for this one. Here is clean formulas with co-pilot. So, here if I look at the different formulas, sometimes they are wrong. The highlighted ones, this is doing income minus costs even though costs are negative rather than income plus costs. This total is not adding the last number. This one is multiplying it by a fixed value rather than referring to a cell which is what you should do. This one also has an issue. So in copilot I'm going to do a new one. You can switch between which model you use. Um generally claude is deemed to be better for financial work. uh GPT writing and for image making etc etc. If you use auto then Excel will decide for you. So you don't have to worry about it. But if you do know about it then it's okay. You can also upload images, files or work content will be stuff saved on SharePoint. And I can say the word I'm going to choose plan in this case. Plan is like allow editing except that it first tells you what it's going to do and then you can say you want it to happen. So I can say uh check and fix the formulas here. Press enter and let's see if it gives us the answers. Difference between Mac OS and Windows version. As far as a as far as I'm aware there isn't. Is there going to be a recording available? Yes, you can watch it on my personal YouTube channel David Benim or the data camp one as well. H John, I love Power Query. I agree with you. Um, can co-pilot build a macro for us? It cannot build a macro at the time of making this session, nor can it build your power query yet. Or it can't even build pivot tables at the moment, at least last time I checked. However, if you use the chat only, then you can ask it for the steps and the code for the macros. Oh, well, Nicholas seems to have managed it. Okay, there we go. So, this is it's still on plan. So, it's told me what it wants to do. And now I have to say proceed if I want to get it to work. So, proceed will mean that it will in theory fix these cells. Let's go in there. What about big data sets in mega gigabytes outside of Excel? What are there options with AI? Um, again, I'll refer you to re-watching Terminator 2 with that. I'm sure that evil guy can help you with it. I megabyte Excel files do work. Gigabyte Excel files, I've never seen that. If you got gigabytes of data, then you can bring into PowerBI. PowerBI has a version of copilot as well. Can copilot read complex pivot tables? Uh read as in understand or create. It does this kind of rainbow outline. Um this is not for pride month unfortunately but this is uh currently just to show you that copilot has done it there. And

there you go. It's fixed formulas. Yay. We all like that. Good. Give me your action if you like. Very interesting I tell you. Yes, yes. I tell you to um generate with co-pilot. Well, let's go back to this data set and let's create a nice little dashboard. So, with this one, I'm going to say create a dashboard from this. Press enter. I've been using Chat GBT for weeks to help me with PQ code. Yes. So, Chad GBT and Copilot and Claude are all really good for Power Query code, but they won't build the queries for you at this time. Um, is a context limited to the active sheet? Um, Etien, good question. I believe not. Uh, it often will create things in a new sheet. Um, yep. Okay, it's done that. I'm going to press proceed. Just going to Yeah, I forget I'm in plan mode, so I have to do the extra step. Okay. Well, maybe I can do this twice as fast if I also do it with the online version. Uh, so here I can say something like allow editing as well. And here I'm going to say here is a prompt. I'm just going to copy this one and let's explore it. what it is afterwards to create a financial model with a monthly repayment on a 5-year loan taken out in USD with principal of 0. 3 million and 8% interest. It must be held in GBP. So, I am asking Excel to look up an exchange rate and create a financial model based on this data. So, let me know your preferences. Gosh, I'm just going to do one, one. says, "I've reached the limit for today. I have the basic version. " Um, okay, good question. That the limit is not something I have come across, so I can't advise on that, I'm afraid. But if you look it up or ask another AI to do that. Hello Dave. H how can I install Copilot on my laptop? So Copilot should be in your Excel. It should be over here uh on the bottom right. You don't need to install it. If you have Excel with Microsoft 365, then you should have Copilot there. If you don't have C-Pilot there, then it's probably because your IT department or someone has blocked it or because it's still rolling out. But it should still it should be there. That's what Microsoft give. Here's the loan schedule. So, give me a reaction if you like that one. I'm not going to go through all the details, but you could add by saying some additional tips. Uh, as I said, I would always say something like write out your documentation in column. I now say column. I used to say cell and then I used to make a ridiculously large cell. And I would also say um mark input cells in orange. So that's usually how financial models work like that. All right. Yeah, we've got some uh some good reactions. Looks like student loan debt. Yes. Yes. I'm sorry if your student loan debt is $295,000. I don't think Copilot can help you with that, Devin, but maybe. Okay. Oh, back to our dashboard. Yes, we were doing two at a time. How cool is this? Give me a reaction. Give me a wow if you like this dashboard. It'll give you these emojis. That's how you know that something's AI because it does emojis a stupid amount. It creates these charts. It does not create pivot tables. I wish it did. I think in a future iteration it will, but for now it does not create pivot tables. That's something to know. It uses sum if and count if and average if formulas which are less robust than pivot tables. And you don't have interactive slicers which I always use in my dashboards. Uh make sure your file is saved locally on is saved on one drive or sharepoint. You don't need that to do that anymore. So that's how it used to be. But now if I turn off autosave or if I save this as F12 by the way, great shortcut to go to the save as dialogue. I can do this on my desktop and this will still work. So yeah, the advice that you used to need to save it. Okay, I thought this let me save it. Uh save it here.

This will turn on autosave and I'm going to show you that it still works. So, let's go back to some reasoning here. Here we go. Something completely different. I'm going to do a new one. Do some shopping research. Look into the top 20 reviewed Windows laptops from 2026. Made a table of comparisons of brand cost on Amazon, link to buy, CPU, RAM, etc. So, let's see what this gives. I'm going to do a scary one in a little bit as well. I had some fun with this. Yeah, the next one is pretty fun. In fact, let's do the next one in Excel online. So, I'm going to go to and copy this one. I used to work for Deote. That's how I started my career in accounting. I'm going to paste this and make it go. Research all the deote firms around the world and list out firm name, managing partner, address, number of partners, email of the managing partner and years operated. So much for GDPR. If this comes about, this can actually get the email address of these people. Uh, okay. Here I'm with plan. I need to say proceed. Yeah, I need to remember to do that. MS offers a plan where you can download the desktop apps as part of 365. Yes. Yes. Um yeah, Microsoft loves just using its names interchangeably. Uh so Microsoft 365 used to just be the online version, but now it also has a definition for the desktop apps. Um I dream of working at Deote. Okay. Well, here you'll get some information uh with who to contact hopefully. Um, okay, this is still loading. As I said, the four things that it does, clean up data, create a dashboard, create a table or create a model using data from the internet, using reasoning, using other models that people have built in the past, and then check and fix formulas. These are tend to be the things they could do. Can Excel 2016 perform automation with C-Pilot. Uh Copilot is only part of the newer version of Excel. You may remember that in 2016 Copilot didn't exist. So you might have your answer there. Have you had to do your Excel co-pilot so that it uses certain formulas over others as per your preference? Um good question. Um, I have not done that yet, but that is something that you could do. Okay, so remember we asked it about the best laptops. So yeah, give me a reaction if you like this one. I think it looks pretty darn cool. Sources. I'm going to say list out your documentation and sources in cell J4. I'll write cell and then you'll see why it kind of isn't ideal. Let's go back to how our online is doing it. Oh, there we go. Okay. Uh put that in this blank worksheet, please. My robot overlord remember I was polite to you. Okay, let's see what that will give. Toggle between this one. Yeah, I already showed you clean data and again clean data is the paid version but clean but the co-pilot to edit is doesn't have to be paid version which is uh which is pretty crazy. Okay, we've already kind of covered that. Um co-pilot licensing I'll do a recap but it is about the size of your company. So I did say this if your company is less than 2,000 people then you should have edit in copilot in Excel. your company is over 2,000 people, do you pay an extra license? If you do, then you will have edit and copilot in Excel. If you don't, if you have a large organization and none, then no edit and copilot in Excel. This is how the licensing works. The clean data and there are a couple of other very small features that are only available with a paid license. I don't think they're that good, honestly. Um, I think the polls are a bit struggling, so we're not going to use them. in this context. It didn't do that. Okay.

Out plan. I'm going to do it allow editing. Add it to cell B29. Yes. So, here's the thing. If you are using this not saved on the cloud, saved locally, you get this extra thing. This file is unsaved changes. Consider saving the file before continuing. I'm going to say save. AI overlord lady. Yeah, that's true. It doesn't need to be uh could definitely be a lady. Can open an incognito private window and get more sessions. Um, sure. Yeah. So, I don't like doing documentation in a cell because it does this makes the cell really long. I usually say in a column. Uh, let's go back to the deote thing. Uh oh, there we go. We have actual email addresses. Okay, we don't have uh I did this with someone else and it did have uh it did have a person's email address. Terrible question. Have you ever tried similar AI agents in a Google environment? Yes, I have. I'm a big Google Sheets fan. Um is AI looking for John Connor? Hopefully not. Um yes, I have. And Google Sheets, I'm a big fan of Google Sheets. You'll find loads of Google Sheets content on my YouTube and my blogging. Um, Google Sheets has some really cool things like the equals AI function and smart fill. I'm not going to show you this because here it's a co-pilot session. Uh, but yeah, I'm a big fan of what Google does as well. However, this stuff with the edit mode and co-pilot, Excel does really, really well. Um, I will explain a couple of use cases that I've had recently. So, um, I have some forms. I have a form for a comedy show I'm doing at Edinburgh Fringe. There's not my own show. I'm doing a a show where comedians will do short presentations about a nerdy subject. And we have four people every day. and I did an online application and we had about 70 comedians respond but we only wanted to pick about 25. So we asked AI to do loads of things with the constraints. So I said from these results I wanted the constraints of have a gender divide and also avoid the same topic if we don't want to have two topics on animals on different animals on the same day. uh each person should only uh perform with five days in between and a few other parameters and then it was able to generate a list of uh how I should arrange my people. So it's like an optimization kind of strategy. Um yeah, they look like hallucinations for email addresses. Yeah, but I did actually advise this to a client. A client was doing something similar to Deote. I won't say who the client was, but uh they were doing something similar to the Deote thing. And I said to them this, if it's a hallucination, then the email will just bounce back. So, you may as well send the email and then nothing ventured, nothing gained. Either it will go through or it's made up and it won't go through. So, years operated column is wrong, is it? Okay. I didn't look at that. There are going to be hallucinations. You just have to accept the AI is going to make things wrong. Okay. Yeah. I don't know uh if these are the lawyers operated for 181 years. That seems ambitious in the UK, but it's possible maybe. Um okay. And yes, so Excel, this is the last demo I'll show you and we can do together. Excel has recently integrated Python into it. So, Python is one of the most liked programming languages for things like data analytics in particular, but also things like web scraping and data cleansing. And what it can do is it can produce more advanced statistical data visualizations like this. Like I'm not going to explain a maths lesson, a stats lesson to you guys, but swarm plots, these are different kind of charts that you can't do with a regular Excel. This is called a violin plot. I really like this for showing distribution of data. It's got a box and whisker chart and a density probability diagram around it. So it combines it. I really like this. You can't do this with the regular Excel. Uh, however, Excel does have Python integration, which means you can do

that. Now, most of us don't know how to use Python and we're probably not going to learn, but Copilot can help with that. So if I go here, I can type in I can say using Python data create a violin plot of let's do life expectancy. Let's call it the same thing by continent. Press enter. Now, I'm going to be frank with you guys. Last time I checked, this didn't work. But Microsoft promised me in a call I had as in my MVP status that this is working. Now, I like Python. I'm glad you like Python. Uh, just to show you how Python actually works in Excel. Whilst we're waiting, hit the formulas tab, you can enter Python, and it will allow you to type Python code inside the formula bar. And then you can type in a data frame. you need a data frame for Python. You press equals and then you can select your data like this. And this will bring your data into a Python data frame. Uh as I said, I'm not going to go into detail with Python. That's not what you guys signed up to this webinar for. But just to show some people who are already using Python how it could work, this is how it could work. Um yeah. Okay. So here it's got reasoned with one step. Press enter and tab. Okay. Uh so you can enter Python scripts in Excel. You absolutely can. Yeah. In Excel desktop and Excel online. Okay. I am not going to go through this one. Yeah. But I was able to make it work as I went through again and again. Uh Python comes pre-installed with the pandas library, the Seabour library, and a few others as well. So let's go into a summary and then we'll take a few final questions here. So we've looked at how to use AI for text cleanup with flash fill. We looked at some quick wins like data from picture like the translate function or translating through the tile language from numbers into text. Then we looked at clean data for copilot. That for me is the highlight. And then co-pilot licensing. I've tried to explain it as best I can. then copilot to generate dashboards generate financial models generate a list of deoid people with managing partners email addresses and then we summarized it with a little bit of AI um okay we're not going to do that again you can follow me on Instagram the excel comedian c come my fringe show I just passed 100 tickets sold yesterday so pretty happy with that even though it's two and a half months till the end of fringe So yeah, so check that out. And if you go to my socials, that'll link you to my YouTube, my Instagram, and uh everything else. Yeah. Okay. I hope you enjoyed that session. Uh we're going to open it up for questions. — Wonderful. — Actually, Reese, could you uh pop in the original link that I sent that has all of my socials? Um because that should just be one text that you can copy and paste as direct links to buy fringe tickets and all that. Uh that would be useful. Yeah. — Um — yeah. — Cool. While we're waiting for that, let's jump in on some of these questions that we saw in the chat. So David, uh we had another question from a David. Does Copilot allow you to create skills so that it always knows use a formula is your preference? — Uh yes, you should be able to do that. Yeah. Yeah. Okay. I see some questions here. Um yes. So it does have memory. So if you tell it for example use XLOOKUPs instead of VLOOKUPs then it should do that for you and it should remember that for future visits. Um doesn't always get it right but yeah. — Awesome. All right. So another question that came in from Ari. He asks as an Excel MVP how much training did you need to become proficient in co-pilot with Excel? Um, so it's a tricky question because Excel with Copilot changes all the time. Um, as I said, I tried it in 2024 and it really sucked. Uh, 2025, new things came out and I got really excited about the clean data feature that I showed you. Recorded a bunch of videos for my YouTube channel and then uh became proficient in that way. But then once uh once edit with Copilot is out, it's

it's all changed. And what I find is the best way to learn is look up online people's examples. I'm going to be posting more and more YouTube videos of examples of how I've used something because that'll open your mind to things you could do. Like today we looked at generating shopping lists, generating research on different firms, a loan repayment model, things like that. Yeah. Um yeah. Okay. I've got some other questions. I'll try and go through these fast. Does copilot allow you to create skills so that it knows how to use a porn formula is your preference etc. So good question from David Ingram. Skills is something that has become really popular with Claude recently. Uh Claude has co-work and co-work can reuse skills and Excel or rather Microsoft is adapting co-work as well but that's only available in preview at the moment. So I haven't shown that to you and it is going to adapt skills to do with that as well. So it's coming but not yet. Um can I use my favorite native language or is English the best way to prompt in excellent with co-pilot? Um your own native language is absolutely fine. I am 99% sure. Um yeah especially German. Yeah, you don't need you don't need English because Excel is smart enough to I mean copilot uses GPT chat GPT and it uses claude so they are smart enough to figure out other languages. Can you estimate copilot for certain EDAs like you do other GPTs? I don't can you customize I don't understand what EDAs means in this case. Um, oh, is this like so you can customize co-pilot in the web version? I assume you mean to have different projects and things like that. So here you can have agents and you can attach certain things to copilot here and have different ways that you do something by adding agents. However, in Excel, I believe the answer is no at the moment. Um, as far as I've seen, was the privacy policy for data being processed by co-pilot? Is this determined by license type? It's not determined by license type. Even if you have the free license, you are still able to go into the work version. You don't need to pay extra for this. Where it does vary is if you have a personal version of Microsoft 365 versus a work version. If you have a personal version and you're not essentially sending uh and you're not paying for the license, then it could use your data to feed the algorithm uh like GPT can. But if you use the work version via the free version or if you have the paid private version then you can avoid that. Is there a platform where I can have a more formal and hands-on certified training on copilot and using copilot for data analysis? Now the issue with that um they do exist. You can find courses on YouTube, on LinkedIn, for that. But what I will say is that it's changes so fast. Like I said, this feature came out yesterday for me. Other features came out a week ago. Uh I'm giving AI webinars in the next couple of weeks. And the thing I pitched is no longer valid. So, if you take a if you spend a long time doing a hands-on certified training course, then what you're learning might be out of date. Um, that's what I risk for you. So, they do exist. Find them on LinkedIn. Find them on various other platforms. I don't have one myself. Um, I do face toface training and YouTube and comedy and blogs, but I don't do um other kinds of trainings. Uh, yeah. All right. So those are the questions I have in the chat. That does take us to on the hour. So uh yeah, I'll throw it back. — Wonderful. Well, thanks uh thanks so much David for uh this phenomenal code along and bring us along uh today's journey. Uh we'll be back again same time tomorrow and we'll see you