Microsoft Excel with Copilot Complete Guide 2026: From Fundamentals to AI-Powered Workflows

1. Welcome Video: Hi, guys. Welcome to my class on Microsoft Excel with copilot Complete Guide 2026 from fundamentals to AI powered workflows. My name is Noi Kumar Das. Just to give you a background about myself, I am an ex Google employee with 16 years of experience into paid advertising, and I've been teaching paid advertising for more than ten years now, and I teach to a lot of young professionals and entrepreneurs and experts who want to get into this field. I wanted to take this opportunity today to let you know what you're going to learn in this class. So we're going to look at understanding the environment around copilot in Excel, activating copilot in various settings, formulating basic tasks and queries with copilot. We'll also analyze the effectiveness of copilot in performing various tasks in Microsoft Excel and then recognizing various supported data types also in Excel. I will also show you how to identify and navigate the key components of the Excel interface. We will use some basic features such as save, insert and export functions and customized interface to improve our workflow. We will apply some basic arithmetic operators as well in Excel and use copilot in that. We will see how we can use logical lookup and match functions as well with the help of copilot tool and then implement conditional functions also for data analysis. We will see how to use data and time functions as well in Excel with the help of copilot. You will also see how we can enter and edit data efficiently, use copilot in Excel for data entry and formatting. We will see various types of formatting options which you get in Excel with the help of copilot and improve data readability and presentation. We will understand the fundamentals of prompt engineering in copilot and use it for giving better prompts for doing various functions in our datasets. We will also try to optimize the use of copilot to automate a lot of Excel tasks and evaluate the effectiveness of these prompts. I hope by the end of this class, you understand how we can make use of co pilot effectively in our Excel work on a day to day basis. Thank you once again, guys, for checking out my class, and I'm really excited to see you inside the class. 2. What is Microsoft Copilot, How to Get Started & Activate Excel Copilot: Hi, guys. Welcome to this session. In this session, we'll talk about what is Microsoft copilot, what are its capabilities and how you can activate it in your Microsoft Excel. So if you look at Microsoft, co pilot is primarily AI powered assistant, which is integrated inside the whole Microsoft 365 apps. So you will find it in Excel, Word, PowerPoint, Outlook teams in all of these Microsoft apps, it has been integrated seamlessly. Now, this is built on advanced LLMs like GPT 4.14, and it helps the users to understand data in a much more intrinsic manner, generate content about it, automate tasks, and work faster as well. So if you see it works directly inside Excel and it can analyze data, summarize insights, I can create formulas, build charts, clean the datasets, and also automate workflows. So the intent of this tool is to design to enhance your productivity by combining various Microsoft graph data in forms of emails, files, calendars with Excel tools and give deeper context aware assistance. Now, if you look at the capabilities of Microsoft copilot, it's ideally around data analysis. I can help you in summarizing data trends, patterns, key metrics, generating insights, recommendations, and explanations it can give. Also formula generations. Creating complex formulas automatically, helping to correct and optimize existing formulas, it helps to do data cleaning as well, removing duplicates, restructuring the data. Filling up the missing values, converting raw data into organized tables, also visualization. I can generate charts, pivot tables, dashboards, creates reports with explanations, workflow automation. As it can provide, which is automating repetitive task, creating step by step processes and macros as well. Also you will see natural language commands. So you can perform these tasks simply by giving specific prompts, and the copilot tool will be capable of doing that task for us. Let's have a look at this, how you can go ahead and activate copilot in your Excel. So once you have your Excel open, it will be pretty simple thing to activate it by going to the top right corner where you have the copilot icon, and you can click that on, and this will be the window where you can start working with it. You can give your prompt right here. It says message copilot. And you can go ahead and give the prompt and based on the prompt, it is going to analyze the data and give you the outputs. So we'll see multiple different scenarios of how we are going to use copilot in different situations. Also, it has the capability of inserting data, whatever analysis it does, that data can be inserted inside Microsoft Excel, and then it will make your productivity, your work much more better, high quality. I hope this makes sense. I hope you understand now what is Microsoft copilot and its capabilities and how you can get started with it on Microsoft Excel. 3. Data General Analysis: Hi, ais. Welcome to the sessions. And this session, we'll do a general data analysis of a dataset with the help of copilot. So let's have a look at this. So let's say this is the dataset which we have, for which we want to just to understand how copilot can help you analyze this data. So let's give some simple proms. Let's say we are going to say So here we can give the prompt. So now it's going to work on that dataset, analyze the information and give us the information about what we are asking from the data specifically. So like this, you can start the analysis, and there are various things which you can do over a period of time. So now you can see it's telling us that the store A specifically has the highest sales. Now, based on this, you can furthermore dig deeper into the dataset and ask for more information like in this manner, which brand had the highest number of sales. Going to look at the data and give us the information. As you can see, it has told us that Nike has the maximum number of sales. It also gives you an option to insert that into a new sheet. If you want, you can do that also in this particular manner, so we have the data here. Now, let's say, furthermore, we want to see specifically, let's say for a specific here, So now we are asking specific information. For a specific year, we want to know which brand had the highest sales. Now it's going to analyze that also. Now we get the answer over here. In 2022, Adidas was the highest sales which it had got. Now, let's say we want to know specifically what has been the net profit? And we're asking it to share the net profit because we already have the sales data and cost data over here. It should ideally be able to do that. So it has taken the data from here, and now it's going to it's adding the net profit, so we can simply go ahead and you can see it's highlighting as when we hover on it, so we can insert that as well. So now the net profit has been added also. Now, other than this, we can also ask it to provide, let's say, the total number of customers. Okay, so which brand at the highest number of customers. We can understand that as well. You're going to look at the customer's numbers and based on which it's going to determine that. So it has that as well, the Nike had the highest number of customers. As you can see, now with the help of copilot, all these we can retrieve from the data. It can analyze the data, retrieve that information and provide us. There can be some instances where it can also provide information which you can update on the sheet adding a new column, possibly, sometimes highlighting certain things. If you want to highlight certain cell, certain data that it can also do. Also adding new analysis that can be added in this particular manner. Hope this makes sense. We're just starting off understanding the capability of this tool specifically when we're using it with Excel. In the coming videos, we'll also see various scenarios in which you can make better use of copilot with Excel. 4. Data Q&A: Hi, guys. Welcome to this session. In this session, we'll see how we can do a little bit of question answering session with our data with the help of copilot. So what we're trying to do here is we're going to ask specific questions to the dataset through copilot prompts and try to see what kind of insights it can give us. So once we have the data set, so this is the dataset which we have and on this we'll have a couple of questions asked, and let's see what kind of responses we get the analysis, what it does of it. So let's start with the first question. This is a particular dataset about a shoe brand, specifically for two stores data which we have, and going to ask the first question, which is going to be around which shoe brand generates the highest total sales across all stores. It's going to work on the datasets going to give us some insights about it. So here we have, so we can see that it has answered the question. Nike generated the highest total sales. It has also given us the total sales value as well. So in this manner, we can understand the data. You can insert this in the new sheet as well in this particular manner. Now look at a couple of more questions. Let's say, what is the relationship between the number of customers and the total sales? It is able to analyze the data and then give us the information. It's giving us some information here. Now you can see in this manner, it is giving you the data as win and it's giving you the analysis as well, giving us the insights about the relationship between number of customers and the total sales, which we can insert as well. So this way, we can make use of it. Now, looking forward, let's say, which store achieved the highest profit margin? So we want to know based on the sales and the cost incurred, what was the, which store worked out well? So basically, primarily we're working on these two particular columns and we want to do a total understanding of both the stores story and and based on which we want to understand that. It's giving us that information. Story in totality was better, had the highest average profit margin with a value of approximately 0.37. You can see like this, you can make use of copilot to get a lot of insights very quickly rather than doing it manually yourself. How does average customer rating vary across various shoe brands? Let's see this as well. Very interesting. In the sense, understanding the rating part which we have got here is going to analyze that is going to tell us how has been the rating across the brands? Now you can see this is how the rating has been. It's going to do an average of the ratings of all the brands and which you can also add to the sheet. Now we know how each of the brands rating was. We know which one is doing really best. In this case, it is already put it in the descending orders. We know that Nike is the best in terms of the customer ratings. Which year record the highest number of customers overall? Let's see this as well. This is going to be pretty simple, I guess, to figure out. Otherwise, in a normal scenario, how we are going to do is we're going to pick up every single year and we're going to find out the total number of customers for that year that we'll have to do manually. Other than that, we can make use of co pilot for the same thing. The year with the highest number of customers overall was 2023. So we have that number in a very short span of time. You can see this is how very quickly you can get your answers from copilot, where it analyzes your dataset and gives you those insights. Let's look at this, which brand has the highest average cost to sales ratio, efficiency of pricing. The ratio between cost to sales. So that we want to know and which has been the highest, which brand had the highest. Puma is coming to that average cost to sales ratio was 0.63 64, which was the highest across all the. Are certain stores consistently performing better in both sales and customer ratings? We want to know whether in both these criterias, is there any specific store which is performing better between store A and B? Here we have the data where we can see the sales and we can see the rating as well. We can say that store B is doing pretty better, comparatively better or both are almost similar actually, in terms of rating. Which products stores or brands have the highest RI Now they're going to look at the revenue part. The ROI, they're looking at the sales part and they're going to look at the cost part and figuring out which products specifically or brands have the highest ROI. Here we have that now. Story. Mostly, it is going to be store A, which has the best ROI at this moment. Is there any seasonal trends across years? We're going to look for any seasonal trends across the years of this particular dataset. We're going to look at specifically for any seasonal trend as well. So it's saying that to identify any seasonal trend across years, the data should be grouped by year and analyze the patterns in sales, number of customers or net profit. So here, what we have now is a different dataset which is for sales, number of customers and net profit being shown to us, which we can also make use of the line chart was created to show how sales number of customers and net profit changed each year, specifically. So here we can see that specific you can say the sales increased post 2022, specifically, and that is what has happened across all the stores, I guess, okay? And also, the net profit also increased, however, number of customers remained the same, usually the same, you can say across all the years. Lastly, which store and brand combination has the lowest customer rate? And what might be causing it. Just going to look at the store customer ratings and based on which is going to identify which has the lowest one. We can see that Reebok in 2022, had the rating 3.7, which was possibly the lowest, which we have got right here. I hope this makes sense. I hope you understand now how we can make use of copilot for analyzing our datasets on Excel very quickly, get the insights from it and based on which we can make our inferences and use that for our work in different scenarios. Thank you so much guys for listening to this, and I will see you in the next video. 5. Data Filtering 1: Hi, guys. Welcome to this session. In this session, we'll see how we can do some level of data filtering as well with the help of Po pilot on ER. Let's have a look at this. Let's say this is the same data which we are working on and we want to do some level of data filtering on this, let's say the first thing which we want to filter is let's say filter by here. So the year column which we have, we want to see all the data for a specific here, we're going to give that as a prompt and let's see how Ppilot works on that. So usually it is going to be able to do so and it's going to filter out and show you specifically the data for that particular portion of the dataset which you're working on. This is really, um, amazing because with the help of this, you can do a lot of dissection of the data, and then you can go ahead and understand it also pretty well. So here it has done that. We can just apply. And now you can see we're able to see the data, which is for 22 in the same manner, let's say we want to see filteration by store. So now what we want to do is we want to, let's say, unfilter the whole thing. And we want to again do it by a different category, which is, let's say story. So we want to filter it by store. So as we know, this data has two stores, specifically, so it's going to look at and now we can apply. Now you can see the store A data being shown right here. Now, in the same manner, a great thing with copilot is you can also be a little bit more specific around what you want here. Let's say, I want to see for store B. Now, in this particular case, what is happening is, first of all, we are being a little bit more casual in the prompt which we're giving it also. Enacting it like a data analyst. Now what is to happen is it's going to unfilter story and show us store B, which is really great. So we don't have to manually unfilter also. Let's see how that works out. This is really good in terms of going one step further where we can go ahead and it can unfilter and give us store B. So now you can see this is going to be really useful when we are working with a huge dataset and giving such prompts can save us a lot of time in analyzing the information which we're looking for. Other than this, you can also do combinations. Let's say I want to do multiple filters. That also can be done. Let's say I want to filter by brand and year both together. I'm looking for filtering a brand like Puma, uh for 2024. Now, it's going to look at this particular data. This is a loophole. Obviously, is going to look at this and give us the data in this. If you want to apply for this, specifically, we can do that. Now you can see it is going to give us Puma, which is for the year 2024. So this is how we can work with filtration as well with the help of copilot. 6. Data Filtering 2: Hi, guys. Welcome to this session. Continuing further with data filtering. Let's look at another feature, what you can do with copilot on Xen. Let's say now we are going to give another specific prompt to copilot where we want to do filteration in a different format. Let's say we are going to give a prompt, which is to filter column store A specifically. Now what we are asking you to do is to find out which year had the highest total sales. So what's going to do is, first of all, obviously, it has to look at all the store A entries, which are these. Now, it has to come to the sales section and find out which year had the highest total sale. For every year, it has to sum up the total sales and then give us the out. That is the expectation. Let's see how it does that. Here, we are not looking for a specific maximum sale. We are looking for the sum of the sales for a specific year for specifically storing. So now you can see here it has given us 2024 had the total sales, which we can insert as well in a new sheet. And you can also see the data. This is the total sales which it has got for 2024, specifically, which has the highest number of sales which it has got. And you can see it has given you some explanation as well. This means store A sold the most shoes in 2024 compared to the other years. So when it says most shoes, which means that it has done this summation, the addition of all the sales together for 2024 for specifically for storing. Now, see how it changes now the output if we make some changes to the prompt. Let's say we are giving the same prompt but now we say that which he had the highest sales, not total, but sales. Now what it will do is it is not going to do a total of it. It is not going to do a summision of the numbers, but it's just going to look at which year had the highest sales. Possibly the answer in the output will be different here. So now you can see it clearly says that for store A, the year with the highest sales was 2024. It has not even given us the output which particular number, which we're looking for. So my point being this that the prompt which you are giving to copilot needs to be that specific. You have to punch in that particular keyword which has to be there, which gives you the right output. So make sure whenever you're asking for specific information, it can be, let's say, you are giving an output where you're asking for highest total sales, highest average sales, highest, let's say, uh, minimum sales which we have got. Then only you can get the specific output which you're expecting. I hope this makes sense. Thank you guys for listening to this, and I'll see you in the next video. 7. Data Formatting: Hi, guys. Welcome to this session. In this session, we'll see how we can do some level of data formatting as well with the help of copilot on Excel. Once you're on co pilot now, what we want to do is some level of filteration, which we want to do. Let's start with a simple prompt right now where we want it to highlight the column rating. For a specific rating, we want to highlight it. We're going to give it a prompt, let's say, highlight rating column in red where ratings are below four. A of the ratings which are below four, that needs to be colored in red. So let's begin with that. Let's see how it operates. You're going to go by looking at the data cell by cell and understand which are the ratings which are less than four and highlight them. We can apply one. Now you can see it has highlighted and which is correct. All of the ones which are less than four it has highlighted, which is absolutely right. Let's move forward with something else. Let's say we want to bold those ratings which are more than 4.5. Again, it's going to look at the same data and try to bold the rows which are specifically ratings which are higher than 4.5. For example, this one, you're going to look at the same data and try to bold that. Let's look at that. It's going to apply the changes. It's also showing you the formula. It is using to do so. Now you can see it has bolded the ones which are more than 4.5. Absolutely correct. Now let's say we want to apply a conditional formatting on rating specifically to bucket them into different categories. Let's say, uh, red, amber, green or green, yellow, amber in segment which we want to do. Let's say we want to add that as well as a conditional formatting where we want to have a green, yellow, red formatting done for rating column. It's going to analyze the data which is available here and it will automatically segment them in that particular category. You don't have to define those categories again. For example, green needs to be 4-4 0.5, that categorization, you don't have to do. It's going to look at the color scale and do it itself. So now you can see it has done that. And as you can see, it looks pretty decent in terms of the categorization done by it. 4.74 0.64 0.6, they look like in the same category, ideally speaking, and so on and so forth. This is how we can do uh data formatting as well by giving specific prompts to copilot on Excel. 8. Data Sorting: Hi, guys. Welcome to this session. In this session, we wanted to see how we can do data sorting as well with the help of copilot on Excel. Sorting data in a very simple manner, which we can do here as well. So we can do in a single format or multiple formats as well. Let's have a look at both. Let's say I just want to sort the data by sales in the descending order. What I want is that it should go ahead and sort the whole data by looking at the sales column, which is this one and sorting it in the descending order. So once we apply, which it has done. As you can see, it has gone ahead and done that. This is pretty simple straightforward, which we can do, which we can do directly as well, but let's look at how we can do it with copilot. The other option can be when we have multiple different layers of filters which we want to apply. Let's say I want to sort the data by store and then shoe brand as well and by year as well in the descending order. I want the sorting done. Let's say store A should come first and then store B, then the shoe brand as well. Nike, all the entries, then Asix and so on and so forth, and then the year as well. That is what I'm looking at. Multiple levels of sorting which we would like to do here. Let's say it's able to do so also or not. So this is again going to be a part of our work when we are analyzing data on Excel. Sorting is something which we usually have to do. So it's going to look at that. It has picked up the right things. Let's see it applies it also in the right manner. Now you can see the data store B. Then in that also it has picked up specifically once, so that's also pretty decently done. Then let's look at store A also absolutely correctly done in the sense over here. This is how we can do a lot of data sorting as well, which can be done and this can help you to analyze the data in the right manner. 9. Handling and Fixing Missing Data with Copilot in Excel: Hi, guys. Welcome to this session. In this session, we'll see how we can go ahead and look at some handling and fixing some missing values which we may have in our dataset with the help of copilot. So once you have the dataset, and here, what we're going to do is we can make use of copilot to understand how many missing values do we have? How can we fix them? So let's have a look at that. As you can see over here, a couple of places where there are some missing values right here. Which we want to identify with the help of copilot. We can give it a prompt, which can be asking how many missing values does it identify in the dataset. So let's see, it's going to look at the data cell by cell and try to identify how many there and count it for us. So this can be really useful when you have a huge dataset and there are a lot of missing values which is difficult to manually, uh, identify. So here we can see now. So shoe brand has one shoe brand. Okay, so then number of customers, there are two. This is one and there is one. It has got the right data. So overall, what we see is there are five missing values, so the output is correct out here. Similarly, now that we have identified how we can fix them. For that, also, you can give it a prompt where we say, how do I handle missing values in this data? It can give us some solutions, some resolutions, steps which we can follow to handle missing values. That can also be done right here. So that is what let's see what all things. Okay? So to handle missing values in eta, you have several options. First is, you can remove the rows with the missing values. That can be one option. Absolutely. So you can apply this particular formula in the Excel sheet, and then you can do that. You can fill the missing values. Okay, that can be done or we can just say unknown in those areas. Impute with formulas, we can do that as well. So this way, we can identify the solutions, the sensible solutions which you can have when you're dealing with a lot of missing values in your dataset. I hope this makes sense. I hope you understand how copilot can help in helping with missing values which you may have in your Excel work. 10. Generating Synthetic Data Like a Pro with Copilot: Hi, guys. Welcome to this sessions. In this session, we'll see how we can generate synthetic data, which we can also use with the help of copilot in Excel. This can be useful for any kind of data analysis you might be doing. So we'll just take one example to understand how this can be done. So we're going to take help of copilot to do this. Let's say this is the kind of data which we want to generate through copilot so this is specifically for Walmart stores. We want to synthetic data, which includes data for ten stores across US cities, and it also has weather data for it as well with location and time, sanity checks. Okay, data generated should make logical sense. And these are the fields which we want to take into consideration like store ID, city, week, weekly revenue, weekly customer visits, and so on and so forth. You can absolutely customize these fields as per your requirement, and you can also build a list of fields which you would want for your synthetic data. So what we can do is we can take this whole thing and now we can give it to copilot, analyze, and based on which it can start generating a synthetic data for us, which we can then make use of. Now, once you get the data, then you can again further customize it for more information, possibly, that is also possible. That can be done as well. Here, as you can see now, it has generated that data for us in this particular manner, which you can also insert into the sheet. So now we have the whole data right here. In this particular manner, we have the whole data here and now you can make use of this for data analysis purposes. The same thing, now you can also do just with Microsoft copilot. You might not need to do this only inside Excel. If you are on Microsoft copilot as well, you can give the prompt right here. And it can work on it and generate that for you here itself also. There are two different ways where you can get a synthetic data which you want to analyze for and you can understand and that gives you much more insight into the business data and analyzing what's happening, what's going right, what's going wrong. That way you can understand. Here also you can see it has done the analysis and now you can download it from here. I hope this makes sense. I hope you understand now how we can produce synthetic data for data analysis with the help of copilot. 11. Adding Formula Columns: Hi, guys. Welcome to this session. In this session, we'll see how we can add formula columns as well inside or Excel data with the help of copilot. Formula columns are basically we are going to have data which is generated with the help of some formulas. For which as well, we can make use of copilot. Let's start with a simple pro where we can say that give us a suggestion for formula. Looking at our dataset, it is going to suggest us a specific formula column which can be added here and if it makes sense to us, then we can consider adding it to our sheet. It's going to generate that formula column right now. We'll see a couple of such similar examples to understand how it is creating it, and then we can go ahead and add them as well. So now it has created one, which is for profit margin, which is a sensible formula column which we can consider and they're also giving us the formula for it, which is H two divided by E two, which is H two is going to be the net profit divided by E two, which is the sales. W makes sense. So we can absolutely consider that. They've given us the data here also. We can simply if you hover on insert column button, you can see it starts appearing right beside net profit column. So we can add that. Similarly, let's ask for one more give. Looking at the whole dataset, it is then going to figure out what else other information can be useful and that we can generate. Usually, this is something which we do manually ourselves so far. But with the help of copilot now, you can have this also created by the tool. Now it's creating average customer spend, which is basically going to be the total money spent by the customer or in our case can be the sales divided by the number of customers, which is correct again, we can absolutely take this also. We have average customer spend coming up right here. And we can just check also once. Will the calculations are proper? Yep. Lastly, let's look at one more. Let's see what it comes with now. So looking at the whole data is going to create more formula columns for us for analysis, profit per customer. So profit per customer, which is going to be H two, profit by total number of customers, it's going to generate that as well for us so which we can get right here. So this is how we can make use of the co pilot tool to come up with various formula columns which can be added to our Excel data, which gives much more intrinsic information about the data which we are analysing right. 12. Working with Text: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of copilot when we are working with text in our Excel data. Let's say this is the data which we are working on and there is amount of text which is there. So what we want to do here is possibly, let's say for the email column, we want to remove the user name from the email ID section and use it for different purposes. So we want a separate user ID column with only the user IDs written mentioned, we can do that as well right here. Where we can go ahead and give a prompt which is extract the user name from the email cool. Now what it's going to do is it's going to look at our email column specifically looking at the user name and extract it from there and put it in a new column altogether. This way, you can divide the text in a certain manner. You can also club text together, so all that can become possible. As you can see, it is using the if error function right here. It's also given the explanation how it has done so. Now if you want to, you can insert that column also. I'm going to add the data in K column. So now you can see all the data has been picked up, the user names have been picked up from the email column and we have added here. This is how you can make use of the copilot as well when you're working with any kind of textual data on your Excel sheet. Let's look at one more example. Let's say we want to club the data which we have on column A or ID and name. So we basically want to club this data with this data together. Let's see if it is able to do so. The intent is basically with the help of this, you are able to work with text, you can make changes, you can divide the content separately. My for various processing purposes, you need to do so so that we can take help of co pilot for. Here in this manner, we can do that. It's going to insert the column in L. This can be really useful and saves a lot of time when we're dealing with a huge amount of data where we have to do these kind of things which helps to make our data better. I hope this makes sense now. You can see here it has been clubbed. The ID and the name column has been clubbed together in this particular manner. Thank you so much Kays, for listening to this and I will see you in the next video. 13. Date Calculations: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of co pilot in Excel to do some data date calculations. Date calculations in terms of when you have, let's say, a set of data set where you have a date of birth of your customers, employees and you want to calculate their age. In such scenarios as well, we can make use of co pilot. As you can see here, we have a date of birth column, and what we are looking for is the age calculation. We can ask in this particular manner. This is also something which the copilot should be able to do wherein it will calculate the age as per the data provided right here and then create a column around that. This can be really useful. Otherwise, this can be really time consuming for us to manually go ahead and calculate this information. As you can see now, it has provided that. It also shows you the explanation of how it is calculating using the formulas which it has over here. You will be able to understand that also and then you can insert it. Here, we can see the age and you can see the formula being used right here. This is how we can make use of the copilot tool as well for doing date calculations, calculating the age of our employees of our data whenever needed. 14. Conditional Statements: Hi, guys. Welcome to this session. In this session, we'll see some scenarios around conditional statements which you can apply to an Excel sheet with the help of copilot. This can be a very realistic scenario wherein you have to pull up some data based on certain conditions on the data which you already have. So let's take an example with this dataset wherein we have the data for two different stores as we have seen earlier. So now what we want to see is based on the profit margin, which we have got from each of the brands, specifically, what we want to understand is which profit margins were really good, bad or not so good. So we want to rate them based on bucketing which we want to create here. So here, what we can do is we can give it a particular prompt wherein we are saying that we want to go ahead and create a rating column. Rating column which we want to create which rates the shoe brand column based on the profit margin. Now, any profit margin which is above 36% should be considered exceeds expectation and anything between 33% to 36% comes in meets expectations and below 33% can be considered as below expectation. We just want to categorize them based on their profit margins coming from each of the brands. Let's see how this copilot understands and gives us the data. This can be really useful because then from here, we can understand which brands are working really well for us as per this data. Then we can prioritize marketing those brands more possibly. This really helped to understand the data in a much better manner. Now you can see here we have got the data. Now let's check this. For example, meets expectations, which is going to be 35.4, which is correct. Anything 33-36 we had defined as meets expectations. Let's look at below expectations here, which is 31.1. Anything under 33% we had defined as below expectations. Exceed expectations is also doing absolutely correct. This is how we can make use of conditional statements as well, which you can give as a prom to copilot to generate insights, which can be useful for our data. 15. Formula Based Formatting: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of formula based formatting as well with the help of copilot in Excel. Once you are on the dataset, we can also apply conditional formatting for multiple different columns, possibly and we can extract data based on that. Let's say this is the dataset which we have. Now what we want to identify is those particular shoe brands whose number of customers are, let's say, more than 100 and also their net profit crossed 100 K. So you want to identify those particular shoe brands. So possibly we can say that those are the winning shoe brands right now in our business. So this you can do with the help of conditional formatting and with the help of copilot here. So we can say highlight. More than 100 and we can say net profit more than 100. This is the condition which we want to add over here and based on which we want to see how copilot identifies those specific data from our data sheet and highlight. This is useful because this helps us to retrieve data in a much faster manner. We are able to identify the data over here. It is using that custom formula over here and giving us so let's apply so now you can see it has applied that and these other particular brands which has met those conditions. Let's check that. Let's say for some sketchers, their number of customers for 2024 was 130 and the net profit was 140. New balance 102 customers and net profit was 105k. So this looks absolutely fine. This is how we can make use of copilot to identify certain things through conditional formatting which we are using. As you can see, these are multiple layers of conditional formatting which we are doing and trying to retrieve data with the help of co pilot. O 16. Color Scales & Data Bars: I eyes. Welcome to this session. In this session, we'll see how we can make use of color scales and data bars as well in our dataset on Excel with the help of copilot. So you can simply go ahead and use these manually as well, but you can also give a prompt to copilot to apply it to your dataset. So let's have a look at it. So we can say apply databrs column. Let's say we want to apply the data bars to our sales column and our net profit column. Okay? So we're going to do it to the sales It's going to look at our dataset and it's going to apply those specifically. So now you can see this. This is how it's done in the right manner. Values which are higher is being put in this molecular manner, you can also sort it. Let's say you're sorting it in this manner, so now you can see the data is done in the correct way. In the same manner, you can apply Pillar scales to let's say net profit and we will not mention column. Let's see if that works. Which is basically this column. It has gone ahead and chosen that as well and applying it. Let's have a look at it. You can see in this particular manner, it has gone ahead and applied the color scales as well to the respective column which we had defined. I hope this makes sense. I hope you're able to understand now how copilot can be used for applying different functions in your Excel data. 17. Editing Spreadsheets: Hi, guys. Welcome to this session. In this session, we wanted to see some additional functionalities of copilot, which you can apply on your Excel data sheet. So let's have a look at it. As you can see, there are a lot of data which we have right here, and sometimes we need to look at all the data, so there can be a necessity for us to hide some of the columns. So that also you can simply do it with copilot, which is easily possible. We can say hide We can say, let's say we want to hide the columns which are let's say for rating and cost. Now it's going to look at the dataset, we are specifically focusing on these two columns and we want it to be hidden. This you can also do manually as well, but there is a possibility with copilot as well where you can do this. Now you can see it has been hidden, so we can see that over here. Similarly, let's say, a lot of times when we are dealing with a lot of data, right format is needed. In such cases as well, you can go ahead and change the format. For example, here, if you see the sales data which we have here, the format is not correct as per the average customer spend or profit per customer, we would want it in the same format, so we can do that as well. Specifically, the sales plum, we want to make it similar to the other polyms which we have because then it looks symmetric is there constant across the board, which is needed for us. We can do that as well. Now you can see it has been changed in that same pot. This is the idea behind how we can there are different functalities of copilot, which can be applied on our Excel data to make it better and more productive for us. Um, 18. Profiling Data: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of copilot to do some profiling data of data of our Excel data which we have. Here you can also do profiling as well wherein you can see certain things insights about it. Let's say this is the dataset which we're looking at. And now we want to do some profiling. So first, I just want to know if there are any outliers in the net profit column, any specific data which is very different from rest of them so that we want to identify. Let's see whether it's able to figure that out or not. So this really helps to understand the kind of data which we are dealing with. Profiling is in is an essential step which we take where we try to identify what kind of data are we looking at and whether it is any different. So here you can see it is giving us that the net profit 315000 is something which is different because it clearly says over here that the value stands out as unusually high compared to the rest of the data. If you now look at the whole data, which is correct. As you can see over here, majority of the data over here is around 100, 100 and 525, but 315k is something really little more off the track completely compared to the overall data which we have here. Which is correct. This we are able to identify, it's able to tell us. Similarly, let's say, I want to identify any duplicates are there in this data or not, specifically in the sales column. I want it to identify duplicates and highlight them as well. Let's see if it can do that. So dealing with duplicates can be really tricky at times and it really skews up the whole data which we are working with. So it's very essential that we identify duplicates sooner when we are analyzing any kind of data. So here, you can see it has gone ahead and identified. So yes, these are duplicates. Rest, there aren't any other duplicates, so this looks absolutely fine. In the same manner, you can also try to find out any mean, max, median values also of the date specifically of any column if you want to. Now, this can be a little tricky at times because it needs to apply these formulas of finding out the maximum medium mean and median as well, which can be a little tricky and sometimes copilot is not able to do so. That can be a scenario, but this can also be something which you can test with the tool and see whether it can identify that or not. This is how we can do profiling of our data as well, identifying what kind of data are we dealing with getting away with setting up the hygiene checks which you can do over here. All those things can be done. Here you can see it has been able to do the mean median minimum max, which is a John which is correct over here, and the mean it has calculated, median it has also calculated. I hope this makes sense. Thank you so much Kays for listening to this and I will see you in the next week. 19. Sum Function with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of copilot to apply some functions on our Excel data. For example, let's say we are doing a sum function which we want to use with copilot in our Excel sheet. Let's say this is the data which we are working on and this is a monthly expense data. So we want to find out the monthly expenses for this particular dataset, so we can make use of co pilot for that. Let's say the first one which we want is to identify the total sum over here. Okay First, we want to identify what copilot can give us as a formula column, it can create for this particular dataset. So there can be different ones which we can have. It's looking at the dataset right now and it can give us the total spend, which we can certainly use the total spend based on each of these. I can give us a total spend over here, which we have got right here. Now, what we also want is, let's say, I want to get the total monthly spend. What will be the monthly spend for September, October, and so on and so forth. Now we want to calculate total monthly spend. She's going to look at the total monthly spend as well and share that with us. Okay. What it is doing is it is giving us the total spend and adding it there. This is not something which we are looking at ideally speaking. What we wanted over here is specifically, we want the monthly spend based on the bills. Again, we're going to say, calculate the total monthly expense based on months and let's see what it does. In the previous one, what it had done was it has just went ahead and added the whole thing out. Now this is looking much better. So it's giving us the specific data. You can see for September, it has calculated 2420. Let's see. Yes, it is coming out to be 2420, as you can see out here and so on and so forth. We have got the data here. But let's say we want to have this data showing up here under the total so we can do that as well. We can say insert the total expenses in row ten. Let's see if it's able to understand what we are trying to say here. Again, it has gone ahead and added the whole thing, which is here to doodle spin, what we're saying is inserting we have to be more specific with our prom We're saying insert monthly total expenses in row ten for each month. Now we have the data here and now you can see we have the data in this particular manner. This is how we can make use of copilot specifically for using a simple function like a sum function, it can do for us for a huge dataset as well. Um, 20. Average Function with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can also make use of copilot to apply, let's say, average function on our dataset. Going back to the same dataset which we have, let's say we want to add one more column for average expenses. We want to know what is the average expenses which we have for each of them. Let's see how it is able to do so. It will add a column for each of them and then do an average of each of it and share the data with us. As you can see now, it has gone ahead and created an average expenses column with the data. Let's see whether it is correct also or not. It has taken the average for each of these C five, D five, and E five. Which is correct. As you can see here, we can also verify by highlighting it, and we can see here as well, it shows us the same value. This is how we can also use various functions, not just some average, but more other Excel functions, which you can use as well here with Copilet and we can apply that on our dataset. A 21. Min Max with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can apply Min Max functions as well with the help of copilot on our data set. Let's have a look at it with the same data. Let's say I want to ask it to add two columns with min and max functions. Let's see how it is able to do that. Basically, we just want to know what is the minimum and maximum in this dataset, specifically, going to look at that and generate those two columns for us. This can be really useful when we are dealing with a huge dataset and we need to identify that it can do that over here in this particular manner and we can add these so let's have a look at it. It is absolutely correct, which we have here. This one as well is absolutely correct. Now, the great part about this copilot is it is only still identifying these as the dataset to refer to. It is not looking at the average expense column or the total spend column at all. So if you look at it, the expense could have been this as well. It is looking at the whole row. But since based on the previous proms given, it is still continuing to look at only this dataset to identify and come up with the results. So it is really great about copilot when it looks at previous references. I hope this makes sense. I hope you understand now how we are using copilot to apply various functions on our Excel data sheet. 22. Adding Formulas with Copilot: Hi, guys. Welcome to this session. In this session, we wanted to see how we can add formulas as well in our dataset with the help of copilot. So once you're on the dataset here, we can also add some formulas which copilot can help us with. It can formula give us the explanation of the formula as well, which we can then apply on our dataset. So let's say we are saying suggest formula column. So it's going to look at our dataset, analyze the data which we have provided, and based on it, it's going to suggest some formula column which we can possibly make use of. So it's generating the formula column right now. So now you can see here, we can see it's given us the brand initials. So brand initials, basically, it will extract the initials of each shoe brand by taking the first letter if present, and then using it to create a column out of it. And it's giving us the formula as well, also. So here you can see the explanation given for it as well. I will generate a two letter abbreviation for each shoe brand name. So this is how we can get the formula. If you want to apply the formula yourself manually, we can do that. Otherwise, you can insert the column right away which copilot provides you with, and it can be added to your dataset. So here we can apply it. In this particular manner, we can go ahead and add the column, which is needed based on the formula which we have used. So this is how we can also use copilot to research on various formulas, which we can apply on our dataset, and then analyze our data in a much more efficient manner. 23. Using Copilot for Subtotals: Hi, yes. Welcome to this session. In this session, we'll see how we can make use of copilot to calculate subtotals as well with Excel data sheet. Let's say this is the dataset which I have and for which I want to go ahead and create subtotals specifically for my sho brand column. So I want to know the data specifically by brand. So now what I can do is I can give it a particular prom asking it to calculate subtotal for the shoe brand column specifically. So it's going to look at the data and now give us a way by which for every brand, we can get the subtotal data. So by brand, we will know all the details, we what is needed. Let's say this is the one, we can insert the data as well. It has gone ahead and used it over here, which is possibly incorrect. We can use the formula. It has given us a formula as well, which we can use ideally, which can give us the same data. Here if we put the data, so now you can see, let's put it right here. Now we have the data properly provided in this particular section. This is the subtotal data by brand. This is the total sales, this is the cost. It has gone ahead and added the ratings as well, which should not be the case. Idally you should be doing an average of the ratings, but rest, it has picked up pretty well, which we can use as well. This is how co pilot can be used for subtotal calculations as well if it is required in your Excel data. 24. Create a Travel Budget with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of Microsoft copilot to create a travel budget. So for this, what we can do, let's say this is the dataset which we are working on, and we want to just go ahead and format this data and do some calculations for this particular dataset. So let's say the first thing which we want to do is this is the budget amounts for the various categories of the travel. So let's say we want to format this in the currency format. So let's see how the copilot tool is able to do that for us. It's going to analyze the data specifically, understanding what the data is all about and then try to format that. Now we can apply now you can see it has been able to format the data for us in the correct currency as well. Let's say we want to just cod and also highlight the headers of the categories which we have the legends which we have, and we want to make use of copilot for the same. We're going to say bold the headers. You're going to look at the dataset and understand which are the headers. This is the great thing about the tool that is able to understand differentiate between the data points and the categories specifically and do the work which is required. Now you can see it has done that. Lastly, let's say we want to do a total of the full budget amount. We can simply give that particular prompt as well to do a total of the budget amount for the travel. It's going to look at the dataset and then apply the total or sum function and give us the output. It has given us the output here as well and telling us which formula did apply, which is B two to B six, which is a correct data area. Now we can insert that also in the sheet. This is how we can simply use Microsoft copilot for various purposes to make changes in our dataset in our Microsoft Texon. 25. Optimizing workflows with Excel Copliot Part 1: Aye. Aye. We'll come to the sessions. In this session, we'll see how we can optimize workflows in Excel with the help of Copilot. Various optimizations, which we have to do when we're working with a huge dataset, so it makes our work much more easier and effective. So for those purposes, also, you can make use of the Copilot tool. So here, let's say this is a dataset which we are working on ideally speaking, and this is what we want to start working with. So there can be some optimization which we can do over here. Let's say, the first thing which I want to do is the order ID column. I want to center align this particular data. So I can give that kind of a prompt to Copilot to do that work wherein it will identify the column and then it will going to center align that particular column. So this is something can be really useful in this particular manner. You can see over here and it can apply. So now it has been center aligned, as you can see over here in the same manner, let's say the customer name. I want the customer name to be split into two different columns, so we can give a similar kind of prompt. Where it is going to otherwise, what you will have to do is you have to apply a specific Excel formula with which you can split the data. But here, as you can see, with the help of Copilot, we can give that kind of an instruction, and it can go ahead and do the same job for us and we can then insert that data in the dataset. Here it has gone ahead and done that for first name, last name, as you can see here and we can insert the columns. The moment you over on insert columns, you can see the two columns getting shown over here, which can be added now. So now we have this over here. Now, let's say, I want to delete the customer name now because that is redundant. So we can also go ahead and ask Copilot to do that job for us, where it can delete the customer name column for us. And we can replace it with first name last name as per our requirement. So it has identified that it is going to be the B columns B one to B 16, so we can delete that. So now we can have that. Now, as you can see, what is happening is this was a particular formula which was being used. So here, what we are going to do is we are going to first go ahead and copy this and then paste it again special wherein we're going to use only values. So with this, what happens is the data does not have the formulas anymore, and now we can apply the formula where we want to delete the column B or customer name column. So now we have the first name, last name as required, and we can also ask another thing which we can do formatting is possibly autofitting the product column, which basically means based on the product name, it needs to autofit. So let's try that also and see if Copilot can do that also or not. We're just trying to optimize our dataset before starting to analyze it, optimizing it so that it looks better and workable for us. So it has gone ahead and autofit it. Let's see how that looks. So now you can see it has been auto it. The product column has been done. So this is how we can use simple prompts on Microsoft Copilot to optimize our worksheets, our datasets before we start doing our data analysis on it. 26. Optimizing workflows with Excel Copliot Part 2: Hi, guys. Welcome to this session. In this session, we'll continue with the various other optimization workflows, which you can do with Excel with the help of copilot. Let's say, going back to the same dataset, there are a couple of other things which you want to do right here. So what we're going to see here now is, let's say we want to calculate the total sales per order. So what we mean by that is we can see here the quantity ordered and we see the sales price as well. So ideally, we just want that we want to know how much was the total sales per order. So we can basically we want the tool to identify that and multiply these two columns to come up with the output. So let's see whether copilot is able to identify that. So this is going to be really useful because this helps to go ahead and analyze our data in a much more efficient manner. So you can see it has identified the dataset, and possibly it's going to give us the output also, which is correct, C two into D two, the formula is correct, so we can simply insert that also as total sales. So the data is correct, which we are getting right here. Similarly, let's say there is a little bit of formatting issues which we identify with the dataset. So we want to check all of that. So let's say we want to specifically look at column E and F, where the formatting can be a little different, and we want the tool to check that for us. And we want to check whether the date format is correct or not. So, for example, here, we can see that date format is not correct. So we want to see whether the tool is able to identify that also or not. So yes, it is able to see that specifically, and we can apply with a red column right here. So it has gone ahead and identified that also for us. Okay. Now, this formatting is important because once you start working with dataset, you can also use the co pilot tool to analyze your complete dataset which you have, and come up with some insights about it. So we can make use of that, and we can now say, let's say that we want it to summarize the total sales, summarize the total sales data per product. So we want to see what insights the tool can give us about which are our winning products, which are losing products, what kind of sales we have got. So now you can see it has gone ahead and given that to us and given an expanded one, we can insert that into the sheet as well. So it's giving us some summary as well. Like, for example, Bluetooth headphones had three units ordered for a total sales of 7497. Well ergonomic chair had one unit ordered for total sales of 22 triple line. So it's giving us some insights here. And now if you want, we can have a separate sheet created for that also. So this is how we can optimize the data set which we're working on with the help of copilot and do a lot of customizations formatting, which we can do analysis of the whole data which can happen, plus any formula columns you want to apply to the sheet that also can be possibly done with the help of this tool. 27. Using conditional formatting with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of condition formatting in our dataset with the help of copilot. Let's look at this dataset where we are going to use this. So let's say this is a dataset where we have various products, first name, last name, quantity, sales price, all that is provided. Now, the first thing which we want to do is we want to, let's say, go ahead and sort this data, the product one, which is you can say by alphabetically. We want to sort that, so we can do that as well. Now it is going to look at our data specifically and it is going to analyze it and sort the product page alphabetically. So now we have the products in alphabetical format. Let's say we want to apply the conditional formatting now to the stock on hold section. With that, uh, in the stock on hand section, specifically, we want to highlight use conditional formatting for the values which are greater than 50. Rather than applying the Excel formula, you can give that prompt to copilot so that it can perform that action for us. You can see now it is going to apply that and we can apply this. These are the values which are greater than 50, which is correct. Similarly, let's say we want to highlight those cells in red where the stock is less than 40. I want to look at the values which are less than 40 and highlight those as well in the same manner. These are all the values, which is absolutely correct. We have got that. Then the last one, let's say we want to work on the status column where we want to highlight them by a specific category wherein we saying that blue is for delivered, yellow is for shipped, orange is for pending, and let's say pink for processing. And black or let's say gray or cancer. So you want to use conditional formatting for status column as well. These are all the ones which we want to use and let's apply. Now you can see it has gone ahead and done that for us as well in the status column. This is how we can make use of conditional formatting as well with the help of copilot without applying any formula and apply that on our dataset and enhance the quality of the information. 28. Enhancing data presentation with Excel Copilot: Hi, guys. Welcome to this session. In this session, we're going to see how we can enhance the data representation in Excel with the help of copilot. So once we start analyzing our data set, it becomes important for us to analyze the data and come up with more visualizations and insights from that data. So for that as well, we can make use of the copilot to. So let's have a look at it. So this is the data which we have been working on. So let's say we want to analyze this first and understand the essence of it, the insights from this dataset. So let's say we want to know which top two products have the highest quantity ordered. Okay? So which are the two products, which has the highest quantity ordered? We want to analyze that so we're making use of the copilot tool to go through this verular dataset and tell us the top two products. So what it has come up with is gaming Musepad and webcam. We can just verify that as well, which is correct. So gaming Musepad and webcam HD are the top two products, and it gives us the number of units as well, right beside it. Similarly, let's say we want to know which product generated the highest revenue. So looking at the total sales column here itself, we know that the product should be Smartwatch. Let's see and verify that. Which is correct. So Smartwatch with the total sales of 13 998. Same way. Let's say we want to analyze further and know how many orders are still pending. So we want copilot to look at the status column possibly and tell us how many orders are still pending at this moment from this dataset. So it correctly points out there are three orders which are pending at this moment. In. We then want to calculate what is the average shipping time. So the difference between shipping date versus the order date, that is what we want. So we want it to identify the time difference between the order date and between the shipping order date and shipping date so that we can calculate that. And possibly what we can do here is here we can see the output is two, here it is five, some of more, ten, 12. So there are different ones altogether. So now, and we can add that in a separate sheet as well. Now how this is going to be useful is maybe we need to look at our processes, how we operate and try to reduce the shipping time. So we also want to calculate what is the average shipping time we are taking currently, right? So that can be a benchmark. So our job or target would be that how we can go below that. Good. It's going to tell us exactly. So average shipping time is one day right now. Okay? So this is how we can take help of the co pilot tool to give us insights from our dataset, and based on which we can get some ideas, actionable steps which we can perform, which can make our productivity much more better and improve the quality of our work. 29. Optimize workflows with Copilot: Hi, guys. Welcome to this session. In this session, we'll see some more other optimizations which you can do with your dataset with the help of Copilot. So let's say this is the dataset which we are working on. So the first thing which we can do here is, again, let's say the sales price column which we have, the data which we have, we want to format this as a currency, so we can ask Copilot to do that for us. So it's going to look at the dataset and transform that into a currency format, which is much more ideal when we are dealing with this kind of data. So as you can see now, it has gone ahead and identified that, so we can just apply and the data has changed now. Similarly, what we can do is also we can ask, let's say we have new orders which are coming in and we want to add that. Let's see, it's able to add another new order ID. So asking it to go ahead and look at adding next order ID to this whole list so that we can add a new entry to it. The new order ID has been generated, which we can add over here and we can add the new entry. Similarly, let's say now what we want to do is we want to calculate the total sales by multiplying. We want to know the total sales. We have the quantity ordered. We have the sales price, so we can easily get the total sales as well, which we can ask for pilot to generate. I can calculate itself and we can just add that column to the dataset so here it has gone ahead and applied the formula E two multiplied by F two, which is correct, and we can insert that column. We have total sales also coming up right here. Now, other than this, let's say we also come to calculate the remaining stock on hand, we want to know how much stock is remaining. Like for example, in case of Bluetooth headphones, one quantity was ordered and the stock on hand was 45, remaining is 44. Ideally it should give us an output. Let's see something like this is something which Copilot can identify also or not. So we just want to know the remaining stock on hand. And we have given the formula as well how to identify that, so it should be able to do that. Yes, it is showing us that, so we can add that also as a column right here, remaining stock. Then let's say we want to highlight the pending order status in red. These are the orders which are still pending. So we want to prioritize them, possibly. Okay, so we can reach out to the support team for these specific orders which needs to be taken care of, given special attention to. So we just want to quickly identify them so here we have the three pending, which is easily done. Other thing which you can do is let's say go on to highlight values in stock on hand, column where the values are less than ten. Any of the stock on hand, um, wherein the values are less than ten, we want to identify them because this is our inventory, so we don't want our inventory to go as low as less than ten, right? So we want to identify that. So we're going to see to that as well and Okay. So here we can see for ourselves that stock on hand, specifically if you see in this particular one, there isn't any particular quantity which is less than ten. In such a case, no changes are needed as such. And then let's say the last one is we want to highlight the total sales, sales where we want to highlight those sales which are really low. So we just want to identify where the values are greater than 1,000. Okay? We want to highlight those particular total sales. So sales which are more than 1,000 can be highlighted. So those are our winning products, ideally, which we can then optimize or prioritize more, possibly, okay. So that is what we want to do here. So these are all the products, all the products have been the revenue, the sales have been more than 1,000. So you see this is how we can use the Copilot tool to do different types of analysis on our dataset and identify the pain areas, the insights out of it, which gives us the next actionable steps to be taken to grow our business. 30. Evaluating Copilot performance: Hi, guys. Welcome to this session. In this session, we'll see another scenario where you can make use of the Copilot tool for analyzing a dataset. Let's say you are a sales manager of a company and you need to evaluate the sales performance of all the reps in your team and how they're performed so far. So for such scenarios as well, we can make use of Copilot, which can analyze our data which we have in place. Let's say this is the dataset which we have right now and we need to analyze this specifically, understand how the sales reps have done. Also, we need to evaluate the co pilots performance as well, the data which it is providing. Let's start with the first one wherein we say that calculate the total sales in quarter. It needs to find out what was the total sales in ir quarter. Is going to look at the ta set. We can identify that right now itself by highlighting it, we can identify the sales has been this much. Amount, which we can see over here, let's say it comes with the same answer or not. Yes, it has given us the same answer. We can insert that also at the bottom, if need be. That's the total sales of ar. Second, let's say, because these are sales data, we would want it to be formatted in currency. So let's do that also, just to format our data in the right manner. This reduces a lot of errors in the future. So we just want to format it with currency. It's going to apply that to all the cells. These are all in currency now, which is great. Now, let's say, we want to see how the reps have performed. We want to calculate the percentage of targets achieved by each of the reps. This is ideally we know how we can calculate this. This is the annual target given to them. This is what they have achieved, so a percentage of that. So that is what we want to calculate here. Let's say Copilot is able to is on the same page with us also or not. And it does so, and it gives us the target achieved. Now, let's say I want to check this further. So I want to check, let's say Copilot whether they're performing it in the right manner or not. So this is my total sales achieved. This was the target given to us, and So you can see more or less it is absolutely correct. It is calculated in the right manner over here, we have the right answer or the output given which Copilot is providing is correct in this particular case. Another thing which we can do here is, let's say, we want to show the sum of the total sales and the average percentage of the targets met grouped by region. These are different regions provided. We want to see by region, how has been the total sales which we have got specifically and what has been the percentage average percentage of targets which we have met here with respect to region. Which is basically we want to do a pivot. We want to do a pivot of this dataset and come up with this solution. Rather than using the pivot table manually, we want to see whether Copilot can do it for us. Now, it's going to group all the data by region. Secondly, it's going to show the sum of the total sales and average percentage of targets which I've met. So region, which we can see here, and we can put it in a new sheet as well to understand better. So here is the region. We can see the total sales for each of the region, and then we can also see the average percentage of target met. Okay? So percentage is also given here. If you let's check this as well for East India, whether this is the correct answer or not. So here we have East India, okay. Total sales. East India is twice over here. This is the one and another one is this. 776 triple zero, that is the correct answer which it has got. This is how we can make use of the Copilot tool to do pivots as well, pivot calculations as well and analyze our dataset to come up with the insights which we require for our business. I hope this makes sense. I hope you understand now how Copilot can be used very effectively with our Microsoft Excel. 31. Improving Copilot performance through customization: Hi, guys. Welcome to this session. In this session, we'll see how we can improve the copilot performance through various customizations. So let's look at the same dataset, and now we can start working on this data to make sure that the copilot is taking into consideration the dataset. We can just ask initially to show data insights. This will just if this provides you a response, then we know that the co pilot has taken the data set into consideration, and then we can move forward with other information which we want to collect from. So it gives us some information over here as you can see over here, average target achieved top performer is Rahul Meta, it has taken that into consideration. Now, let's say we want to find out what is the sum of the total units per region. So we have total units sold over here. We want to know per region, what would be. There are different regions provided. Ideally, we can do this manually by filtering by region and then summing up the units. That way we can do it. But now you can see very quickly we can identify it by region with the help of copilot. Same way, what we can also ask it is to generate the sales generated per region. Looking at this, we can estimate that most probably North India would have the highest revenue because there itself, we had the highest units sold, should be like that, which is the case as well as we can see over here. North India had the highest sales generated. Also what we can do is we can ask it to tell us who has been the best performing sales rep, in totality from the dataset. So the way it should be doing it is looking at the total sales. It can sort that by, uh, top to bottom, descending to ascending order here in this particular manner. We can identify that the best performing sales rep has been Rahul Meta. Which we want to see whether the co pilot tool is able to do it also or not. Yes, it is able to do that. It can also tell us the region for AhulMta is South India. Okay, total sales it has generated it has given us. And lastly, we also want to see whether it can compare the different quarters performance by rep. This can be a little tricky where we want to see quarter by quarter comparison of each rep. That way, it's more like a pivot where we want to compare the ter coqterthuter, cir quarter sales per rep, how the reps performed across every quarter. Okay. So that is what we want to identify. There can be scenarios wherein it might not be able to give us the output. Okay. So in such cases, we would have to manually do the analysis ourselves. So this is really great with the help of which you can see with the help of copilot very quickly we are able to analyze our dataset. We don't have to manually apply any formulas here, and then do it. This all can be done very quickly with the help of a simple prompt which we can give to the tool and it can analyze our data. I hope this makes sense. I hope you understand now how we are making use of copilot to analyze our data much more efficiently. 32. Evaluating Copilot performance: Hi, guys. Welcome to this sessions. In this session, we'll see another scenario of how you can evaluate the copilot performance for different optimization workflows you do with the dataset with the help of the tool. Coming back to this dataset again, we're going to do a couple of more insights, let's see. First is where we want to highlight the values which are less than in total sales. We want to know which are the sales which have been less than 400 k. Let's see whether the tool is able to identify that looking at this. Idally we can do that by adding a filter to this And we can identify that, but here it is able to do so. Right here very quickly. Same way. Let's look at which are the top three sales reps based on revenue who have brought in the highest revenue, which we want to see. Idally we will look at the total sales of each of the reps. We can sort it by ascending or descending order and then find out the top three. But here very quickly, it can give us that. There is Raul Mata, Sura redial Kapur, and the revenues are also mentioned out here, which are the top three, which is very good. Then we can also highlight the sales steps with the total sales is below average. We want to know the bottom three. Okay. So we want to know the reps who have not performed that well and whose sales have been below average, so we need to find that out. These are the people whose sales have been below average much lesser than the top three, so we can identify that also very easily with the region also, which it is mentioning, which is very good. Let's say now we want to apply conditional formatting on the data where we want to apply color scale to new clients specifically in this column new clients to show the sales with the highest and lowest number of new clients. So conditional formatting of segmenting it by knowing, which are the highest number of new clients sales reps have got and the lowest number of new clients they have got. So here, it is going to apply that. So now you can see this is correct. Okay. So these are the highest number of new clients got by sales reps and the lowest ones also highlighted in red. Let's try to look at doing something different, which is going to be creating a bar chart, summarizing the total sales per region. Now we want to know the total sales, but in a bar chart fashion. We would want copilot to generate a bar chart, looking at the total sales. By region, it needs to segment that we will have bars per region and showing us the total sales for that. Ideally, in that particular manner, it should be able to generate Otherwise, you can do that also certainly manually wherein you can filter the data out, then you convert them into a bar chart manually yourself. You can choose bars, you can choose Pie chart as well, scattered chart as well. All those can be used out here, which can be done purely manually. But here what we want to see is whether the copilot tool can do that work automatically for us. So right now, as you can see, it's unable to create the bar chart. Okay, we need to try once again later. Okay. Let's see another one wherein we want to summarize the total sales trend for top three sales reps. Basically what we want is to summarize the total sales trend for the top three sales reps, it had already calculated that the top three sales reps who are there at we want to summarize their total sales. So that it has been able to create. You can see for hul Meta, Surajadi and Kunal gapur, it has provided that we can add this to the sheet also Same way. Let's look at summaries for the total sales for trend. The last one compare the number of client meetings versus the number of new clients per region. Let's say it's able to do that or not. What we want here is to compare the number of client meetings which has happened versus the number of new clients per region. Okay. It's going to look at how many number of client meetings, which also it has been able to generate. So now we have region wise, client meetings plus new region, which we can also insert. As you can see now, with the help of co pilot, we are able to build these very fast and which can be used for our data analysis in a much more effective manner. I hope this makes sense. I hope you now understanding how copilot can be used effectively in analysing our data and telling us insights which would be really useful to improve our productivity. 33. Introduction to Excel: Basic navigaiton : Hi, guys. Welcome to this session. In this session, we want to do a basic navigation of the Excel sheet, which we are working on, specifically understanding what are all the features which we get to see on the Excel sheet. It can become a little overwhelming to see so much information out there. That's why we want to just do a simple navigation to get comfortable with the interface of Excel, which we're using right now extensively with copilot. So this is how the worksheet is going to look like. As you can see, this is the worksheet where you're going to add data. So here you can write your data. You can do multiplications. All that you can do. You can do a function as well in this particular manner where you can add So like this, you can go ahead and data. So this is this sheet where we are going to add all the information. Same way, you can add multiple sheets. So there can be more sheets which you can parallel be working on. Okay? So that can be there as well right here. Okay. Apart from that, you can see the menu over there, which is the classic ribbon where all the information is available. So here, if you want to make any changes, that can be done wrapping the text, merger or center, conditional formatting you want to use, all that can be done. Also, if you want to install or kick off co pilot, you can do that right from here. So this is how we can start with copilot and start working on it. Um, now, in addition to this, you can also see the formulas column, which is going to give you all the formulas which you can use from here, while you are analyzing your data. There is also a data section where you can upload data from different sources. You can group ungroup them as well. You can add a filter to the data as well if you want to do filtering. So all that can be possible right here. Review will be a case where you can add comments. You can add comments. You can ask it to show the comments. Okay, you want to add notes for your data. So whatever data analysis you're doing if you want to add any comments or notes for it, that can be done from this section. There's also a view section which gives you if you want to see 100% view of the whole data set, specifically, how it looks like with grade without grade, you can do that as well. You can freeze the pans as well. Okay, formula bar, if you want to see it, you can do that also through the view section. Apart from this, there's a help section as well. If you are stuck at somewhere some issue is happening, you can take some help from the help section, the support section we have contact support, which we have here. Also, you can search for any kind of information in the search box provided on the top of the screen, middle of the screen. So this is how overall, uh, the Excel sheet is going to look like for us, where we are going to do most of our data analysis, ideally speaking. And from here, you can go ahead and connect it with the copilot to make those necessary changes, analyze different types of dataset, figure out insights, and, build some action plans around them. 34. Introduction to data types: Hi, guys. Welcome to this sessions. And this session we'll see the different types of data which we need to deal with when we are working on different types of datasets in Excel, specifically. So once you start working on data, so there can be different types of datasets which we are working on. So as you can see, this is one of the dataset which we have. So one, we have the textual data which we get to see here, right? Okay? So there's textual data. There is numbers as well. There will be dates as well, Boolean, as well. There can be various types of data which we need to analyze in a dataset. To give you some examples, textual data is something like this, which you get to see right here. Now, something like this, the movie titles, country, you can say the director, genre. All these are textual data which we work on a daily basis with. Numbers are going to be in different formats, numbers, with currencies, numerical numbers, decimals will also be there, as you can see over here in rating or revenue. These are going to be runtime. All these are going to be in the numbers format data which we need to also deal with and we need to analyze. There's also going to be dates, so different types of dates which are there in the dataset specifically in different formats which you will find, which we also have to look into which becomes a part of our work as well to analyze how uh the product is working and based on which we need to make the necessary changes. Then comes Boolean. Boolean is primarily going to be where you're going to, um, the output can be yes in a yes on or no, or it can be on and off. So based on which you give some inputs related to Boolean, which will also be there. These are the various types of data which we need to day in day out with when we're doing any type of data analysis in Excel, and we make use of copilot over here, which recognizes all the different types of data in the same manner and gives us the outputs for it. 35. Converting data types: Hi, guys. Welcome to this session. In this session, we'll see how we can convert the data types with the help of copilot in an Excel data set. So let's say this is the dataset which we are working on right now and we need to change the dataset, the data type into different format. Like, for example, the revenue column. Okay? So the revenue column is specifically, this is the revenue parts, we want to have the currency format on this. So what we can do is we can give it a specific prompt where we're asking it to convert the revenue column to $1 currency format. So it's going to look at our dataset specifically, identify, okay, which is the numbers which you're looking at, specifically the numerics. And then it is going to make those changes. So let's have a look at that. And we can apply the changes. So now it has been able to do so. Apart from this, you can further more possibly go ahead and format this in the more relevant manner. Let's say we want to replace the periods, which you see in the revenue column to comma because this is all in millions, so we want to have that much more realistic. And let's see if it is able to do that also or not. Okay. So this is how, we are using the copilot tool to format convert our data types in the right format, and then we can make use of it to go ahead and change that for us. And then analyze the data in the right manner. I hope this makes sense. I hope you understand now how AI tools like copilot really helps to format our data, to understand the data in a much more better manner and improve our productivity. 36. Working with data types: Hi, guys. Welcome to this session. In this session, we'll see how we can work with different types of data in our dataset, specifically with the help of Copilot as well. So there can be, as we discussed earlier also, various types of datasets which we need to deal with. So let's say the first one which we have here is the textual data, and we want to make this bold primarily, so we can just highlight that and we can make that bold, right in this particular manner. Okay? And then we can also go ahead and change the text as well if need be, into a different format in this particular. The other thing which we can do here is formatting the revenue section. So the revenue section is here, so we can format this as well. So there are two ways of doing it. One is, obviously, you can use Copilot and give it a prompt to do so. The other one which can give you other options as well is right clicking highlight the information and you can format the cells. So when you come to formatting options here, you will have various options. So currency is already there or you can go to accounting, which can give you various options. So you can decide the decimal currency, all of that, and you can format the cells. The other thing which you can do here is going to be, let's say formatting the year of release. Y of release is also in a certain manner format given and let's say we don't want to use this, we want to use a different one altogether. These are all the options which you will get, which you can also reach by right clicking and doing format cells. Here, let's say, I want to do it in this format. I can use that possibly or else I can choose textual essay as well in this particular manner. We have done this and then unwrapping the movie title, you can also make use of Chat GPT primarily to copilot specifically to unwrap the movie title and the director column. So it is going to look at the dataset and it will unwrap both the columns for us. So this is how you're going to use you're going to work with different types of data ideally, and then you can make use of Copilot as well for the same where it can unwrap the movie title. I can unwrap the director column as well, and any type of data which you may have. I hope this makes sense. I have able to understand now how we are supposed to work with different datatypes in Excel with the help of Copilot and then analyze it to produce great insights for art work. 37. Understanding data types: Hi, guys. Welcome to this session. In this session, we'll see some more other different types of optimizations which we can do on the workflows with respect to dataset in copilot. Let's say we are working on this particular dataset where we need to perform these particular actions. So this is the dataset which we are working on right now. The first thing which we want to do is to center align the headers. The header which we have here, you want to header in the table needs to center aligned. Let's say if copilot is able to do that for us in the same manner, we can do some level of formatting of the other data as well in the dataset. I guess it has been able to do that, we can apply. You can see it has been center aligned now. Similarly, let's do a left alignment, which we want to do for year of release. This we want to left align. It has highlighted the data, it has identified which particular column to do this for and now apply. This has been left aligned. Then we can also look at which movie generated the highest revenue. We want to identify the movie title which has generated the highest revenue out from the revenue column. So this requires the copilot tool to identify. We're talking about the movie title, so the output has to be coming from movie title and the condition is being the highest revenue. We have to from the revenue column, it has to find out the highest value, and against that, whichever is the movie title, that will be the output. So that is what it is supposed to do. Let's see it is able to identify that or explaining exactly what has to be done. It has ntified the movie is Avatar, which has the highest level. Same way, let's ask which movie had the highest runtime. We're looking the output to be in movie, not how much runtime, we're asking which movie had the highest runtime. Now it's going to look at the runtime column, identify the highest value there, and give us the name of the movie. Right now, it is not able to produce that, but in the same manner, you can also generate that information. I hope this makes sense. I you understand how we are using the co pilot for various different reasons to find out insights from our dataset which we can use further for our work. 38. Using basic arithmetic in Excel: Hi, guys. Welcome to this session. In this session, we'll see some basic arithmetic which we can do in Excel and also the usage of copilot in our dataset. So once we are in our data set, let's say this is the dataset which we are working on and we need to find out these particular data. So we can use some apply some arithmetic over here, like percentage ownership. So we want to know that this is our ownership right now, these are units which we have bought specifically out of the total units. So we just need to know the percentage of that. So this is a simple way of applying a formula. So whenever you're applying a formula on an Excel, it starts with an equal to, and then we can give our total number of units or shares which we have bought, divided by the total number of units. Which is what is going to give us our percentage ownership over here, right? Okay. So now what you can do is to find out for the rest of them, you can just on the bottom right corner, you can just double click on that. It's going to generate for the rest of the entries as well. This is how we can go ahead and calculate the first one. Let's look at the second one, which is our FMB, which is our fair market value. So for this again, what we're going to do is to calculate our fair market value, we're going to apply a formula so equal to, and whatever percentage of ownership which we have multiplied by the current market value, current fair market value. So this is what is going to be calculated over here. So now we have that as well, our fair market value. Same thing which we're going to do for the rest of them, we double click and we can get the data right here. Once you have the fair market value as well, now what we want is, let's say the return per investment. So our investment is in the first column in the B column. And this is, uh, fair market value right now, return on investment which we want to see for them. Let's have a look at that. For that, we're going to see a difference between the two. So our current value minus the initial investment is what we are going to see out here. You can see this is how we can get our return per investment. This is the subtraction which we have done over here for calculating return on investment. Now, for summing up our total FMV, fair market value for all of them, we just need to do a sum of it, so you can do it in both ways, which is you can either apply a sum formula here and do it or you can make use of the copilot as well to provide us that information. Let's see how copilot does that. It's going to look at our dataset, look at the RFMV column, ideally speaking, and then it should be adding the whole thing. It has found out H column, and it has given us the total as well, which we can add over here. You can see the total has come up in the same manner, what we can also do is calculate the ROI as a percentage. So whatever is the return on investment right now it is in whole numbers. We want it in percentage format. So let's see how it is able to do that. It has found out that as well and we can insert that column right here. We have the ROI percentage also coming up right here. This is how we can make use of the uh we can do the basic arithmetic calculations on the Excel sheet. We can also make use of the copilot tool to provide us solutions for specific queries which we may have. 39. Applying mathematical functions in Excel: Hi, guys. Welcome to this sessions. In this session, we'll see how we can apply some mathematical functions in Excel and with the help of copilot as well. Going back to the same dataset, we're going to see a couple of things over here. First is, sum of current FMB. The current fair market value is here, so we can do sum of it. Let's have a look at that, how we can do this. For the sum, we can use the sum function provided and we can highlight the area and we can get the sum over here. In the same manner we can we can also ask to copilot to calculate the total current FMB as well. Let's see if it's able to do that. It's going to look at the particular dataset and try to do it itself so that also we can see so that we have both the versions available. So it has given us the information over here, the amount is the same as you can verify out here. Next is, we want to find out the average RI. So this is the total amount which we have. So let's say we want to find out the average. So we can again apply a formula And now we have the average RI also available. Let's ask the copilot to calculate the same thing for us again, and hopefully it should give us the same output. You can see we are trying to apply both the options, which is manually calculating so that we know which are the formulas being applied and using copilot also to do the same work. Here it is going to do that, calculate the average it has given us the formula how to do that, and it has given us the average RI also, which is 33.34. Now let's look at the min and max value. Let's say we want to find out the minimum value in column B. Column B is ideally our initial investment, we want to find out the minimum over here. We can find out the minimum by following the function over here, min and we know that the minimum value is going to be 2,500. Let's try to identify that with copilot also. You can see these are various functions which we can easily use with copilot and we can get the output from it. It's using a different formula altogether. The minimum value it has also find out to be 2,500. Same way. Let's try with MAX. Max is 1,500. Let's try that with copilot. It's given us the max amount also as 15,000, same as what we had calculated. Now, similarly, what we can also do is we can find out the calculate the lowest ROI. So lowest ROI. So let's try to find out the lowest ROI, highest ROI as well. Okay. So here again, we are doing minmax. Let's see if copilot can also come up with the same output. A Over here, it is giving us a different output altogether. Let's look at the highest ROI. Also in the same manner, we can calculate. Let's look at a count function, which is going to be counting the number of investments which we have. We can apply a function over here, which is count A and we can see the number of investments is 15 and let's ask the same thing to copilot to provide us also. It's going to look at the investment name column and should count all the investments and give us the same output. That is the expectation. Let's see if it's able to do so. As you can see, it has been able to provide that also. This is how we can apply different arithmetic functions in Excel with copilot without copilot as well and get our desired outputs. 40. Applying logical functions in Excel: Hi, guys. Welcome to this session. So in this session, we'll see how we can go ahead and apply logical functions as well in Excel with the help of Copilot. So for this, we're going to look at a different dataset where we have the name of the employees, department, their sales target, actual sales they have got. And based on which we want to check whether they have met the target, their eligibility for bonus and whether they need improvement or not. Okay? So let's look at first is going to the sales target met, whether they have met the target or not. So for this, we're going to use a simple if function where we are saying that if the actual sales is more than if it is more than the sales target given, then yes, they have met the target, otherwise, no. That is what we want to apply here. Here we are able to apply that and we can get the target met for people and not for those people who have not met. So this we have got. Now, we want to apply, use the co pilot now to check if the employees have met the target and the performance rating exceeds 90%, then it makes them eligible for the bonus, right? So in this case, what is happening is both the things need to be the case. First is, they have met the target. Second, the performance rating also needs to be more than 90%. So there are two conditions to be met. Only then they are eligible for a bonus. So this is what we want to get from the co pilot as well, whether it looks at both the conditions and then give us an output. So let's have a look at it, whether it's able to do that or not. So now it is going to look at our dataset, and it's going to look at two parameters which is target met and performance rating. So both the conditions should be applied, which has looked at that, and now we can insert that it is eligible, eligibility is met over here. We can in the same manner, now we want to see the needs improvement. Needs improvement is you want to calculate whether an employee needs improvement by checking if their actual sales is less than or equal to, it is less than or equal to their sales target or if their performance rating is below 85%. Even any of the cases if it happens, then we can mark them as improvement. If either condition is met, otherwise, we can mark them as okay. So here, any of these conditions, if they are met, then it will be needs improvement or else it can be okay. So here you can see, we're making use of logical functions like if and or is being used with the help of Copilot, right? So Copilot is automatically using these functions to coming up with the output needs improvement, and it has used if or function you can see over here, it has used that particular formula, and now we can get the output. This is how we can make use of the Copilot tool to apply logical functions as well in our dataset to analyze the data and come up with the insights which we need for our day to day work. 41. Combining logical and LOOKUP functions for advanced data analysis: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of Copilot to apply lookup functions in our Excel data. So once you're on the data set, so let's say we have to find out certain information here. The first is what we want to find out is return the name of the employee ID. So we want to find out what is the name of the employee ID e005. This can be done with a V Lou specifically, but rather than that, we can also use Copilot to provide us that same information. So here, it is going to look at the employee ID, and against that, whatever is the name provided that it should be giving as an output. Let's see if it's able to do that. Same manner. Other lookup functions like X, lookup, H lookup, all those functionalities, you can give it to Copilot to figure out and it can give us the output. Let's see that with the first one So here we can see it has given us the output AspinalGupta, which is correct. Okay. Let's look at another one which is written the Department of name Kaba ED. Employee ID employee name is Kaba ED and we want to know the department name over here. So in the same fashion, it is going to look up for that. Now you can also start giving other conditions as well. While this is going to be a pretty straightforward information which we are asking, but you can see we can provide some specific conditions as well in the prom to give us much better output. So here, we can identify that it has given us the right output. It is going to be HR department. Now look at the other option, which is look up the employee ID of Puja a year. And check if her target was met. So now we are not asking a direct output, but we are asking Copilot to assess itself whether this particular employee had met her targets or not. Now, ideally, the tool needs to look at her sales target, needs to look at her actual sales, then figure out whether she met the target or not. And here you can see it says target met, yes. This is how we can make use of the Copilot tool to apply various lookup functions easily on a dataset and get our output. 42. Using conditional, data, and time functions with Copilot: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of conditional data and time function as well on a dataset, specifically in Excel with the help of copilot. Let's say this is the dataset which we are using right now and we can start with some of the actions which we can take on the dataset. Let's say we want to find out the date and time function we want to use. What we want to find out is the difference between the date admitted of the patient and date discharged. Here, we can do that specifically, which is going to be in this particular manner, we can identify the data in time, which we can see the difference, how much time it has taken for the discharge or else we can make use of copilot as well to provide us the same output. So we can give it a prompt wherein we say that add a formula to calculate the difference between the date admitted and date discharged in days. So it's going to look at our dataset and then it is going to calculate that for us. This is something like using date and time function with the help of co pilots, it's given us the formula, Okay, I two and H two and with that, we can apply that. Same way, let's look at countif as well. So when countif, what we want to do is we want to calculate the number of aema patients in the diagnosis section. Okay. So here, it can be done with two ways. One is through Excel formula like countif, and the second day is we can do it with copilot as well. So let's look at countif. So here we can apply Countif. And we take the range, which is this, and then we say thema. So thema is here, so we could find that out. In the same manner, we can give it a simple prompt to copilot to calculate that to find out how many patients are there from asthmas. You can see the prompt is also in the sense, human conversation which we are having with the tool, where we are just asking how many patients have diabetes? We are not mentioning diagnosis over here, but it's able to understand that and trying to provide us. There is one patient diagnosed with diabetes. We get the same. Same way, we can do some if as well. So if is where we're trying to do a sum based on certain condition. So let's try to find out for a specific treatment plan, what is the outstanding bill? So we can do a sum if let's say the range is, this is the training treatment plan. So from this range, specifically, we want to find out for dialysis, what is the outstanding bill. So we will say dialysis. And then we are going to give the outstanding bill area. So with that, we get the amount, which is right now 85,000. This is how can we make use of the semi function. But now let's say we want to do the same thing with the help of copilot. So we can give it a prom which says, add up the total outstanding bill for patients with dialysis. So ideally it should give us the same output. So it's going to analyze our data and use the sum function, you can see all the SumF count if date. All these are functions are used by copilot itself to find out the out. We have the output. This is how we can use various functions of Excel with the help of our copilot tool. 43. Using nested functions for complex calculations in Excel : Hi, guys. Welcome to this sessions. In this session, we'll see how we can make use of nested functions as well in some complex calculations in Excel with the help of Copilot. This is a case where you want to use count ifs, which is multiple conditions or sum ifs, multiple conditions are being used to do counting and for summation of numbers. Okay, so this you can also do with the help of Copilot where it is going to use these two particular functions. So let's say for this dataset specifically, we want to calculate how many patients diagnosed with migraine were admitted in Jan 2025. So now there are two conditions. One is that we are looking specifically for u diagnosis, which is migraine. So this is one condition, and they were admitted in Jan 2025, Jan 2025, which is 12th of January. So we want to know how many such patients were there. So, ideally, if you look at it, which is you have acne for migraine it's going to be only one, that's the answer. Well, let's see how Copilot uses the countif function to count Is function to come to this particular solution. So it's going to use the countifs function specifically to put these two conditions, specifically for diagnosis and for date admitted. And with that, it will try to provide us the result. Same way, you can find out for some ifs as well where multiple conditions will be used to finally come with the output. It says only one patient diagnosed with migraine was admitted in January 2025. So you can see it has come up with the right result. Now look at SIFS. In case of SIFS, what we are asking for is the outstanding bills for patients, outstanding bill for patients receiving Gioplasty. So the treatment is NgioplastyO condition. Second condition is, which is admitted on first of January 2025. So these are the two conditions and the outstanding bill for that was 12120 k. So ideally, that should be the output, which we should be getting right here. So we can see the total outstanding bill for patients who received NGI plus t where admitn for Jan first was 120 K. So this is how Copilot makes use of complex functions like sumFs and count taps to solve our calculations and provide us the right output. 44. Data entry techniques: Hi, guys. Welcome to this session. In this session, we'll see how we can make use of copilot to do data entry work as well. So if you want to add any particular data in the dataset which you are working on, so you can make use of copilot over there as well. So let's say this is the dataset specifically, where in the supplier column, we want to add the supplier names or let's say this is going to be not applicable, so we just want to add NA to this. So we can do that with the help of copilot also. So we can fill the entry cell in supplier column with NA. So now it is going to analyze our dataset and based on which you will see the supplier column specifically, finding the empty cell in it and try to replace it with NA. So now you can see it has identified that and with this, we can go ahead and replace it with NA, and then we can enter any type of such data in our dataset with the help of copilot. Um, 45. Using data validation for accurate data entry: Hi, guys. Welcome to this session. In this session, we'll see how we can use data validation as well in our datasets in Excel with the help of copilot. Data validation can really help in identifying certain type of data which you want to have in a specific area in the dataset. Let's see how we can do this. For example, right now, let's say this is the column, which we have stock level. We want to create data validation wherein we don't want to have any value which is more than 500 or less than zero in this particular column. So how we do that is we can highlight this particular column and we go to data validation. Where we're going to use whole number and we define it as between minimum zero to maximum 500 and we apply. What it means is that this particular column should not have a value more than 500 or less than zero. Now if I go ahead and put, let's say, hundred here, it's going to give me a data validation rule which says that this cell must contain a whole number 0-500. Same way, if I put a negative, let's say 15, it gives me the same kind of notification. So this is how data validation can be used. Now, if you want to take help of copilot for the same thing, you can give it a prom to provide you with a formula or the way to apply data validation. So it can certainly research on that and tell us how we can create a data validation rule for a specific whole number. And then we can apply that on our dataset. Select the cells where you want to apply, which we have done, go to Data tab, and then in Data tab, go to data validation, over there, we can allow to hold number, and then we can set it. Exactly the same steps which we did right now. Same manner, let's look at another data. Let's say the supplier column. Specifically for this, we want to create a data validation where we want to create a list. And here we are going to put a list of all the suppliers which we have separated by comma. Okay. Like this, we can create it and we can apply. Now, you will identify a data a drop down, which will give you all the options. Now you can select and you can change in this particular manner. You can do that. Even for the new sets if you want to do so, you can apply it in this particular way going for. This is how we make use of the data validation in our dataset and also with the help of copilot, we can apply it on our data to generate insights out of. 46. Practicing Data entry techniques Activity: Hi, guys. Welcome to this sessions. In this session, we'll see some of the data entry activities which you can do with your dataset to enter data which you would like to analyze. So once you have the dataset, let's say there are a couple of things which we want to do. One is going to be filling the product code over here. So you want to fill the product code so we can fill you can define the format in which you want it. Okay? So let's do that first. So let's say we want it in this manner, the first name, and then the prototype. This is how you want it. So now what you want is that in this same format, you want to add all the other details. So all you have to do is you can do Control E. When you do Control E, automatically, all the format gets added over here, and you can just double check once whether it has been correctly done or not. This is one way of entering data, which can be really fast. Second is, let's enter the data at the end. So here, let's say you want to add a new data altogether. So for that, you can just go ahead and do Control D, here you can add the data. Let's say this is the new one and product ID number is this, and you want to add the product code. Again, the same thing, Control E will add the product code over here. This is how you can enter data at the end. Last is let's do stock level data validation which you want to do so you can just select the area, go to data data validation. Here you can define. Now we have done the data validation. Let's do with a negative first to check whether it's working properly or not. It is working properly. It's giving us that there is a rule applied for zero to 1,000. Let's try for that as well, we can see there is a rule which is showing up. This is how quickly you can do data entries into your dataset which you need to analyze for your work. 47. Formatting data in Excel: Hi, guys. Welcome to this sessions. This session, we'll see how we can format the data in our dataset in Excel with the help of copilot. Let's say this is the dataset which we are working on today. So there are a couple of things which we want to do here. The first is going to be formatting the amount section, which we can do here. You can go here specifically and you can change that. You have multiple options there, we can put it to Control Control one. That will give us the option to format this. Let's say we want to format this as currency and dollar signs, we have that now. Now the next is you can do the same thing with the help of copilot as well, where you can give it a prompt to format the values in the amount column to add currency. I' going to look at our dataset and based on which it is going to apply, make those changes, which we can apply to the dataset. So now it can apply which is E two to e 16, which can be done. Third, let's say we want to format the header row. We are formatting, so we want to make the data look a little bit more neat and proper. We're giving it a prompt, which is format the header row as bold and set the color to blue of the header row. Then make the font color white. Let's see how this works out. Just going to look at the header row. It is going to make it bold and then make the background of it as well blue and the text color of it, the font color to be white. And we can apply. Now that is done. Let's look at another one. We can add borders to the table in black. These are all formatting prompts which we are giving to copilot to make our data look a little bit more neat. So now we can apply. Know table has been added. Okay. Next, what we want to do is we want to see for specifically for the expense column, which we have the type column which we have. We want to demonstrate expense as negative, because that's the money going out. So we ideally want to see it as a negative amount. So we're giving it a prompt, which is where the type is expense. Convert the amount to a negative. So let's see how it does that. I So we're calling it assigned amount and which we can add over here. Though signed amount is added, then what we want to do is we want to sum the signed amount. Whatever is the assigned amount now, keeping in mind that there are negative expenses as well. We just want to do a summation of all of these. If you just highlight it, you can find the sum here, which is 495 K. Idally copilot should also give us the same output. So 495 K, which we see, Okay, which you can apply as well. So we have our total over here showing up And then lastly, let's say we want to highlight the expenses in the signed amount, which are less than 5,000. So we just want to look at those expenses which have been less than 5,000, or let's say we are making it a little bit more 45,000 and see. It's going to look at the amounts and look at the specific expenses particularly and see which expenses were less than 45,000 and highlight. So let's see over here, this is the one. This is the one. So it has highlighted all those expenses are less than 35,000, 45,000. So this is how we can easily format our datasets as well with the help of copilot on our Excel guys. 48. Formating techniques Activity: Hi, guys. Welcome to this session. In this session, we'll see some formatting techniques which we can apply on our dataset in Excel, also with the help of Fo Pilot. So let's have a look at it. Let's say this is the dataset which we have and we need to work on this. The first thing which we want to do is we want to format the whole data properly, so we can highlight the whole information, and then you can right click anywhere. You can just right click in any of the columns. And when you do so, it can be formatted in this particular manner. You can do that. Then let's say we want to format the date column, specifically, you want to have it in a particular manner, you can do that as well. If need be, that can be done also. Now, other than this, what we can do is we can format the amount columns as well over here so we can highlight that. And here we can format the cells. So you have the option over here to choose, let's say we want to put it in currency In this particular manner, we have formatted the amount and the budget column. The next thing which we can do is we can highlight the headers. We can just select and we can highlight the headers in this particular manner. Now let's do some sort of formatting with the help of copilot. The first thing which we want to do is we want to see, let's say, where the type is donor. This is the donor column where the type is donor, we want it to be highlighted in light green. Let's see if co pilot is able to do so. We are asking it to look at the type column and look at the donor data which is provided and only highlight them in light green. Let's see it's able to do that. And we can apply. It has gone ahead and done that. Now, in the same manner, let's say you're giving another format which is where we are saying that where the type is expense. Let's highlight it by light red. The same thing, we are looking at the type column and in that we're looking at the expense part and we want it to be done in specifically light red. Now you can see it has gone ahead and done that. Let's refresh the page once. We have the data in place. Let's bring back the copilot. Now, other than this, let's say we want to also do we want to see where the donor is donor B. In the donor column, where the donor is donor B, we want to highlight the amount in yellow. There is donor B here, there is donor B here, we want to highlight the amount. The amount is this one which needs to be highlighted. Let's see whether it's able to track that. It's going to look at the same dataset. Look at the donor column, identify donor B in that and needs to highlight the amount against them. There are different ways by which you can absolutely use copilot to format your dataset and do that as well. So it is giving us specifically, the steps how to do it over here. Let's do another one, which is donor C. We are saying that where donor C has donated income in June, Donor C has donated income in June, specifically, highlight the amount in There can be different ways you can see now with the help of copilot where we can format our dataset, make it much more better readable, and then you can use it for analyzing data to bring out insights which we can use for our own business. I hope this makes sense. I hope you understand now how copilot can be used for various formatting techniques. 49. Enhancing data presentation: Hi, guys. Welcome to the sessions. In this session, we'll see how we can enhance the data as well of our dataset in our Excel with the help of copilot. So let's see a couple of things which we can do right here. So the first is going to be, let's say this is the dataset which we are working on. So first, we need to format the market value. Okay? So this is the market value specifically to currency and the purchase price as well. For these two, we need to format the market value in the right currency format. Okay? So what we can do is we can do it with controlled one and we can choose the currency over here. Now we have the market, um, the values are formatted to a currency format. Second thing which we can do is conditional formatting. So here you can see, with Excel, there is multiple conditional formatting options provided like highlighting cell rules. So you can set up certain rules, which can be greater than or between equal to texts that contains. So there are various rules which you can set based on which you can do a lot of conditional formatting of your dataset. Then there is also top or bottom rules. So these can also be useful, which is like bottom 10% you want to highlight or top 10% you want to highlight, above average, below average. Those can be also formatted. Conditional formatting can be applied on them. Data bars which you can use right here. We'll see how we can use these as well, color scales which you can apply. Let's say you want to apply color scales, which we can do on these values specifically, then you have icon sets also. These can be used as well in the datasets, ideally, speaking. Okay. Then if you want to create a new rule yourself, you can do that also from here and there are ways to remove the rules as well from selected areas of the dataset. So these are all the options which you get right now. Now, let's do some color scales which we want to do. So let's say for the market value which we have, we want to do color scales. We wanted to apply color scales. So now it has gone ahead and done that looking at the data. So, for example, the highest data is this one, so that's in green, but the lowest ones are in dad. Right? Now, in the same manner, let's apply some data bars on purchase price. So let's say we want to apply data bars on this. So let's see how data bars actually look like. So here, you can apply the data bars. Solid fill or gradient fill, you can choose based on your requirements. So let's say you want to do light blue, it's going to be in this particular manner. So now you can see how it is going to work, okay? So the highest amount is going to be in this particular way we can apply. Let's see how different it is going to be if we are doing let's say we are doing data bars with solid fill. Solid fill, let's say green. So in this manner, you can set it up. So this is how you can make your data look much more effective when you're analyzing, when you are trying to make the data much more accurate for your audience. Now let's look at a couple of other things which we can do with the help of co pilot, which is applying data bars to rental income. Let's say I want to do the same data bar now, but with the help of copilot. So I'm going to give that particular prompt let's see whether co pilot is able to identify that. For rental income, I want data bars to be applied. So that's the basic requirement. Ideally it should be able to understand the dataset, identify which is the rental income column, and then on that it's going to apply the data bars customized to. As you can see, it has been able to do that. So data bas apply to rental income as well. In the same manner, let's say we want to do another one which is highlight the bottom five market value. In market value, we want to identify the bottom five, like we were seeing in conditional formatting, where we could do it manually. This is something which copilot can help us with. In market value, we want to identify the bottom five. Let's apply. It has identified the bottom five. This one, this one, this one, and this bottom five are in red. And lastly, let's format the size columns. These are the size columns, we would want them also formatted in the right manner, which is squared meter. Let's see it's able to do that also or not. Idally it should look at the size columns, Intify the values are not formatted properly, and add that squared meter specifically to each of them. That is the expectation. Let's see whether copilot is able to apply that on the dataset. This is how we are basically going to make our data look much more better and it just enhances the whole data which we're trying to analyze over here and gives us better insights. So now you can see it has able to apply that as well to our data set. I hope this makes sense. I hope you understand now how we are making use of copilot in Excel data to make it much more effective. 50. Creating basic pivot tables in Excel: Hi, yes. Welcome to the sessions. In this session, we'll see how we can create pivot tables as well in Excel. And also, let's see how we can use C pilot in this. So for that, we can go into the data set. So let's say this is our dataset which we're working on. And now we want to insert pivot table, for which we can highlight the whole dataset first, and then we can insert. The first thing which you get is pivot table over here, which will give you the option. So now you can see it gives you multiple pivots already created. It will analyze the dataset based on which it gives you certain options which you can select from. So now, it's your choice. If you want to select any of these or you want to do a new one completely, you can do that as well. So in this particular manner, so let's say we want to do this in a new sheet altogether. So now we have the whole thing over here. So now you can start analyzing. So let's say you have property address, which is going to be there in the Rose column, which you can put out here, or let's say we want to put the property type first. What are the property types? Okay. And we want to know what is the purchase price or market value of these so we can put it in here. So we get to see the sum of the market value right out here. Okay. In the same manner, you can now go ahead and make other changes. Let's say for property addresses, you want to see specifically property addresses are in the rows, and now you want to see their size as well. So we can put that in values to understand the property size exactly. Okay. So in this way, we can go ahead and mix and match the data set ourselves and then use it as per our requirement. So here you can see, we can see the rental income for every property address as well, how it is going to be. So using Pivot Table really helps to analyze the data, get insights really quick. Now the same manual thing, which we do here can be done with the help of copilot as well, where we can ask it to create a pivot table summarizing the market value by property type. Let's see how that is going to happen. Just going to look at our dataset, try to create a pivot table based on it, and the requirement is that it needs to summarize the market value based on property types. There are various property types provided on which it is going to provide the market value. Let's have a look at that. As you can see, it is analyzing the data set right now. And it is going to give us the information. It's going to segment it by property types. So we have apartment villa Independent house, duplex, it's going to segment it by that, for each of the property types, it's going to give us what is the market value? Summarize it. Is going to add all of them together and tell us where is the market value most for a specific property type. So you can see this is going to be really useful because pivot tables really helps in analyzing data in a much better format. So here we can see over here, it is not given us any data, but that is how we can make use of pivot table going forward in our Excel and with the help of copilot. 51. Advanced formating Activity: Hi, yes. Welcome to this sessions. In this session, we'll see some formatting activities which you can do on your dataset in Excel with the help of copilot. Let's have a look at that. Let's say this is the dataset which we are working on. The first thing which we want to do is we want to format the market price, purchase price, specifically the rental income in the normal currency format. So we can ask copilot to do that for us this time. It's going to look at the dataset and based on which it's going to format it for us. The other way is you can do it manually as well with the Excel specific tools which you can use and you can do that also. Let's see how copilot is able to do this. This is really useful because once we are able to do these data validation formatting of our dataset, then the data looks much more understandable and we're able to analyze the data better, get better insights out of it. As you can see right now, it is highlighting the area, and now we can apply et's refresh the page once more. We are going to give the prompt once again. We just need to wait for the tool to come back again. Sometimes there is a lag on the tool as well, wherein it is unable to fetch the data, identify the data, and then apply its actions. We just need to restart the tool, the Excel sheet, possibly, and that helps to get the right output. Let's see how it is able to identify the data first and then format the specific columns. It has highlighted the data. And now we can apply. It has added the formatted in currency. Let's move forward. Another one which we want to do is in the market value column, we want to highlight those properties which are top 10%. It needs to identify which are the top 10% out of the lot and then highlight them. Specifically this column which we are working on, we want it to identify the top 10% in this. This can be really time consuming when done manually. If we have to do this manually, we will have to go ahead and sort the data first in descending to ascending order, then identify what is the 10% of the whole lot. All that can take some time, whereas here with the help of copilot, you can easily do that. Let's look at it. It has identified the top 10% is this one. Next, what we want to apply is let's see a data validation rule which you want to apply, specifically for the property type. Now mostly, we try to do data validation manually ourselves, which we can do by coming to data, and here we have data validation, which we can do. Let's see if we can make use of copilot to do that for us. We would want the same thing to be done through copilot automatically with the help of a prompt. We have given the prompt as create a data validation list in the property type column to only allow apartment, villa, cottage, duple, independent house, row house in the list. We just want a data validation to be created. As you can see, it's giving us output. I can't help with this. So in such cases, sometimes copilot provides you the steps which you can apply manually and do that. Here you can see it has given us the steps as well, how we can do that. So we can come here. We can go to data validation We can choose list, and this is where we can add the whole thing. From here, we can do that. We can add all of them once again, let's say apartment. So now you can see it has come up over here. This is how we can apply data validation. Let's look at another data validation if it's able to do that, where we want to create a data validation rule to only allow the positive numbers in the rental income column. So all the positive numbers it needs to provide. So most of the time, copilot faces issues with data validation prompts, so it is a much faster option is that we do it manually with the tools provided. Again, it has come up with the output that I can't help with this. Let's move with another one. Are there any current errors? We want to see if there are any current errors in the dataset. So here as well, I guess you will not be able to see much output because the rate of validation has not been done by the tool. Asking us more information there. Let's try another one where we want to summarize the total market value and total purchase price in a pivot table. Let's see if copilot is able to create the pivot table for us. Let's try another one, which is going to be creating a pivot table summarizing total rental. As you can see, there can be certain issues which might happen, but otherwise, copilot is able to apply a lot of functions. As we saw earlier also, which really helps to analyze the data and then get better insights which we can use for our business. 52. Engineering prompts for Excel Copilot: Hi, guys. Welcome to this session. In this session, we'll talk about the engineering prompts, which we need to do for Excel copilot. So when we are looking at designing the prompts, we need to keep a couple of things in mind. So the key ideas would be that first of all, the prompts which you are going to give to copilot does not need to be ambiguous. We need to avoid ambiguity as much as we can. Secondly, we need to ensure that we have a clear goal of what is the output which we are looking for. That has to be clear in our prompt. Third, we need to provide a context in our prompt, exactly what is the context behind the background information based on which we are going to ask the question, the output, which we are looking for. We are also going to define our expectations. What kind of answer, how we want the answer response to be? That has to be defined clearly in the prompt. And lastly, we need to make sure that we are not giving a prompt where we are making copilot to assume. So we have to give concrete information, detail information to the tool. Then only we can expect a proper outcome or a response from the tool. Let's see this in reality how it is going to be. Let's say this is the dataset which we are working on. Now here if I just give it a prompt, something like this. Summarize the data. Okay? Summarize data is going to be a very vague prompt which we're giving to the dataset. We are not defining what exactly do we want to summarize? What kind of data, what information do we want it to give us an output? That can be a little tricky for for copilot, obviously, what we are trying to make it do is assume assume a lot of things and it can give us any type of information as an output. Rather, whenever we are defining a prompt, we need to break it down into four categories, which is going to be what specify context and format. What is where we are going to summarize what we want it to do? Doesn't need to summarize, create, format, sort, filter, calculate list. Whatever we want it to do, that has to be given out in the W section. Specify is, we need to specify the context in which particular section, range, column, row, table which we need to specify here, and then the context which we have to give. We have to give the context based on the background information which we are providing based on which we are looking for the output. The last is going to be format. In which format do you want the output to come out? In a table format, in a graph chart or a list format, how you want the output to come out, that also needs to be defined here. Now if you look at this example, we are saying that summarize the data in the batch results table by listing the total number of batches that passed or failed at each inspection stage and present the summary in a pivot table. We are covering all the four aspects, grouped by inspection age with a breakdown of the results. This is how a prompt structure should be where these four components are part of it, and that is how the tool understands our query much more clearly and is able to provide the desired output. I hope this makes sense. I hope you understand now what are the things we need to keep in mind while engineering prompts for our copilot. And 53. Advanced prompt engineering techniques: Hi, guys. Welcome to this session. In this session, we'll see how we can apply some advanced prompts as well to get out data from our dataset in Excel with the help of copilot. The intent is when we start building out a good prompt. These are some foundations of a good prom which we need to keep in mind, first is going to be the prom needs to be very clear and specific exactly what is what is the output which you're looking for from the tool. Second, the goal has to be very clear, which has to be provided in the prompt itself, and the expectations needs to be clarified. That what is the expectation out of the tool? What kind of response are you looking for? Then the format has to be specified. In which format do you want it? Do you want it to be in a list format or a tableau format? That has to be defined clearly. Then we need to keep the prompt as simple as possible. It becomes all the more easier for the tool to give us the right output. So let's see this in practice how this is going to be. Let's say this is the dataset which we have right now. Now, what we can do here is, again, like we saw in the previous video, we can create a different structure which is going to be what specify context and format with which you can build out the whole prompt. Now the first option is we can simply give this particular prompt, which is tell me about the failed goods during the first stage and the type of defect. So we need to find out the failed goods which is in this column, during the first stage. The first stage specifically will also be told over here. Now and the type of defect is this. Basically, we require the output from these three columns. Now, the copilot tool might be able to provide the information, but a better way of doing it is that we make the prom based on this structuring. Wherein we give the context. The context would be that the failed products, assembly stage is what we are looking for. Scratch defects is what we are looking for, and then you specify the range. We are looking for the batch IDs specifically, and you want it to be in the format also which you can define and what you want. Do you want them to list it or summarize it that we need to define? Like this, if you structure out your prompt, that is a little bit more clearer to the tool and it is able to give us the better output. Now you see we are saying summarize the product ID. That failed during the assembly stage. It is assembly stage and it is failed, where the defect is surface scratch. Now we are giving specifics from the three columns, ideally, and with that, we are looking for an output. This is the idea behind building a better prompt, creating an advanced level prompt, which will give us the right output, the desired output from the two. 54. Writing basic prompts Activity: Hi, guys. Welcome to this session. In this session, we'll see how we can write some basic prompts to copilot and get outputs based on that. We're going to see it on a specific dataset, different types of prompts and see how copilot is able to cope with it and provide us the response based on it. So once you're on the dataset, there are different types of proms. Let's say this is the dataset which we are working on right now. And the first thing which we want to understand is what type of tasks can copilot do that will, uh, do the quality control. Okay. So here, we're just asking and understand what are the capabilities of the co pilot tool for the dataset specifically. So we want to know those which it can give us information about. Okay? So this can involve various things analysis of the data, which can be done. So based on which we just want to see the scope of the tool, what are things it is capable of doing. Now, if you see here, couple of things which we already know which copilot can do, which is going to be identifying failed batches, summarize inspection results based on this dataset, specifically, visualizing quality trends, analyze inspector performance, track process improvements. These are certain capabilities of the tool itself, which it is suggesting us right here. As you can see, a few more create charts, spot pattern, summarize the sample sizes, all these things is the capability of copilot based on the dataset which we have given. Now let's look at similar examples. What we want is it can summarize the result by batch ID. So result is here and we want to summarize it by batch ID. Let's see whether it's able to do that. It is going to look at our dataset and based on which it is going to summarize This is a straightforward prompt which we've given with two factors in where they're going to look at the result column and the batch ID column. Now you can see it has gone ahead and done in this particular manner, we can expand it also properly. This is how it is giving us the output. If you want, we can insert that in a new sheet as well. Let's look at something different where we are asking copilot to create a bar chart showing the count of each defect type in the dataset. Defect type is this. We wanted to create a bar chart based on that. With these prompts, we are just trying to see the capability of the tool, understand how it is analyzing the data. Also, we are trying to assess different types of prompts which we can use with Coplot too. Right now it has identified the data, so should be able to perform the action needed over here. So we are specifically looking for a bar chart. Let's see it's able to do so or not. As you can see, the tool takes its own time to analyze the data and then build out the output. A lot of times, we get to see that tool is not able to provide the desired results. That is what is happening again here, we just need to try once more, maybe sometime later. Let's look at another one where we just wanted to identify any trends between the sampling methods used and the results of the batches. We wanted to look at the sampling method column and the results section and identify a trend. It is not able to identify any trend at this moment. Um, so let's look at one more prompt where we would want it to generate a report that summarizes the average sample size. Sample size is given for each defect type and group the data by process step. Now here, there are three different columns we want to take into consideration. Based on which it is supposed to give us the output. Like this, we can give various prompts to copilot tool to get the desired results based on our dataset. 55. Writing effective prompts: Hi, guys. Welcome to this session. In this session, we'll see some other examples of how we can write effective prompts for our dataset in Excel for P pit. So once we are on the dataset, this is a new dataset which we're working on. Let's say for this specifically one, what we have here is customer IDs, survey date, ratings, feedback comments, sentiment score, and keywords. Okay. So now what we want it to do is to summarize the average rating for all the customer feedback which we got in the month of February. So ideally, what it needs to look at is the ratings, which is in this column, specifically for February, which is this and average out the rating for us. So that is what we are looking at, and we will verify this as well manually to understand whether the output given by copilot was correct or not. So this is another way wherein, again, if you see we're giving the context, we're giving the range, we're giving the context, we're telling what to do and the format as well is summarize which we asked it to provide us with. So it should be able to give us the desired output. Let's see how it is going to do that. So ideally, what it needs to do is it needs to identify the February customer feedback ratings and then average them out and give us an output. That should be the right solution, but let's see how copilot is able to do that. The intent of doing these is that we are giving different types of proms to the ata set so that it is able to identify troubleshoot and give us the information right here. At this point, it is not able to do that. If we do it manually, we can see we are able to get an average over here, which is 3.11. Let's try with another example wherein we will try to see whether copilot is able to provide us the output. We're going to analyze the sentiment score and categorize the feedback as positive, neutral, or negative. It needs to look at the sentiment score and then identify which are positive, which are negative, which are neutral. Let's see whether it's able to do this one. It needs to look at the sentiment score column. It is able to identify the dataset, but then in the first prom, it was not able to give us the output, so we are trying with a different output and see whether it's able to do that. So the intent is over here that we are going to see apply different prompts on copilot, analyze our dataset in different manner so that we get the insights out of it, which we can then use in our business. So we want to see the capability of the copilot tool, how it is able to figure out the information. So what we are going to do is in this particular way, you are going to use right effective prompts for your co pilot tool and then get the insights from the data set. 56. Evaluating prompt performance: Hi, yes. Welcome to this session. In this session, we wanted to look at how we can evaluate our prompts performance. So the prompts which we are giving to copilot to analyze our dataset, we also need to analyze and understand, evaluate how good the prompt is and what kind of how we can better it. We can better the prom to get the more desired results. So for this, let's look at a dataset. So let's say this is the dataset which we have right now and we go ahead and give it a specific prompt, first of all, so wherein we say summarize the percentage of completion across departments. Right. Okay, so now here, in this, particularly if you look at the data, there is task name, start date, and date, duration, dependencies, people person responsible and RAC status, right? So on this, ideally, percentage of completion is something which it is difficult to calculate out of this, right? So giving it a prompt like this is it's a little vague for copilot as well to identify how it is going to do so. And most likely it is going to give us an output which is depending on other data sets, it's going to provide that. Okay? So here, the data contains personal responsible column, but there is no exclusive department column. That is also it is giving us to summarize the percentage of completion across departments, a mapping between each person and the department is required. So you can see the prompt itself is not good enough, which is why it is not able to provide the right output. Now, in such cases, what we have to do is obviously we have to make the prompt a little bit more specific to the dataset so that it is able to reduce that particular information for us. Okay? Uh Other than that, you can test out the prompt with other prompts as well, wherein you can simply ask, where is my data. So which basically will make copilot to identify the dataset specifically and give us an output. This verifies that the copilot is basically working. Clearly, it is giving us the clear output here. You dataset is in the table sheet one in the range a 12g 60. So it has clearly identified that. This way, we understand the effectiveness of the prompt is there. Now, to make it more better, what you can also do is you can possibly insert a table into the dataset. That also can be done. In this particular manner. And if need be, to troubleshoot these areas where let's say the prompt is not working really well for us, we can just close the file and then we can reopen the Excel sheet specifically which we are working on. That is how possibly we can go and evaluate a prompts performance in different aspects, and then we try to make it better. The idea is to make it better by giving what, context, specific information. The more information we are giving to the prompt, the more better results we're going to get out of the prompt. 57. Optimizing prompts for better results: Hi, guys. Welcome to this session. In this session, we'll see how we can optimize our proms to get better results on a dataset where we are applying the copilot. Now, there can be sort of scenarios wherein we are applying these proms and we need to get better results out of it. So that's why it becomes important for us to optimize our proms wherever possible. So let's have a look at it. Let's say this is the dataset which we have for the first which we want to look at is listing all the tasks that are behind schedule. So we want copilot to analyze the data and see which all tasks are behind schedule, which is based on RAC status. So RAC status, wherever it is red, we can say that those are specifically task behind schedule, but that is what copilot needs to figure out itself. So it has gone ahead and done that. Like, for example, SE optimization. This is red and database migration. Which is absolutely correct. It has done that. Okay. Let's be a little bit more specific here wherein we ask it has given us the output, but still we are asking it to list all the tasks that are behind schedule, including task dependencies, which we want from this column and the person responsible. Okay. So now let's see whether it's able to pick those ones as well or not. So it's giving us the steps over here. So task dependencies, it has given website design, redesign and the person responsible, which has also come out, which is really great. Now, let's say we want to apply a particular color to it, so we can apply a red, green, yellow highlighting to the RAG status. Here we want to apply this. Basically, we are trying to optimize the prompts to see where it is going to need more detailing and in other places where it can be straightforward. It is giving us those options, so we can easily apply Now, if you see it has not gone ahead and applied that for Amber. So for Amber, specifically, we can give it a prompt, highlight all the cells. That is how you're optimizing the prompt. Wherever it is missing out on things, we'll have to give those additional prompts to get that results out. Now it should be able to go ahead and fill up all the details. This is how we are going to optimize our prompts to get better results, as you can see, so that we get the desired output. A lot of times the prompt can be incomplete and not specific enough or it can be generic vague as well, and because of which we are not getting the desired output. So in such cases, we have to optimize it. We have to make it better, repmptGive more context and details, and then we can expect to get the desired results. 58. Evaluating prompt performance activity: Hi, guys. Welcome to this session. In this session, we'll see how we can evaluate the prom performance by doing multiple activities related to the dataset and giving those prompts to dataset to analyse how the prompts are performing and what kind of output it is providing us. Let's say this is a dataset which we are working on and now we want to go ahead and build out, let's say, the first Gang chart. The Gang chart which we want to create over here is to visualize the start and end date for each task and listed along with the duration and task dependences. We wanted to create a gang chart. Now, ideally the copilot tool should be able to create that, but there can be certain limitations and in such cases, a copilot can provide us the steps to do it ourselves manually. As you can see right now it is saying that I couldn't generate, but you can refine your request and check for other details. So what we can do is we can look at the manual steps to do. The other thing which we can do here is we can ask copilot to assess the completeness of the Gan chart dataset and identify any missing task dependencies or inaccuracies in the task durations. Here, we want to look at various specific missing information if it is okay, and identifying those missing task dependencies in the dataset is what we are seeing whether copilot is able to highlight, is able to highlight in the dataset, understand that thing, and then we can get the information. So here, copilot is giving us the output to a certain extent, wherein dependencies are one output has been provided right here in this particular manner. Now let's look at if it's able to highlight the task dependency, missing task. So here, there is a missing task. So we just wanted to identify that. So let's see whether it's able to do so. So the idea is that we are testing our prompts, the copilot prompts on the dataset and seeing what kind of output it is able to provide us. Now it is telling us where exactly. Let's say we are saying that it has to be in the dependency highlight rules where a giving us all the options. We are saying for dependency specifically. At least that it has been able to identify and giving us the options to go ahead with. I guess it would be able to do that. Yes, it has identified that and we can apply. Now if you look at asking it to add a column, now we're going to ask it to add a column to compare the start date end date, compare the result to the duration column to identify dependencies. Basically, we wanted to create a new column where it can compare the start and start date and the end date and compare the result of the duration column. Result of the duration column to identify dependencies. It's going to create a dependency check. So So it has gone ahead and done that. So duration matches, Okay, which it has produced over here. So duration matches, which is absolutely fine. Now what we want is to identify any overlapping tasks. If there are any tasks which are overlapping specifically and suggest adjustments to prevent scheduling conflicts. If there are any tasks which are overlapping in looking at the task name, here, there are some overlapping tasks as you can see here. This is what we want copilot to identify and tell us and suggest any adjustments which can be done to prevent the scheduling conflicts. Now, this it has not been able to provide us with. As you can see, there will be a lot of back and forth, which is going to happen with the tool while you're working with it in the dataset. We have to keep improvising with our prompt and try to give it better prompt, which it can execute. Let's give it a simple prom. Let's say provide a list of overlapping tasks. I just want to see a list of overlapping tasks, which seems like a much simpler prompt which we are giving it now. Idally it should be able to give us the writer. So here you can see specifically, so it has been able to look at the task over here. List has been created. Now let's ask a different one, which is going to be generating a report showing personal assignments for tasks marked as red in the Rg status and suggest alternative scheduling or additional resource needed. Now, this can be too many things which are being expected out of the tool. Like we want to give it suggestions about alternate scheduling, additional sources, resources, information. So there are multiple things which we are expecting it to look at based on the conditions provided. Okay? So we are specifically looking for task marked as red. Now it's saying to generate the report, the personnel assigned to these task must be listed. Lastly, we want it to do also is to create a dynamic updating system or a mechanism in the Gan chart dataset that automatically adjusts the task durations and dependencies based on real time data inputs. Now we want it to create an updating system as well or a mechanism which can help with automatically adjusting task durations dependencies, based on real time data inputs when we do that. We recalculate. It's giving us the process. It's giving us the process that can be done by recalculating task durations whenever new start and end dates are entered. You can see now this is how we are going to evaluate our prompts performance on any of the dataset which we are working with Copilot. You have to try out different proms to see which ones are giving us the route output, write output, and which ones are not. Then we need to improvise, change those proms as much as we can to get the desired results. 59. Introduction to data cleaning and processing with Copilot in Excel: Hi, guys. Welcome to this session of data cleaning and processing with copilot in Excel. In this section, we're going to learn a couple of things. But in the first module, we will be learning to spot common data errors that can derail your analysis and discover how to correct them using copilot in Excel. We will work through practical examples, case studies, and hands on activities that demonstrate how to clarify your data by addressing these errors early in the process. Next in the Module two, we will talk about handling missing values and duplicates. Here you will master the techniques to identify and address missing values, remove duplicates and apply both manual and automated imputation strategies. These skills are crucial for ensuring that your data is complete, consistent, and free from redundancies that can skew your results. In Module three, we will explore data type conversion and standardization. You will learn how to convert data into the appropriate formats for analysis, standardized data to maintain consistency across your data sets, and perform data normalization. These steps are vital for ensuring that your data is accurate and aligned with your analytical goals. In Module four takes us into the world of text manipulation and column operations. You will discover how to manipulate text data, perform column operations like merging and splitting, and use text functions to enhance data organization. These techniques will help you improve readability and the structure of the datasets, making them easier to analyze. Then in Module five, we will focus on prom development for data analysis. You will learn how to and refine effective workflows that streamline your analysis process. By utilizing copilot in Excel's top notch, top down approach, you'll be able to prioritize key insights, allowing you to develop a structured analysis that uncovers meaningful trends and patterns effectively. 60. Data error correction with Copilot in Excel: Hi, guys, welcome to this session. In this session, we'll look at how we can do data error correction with copilot in Excel. Let's have a look at this dataset where we have this information. Now what we want to do is we want to correct information. This is a dataset for students spores. We have the student ID. Then student name, signs, then the subjects. Specifically, I think is a mistake with the spelling over here, which we get to see physical education. Let's try to correct that with the help of copilot. The first thing which we want to ask Coptsk are there are the column headers spelled correctly, which we just want to check. Let's see if copilot is able to identify that and then gives us the right output, which we can then put on the dataset. It's going to look at our dataset and identify the spelling mistake in the headers. This is a you can say data error correction which we are trying to do, and this is the formatting which needs to be done. We understand that it is a physical education which has to be the correct one which we can put out here and we can make it like this. The second one which we want to identify is any duplicates in the student ID section in this en duplicates, we want to identify that. Let's see it is able to figure that out. If you look at it, there is a duplicate which is here, student number ten, and then it is again available here. That is what we want it to identify also. It has quickly done that possibly and giving us that option to apply. It has identified. Then we're asking it to remove the duplicates. Let's see if it's able to remove those duplicates which we have identified in the dataset. Ideally, it should be able to do that considering that it had identified those duplicates itself. As you can see, sometimes it is going to give us the information and then it's going to give us the steps. Right now, as you can see, it's giving us some random output at this moment, which will become clear as well. Let's try to open this up once again. Uh, um, and let's try to give the prompt once again. It has given us to remove duplicates, it's giving us the steps purely, which we can identify. If it is not able to do it, then it will give you the steps to do it manually. Last thing which we want to do is we want to find out the average of the scores. You can just highlight the spores and you can see the average at the bottom as well manually if you have to do it. But what we want to see is if copilot can identify that. We want it to look at all the scores of the students for every subject and tell us what is the average score. Again, it is giving us the steps, and it has calculated as well. Let's compare for science, it is 80.45 correct. For mathematics as well, I guess it would be correct 82.4 and for physical education, 85.8. For this, it has been able to provide the write output. As you can see now, Copalt is able to go ahead and correct errors. Specifically, corrections are possible, which you can do on a dataset before starting to work on it. 61. Common data errors overview: Hi, Ayes. Welcome to this session. In this session, we'll understand and what is the common data errors which happens when you're working with multiple datasets. Let's take an example to understand how it is going to be. Let's say this is one dataset which we are working on right now and you can identify, you can see there are a couple of things which are not so correct over here, for example, ID. Now when we say ID, it is a little generic. Are we talking about a product ID? Are we talking about customer ID? There's not much clarity around that. Okay? Same way, item, what do we mean by item? Are we talking about a product item or a category? There is not much clarity there. Then again, what we see here is it's a cost column without the currency. So this also is a red flag for us. We need to make this correct before we start analyzing any type of data. Then again, if you see in the ID column, there is a it's an ID, but it has an alphabet as well in it, and this particular dataset looks very different. Data in the date format, if you see, there is a different format being used right here. Again, in the nodes column, when we look at the item and we look at the nodes, they look very similar to each other. So we really don't know what exactly is happening in the nodes column. It looks like redundant, might be this is a column which we don't require. These are some of the red flags or areas of errors, data errors which we can identify right away. Also, one more thing, you can see the total. The total is again a part, it has to be a separate line item together rather than being a part of the dataset. So these kind of things raises a red flag for us. And before we start any kind of analysis of the data, these need to be fixed. So if you see a better solution of this, can be something like this, where you have a proper product ID, a product name, which is being given over here. And then in the pricing column, we are given the proper currency to it. Then we also give the quantity sold, okay? And then the date format is also in the standard format. This is how we need to look at the data errors which might happen when we're analyzing any data set and need to fix these errors first and then move forward with any type of analysis. 62. Identifying errors in datasets: Hi, guys. Welcome to this session. In this session, we'll see how we are going to identify errors in our datasets before we start analyzing it. So once we have the data set in place, we can go ahead and analyze it first. We just want to do some error searching and format the data in the right manner, and then we can start with analysis. So that is what we are here for. So the first is we're looking at the ID, the legends, specifically the headers, which needs to be proper, so we can give it a proper name over here, like a product ID. Name. We can say price, quantity sort, date and notes. This way, we can give it a proper name. Then comes, we need to remove the irrelevant data over here. This particular row does not look good, so we can just remove this. Also the subtotal here does not make sense, we can remove this. Also this notes column and product name column looks same. I don't see a reason to have this. It's a redundant data, so we can remove this. Now this looks a little better. Then we're going to format the date column. The date column looks fine here. The problem is here, so we can just make it in that particular manner and make it consistent. Let's say, we're putting a different date so that the date looks different. Now this is confirmed. Then the pricing as well, we can just format that to make it a currency. We have done that as well. Now the data looks much better in position. We have identified the errors. We have fixed the errors as well and formatted it in the right manner. This is how you're going to do formatting of any type of data which you have before you start analyzing any data sets with the help of copilot. 63. Using Copilot to address data errors: Hi, guys. Welcome to this session. So in this session, we'll see how we can make use of Copilot to address some data errors and try to fix them in our dataset. So let's say this is the dataset which we have in place, which is a huge dataset of 200 plus entries for all these particular columns which we have got right here. Okay. So now what we need to do is we need to go ahead and make some changes. As you can see, there are some mistakes formatting errors are there, the missing data is there, so we need to fix these errors data errors over here with the help of Copilot. Let's try. The first is we want to highlight the duplicate values. If there are duplicate values in the passenger ID, which is the first column, and duplicate values which I wanted to highlight. So let's see how Copilot is able to identify duplicate values. And once it is able to identify the duplicate values, what we would want it to do next is remove them. Okay, so it says, Sure looking, and it has found, I guess, so it has found duplicate values as you can see now here. Okay, so we are next going to ask it to remove those duplicate values. If it is not able to do that, then possibly we will do it manually ourselves. But let's see if Copilot or we can just remove picks. It is giving us the steps. Let's see if Copilot itself can do it. If it is not able to do so then we will follow the manual approach. I can't help with this. What we're going to do is we'll follow, we'll go to data and we go to remove duplicates, Column A, Column A, we are looking at and we're going to highlight this whole column. And in this we are looking at passenger ID. And 33 duplicates were found and removed. All the duplicates have been removed, as you can see now, not there anymore. Next, do I have any incorrect data types? We want to check with Copilot to see if there are any incorrect data types. What we mean is something like this. Age column ideally should have numeric values. It should not be having alphabetical values. Yes, it is identifying in age there is. Currently stored as object. It is identifying those values as well. Textual data can be here. In the He column, highlight the entries in non numeric format. That is what we wanted to highlight it next. It has been able to do so. I think it should be able to highlight it also easily that in the age column specifically, we would want all the textual values to be highlighted. We can apply it has highlighted all of them. Next, what we want is to highlight the cells with missing data in column I. Okay? So in column I, we noticed that there is some missing data like here, here, here, we have missing data. So we just want Copilot to help us with that. Imagine doing it for a huge dataset. Right now we are looking at a small dataset, so manually, you can go ahead and do it. But if suppose you have 1,000 2000 or 10,000 entries, then how would you be doing that? So then Copilot can be really useful. So it has identified that and we can apply. So now, over here. So in this manner, it is going to go ahead and find the missing data. It has not completely done that for all the areas, but some of it. Then what we're looking at is suggesting actions for the missing data. So we want what should be done. We also, let's say don't know what kind of data should be there in those particular cells. We want to take some idea from Copilot giving us idea what type of data should be filled in those cells specifically. It is recommended to first identify which columns have missing problem and then decide where to remove, impute or correct those values. Okay, giving us some information. Next is what header do the missing values come under? So we want it to tell us the missing values which we see in the dataset. They're coming from which headers. Okay? So mostly it is coming from continents. In continence, we see missing values. And then there can be other ones as well. So ideally, it is continence only. Also, we want to know, is there any data in the table that can help infer the continent? Okay? Tell us infer the continent, tell us which continents specifically it's observed that the table contains column, it is country name airport name, which can be used to infer the continent for each roof. From the country name and airport name, we can infer the continent for each room. Lastly, what we wanted to also do is create a new column with continents corresponding to the countries in column H in that column. Basically, for each country, we want their respective continents to be uh mentioned in the new cm. For example, we look at the country, let's say US, that's going to be North America. We want a proper column to be created for that. It has given it a name continence too, which we can insert over here. As you can see, this is how we can make use of Copilot to go ahead and correct data, make the corrections which we can do, fix the data first, and then we can go ahead and analyze it. 64. Correcting data errors: Hi, ais. Welcome to this session. In this session, we'll see how we can make use of Copilot for cleaning some data errors which might be happening with our dataset. So once we have the data sets, let's say this is the dataset which we have, and we need to correct some data over here, so that is what is needed. So if you see in case of attendee column, you can find there are some duplicates which we can identify here, which we need to remove. Then there are some issues with the formatting of the data input over here, then there are some missing data in this particular column. There are a couple of issues with the dataset. We need to fix those first which only we can start analyzing the data. This we can take Copilot help to do that. Let's say we want to highlight the duplicate values. So what we wanted is that to identify duplicate values in this whole dataset. Ideally it should be able to do so. We can be a little bit more specific in our prompts by mentioning the column name as well in which we want to highlight the duplicate values. But let's see if Copilot is able to pick from here itself and give us the output. These kind of formatting is very much needed when you're working with huge datasets. Imagine you have a dataset which has 200 plus rows. In such cases, identifying it manually can be a very cumbersome job and time consuming. So that is where Copilot like a tool can be of real use, where it can quickly identify those. So as you can see, it is not able to do that. Let's try with a little bit more specific wherein we say that highlight duplicate values in attendee column. As you can see, the tool is not working. So in such cases, what we do is we try to refresh ones and restart the tool so that it can start from the beginning and look at our prompt as a fresh prompt and works towards it. Let's be a little bit more specific with our prompt. Idally it should be able to do that because this is a pretty simple task, and now it has been able to do that. If you apply it, it has identified. As you can see, now it has identified the duplicates, what we want to do is remove the duplicates. Let's see whether it's able to identify. Now that it has identified, it should be able to remove them as well. Now you can see it is not able to do that. In such scenarios, what Copilot does is it will go ahead and give you the manual steps. That is what it is doing. It is giving us the manual steps towards how we can do that. So we can select the whole dataset. Then we can go to data. We can go to remove duplicates. Let's say we are only doing it for an ID, and we're asking four duplicates have been removed. Now we have the proper data. Let's look at in the years of experience now we want to because this has to be in the right format. Some of it is numeric, some of it is in alphabetical. Ideally, it is years of experience should be in numeric. We just want Copilot to identify that. Sadly, it should be able to identify the textual data provided here. Again, it is not able to do it right now. What you can do is ideally, we can try again later on possibly or else we can manually change let's look at another different particular calculation which we want from Copilot to do, which is with total sales. Now, as you see in the total sales column, some of the values are missing. We want to provide these values now. You understand that this total sales is coming from ideally the price of the ticket and multiplied by number of tickets bought. From there, we are getting it. So ideally it should be able to calculate that and create a new column for us. So let's see it's able to do that or not. Looks like it has been able to do that. Now we can insert that as well. Let's see, yes, it is calculating in the right manner. You can see the formula also implemented properly over here. This is how we can go ahead and, um, make some fixes in our data and correct the data which we have sometimes errors which we find in dataset before we start working with the help of Copilot. 65. Transpose data for trend analysis: Hi, guys. Welcome to this session. In this session, we'll see how we can transposing data as well with the help of co pilot for our datasets. So once you're on the dataset, let's say this is the dataset we are working on. What we can do here is we can also transposing the data in whichever format you want to. Let's say we ask Copilot to do that for us. We're asking can you transposing the rows and columns in this table. So ideally it should be able to give us that data in that particular manner. But sometimes what we can see here is Copilot might not be able to do that. In such a case, it will provide you the formula. The way manually you can do that yourself also. Let's see what output it gives. Also, what we will see here is whether Copilot is able to provide create a pivot table based on the data set which we have. So right now, as you can see, it is not able to do that, so it's giving us the formula to do that. So we can apply it here. Let's say here, we say transpose, and we select the whole data. And we have it. We have the whole data transposing as per R point. This is how we can do it. Let's look at another one, which is pivot table creation. Let's ask Copilot to create a pivot table based on the data set. Usually Copilot should be able to do that and it can analyze the data, create the pivot table for us, which we can then use for analyzing our dataset. This can be really useful when you're dealing with a huge dataset with huge amount of data in it, quickly, it can create the pivot table for us, which can be really useful in analyzing. So let's see how Copilot is able to do that. Again, right now, what it is doing is it is giving us the manual steps to do so. This is the idea behind how Copilot can be used for transposing data as well and then making use of it in our day to day work. 66. Date-time formatting techniques: Hi, guys. Welcome to this session. In this session, we'll see how we can go ahead and do data date time formatting as well with the help of Copilot in our dataset. So let's have a look at that. Let's say this is our dataset which we are working on. And if you closely notice, there are some issues with the date format over here, time format as well. This is 9:00 A.M. But this is again, 24 hour format which we get to see here. This is 1030, which we don't understand whether it's am or pm. So there is some issues with the dates and the time format which is provided in this dataset. Let's see if Copilot can help us with that. So we can simply ask to change the time formatting in this table. So we're asking it to look at the time formatting provided in the dataset and change that appropriately. Now, this can be a little this is a little generic prompt which we are giving. Okay? We have not specified the specific format in which we want it, so it is going to look at the dataset and try to identify that. Okay? So same manner. Right now, as you can see, it is only identifying the one which is there in the first table. Let's see it has given us certain steps and let's autofill It's going to apply those. Let's see now, this is for the time format. Time format has been seems like corrected. It has been corrected. Now in the same manner, let's say we're asking it to change the date formatting as well. Let's see how it does that. You can see the date is very wrong over here provided, we just need to correct that properly. And it has given the data for that also. So let's supply. So it has been able to do it for all of these, but not for these ones, so we'll come back to it. Okay? So now, on the contrary, the other table, it has not even identified. It has not even identified the other table to make those changes for the date on this one. Okay? So what we can do is we can also ask. Let's see if we ask it to do both of them. How can I format the date and time? It's going to give us certain steps, possibly, manual steps which we can use to format the data. Okay. So you can see to a certain extent Copilot is able to format date and time in our datasets, which you can certainly make use of when you're dealing with huge amount of data, and it can be really useful in saving a lot of time. Or else you can simply do this manually as well by highlighting that particular column and going to format cells. And from format cells, you can choose the format which you want for your date and time. So let's have a look at that. Let's format this first. And let's say we want it in this particular format. It has done that for this also. Time was done. Let's do this one. This is already in this format, so it's fine or else we can do it in this manner. For time as well, let's look at time. This is how we can go ahead and make the change. Here you can see what it has done is it has given us the steps to format our data time in Excel. I hope this makes sense. I hope you're able to understand how Copilot can be used in formatting date and time also in our datasets. 67. Thank you for taking this class!: Hi, guys. Congratulations for coming to the end of this class. I hope the content was useful. The intent of the class was to make you understand how we can use Copilot effectively with Microsoft Excel to improve our productivity. Thank you once again for taking this class and I'm really excited to see you again soon in a new class.