How to use NEW Microsoft Copilot in Excel

we live in a world where data is now King and often while you'll be picking up a data analyst role even though it wasn't included in your job spec and that's because while your peers your manager or even your team members will send you data in Excel it could be invoice data or project forecast information and you're expected to make sense of that data report back and also make strategic decisions on the team and and also the business and that is obviously going to be impacting those important words that you left on your CV or resume proficient in Excel yes how many of us have those words and we hope while they never put to the test right but fear not we now have tools like Microsoft co-pilot which can live inside a Microsoft Excel and provide AI assistance when it comes to understanding your data yes we now live in a world where co-pilot can summarize and understand your data identifying key points in there it can also automatically generate reports and charts so you can share them with your peers and managers you can even make changes to your data using co-pilot to do all the work but of course co-pilot well it's not autopilot remember in AI does make mistakes but the information I'm going to be showing you today should certainly help live to the words on your CV of being proficient in Excel before we dive into these awesome co-pilot capabilities in Excel why not hit on that subscribe button and look at more great content that we have on copilot of Microsoft 365 to turn you into a productivity Superstar with the tools that you already have and of course if you like this video I would love it hit that like button and so you can let me know that this video certainly helped you out otherwise let's dive into Excel and find out the new world of co-pilots and how it's going to impact you and change the way that you work with data here we are inside of Microsoft Excel now the first point to note that's really important is that co-pilot well it works with file stored inside of the cloud and this file is stored in one drive for business and the version of co-pilot I'm using today is Microsoft 365 co-pilot but you can also see similar capability in co-pilot Pro as well with this data therefore stored inside of the cloud in one drive for business but we can use co-pilot to interact with a data if this data however lived on my desktop well co-pilot would not be able to actually use or manipulate this data to just ensure that your data is stored inside of the cloud now with that done well we here we have some data this currently is not a table of data you can see by clicking into it it effectively is text and co-pilot well it cannot work with data in this format so what I'll do is open on the right hand side the co-pilot button and the great news is while co-pilot will recognize that we're looking at data inside of excel we actually see we can convert this data range automatically so co-pilot can use a data I don't have to do this myself so let's go ahead and select convert to convert this data into a table instantly so our data now converted into a table I can also explain that this is invoice data now it's purely dummy data yes it's not real but you might have very similar data when you're working on projects you're doing forecasting or you're running your own business this information is really handy to understand because it ultimately says about the performance of our project and how much it's going to cost us and also any other insights so in terms of the data here the first thing we're going to have a look at is how we can get insights from this data now in the co-pilot sidebar on the right hand side we can now begin to ask information to co-pilot I might be interested here on all of our high value invoices anything above £5,000 would have interest to me I maybe have to understand where most of our project spend is going so let's go ahead and note to co-pilot to highlight all invoices which have above £5,000 as their invoice amount let's go ahead and send that into co-pilot and there we go we now have values highlighted by copilot we can see all invoices amounts above £5,000 appearing a quick access to them now anything I ask co-pilot to do well it can be undone because that's useful to know for highlighting but I won't want it apply to my data permanently so let's go ahead and undo that change now next I might want to understand well where all of the spend is on our development resource and our category of development so again let's ni ask co-pilots do a total sum of the development spend and in that sense it will go ahead and work out the overall spend automatically in the right hand side and there we go it's now total the amount that we've also spent on development and not only that it's even created a pivot chart that we could bring straight into our Excel spreadsheet I'm going to be covering that a little bit more later but here we want to understand a little bit more so I'd like to understand what are the most common categories we have lots of spend information here where is most of this spend going what's the most common categories well let's go and add that into our co-pilot chat and I look through the data to work out where the most common categories are it found out that the most common category was development there are 12 invoices relating to development activities so here we found out that the category that I have most invoices for is software development which is what I would expect from our project but I'm also interested about the percentage allocation across all of our categories let's go and add that into copilot and find out in more information around that and what we can see that copol has now responded it's totaled the count of rows for each category and in further down it's also set the percentages so I'm now aware that 24% of all of our invoices are for development we're only for Quality 4% and marketing is 10% so it's very handy to understand the overall percentage when it comes to invoicing so you can report that back to your peers maybe you're also asked well which of the supplier are we spending most of our project Budget on let's go and ask co-pilot to calculate that for us with a simple question and there we have it GA resourcing of over £42,000 of spend in the overall project I now know where most of our cost is going and which one is the most Key Supply for us can potentially holds the most risk and finally for these insights while I want to understand how our budget is tracking against our total amount let's go ahead and ask co-pilot of our total budget of £200,000 how are we tracking against that amount considering the overall spend let's go ahead and find out what copal has to say about that and there we go copilot's now identified over by £87,000 what's interesting on that question is well it needed to work out what the budget was it had to do that using human language I never told it to sum the total and then do a comparison against the total I provided it's worked out itself and provided the relevant answer so you can go further in your insights and ask questions of copilot on your data you're not limited to these scenarios but as we can see getting insights from your data quickly is possible we're using co-pilot before we get co-piloted out I'd love to tell you a little bit more about what we do and how we can help you because here at your 365 coach we're dedicated to make your journey in 365 while the best possible and our website below you'll find information on learning courses and Journeys to help become even more proficient in using the tools that you have today including SharePoint teams planner and more not only that you can also take advantage of downloading a free Microsoft 365 ebook where you can find out even more about what's possible in 365 so head to link below to find out more otherwise let's dive back into Excel and co-pilot and continue our journey and how going to change the way you work with data and of course in Excel we spend a lot of time building reports and charts so can copilot help us out I'm going to ask you to create a new bar chart based upon the total spend for each supplier let's go ahead and add this into co-pilot and see if you can generate the chart for us without me doing anything and there we go co-pilot's now generated a chart for us but it's not very helpful being in the right hand side sidebar I actually do is click on add to a new sheet and it will insert this table into Excel we now have the total sum of all of our different suppliers as well as a bar chart here showing each of them as a comparison to each other with a total amount shown Below in pounds so copilot's handly generated us a pivot chart straight from a question didn't take very long at all so now we're back in co-pilot let's ask for more insights into our data because we just don't want one single bar chart for my next meeting so let's ask it to show Data Insights it'll take the full data from the table and pull back more insights for us now it's pulled back some insights on the total spend by category but we already knew that anyway to begin with that development was the highest spend so what I can also do is add all insights to a grid let's go ahead and select that and co-pilot in real time will begin to create lots of different pivot charts on a separate tab within your Excel spreadsheet here we now see the amount by category the frequencies and so forth and which supplier has the most spend and we can even find more insights from our data now of course you can change any of these these aren't pictures if I click into this one here as an example you can see all of the fields you can change that to meet your requirements so copilot has done the hard work of pulling together and you can make amendments to improve those charts we're seeing here is a great way to report on data quickly using copon to do all the hard work so your reports can be generated nice and easy and also adapted to meet your particular requirements and co-pilot well it can also add columns and change data now you might be also wanting a particular column that can have data taken from each of these different columns as an example I'm going to ask co-pilot to create a new column in this data that will take the invoice ID add a hyphen then add a description and in a hyphen and the category and put it into a new column into our table and it'll go ahead and create that for us as you can see well the result from copilot tells us what the recommended scenario and a syntax is to use inside of excel but this time I can go ahead and click on insert column rather than writing that it's actually added it and into all of those different rows we can see we can just click into any of these celles and make a change we now have the ID we have the description we have the category that's just an example of what copilot can do with column manipulation by creating new columns and it can also do more here I'll give it a question to create a new column extracting the invoice month and go ahead and send that into co-pilot and there we go we now have another example here where it can extract the invoice month directly from the date field go ahead and insert that column and now we can work out well when most our invoices come up over the period of the years good for forecasting right so that's another capability that co-pilot can do but co-pilot cannot also change some data no we also need to consider here that sometimes copilot can't do certain things for example in our date column it's showing here in a US format what I could do is ask co-pilot to make a change and change that local in format into the UK let's go ahead and try that out with that question asked to copilot you can see here well it didn't actually do the correct change it added a short date format it didn't change the local or formatting of the column that's because copilot can't yet do that but instead I can ask co-pilot how to do it instead need to do a web search there you go you can see that co-pilot well will tell me how to make certain changes and that's the benefit whilst co-pilot can't always do things for you it can certainly tell you how to do things saving your time looking around the web or buying additional books to work out how to use Excel so copil even when it can't do things can still help you get to the end task and guide you through it but I know what you're thinking right you're going to say Scott well that was all in Excel maybe you're in a meeting with one of your peers or one of your buses and they want you to understand the data inside of that spreadsheet and ask you questions there and then around the project spend do you have time open up Excel the co-pilot sidebar and begin to ask all those questions and wait for the responses my suggestion would be not instead we can use co-pilot chat whe this is in teams or via the web on copilot microsoft.com you'll be able to then ask questions of your data in a conversational way so let's go ahead and Define our question as you can see here I've drafted my question I'm trying to understand where the total suppli spend is highest based on all the supplies in that data now we need to point co-pilot to a file so I'm going to click on the for slash key head over to our file you can see our project data invoicing go ahead left click and then submit that into co-pilot chat it'll pick up the file within one drive for business analyze and understand your question and return with the relevant results and there we go we now got the result back it identified that GA resourcing has the highest total spend of the SEC I didn't go anywhere near Excel to get that information it's able to pick it up and I can respond quickly to one of my colleagues or peers but let's go further I'd like to stand of that and have a chart because again one of my peers on my bus said can I see that in a more visual form let's go and ask it for a chart without going anywhere near Excel now we can see here the response well it couldn't give me a bar chart but what it has done is it showed me each of the spend and the percentage for each of the supplies we used from that data so I've also asked co-pilot about what the next few months look like and as you can see copilot was not able to help and that's because while copilot has no information about upcoming invoices if it did it could potentially answer that question for us of insights but it cannot guess information it does not know about so as we can see copal is limited to the information we provide it with but equally as with these capabilities you have access to the web and I know that development well most of our project spend was going in that direction right so let's ask co-pilot how we could also optimize our development cost to keep those down in the future that is not included in our spread sheet or a table of information so here co-pilot will go out to the web to find out ways that we could optimize development costs and to apply best practice and there we go just as we expected information from the company and the web so it actually pulled information here that it found in the overall content I access to but also more importantly those web results to be able to improve the way that we also work so co-pilot has changed the way works for Exile data in many ways inside sites reporting changes to existing data and also asking questions of it quickly when you're in a meeting and need answers fast so co-pilot can certainly change the way you work inside of Excel and there you have it we've now covered how to use copilot in Excel which may now mean that you're going to live up to the hype on your CV or resume are being proficient in Excel maybe you could even call yourself an Excel Pro but if you do that just make sure your next role well has co-pilot available or else that could end badly but joking aside what I've shown you how to do today should hopefully help you be able to manage data better and make decisions more quickly but of course what I will say is co- pilot is not autopilot is an AI tool an AI well it makes mistakes ensure that any data you give to co-pilot and information gets returned is correct we certainly shouldn't rely on the output of co-pilot to make really important decisions but it can help us on that journey and considering while all of the co-pilot tools are very much a version one they improving every single day and over time I feel they'll be even better what I've shown you today but there are some limitations in how we can use co-pilot today in Excel so what's next well we've also got other videos on our Channel covering how to use co-pilot in Outlook word and Microsoft Loop and even more more on Horizon so check those out if you want to see how you can improve how you work with Microsoft co-pilot otherwise if you like this video please hit that like button don't forget to hit the subscribe to find more great content like this and otherwise well I'll be seeing you in the next [Applause] [Music] one

Channel: Scott Brant

Views: 4,169

Keywords: microsoft copilot, copilot in excel, excel copilot, copilot tutorial, microsoft 365 copilot, microsoft 365, microsoft copilot tutorial, how to, excel ai, ai excel, copilot for excel, generative ai, co pilot, how to use copilot in excel, ai for excel, excel ai tool, microsoft copilot pro in excel, data analysis, ai with excel, ai data analysis, copilot in excel for charts, analyze data with copilot, microsoft copilot excel tutorial, use copilot in excel, Your365Coach, 2024

Id: ntLOE1HrF_M

Length: 17min 57sec (1077 seconds)

Published: Thu Feb 29 2024