hi everyone welcome back to the channel if you're new here my name's Deb Ashby I'm a Microsoft MVP and today we're diving into something really cool using AI to make your work in Excel easier and faster whether you're handling complex data managing spreadsheets for work or just need a quick way to generate sample data sets then this is the tool for you and the latter is exactly what we're going to cover in today's video so imagine this scenario you're working on a new project and you need a data set but creating one from scratch sounds
like a complete time suck which trust me from experience it is you also don't want to waste time browsing through the internet looking for free data sets that you can use that are not only relevant to what you're doing but also are available in the public domain so you're not infringing on anyone's copyright this is where chat GPT is your best best friend and I have to say since the release of chat GPT this is probably the number one reason that I use it and that is to generate sample data set so I can practice
my Excel skills or create tutorials like this one and there are many people out there who are always looking for data sets it's one of the number one comments I get underneath my Tik Tok videos can we have the data set where can I find data sets and these days I always direct them to chat GPT so let's dive in and take a look at how we can create a really mean meaningful data set that we can use to practice our Excel skills so the first thing we're going to do here is we're going to
fire up chat GPT and you do that simply by opening a browser of your choice and going to chat gp.com now I'm using chat GPT 40 or 40 however you want to call it and this is the free version I haven't paid for anything now if you've never been into chat GPT before this is what it looks like it's a really really simple interface we have some suggestions in the middle here and then at the bottom this is is where we type in what we call a prompt and a prompt is basically what you want
chat GPT to do and there is a ton of information out there related to prompt engineering and how best to word your prompts so that you get relevant results because the more detailed your prompt the more accurate the results are going to be that you get from chat GPT and I always say with software like this that's in its infancy it's not perfect at the moment so sometimes you have to tweak and change a few things but in general I find this a great timesaver so let's start to construct our prompt and I'm going to
start out just by giving it some context so let's say I work in HR and I need a sample data set to practice my Excel skills with so I'm setting the scene so that chat GPT has a little bit of context now I'm going to type in what I actually want chat GPT to generate and a little tip here when you're working in this prompt area at the bottom if you want to get onto a new line so you don't have everything just written in one big long paragraph you need to press shift enter and
that will move you down if you just press enter it's going to send your prompt through which we don't want to do at the moment so now let's type in the rest of our prompt and I'm going to be really specific about what I want so this is my prompt please create me a sample data set that has the following columns employee name job title department extension number hire date employment status salary and manager so I've given it the column headings that I need and then I've said I would like this data to be in
tabular format so I can work with it in Excel and I always like to add this on the bottom of my prompts if I didn't put that a lot of the time what chat GPT will return is the information but delimited so we'd have the employee name comma then the job title comma then the next column so on and so forth now that's fine you can copy that and paste it into Excel but then you need to go back into Excel and you need to do some data cleaning because it's all going to be in
one column separated with commas now it's not difficult to break it up you could use text to columns you could use various different functions and formulas but we want this to be as easy as possible so I always like to specify tabular format I found that's a lot easier so let's send it through and see what it produces okay we're looking pretty good so I can see it's given me 50 rows of data now I actually want a larger data set than this I want 200 rows another thing I can see in here is that
chat GPT hasn't quite got the context right it's given me all my columns I'm perfectly happy with that but let's take a look at the first record where it says Allison Hill her job title is HR specialist but her Department is operations if we take a look at Noah roads his job title is software engineer and his department is finance so these two columns don't quite seem to match up I would expect an HR specialist to be working in HR and a software engineer to be working in it so it hasn't quite got it right
so let's make some modifications and the good thing about chat GPT is that you don't have to retype everything in because it members what you've already typed so I've set the scene it has the context I don't need to type it again so in my prompt I could say that's great however I would like 200 rows of data and I would like the job title column and the department column to be contextual and then I'm going to give it a little example I.E if someone has the job title HR specialist they they work in HR
please try again let's send this through and see what it does so this is looking a lot better if I scroll down I can see that I now have 200 rows of data which is perfect and if we take a look at the first employee Cody lson they're an operations manager and they work in operations Cynthia is an HR specialist and she works in HR so now everything is tying together a little bit better and like magic just with a couple coup of prompts we've created ourselves a really nice sample Excel data set that we
can now use to practice formulas functions and other Excel utilities so now that we have this what do we do with it how do we get it into Excel well for things like this notice that we have this little download button at the top so we can download the entire table into a CSV file so if we click this I can see it's now downloading at the top let's double click to open it and there is our lovely data set so all we need to do now is a little bit of tidying up I'm going
to select all of the columns let's double click to widen those out maybe I want to put this into an Excel table contrl T yes my table has headers let's click on okay I can go in I can change the table style so maybe let's go for this one and then maybe I want to change the formatting on the salary column let's just do comma formatting with zero decimal places but in a very short amount of time we've managed to create ourselves a really nice 200 row data set and if you need a much larger
data set you just tell chat GPT that now what about if I wanted to generate a data set that I can use to practice pivot tables because pivot table data is a little bit different with this type of data we have unique records so each record here each row is a different employee with different information whereas for pivot tables we use aggregated data so num records might have columns with the same values for example we might have a list of sales for certain stores within specific regions there won't just be one sale per store per
region there will be multiple that's the type of data that we put into a pivot table so can chat GPT cope with that can it create us data that we can use in a pivot table let's give it a go now when we go back to chat GPT you can just carry on in this thread if you want to I tend to like to clear the chat when I'm doing something different so if we go up to the top here we have a new chat button if we click that it's just going to clear out
everything so now I need to set the context again I'm a data analyst and I need you to generate some sample data that can be used in a pivot table let's press shift enter now this time I'm not going to give chat GPT any of the columns I'm just going to let it do it itself and see what it comes up with but I do need to tell it the type of data that I want please use data related to Coffee Shop sales I would like at least 1,000 rows of data in tabula format let's
send this through and see how chat GPT handles it and here we go let's have a look now underneath it says I've generated today set of a thousand rows of coffee shop sales data that can be used in a pivot table the data includes Fields such as the date and time of sale perfect store location great coffee type size payment method quantity sold total sales amount and the day of the week you can now use this data for pivot table analysis in a tool like Excel Google Sheets or a python environment and if we take
a look at this data this is actually excellent data so here I haven't even provided the columns that I want I've just said you decide and I think what it's come up with is actually really brilliant the only thing I might change here is in the store column it's just given me some very generic store names I think it's store a b c and d it's added in here so I could modify my prompt and just add in some fictional store names to make it a little bit more interesting so let's do that I'm going
to say great always good to praise chat GPT can I have the same data set but instead of store a b c and d replaced with jar bucks Coffee Bean Aromas and coffee pit let's see if it can do it and there we go like magic this amazing uses me every time it works so you can see that it's done exactly what I've asked so now it's time to download the data set we're just going to download the table again because it's a lot of data it's going to download as a CSV file which we
can then double click to open and then we're can do add tidying up so let's just double click to widen out these columns and if I press control down arrow you can see that yes we have a th000 rows it says a,1 because remember we have that header in there now I might want to do something with column A because we have the day and the time in there I don't necessarily want the time but that could possibly be quite useful I might want to split these up across different columns though now I could go
in and ask chat GPT to do that I could say where we have the date and time put those in two separate columns that could be the next thing that I ask chat GPT to do or alternatively I could just do it in Excel I'll leave that for you to decide once again I'm going to do contrl T let's put it into a table I'm going to turn off banded rows and now I have a data set that I can use in a pivot table so if we click on summarize with pivot table let's put
it on a new worksheet and maybe I want to do an analysis by store so let's drop that down into rows and I want to see the total sales let's drop it into values and there we go maybe I want to see by store and coffee type let's drop that down and we can see how cool this is so for me chat gbt is absolutely awesome for generating data sets that we can use to practice with have you tried it out let me know down in the comments and if you enjoyed this video make sure
you give us a thumbs up and maybe consider subscribing that's it for now I will see you guys next time
