Microsoft Copilot in Excel: What It Can and Can’t Do

have you ever wished for a co- pilot while navigating through Excel well your wish has been granted by Microsoft what's up party people I'm Dave I'm a CPA I go by piggy bank accountant and today we're going to explore Microsoft co- palot in Microsoft Excel a tool designed to increase your productivity and efficiency in your spreadsheet we are going to go over the features of Microsoft copilot in Excel and also its limitations Microsoft C- palot is a generative AI assistant that integrates into your favorite Office 365 applications it's designed to assist you by generating code text images and other content based on your inputs and the context of your work in Excel for instance co-piloting help automate tasks generate formulas and provide insights into your data it's like having an assistant sitting next to you ready to help whenever you need it in other words copal is the clippy of 2024 please not that I'm using co- palet Pro and it is a paid version I recently made a video walking you through the main differences of the free and paid version of Microsoft co- pallet and it's right here at the top we are in Microsoft Excel and co- palot is located at the top in the ribbon something that is very important to note is if that the button is grayed out you need to save your workbook on one drive if your workbook is saved on your local disc it won't work I would click the button here and we have a warning here that co-pilot is in preview mode what does it mean it means that co-pilot right now is in feedback mode it's not in production mode it means that the community is more than welcome giving feedback on copilot and also please know that right now co-pilot in Excel is available only in English in other apps such as word OneNote and PowerPoint it's available in every languages another thing that you need to know is that you need to be very specific with your questions with Microsoft co- pallet in Excel and also that your data need to be formatted as a table as you can see here I have data it is not in a table and co- pallet also suggest me do you want to convert this as a table you can click on convert and here we go I have a table if it doesn't suggest you to convert as a table for some reason let's click on undo you can just select your data go into insert and then click on table here and make sure that my table as headers is checked here we go we click okay it does exactly the same results we're going to play around with cop palet and Excel and what I'll do is actually play with the analyze function so as you can see I cannot do anything if I only selected a cell that is not in a table and as soon as I click on the table Coop palot activates itself in the right pane I have a data set here which is the year C category product sales and the department and we are going to see what copal is suggesting me when I click on analyze it says I can analyze data and show Insight in charts and pivot table and then it asks me to select a prompt let's try to ask copal the total sales by category as a column chart and let's see what it gives me I click here and then it suggest me a pivot chart and let's click add to new sheet click here and here we go we have a sales as as a category copilot did it by himself using the thousands and it also sort the data descending sort Z2 a so we have our Top Value at the top and we have the movie Magic $3,000 at the bottom makes a really great chart already titled everything's perfect on this one let's go back to our data and see what I can do what if I do inside for sales let's just click on that here's a pivot chart that shows an insight about your data let's add to a new sheet and what it gave me actually is the department and the sum of sales so basically it gave me pretty much the same as my previous data except that it was a category but this time it went by Department it also highlighted me the biggest sales which mean the headgear and the Playtime Workshop I'm not too sure of the results behind that which means I'm not sure what he did like did he put only the top two did he say Okay totalize 40% of my total data so we're lacking some information here on what it actually did in the back end I'm going try and refresh the prompt that co- pilot is giving me and see if it gives me idea I just click on refresh here in the Paine and let's see what it does when I click on total sales for product excluding Beyonce's baret I don't know why we will want to exclude Beyonce's Barett but we'll do it all right let's add to a new sheet again so copal is just creating me a basic pivot table if I click on this pivot table it is basically a normal pivot table so this is really useful if you don't know how to make a pivot table at all you don't really need to learn it copilot just did it for you but what's dangerous I think here is that when you click it filtered out beyon say Barett but as you can see here it's still checked but it went into the label filter and it put a label filter which I think is not really obvious and it's kind of dangerous so you can clear the filters again it's appearing again here and then you can just uncheck it I think it is much clearer when you see it like that but basically simple pivot table product sum of sales and it's sort descending once again one of the limitation is that co- palet cannot act on the pivot tables themselves if I select my pivot table I go in co- pallet and I just cannot ask any question it's grayed out once again I'm going to ask another question to cop palot and I'll ask him can you give me the top five products in term of sales in a bar chart make the bar chart pink gradient so this is one of the limitation copal tells me sorry I can't do multiple things at once for the best experience add one request at a time so let's see if I just tell him can you give me the top five product in term of sales what it's going to do so it took roughly 20 seconds here's a pivot table based on your prompt I will add to a new sheet and it didn't do any chart it just gave me the top five product and it used the top 10 function in the filters so value filters top 10 and it put the top five item okay let's ask co-pilot question let's not use the built-in prompts so I will say can you do a percentage of sales by line based on the total of sales let's see what it gives me so co- pallet answers me I could not create a line chart but here's an answer that may be helpful is if I had to new sheet it just gives me the sum of the sales so copal didn't quite understand my question here let's try it again I will [Music] say so here's a suggestion for you calculat the percentage of total sales for each product by dividing the sales of the product by the sum of all sales in the table so it actually works if I just Mouse over on insert columns it shows me in column f what it's going to add we're going to add it click on insert column done I added the sales percentage column F this is very interesting I can click here below explain formula and it's going to explain me the whole formula that it did so co- palet added the sales percentage in the colum if I do the total it gives 100% it actually did it right good job we're going to test once again the add a colon feature so I'm just going to click on my table once again and click on ADD formula columns and see what scope palot is suggesting me it tells me add a column to compare sales and sales percentage let's click on this I am kind of curious I'm going to insert Colin and it actually didn't give me anything good here that's not really great what co- palet is also suggesting me is how many category of celebrity hats this can be useful I will click here and it's giving me the result six so it just told me that there is six line of C celebrity hat it basically did a equal count but it put it in a pivot table so once again not super useful will be useful if there was like 3,000 lines pretty much we could have only done a count if but it did it like pretty faster so that's cool one thing that I like that actually co- palet is not suggesting me right now but it did when I tried to practice and show you guys the best of the best of Co pallet is that it added the sales of the latest year add a Colin [Music] insert colum and as you can see this can be useful instead of doing a pivot table and then sorting on 2024 it gave me only the sales for the year of 24 for each category it can be useful I mean not all the time but that's something it can do so I didn't have to type the whole formula like either the if the year and then like it's a really complicated function but it actually worked I will do delete a few columns here and try to add the unique product ID for every product using a vup and let's see if copilot can do this that will be nice if you tell him can you use vup or can you bring the product ID for each product they are in the product ID tab here so as you can see all my unique IDs are there I go back in my master sheet and I will ask [Music] calot sorry I can't do that it doesn't work I'll try to to be more specific because you need to be specific with your [Music] questions it doesn't work once again each like on this video equals a day of watering for my plant support plant live give a thumbs up to this video we're going to test the sort and filter features click here sort and filter and it gives me both the top 10 values in year I don't think that's what I want I would probably want to bold top five values in sales maybe right here we go we have the top five value that are bold I will ask [Music] him Co pilot here applied my colar on the colum cell so I have my top 10 if I go filter by color I have 10 values it also tells me that it puts some color in the department colum but let's see what it did as you can see if I remove my colar it doesn't work it's because it use the conditional formatting if I go in conditional formatting I manage my rules so as you can see what it did it put the top 10 as well in column e but it's based on numbers and my column e is not numbers it is letters so it worked for my colum D because it is numbers so it tells me apply some color on the top 10 these are numbers but these are not number but I wanted copilot to actually copy the format of the column D in column e didn't understand what I told him fair enough other than that you can do basic stuff if I ask opot can you sort descending my data based on the year it is probably just going to put descending based on column A as you can see you have 24 at first and you have 2019 at the end it can be useful once again if you're not really used playing around with filters but for someone that is intermediate or even that uses Excel every day it should be pretty easy so I don't think it saves anyone any time here except if you have a very very large data set that you know already let's say you have 55 colins you know them by heart and you don't want to go at the end you can just ask co- pilot sort them descending but again I don't really see the use case here right now my honest thoughts on copilot and Excel right now it is in preview mode so it is normal that it is lacking some major features I think it is still good for someone that is beginning in Excel like if you don't know how to do something you can just straight up ask it it's going to probably give you the right answer it's not good for people like me that are experienced in Excel I think it doesn't make you go faster I think it's just some noise would it get better I hope I like to tell people do you remember when internet first came out when your mom was getting on the phone and it was cutting out your internet I made other videos on copilot right here and I think you should watch them because this is the future of work of course subscribe to the channel for more content on copilot and Xcel cheers

Channel: Piggy Bank Accountant

Views: 3,498

Keywords: Microsoft Copilot, Excel, Office 365, AI assistant, Tutorial, Review, AI in Excel, Microsoft AI, Copilot Review, Excel Copilot, Office Tools, AI Technology, microsoft copilot ai, microsoft copilot pro, microsoft copilot tutorial, microsoft 365, microsoft 365 copilot, generative ai, copilot, copilot for work, copilot pro, microsoft copilot in excel features, Use Microsoft Copilot in Excel, What are Copilot features in Excel?, Analyze data in excel with microsoft copilot

Id: -fsu9IXMZvo

Length: 13min 5sec (785 seconds)

Published: Tue Jan 23 2024