10x your productivity with these AI tools in Excel 😲

foreign [Music] these days there is a lot of hype around chat GPT but do you know that Microsoft Excel itself has some really useful artificial intelligence features built into it in this video let me share five of my most favorite AI feature selection let's go oh the first thing is pattern recognition here I have got some names and email addresses of not so famous pop stars and I would like to extract the domain name here but I don't know the formula what I really want is anything that appears after the at the rate symbol here is how the pattern recognition works you type some values and Excel will figure out what to do for the rest of the cells [Music] so multiply.com Mappy and you can see that as I type the first two three letters Excel has already done a pretty good job when you are ready you just hit enter and all of these values get automatically extracted for you this feature is called Flash Fill you can also trigger this by pressing Ctrl e let's see this in a more advanced scenario further down here I have got some details and I just want to extract the amount now the problem is the amount can be anything in the end just the number but the separator is sometimes it is colon sometimes it is hyphen sometimes it just follows and I just want to extract the amount so I'm typing 500 120 in 300 and after typing few values I'm gonna press Ctrl e and Excel will extract the values again for me pretty good job really so this particular artificial intelligence feature of excel is called Flash Fill and you can access this with either Ctrl e or if you go to the data ribbon you'll see that there is a Flash Fill option right here while the Flash Fill does a pretty good job of extracting the data for you it doesn't tell you what formula it has used to extract the data so now let's use Excel to generate the actual formula logic so here I have a file with 1000 names and what I want is I want to generate an email address for these names the email address would be first letter and then the last name at the rate awesome dot shock but I don't really know what formula to use or how to generate that email address and the bigger problem is this file keeps changing so today we have 1000 people next week I might receive another 500 IDs and I need to generate their email addresses so let's let Excel write all of this for us we'll go to the data ribbon say get data from file from text file and point to the file say import it's going to show you a preview of the data and then say is this what you want yes but we do need to transform this data to generate the email address so I'm gonna go to the transform data option and this takes us to a special feature of excel called Power query power query is a very Advanced feature built right into Excel so that you can do a lot of data related stuff quite easily so let's understand how we can use power query to generate that email address with a formula the first thing that I'm gonna do is make sure that the name is actually used as the heading of this table so for this from the home ribbon I'm gonna click on use first row as headers next up I want to add a column that corresponds to the email address so I'll say add column and I don't know what logic to use so I'm going to use the column from example option and here in this new column we can write the example of what we want and power query will generate the relevant formula so here this will be B at awesome.chalk let me get some here and after you type a couple of values the rest of the names have automatically been generated by power query you can see that they all look correct and that is perfect and when you click OK it not only adds this but when you go and edit this you will see that it has generated a formula to actually write that email address for you so this is an artificial intelligence feature built right into Power query and once you are done with all of this you can say home close and load and it's going to load that data with all these email addresses you might think oh this looks good but what if my data changes so let's test this out I'm gonna go to the very first here and let's write my name chandu and save this file close it out and right click here refresh and you will get my name and the corresponding email address automatically generated for me how awesome is this the third artificial intelligence feature built right into Excel is AI analysis of data here I have got a sample of our awesome chocolates sales data there's quite a bit of data and my boss wants me to quickly analyze it and tell me what's going on but I don't have the time to do the analysis no worries just select the any cell in this data and from home ribbon click on analyze data button this button looks fairly harmless but when you tap on this it's actually gonna look at your data and do the analysis for you so for example here it has done all of this analysis and produced some pretty cool and interesting graphs so for example here it has done an analysis of expenses by sales person and product and provided me with a matrix format if I think this is valuable I can insert a pivot table and I'll get all the corresponding numbers straight away generated for me without me doing any work let's go back and see what else it has done it has produced a graph here and then shows that box has six outliers this looks really interesting I'm going to introdu insert that and then take a look at this so this is how many boxes we have been shipping over a period of time and you can see that these data points here all the way at the end of the graph there's some extreme values there of course this is kind of known in our business simply because we have had a massive surge in demand around December 2022 and hence we had quite a few highs and lows and naturally the demand for chocolate is kind of Peters down after Christmas so we have less boxes going out after Christmas not only it analyzes the data but you can also ask questions so this is like really like chat GPT but you can talk to your data you can ask what you want from Excel and it will do the analysis so I'm gonna ask a question like what is the total sales in December 2022. [Music] and here I'll get a number which corresponds to December 2022. I'm gonna make a note of this number three million seventy thousand and let's just double check this so let's put filters here and expand and select just the December portion so this is only December data points and I'm gonna select this sales thing and make a note of this number so three million seventy thousand two hundred and twenty Twenty Eight that's exactly the number that Excel has come up with not only that but you can ask more specific questions too for and then let's just say after ninths so that is a very specific product after nines within the month of December 2022 and then that total has come up so you can ask many questions like this literally talk to your data like how many different products are there 22 because it is doing a distinct count on the product or how many different products are there in last week of December 2022 1 so it tells you within the date period 26th of Jan 2022 to 1st of Jan 2023 we have sold only 21 different products even though we have total of 22 products one of them did not sell at all in that last week so that is the number three I hope you like this feature and that reminds me if you are enjoying this video give it a like it really helps me let's go to the fourth one not only you can ask Excel to do the analysis but you can also ask Excel to recommend you what to do when you are not sure so for example here I have got some data for some of our products in the last three months and I'm not really sure what chart would represent this data better so I'll select this data and then go to insert ribbon and click on the recommended chart option this is going to look at your data and recommend you some chart options based on what is available in Excel while the recommendations are not always perfect they do work pretty well and give you the chart right out of the box so for example this one looks interesting I'm gonna just click and insert that straight away it does tell me for each product how we did in the last three months in a kind of scene for example here I can see peanut butter cubes add a growing thing whereas white chocolate went up and went down likewise if you have got more data for example here I have got my awesome apps data how many downloads how many uninstalls five star ratings one star ratings and total ratings and out of all of this data I'm not really sure what to analyze this how to represent this in a pivot format so again I can go to insert and click on recommended pivot table option the normal option would be to use pivot table and figure it out yourself but if you are not sure you can use the recommended pivot table option tap on this and it's going to generate some pivot table formats for you so for example it's going to show you five star ratings by month and app and how many file Stars ratings we are getting for each of our apps or five star ratings and downloads by app like that once I like a particular paper table I can just click on the new sheet button and such a pivot table will be generated for me and neatly presented this is also a good thing for beginners who are not sure how to set up a paper table they can use this feature to generate the pivot table study it and learn more about it so those are the two kinds of things that Excel can recommend for you pivot tables and charts before we jump into our top 5 option I would like to share a bonus trick with you for this we need to go to OneDrive let's create a new Excel workbook here and I'm gonna paste some data into this workbook this particular feature is called automatic formulas for this to work currently you need to use Excel online on OneDrive for either personal or family accounts eventually this feature is going to be available on the desktop as well as business accounts too once you have some data make it a table so select all of this and use the format as stable button here or insert table button here this is going to turn your data into a tabular format now let's see what formula we can write to combine the first name and last name into full name again we'll assume that we have no idea about what formulas we can use so here if you type some data Excel is gonna recommend that this is the pattern and here is a Formula that you can use and if you tap on show formula it's going to show you the formula that you could use to generate this data automatically when you are ready just hit the apply button and you will get all the names automatically calculated to you through formula let's write one more formula this time we want to generate an ID number for these people so we'll say ID number and then say id001 id002 ID 0203 [Music] at this point Excel has figured out the pattern and it has recommended that this is the formula if you see the formula it is using id00 Ampersand text of row minus 3 in the zero format now this is not a perfect formula I'll apply and then you can see where the problem is it's actually adding an extra zero here what we wanted is really three digit formatting but it has done a decent job it has given you what you want and you could probably take this and improvise this by adding some additional conditions here or do something else this particular feature is called formula by example and it is evolving constantly let's hope this will be added to desktop Excel as well now let's talk about the fifth example of excel AI features and this is by far my favorite as well so here I have got some app downloads for our chocolate Crossing app it's a small game and here is the download information for the first six weeks of 2023. given this information my boss is asking me hey can you forecast what would be the total downloads going forward in in the next three weeks so one way to do this is you can select all of this data insert a line chart or if you're not sure you know use the recommended chart and it will it will recommend a line chart anyway and once you have added this you can use the plus button and add a trend line now we can see that on a weekly basis we have this kind of a Sawtooth pattern in our downloads people download the games more towards the weekend and during the week days like Tuesdays and Wednesdays fewer downloads are happening but in general we have been trending upwards so given this what would be the future look like that's the thing if I have the trend line I can select the trend line right click and format it and then I can say can you forecast it for next 21 days so and then hit enter it's gonna forecast but this forecast will only indicate a linear Trend it says this line will continue like this this is not good for me I would like to see what that Sawtooth pattern itself will look like so this is where one of the coolest features of excel is really helpful just select the data go to data a bun and click on forecast sheet option this is going to study your data figure out that you have got a Sawtooth pattern going on and try to repeat that pattern while keeping that upward Trend continuous and then it will generate this kind of a graph it will give you boundaries also based on statistical significance so it kind of gives you those you can see the options here and then see that for example those lines correspond to 95 confidence interval and it has done a pretty good job of detecting our seasonality which is a week or seven days once you're happy with all of this you can set how far into future you want to forecast so it's forecasting for the next two weeks if I want it for three more weeks I'll pick that date and then say create it's gonna create a sheet with all the graph and here is the best part It's Gonna Write the actual forecasting formulas also so if your data changes you just have to update these values and it will figure out what the forecast would be as per that using these five techniques we can get a lot out of Excel without even doing any work we just tell Excel what we want and Excel does the work for us but if you take it one step further and understand the actual language that Excel speaks the formula language then you can get even more out of your data here is a video in which I introduced the 10 most important functions in Excel check it out and learn the important functions for data analysis catch you there [Music] [Music]

Channel: Chandoo

Views: 499,402

Keywords: chandoo, chandoo.org, Excel, spreadsheets, chat gpt, chat gpt excel, how to use chatgpt, excel tips and tricks, chatgpt excel, excel chat gpt, ai in excel, ai, chatgpt alternatives, how to use chatgpt style ai in excel, use artificial intelligence with excel, Top Excel AI features

Id: xGqH__hLVcU

Length: 18min 42sec (1122 seconds)

Published: Tue Feb 28 2023