 Hello everyone and welcome back to another video. In today's video we're going to be taking a look at the new AI functions we have available to us in Excel thanks to a recent add-in that uses chat GPT but before we get into that please if you have not subscribed to the channel already please hit that subscribe button below and if you enjoy this video please don't forget to hit that like button. So the first thing we're going to need to do in order to get access to these new functions is go to the home tab in your ribbon at the top of the page and then go all the way over to the right hand side well particularly in my case and click on add-ins and you'll see you get this pop-up. So for me chat GPT has already been installed however assuming you haven't already got this installed you just need to go to the more add-ins which will give you this pop-up and you can see we've got office add-ins here and simply search chat hit the little search icon and I think it's the second or third icon we've got there is chat GPT for excel so if you click on add that will get it installed and you'll then have it available in your add-ins like we just saw I already had. I can't remember off the top of my head if you need to close excel and then reopen before they become available so if this doesn't work first time then obviously suggest giving that a go so as you can probably see on the screen we have five tabs at the bottom here each containing a different one of these new AI functions and if we were to go into my answer box at the top here and type equals AI you can see how we've now all these are all of those functions that are now available thanks to that add-in so the first one we're going to look at as you can see titles on the page is AI dot ask so simply what this allows you to do is ask a question and as you can see just above where I've entered that formula we've got a question of when was Microsoft excel first available to access the relevant function and you can see it's already been highlighted there but we're going to continue typing it the type dot ASK for ASK hit tab you can see obviously is selected that formula all we need to do is select the cell reference in this case contain that contains our question close our brackets hit enter and you can see it first it's given that busy but very quickly after it's given us a result and let's just do a wrap text here oh and merge as well so you can see that you can see we've got a result for our question so Microsoft excel was first released in 1985 and of course yes you go and change your question that you have here I can't think of one on the fly but the formula will continue to load and give you a response to the question so particularly useful if you have a workbook where you might have users who could have questions or you want them just to offer the general ability to ask a question so that is our first function the next one we're going to look at is fill so what fill allows us to do is pretty much fill in the blanks using some existing data so for me you can see we have three columns we've got city country and population and obviously you can see we've got all of our cities here however we've only populated the first two rows for country and population and if you're anyone like me your geography around the world isn't the best so rather than keep going to google to find the country and the population and I'm just stopping myself there because I realize I've got the word population but we don't want that let's call this region so we're sorry as I was saying rather than going to google to find out the country and region for each of your cities we can now use one of those ai functions to populate it for us so we'll do equals ai dot and this time we're going to use the one fill and I've just hit enter there as well and you can see there's two inputs that it requires us to enter or provide the example and the next part will be partial so for us we're just going to provide these top three rows including the headers as our example so it can see what information we're after and where I've already provided it we'll do a comma and then for the second part in the partial we'll provide obviously these obviously but our four remaining cities that we want to get information for we'll close brackets and hit enter and you can see initially get the load and then once it's ready it's provided us that additional information so we can now see for Rome we've got the country of Italy and the region of Europe and as you can see it's one of those spill functions where we've just entered the formula once and it's expanded into the range that is required to provide the additional information so again I've used this for city country region but I'm sure there'll be many other scenarios that you can use this where there's a pattern to be recognized and provide the additional results the third option or formula we're going to look at is extract so this is particularly useful in the scenario that we've got here where we've got a list of well not really four sentences but we've got four strings of text of which a name is captured within each string and what we want to do is simply extract the name as you can see what I've mentioned here so that we've got a list of all of our names corresponding in our output section here so previously you could have done this by doing and you may still do using a multitude of mid left right formulas and functions and obviously searches as well to try and find names if that's even possible in this scenario but we've now got obviously there's a lot easier way to do it because our AI function recognizes what is a name and obviously what is the should we call it the text that you want to drop so really simply all we do here is go equal AI game this time we'll use the extract function and we're going to select our range so you can see we've got we're going to select all four rows in our example we'll do a comma and we'll select then obviously the value of the cell containing the information that we wish to extract close brackets and hit enter and that will quickly load for us and you can now see it is giving us our nice concise list of names so another scenario this could be useful is if you're working with email addresses and you want to extract the name from the email address and I'll leave you because there's obviously again another multitude of ways to do this to look at other types of information that you could extract rather than using just name a fourth function is going to be translate so again a really useful function if you're working in a a large corporation where you've got multiple countries and need to translate text or just useful to have to hand as well and a great addition in your report where and if required so you can see we've got a source at the top here and I've just provided some English text here just giving us a nice simple couple of sentences and what we want to do is translate this into French so that we have the French translation below so once again we'll do equals ai full stop and this time we're going to go and use translate so let's type translate hit tab we've now entered the function and again it requires the two inputs so the first part is going to be our value which is going to be our source at the top here comma and then we'll select the word French or more so this the cell containing our desired language close brackets hit enter and once it's ready you can see we now have our French translation and I don't think I've done this in our previous examples but let's say we now want this in Italian now maybe shall spell it correctly you can see all we need to do is change of the language desired language that we have there and it'll do that just for us let's try one more let's go Spanish of interest and yep there we go we have now got Spanish in there so you could obviously improve upon this greatly by having a drop down here giving the user the ability to select a list of desired languages and it's probably all the ways worth mentioning although the model is looking really well and I'll say I am by no means an expert in translation but it's always worth obviously doing some tests to ensure that the translation has gone through as desired our fifth and final function we'll be looking at today is for table so the general purpose and use for this will be is providing a table based on an input such as the one we have here which is really a question to list of 10 biggest countries of the world by population so again another task that we might have previously had to do going to google and obviously doing a search now we can simply do that within one function so we'll do equals ai.table hit tab and all we're simply going to do here is select the cell reference containing our question close brackets hit enter and you can see what it's now done is given us not only a list of those 10 business biggest countries but it's also provided the population for each country using a consistent point in time which was 2021 and of course we wanted the top 10 so what it has done for us as well is provided a ranking down the left hand side there as well so we just make that ever so bigger so we can fit it onto the page and there we go with our fifth and final example function using table so I hope you enjoyed that video and it introduced you to these new functions that we now have available to us thanks to the add-in that we added at the beginning of the video if you have any questions at all with this or future videos please just drop a comment below the applicable video and I will get back to you as soon as I can and lastly if you do enjoy these videos please don't forget to hit that like button it's not only greatly appreciated by me but it does help that all important youtube algorithm enabling other people to also find these videos as well