AI Powered Business Analytics with Excel Full Course | Excel For Business Analytics | Simplilearn

What if I told you that the same Excel you've been using for years can now think, analyze, and even suggest insights for you? Yes, the spreadsheet tool you thought was just formulas and tables is now powered by AI and that changes everything. Welcome to this course on AI powered business analytics with Excel. In today's business world, data is everywhere. sales data, finance reports, customer records, performance dashboard. But here's the real question. Are you just storing data or are you actually extracting intelligence from it? That's exactly what this course is about. Now, let me quickly walk you through what we are going to cover in today's video. First, we will understand what business analytics really means. Not in textbook language, but in practical real world terms. You will clearly understand the difference between descriptive, diagnostic, predictive, and prescriptive analytics. We will be mastering data cleaning and preparation in Excel. Because let's be honest, raw data is messy. We will learn how to remove duplicates, fix formats, clean text, and prepare data properly for analysis. We'll dive into powerful Excel functions like if and or lookups and understand when and why to use each one. We'll explore AI powered features inside Excel like analyze data, smart insights, forecasting, and how Excel can automatically detect patterns and trends for you. We'll also build real business dashboards like sales analysis, profit tracking, month-on-month comparisons, budget versus actual analysis, the kind of reports that managers and leadership actually uses to make decisions. Finally, we will see how to combine Excel plus AI to save time, reduce manual effort, and become smarter business professional. And by the end of this course, you won't just use Excel. You will think analytically. You'll spot trends faster. You will make datadriven decisions with confidence. And if you're ready to level up your Excel skills and step into the future of AI powered analytics, let's get started. Now, also if you're interested in boosting your career in business analysis, do not forget to check out our AI powered business analyst course. This course is perfect for professionals who are looking to enhance their skills with the latest tools like PowerBI, Excel, SQL, all while gaining hands-on experience with real world projects. You will also learn how to leverage generative AI for smarter, faster decision- making. Our program is IABA Babok B39 and it will help you prepare for certifications like CBA CCBA. You'll be engaging with 10 plus industry projects, 40 plus practical activities and benefit from live online sessions led by expert and with simply learn job assist program you'll get the support you need to land your next big role. So what are you waiting for? Hurry up and enroll now. You can find the course link below. Before we move ahead let's start with a quick quiz to see where you can stand. The question is, which of the following best describes AI powered business analytics in Excel? Your options are only creating charts and tables, automatically cleaning data without any effort, using Excel with AI features to generate insights and support decision- making, just applying basic formulas like sum and average. Take a moment, think about it, and drop your answers in the comment section below. And let's see how many of you got the correct answer. So, without any further ado, let's get started. So I welcome you all to this course on AI forward business analytics with Excel. Now uh so what is analytics? Let us just get started with this. So analytics is analyzing the data. So guys there is a a small situation okay scenario just imagine you have noted down all your uh daily habits in a journal. See do you have the by the way do you have the you know habit of journaling your daily activities. So if you have had the habit of journaling your activities daily in a diary or in some uh uh Excel sheet or in some app then over a period of say uh 6 months or one year you'll be having lot of data right now when you analyze the data like u uh uh uh like when you are happy or when you are uh not in a happy mood or when you are waking up or when you are on time to your office or when you are uh uh uh uh sleeping better. So if you want to analyze those kind of things over a period of time so it's easier to analyze uh having captured the data you can just you know use some techniques in the excel and you know you can uh analyze the data okay so that you can find out the insights the underlying insights like you know when you are happy or when you are sleeping when you are sleeping like when you feel that when you are waking up in the morning when you felt fresh like you have had enough sleep. Okay. So those kind of things you can monitor like based on your number of hours of sleep or the time at which you you are uh uh starting the sleep. Okay. So those kind of uh insights you can generate. Are you getting? Okay. So this is just an example for uh uh uh analytics. Okay. No. Okay. So by end of this lesson we will be learning about different types of business analytics with real world examples to understand their application in different business text and also the applications of business analytics to highlight relevance in making the business decisions. Okay. And also we'll be uh you learning the capabilities and features of Excel as a business analytics tool. Okay. So these are the learning objectives of this particular module. Right. Now so as you have already mentioned the analytics so it is analyzing the data to get insights to make a business decisions. Okay. So uh as a formal definition so it is the process of analyzing raw data. Okay. So this is raw data. Raw data means which is not clean or which is just collected from a source. So you have different data sources right? So in today's datadriven world analytics plays a key role in different domains like sports, finance, business, government and uh administration in retail sector whatn not in manufacturing in all the different sectors. Okay. So in the sports so what do you think the uh analytics will be helping the sports people sports person and uh in the finance again it will be used in the finance we have a lot of applications of analytics like you know fraud detection or know uh eligibility for loans when somebody applies for a loan whether this person can be given a loan or not and also uh like know uh creating uh uh uh specific group based uh advertising campaigns okay like you know high net worth individuals now in the business it is used for a lot of purposes so as you know it is used for you know moniing of the KPIs or operations optimization or inventory management and uh uh or uh know supply chain management or analytics. So there are lot of applications uh in the uh business uh world. Okay. Yeah. Market analysis, sales analysis and in the government sector we have lot of uh applications of analytics. So uh the prime thing in government sector is in a public policy formulation like especially with related to collecting of census data. Okay. population related data and it like age group wise, gender wise, region wise or state wise and formulating the policies for a different uh uh you know groups of people like suppose in a place you have lot of children say uh uh uh 3 years to say 5 years. So you will have to have some creature kind of thing for the children. Okay. Or schools or hospitals or playgrounds. These kind of things can be you know policies can be made on these kind of facilities or infrastructure creation by the governments based on this kind of analysis. Okay. And also during COVID 19 time you might have observed in the 2021 in in those two years. So we used to get lot of analysis right like how many COVID positive cases and and uh uh how many are tested how many got impact uh I mean affected positively. Now uh why do we study analytics? So it often it so analytics it is often related to the study of historical data that is past data okay previous data previous collected data okay so on that we'll be studying to find out the potential trends and patterns inside them and here as businesses we'll be studying uh two important things okay one is to understand the effects of certain decisions taken like say uh uh just imagine a company's sales are going down. Say last year company has observed that sales is going down. Okay. So based on uh this uh insight reported the management has taken certain decisions. Okay. To create a new uh models and also to uh uh uh give more discounts to the customers. Okay. Those kind of many other uh decisions they have taken. Now this year they want to see the effect of those decisions. Okay, whether they are helping business to grow or not or improve the sales or not. Okay, so these kind of things can be done in the analytics and also it is used to evaluate performance of the businesses like you know the goals and targets of the companies uh how far they have achieved. Okay, each company each business has their own goals, right? Targets business goals for the entire financial year. Okay, quarterly also they'll be having the targets. Okay. So in such situation they they can you know analyze their actual performance with their targets and they can know where they exactly with respect to the targets and based on that gap if there is any they can you know speed up their performance they can engage more resources and they can speed up the performance. So let us try to understand what is business analytics. Okay. So business analyt analytics it is the process of collecting collecting the data cleaning the data analyzing it and interpreting the data to identify trends patterns and insights to make datadriven business decisions. Okay to make uh uh business decisions guys. So this business analy is useful to make business decisions. So that is very important. Okay. Datadriven business decision. So you'll be collecting the data related to the problem that is at hand and uh cleaning the data and analyzing it and interpreting the uh trends. Okay. Interpreting trends, patterns and insight. So this will help the businesses to take the decisions. Okay. So like say a company sales is going down. Okay. Now this task has been assigned to you to provide to analyze the data sales data why the company sales are going down and to provide uh uh recommendations. Okay. So you have analyzed the data you have found out that some products are not doing well in certain regions. Okay. And because of competitor uh uh effects and also market situation. So you have come up with some uh recommendations okay saying that you'll have to increase the features or reduce the pricing or you'll have to deliver it faster than the competitors okay those kind of recommendations you have provided so this decisions uh I mean once you have added the recommendations based on your analysis so the business the uh uh person who is in charge like it it could be president or CEO or the general manager whoever it is so they will take the decision to implement those recommendations. Okay. So these kind of a decisions are called datadriven decisions. Is it clear everyone? So business analytic analytics is more related to the uh D making based on your analysis you are going to take decisions. Those decisions are going to be implemented in the business for the benefit of the business. Okay. Now let's get into the uh another business analytics scenario. Okay. So just imagine a store manager of a clothing retail chain is facing challenges with inconsistent sales performance across different locations. So imagine that the person has this clothing retail chain has their stores in different cities. Okay. say one in New York, one in London, one in Delhi, another in Mumbai. Okay, like this. So here the problem is some stores frequently overstock certain items while others quickly run out of best sellers. Okay, so to solve this problem, company launches a business analytics initiative to study the sales data, inventory movement and seasonal trends and regional buying behavior. Okay. So data such as store ID, product category, sales volume, inventory turnover and local events are collected and analyzed to generate meaningful insights. So in this situation in this particular example, so you will have to analyze like what are the stores where you will you are seeing over stock that is stock is not at all moving and for what products what items it is not moving and for how long it is not moving. Okay. So that is about the over stock and for the other stores where it is running out of the stock for the best seller products. So you'll have to find out where in which stores it is running out of the stocks. How frequent it is running out of the stocks. Are there any uh events around which it is running out of the stock like you know New Year or Christmas or Diwali. So around this you know festival seasons or any such seasons are they running out of the stock? Okay. So those things you will have to analyze and find out and inventory are you maintaining sufficient inventory or not. Okay. And is there any seasonal uh trend or not around these festival seasons is the stockout is happening more frequently. So these kind of things have to be discovered using business analytics. And then we'll have to balance this based on the uh insights that we found out. Okay. So the steps include first we need to detect the issues that is wherever this over stock or running out of stock is happening and then we have to analyze the demand uh trends okay we have to track regional buying trends like on festival seasons or season any other seasonal events where demand is more and then we have to optimize the stock distribution. So we have to balance the things based on insights. Now guys, so let us try to understand different types of business analytics. So here we have these four different types of distinct types of business analytics are there. Okay. So the first one is descriptive analytics and the second one is diagnostic analytics. Third one is predictive analytics. One is descriptive analytics. Okay. So what is descriptive analytics? So in the descriptive analytics we'll be discussing what has happened previously. Okay. See you let us discuss it in more detail. Suppose you have sales data a company's sales data. You can find out how much sales you have made in what regions how much sales you have made. What products have contributed to your sales. Okay. So those kind of things can be discovered or found out using the descriptive analytics. Okay. So it is purely based on the historical data or past data. Now so in the diagnostics diagnostic uh uh analytics okay the diagnostic analytics we'll be determining why something has happened. Okay so we'll discuss them in detail now after the break. Okay. And in the predictive uh analytics we'll be doing the forecast. Okay. So what will happen next? So during the COVID 19 you might have observed or you might uh you might be remembering now we have got say uh two lakh cases okay or today. So you'll be having predictions for tomorrow or next month like three lakh cases or four lakh cases right? Okay. So what are these things? These are the predictions based on the past data. So what could happen next? Okay. So that is about the predictive analytics and the prescriptive analytics it talks about what should happen. Okay. So it mostly talks about the optimization. Okay. So optimal resource utilization and many more things. Okay. So these are basically four different types of business analytics. Is this clear everyone? Okay. Now let us see in the descriptive analytics. Okay. So in the descriptive analytics so it is the process of analyzing historical data that is past data okay to uncover trends and patterns inside the data which will help the organizations to understand what has happened in the past okay so it is the most basic form of the data analytics okay so suppose you have sales data company sales data now your management wants to find out how much sales did we make. Last year, how much sales did we make? So, what we will be doing? What we'll be doing? We will be analyzing the total sales, right? Okay. So, let me just show you here uh with one Excel file. Just a moment. It sales related data. Okay. So we can just have a glance at it. So we have the row ID, order ID, order date, ship date, ship mode, uh customer ID, customer name, segment, country, city, postal code, region, category, subcategory, product name, sales, quantity, discount and profit. Okay. Now this is the sales data. So what we have is we have uh total of 9,994 records. Okay, now we want to find out what is the total sales amount here. Is that clear? Is the business question understood? What is the total sales? So now we are discussing about the descriptive analytics that means what has happened in the past. So this we are uh uh uh uh we have collected the past data that is historical data. Now we want to find out what is the total sales. So column R just select the column R here. Okay. So total sales you can find here. Are you able to see the total sales here? 22 lakh 97,200 that is about 2.2 million. Okay. Approximately 2.3 million. So this is one uh descriptive uh insight that we can get from the using descriptive analytics. Okay, this is an example for descriptive analytics. Now we want to just say we want to find out the total profit. Okay, so just select the column U here. Okay, are you able to see the total profit here? 286, 397. So this is uh uh another uh you know descriptive analytics. Okay. On a different metric. Earlier we have seen it on sales. Now we are seeing it on the profit. Is it understood everyone? This is a simple uh uh analytics. Okay. So now we want to see like uh suppose there is a uh case which region is uh making less sales. Okay. So if you want to find out like uh say one particular region uh east region is doing less sales. Suppose it was reported that east region is doing less sales. Okay. Now you want to find out why it is doing less sales. when we try to find out why. So what is this kind of analytics called diagnostic? Okay. See we all go to doctors right. So what the doctor will do when we go to the uh hospital. So they will a doctor will try to speak to us. They will try to you know uh uh uh understand the situation like you know based on the symptoms that we tell that we are getting fever or headache or some pain or uh some other thing okay based on those symptoms doctor will try to diagnose us right they'll try to find out what what could be the uh uh health problem that this person is having okay and if it is not confirmed through uh through the tests that the doctor is doing uh using his equipment and he will send it to advanced testing okay like MR scan or CT scan or many other things okay blood test many other things okay so using those test results doctor will diagnose us right they'll try to find out what has happened really okay why the person is getting these symptoms are you all getting why the person is getting say fever continuously say for 10 days or 15 days Okay. Though many many kind of things. Okay. So here doctor is diagnosing the patient. Okay. Similarly here also when we diagnize the data it is called diagnostic analytics like where where exactly it is happening why because of what reason it is happening. Are you all getting? So here profit is this much. Okay. Now if you want to see in which region is profit more and or less why it is less. Okay. Say we'll select this one. Okay. And uh let us just uh uh uh create a pivot table random pivot pivot table. We'll be doing this later on as well. So here we'll just select the uh region and save profit. Okay. Now are you able to see here which region is having the less profit east region is having the lowest profit right 90,522 okay so if you want to drill down more why it is less in the eastern region so basically this diagnostic tool is diagnostic analytics is nothing but so you'll be using drill downs you'll be using uh you know lot of other analytical tools to find where exactly the problem is. Okay. So you can use say uh some kind of say category. So you can use category here to find out which region is uh having less profit. You can see here in the eastern region furniture is having the lowest profit only 3,000 you can see here. Okay. So that means furniture is contributing to the lowest profit in the eastern region. So this is one of the contributing uh uh factors. Are you all getting? Okay. So these kind of insights we can uh get using the diagnostic analytics. Okay. Let me just show you just a moment. Uh let us just remove the category. Okay. So central region is having the lowest profit that is 39,76. So here uh that is central region. Okay. Sorry category. So when we bring the category you can clearly observe here. Okay. Uh central region furniture. Okay. Furniture category is in the negative that is it is causing the loss. Okay. So this is one of the contributing factor for the uh central region uh profit to be low. Okay. Now inside the profit inside the furniture now you can find out what exactly is this subcategory. Okay. You can just find out more details. Okay. Inside you can see here. So bookcases are native, furnishings are in the native, tables are in the native. Okay. because of these things we are having total negative. Okay. So this kind of drilling down is called diagnostic uh analytics. Is it understood? Anyone all of you see sales and profit need not go in tandem. Some products uh might be more profitable, some products might be less profitable. Okay. But sales could be different. Okay. Now predictive analytics. So predictive analytics is nothing but it uses the historical data and it will predict for the future using some model some technique. Okay. See uh uh uh let me give you a few examples. Now take the case of a company. Okay. Sayan. So okay we need not use any specific company name. So take a business. Okay. Suppose uh you have this sales data say for 2000 uh 21 22 23 24 and now 25. Okay. So you have this five years sales data. Now you want to know what could be your sales next year. You want to predict or forecast your sales for the next year. Are you getting the business request? Okay. So this is done by a prediction. Okay. Based on the historical based on this past data, we are going to predict the new data. Okay. For 2026. So this is done using various statistical models like prediction algorithms or there are lot of algorithms like you know uh uh exponential smoothing technique and many other techniques would be there to predict they assume certain uh they make certain assumptions and uh they will be predicting for the future values. Okay. So we have lot of you know statistical algorithms machine learning algorithms to predict for the future values. So how prediction is done is see let me just show you okay suppose you now say we have the sales data okay sales data related to 2000 21 22 23 24 25 just imagine okay so this is your graph okay you have x-axis here and yaxis here okay now In x-axis you have the year 2021 year 22 24 25. Okay. Now here you want to predict for 26. Now on the y-axis you have the price. Okay. That is sales. Suppose you have the sales here. Okay. Say for 2021 you have some sales like this. 2022 23 24 say 25. Okay. Now, so uh where do you think 2026 will be there? So, in order to find out what will be the uh for what will be the sales for 2026, what we have to do is we have to draw a line which is touching all these data points or very close to these data points. Okay? Say some line like this. Okay? So this line is called aggression line. Okay. This line is called regression line or uh uh yeah line of best fit. Okay. Line of best fit which will fit uh uh all the data points most the data points if not all. Okay. Yeah it could be up or down satas just it is hypothetical. Okay. Just I'm trying to explain. Okay. Now, so we have come up to here. Okay. Now, if we continue this straight line, okay, if we continue this straight line using say dotted lines, okay, the straight line. And now you draw the dot vertical dotted lines. Okay. So, wherever it is touching, okay, this is your predicted sales for 2026. Are you getting? you you extend the vertical line of 2026 year and you extend line of this best fit. So wherever these two touch this point is the uh predicted sales value for 2026. No we will not be using those things uh goro we'll see some forecasting techniques we will see here in Excel if time permits okay based on the curriculum. So this is called prediction. I hope it is understood. See uh we have lot of applications of prediction guys. Okay. See uh uh you might have uh used this weather forecast. I think all of us have used weather forecast right whether it is going to rain tomorrow or whether it it is going to be sunny or uh what be the temperature tomorrow. Okay. So weather forecast I think most of us have used right and then uh we might have used uh we uh uh in the sports especially in cricket those of you in India you might be knowing and many other countries as well like uh cricket is now a popular sport as well other than in the subcontinent Indian subcontinent so say we have a 50-our match okay just imagine we have one day match okay so in the one day match 50 hours. Say uh the match is match has happened up to say 25 hours. Okay. Now there will be a score predictor. You know that so up to 50 hours what could be the likely score. Okay. So what is is it doing based on the past data based on the net run rate that is available here and remaining or how many or are remaining. So based on that it will do a calculation hypothe calculation. Say the run rate here is say uh uh six. Okay. So for 50 hours it will be 300 runs. Okay. So like this it will predict. So this is called score predictor. So it is forecasting for the future. Are you all getting? Similarly we have this forecast applications in many applications like you know COVID 19. You might have extensively seen that seen those things like a prediction of positive cases, deaths and also many other things and uh yes correct that is also there nish. So if the uh chasing team if they have certain run rate how how much they can achieve or whether they can win or not okay in how many hours they can win the match if they continue the same run rate okay those kind of things and also this uh those uh I think most of you are in finance right most of many of the participants here are in finance so in finance you have a lot of these predictions forecast, fuel average prediction. So there are so many things, sales prediction, there are so many predictions guys. These are all b historical data and they use some specific statistical models or algorithms and they make some assumptions. So based on those things, predictions will be made for the future. Okay, hope it is understood everyone. Now let us try to understand why do we make predictive analytics? Why do we make predictive analytics? to prepare the sources in advance and to meet the demand of customers in advance. Okay, I mean to meet the demand we are forecasting and seeing what is what. Okay, how much is the demand for us to understand the trends. So those of you in banking and finance sector you can find out that uh while we are taking loans while the businesses are taking loans they also wants to see the uh loan giving agencies basically the lending agencies bankers and financials they want to see what is the future business of the company. Okay they want to see whether the business is sustainable or not in the long run. Okay, they want to see the predictions for the future next two years, 5 years, something like that. Okay, so based on that they will gain some confidence and they can grant the loans or any such thing. Okay. So there are so many uh uh yeah credit ratings also risk related things also future loan forecasting also how much uh uh loans might be required for different segments. Okay, those kind of things. company quarterly results also can be forecast. So now we'll discuss about the prescriptive analytics guys. Okay. So prescriptive analytics. So as we have seen where we have started we have started at descriptive analytics. So it talks about what has happened previously and then we have discussed about diagnostic analytics that is uh to find out why something has happened and then we have discussed about predictive analytics which says what would happen in the future based on the past data or historical data. Now prescriptive analytics is the one which improves the decision making by identifying the optimal choice among the various alternatives that are available to us. So here the classic examples are like you know uh recommendations guys. Okay. So now if you watch say uh uh Netflix movies. Okay. So based on your previous uh uh viewing of different uh films or uh yeah different kinds of films like action films or uh say uh comedy films or many other things or love story related things. So based on your watching history the Netflix or any uh such kind of platform will recommend movies to you. Okay. So what is it doing based on your past history? So it is recommending based on your based on your previous decisions. So it is providing optimal uh uh choice to you. Okay. Similarly in the e-commerce sector also you'll get product recommendations based on liking also. So yes in a social media platform like in YouTube or in based on liking or based on comments that we provide even in LinkedIn also. So those kind of things will be recommended to you shown to you. Okay. So in the background these recommendations engines will be running. So it will be collecting all the statistics based on your uh viewing of a particular post or particular ad or uh your liking or not liking. Okay. So based on those things it will collect analyze and it will personalize it. Okay. It will personalize it to you. Okay. to the individual and it will show you the results product suggestion that is in the e-commerce especially Amazon or Flipkart or many other uh online portals. So for the businesses, so for the businesses this descriptive analytics are useful to make optimal business likeation uh resources in the form of uh people and also resources in the form of money and machinery. Okay. So those kind of things will be possible. So now hope you have understood all the four types of analytics. Okay. descriptive, dynastic, predictive and prescriptive. So shall we go ahead guys? Let us discuss about the applications of business analytics guys. Okay. Now we have discussed the types of business analytics. Now let us discuss about the applications of business. Okay. Now so we have vast areas of application of business analytics. So uh do you think there is any restriction on the use of business analytics in a particular sector? Or it can be applied in all sectors. It can be used in all sectors. So now uh this business analytics we can use it in all sectors. Okay. So it's in financial analytics we can you know collect the data related to the customers and see suppose you are in say telecom sector. Okay. Just imagine say you are in uh now most of you are in finance sector. Now you can use this uh uh business analytics in different forms. Okay, like you can use this uh uh descriptive analytics to find out what has happened in your company like with respect to the sales and you can drill down and dig deep into why some sales why the sales are low in particular region or for a particular product or particular uh uh uh model of the car. Any such things you can find out. Okay. And you can also use this uh uh uh predictive analytics to uh forecast the future sales or forecast the uh uh future earnings or revenues or uh even you know uh these uh uh clients or any such thing. Okay. You can also forecast uh say uh population or GDP of a country. Okay. So all those things come under financial analytics. And in the performance analytics, so let us uh yeah see in the financial analytics. So we can see this. So it helps finance executives excuse me to uh uh explore different ways answer specific finance related questions and forecast future financial situations. like what could be the sales, what could be the uh you know uh in in the banks especially in the financial sector if you closely observe the uh uh balance sheets uh balance sheets or the annual statements okay annual financial statements of the companies. So uh they'll be doing some kind of provision okay provision for some you know uh uh uh bad loans or any such kind of things okay uh did you ever observe those kind of things. So these provisions how do they come how do they arrive at these provisions? They will be doing the forecast based on the previous uh data that is available. they'll be forecasting for the pre uh next year. Okay. Then they'll be providing this that much am I say 10 crores. Okay. 10 cr rupees as you know right of or something like you know uh uh like uh loans which cannot be you know recovered. Okay those kind of things right and also you can use this uh kind of financial analytics to maximize the investment value. So this investment value especially with the you know u uh uh angel investors and uh uh various uh uh you know investors who will be investing mostly in the startups right okay so nowadays this uh trend is very high in Indian market as well okay so these investors they'll be looking at the startups and then they will be based on their uh confidence and based on their financial uh soundness they'll be investing in the particular startup to maximize their investment value. Okay. So if they invest in some particular company how much growth they will show us how much growth they will get in say 2 years 3 years or 5 years. So they will try to maximize their investment value. So investors will look for such kind of opportunities. Suppose you have say uh uh uh two companies okay two startups say startup one and startup two say one is in retail sector and say one is in IT sector okay now there is an investor now investor he will try to know find out if he invests say 100 uh say $10 million say in this one and this one so how much uh maximum value investment value roy he will get he or she will get okay in the retail sector and in the IT sector AI is trending right so as an investor if you invest in a technologies okay so you you'll be able to know get maximum investment value okay so investors will always be looking at this aspect how to maximize their investment value okay now we can also use uh it financial analytics We can use it to gain multiple views like if you have the sales data you can give it a region wise sales or category wise sales or segment wise sales or a combination of these things okay so that you can take better decisions. So if you remember earlier what we have done we have done a drill down so diagnostic analysis. So where we have seen that the central region and the furniture category is doing the lowest sales profit. Okay. So those kind of multiple use can be seen there. Okay. So this is with respect to the financial analytics guys. Hope it is clear. Now coming to the performance analytics. So performance analytics it is used in the businesses for different things. Okay. So for planning the strategies uh uh suppose you you have say sales. Okay. Uh a company products are being sold. Okay. Now, so here in the performance analytics, so uh for planning of the strategies like you know where to invest like here uh uh suppose if you have uh uh uh say a car manufacturing company. Okay. Now you want to say uh what is trending in the cars nowadays? What is trending is EV electric vehicles. Very good. Borrow. So, electric vehicles is trending nowadays. Okay. Now, so you can use this performance analytics to you know find out like where do you uh you can create a strategies like you know which region is having low penetration of electric vehicles and where uh there is more concern for the environment. Okay. So based on those things you can you know uh plan your strategies to uh diversify or to go deeper into those markets to invest more into those markets. And uh we have other applications as well. So pinpointing areas of improvement. Okay. Suppose uh uh you have uh EV market. Let us take the same examples electric vehicles. So now you are in a different cities say five cities. Okay. Now uh few cities are not doing good in terms of sales. Okay. And there are also uh say uh vehicles are called back due to some issues. Okay. So then you can analyze this performance analytics because of what parts what you know supply suppliers are the parts of lower quality the uh vehicles are called back okay they're not performing better okay so those kind of areas you can uh pinpoint and you can uh identify them for improvement okay yes uh another example good example is charging stations Okay. Now you can analyze where you have the EV charging station. What is the distance between uh highest distance between two EV charging stations. So you can try to optimize that. You can try to you know uh uh create a new EV charging stations in between those uh uh long distance EV charging stations and uh you can also use it for creating budgets for the businesses. Okay. uh like uh if the uh suppose uh uh if you are doing uh in a project suppose uh you are main you are IT project manager just imagine you are IT project manager now you are doing analysis of your quarterly resource allocation okay so when you are doing quarterly resource allocation uh I mean analysis quarterly resource analysis you found that uh you are not able to do the work towards the third or fourth quarter. Okay. So there is lot of work pending in the third or fourth quarter. So what can you do about it? OPD center. So like this you have you know managing daily operations in the shop floor or any such things and here meeting service level agreements. So this is especially useful for uh service companies. Okay. So when they are uh uh uh providing some software or some services say you have uh uh uh tickets. So when you want to monitor these tickets okay so tickets means these are the issues that basically these are the issues that the customers are uh reporting. Okay. So each each issue is give a ticket. Okay. So these tickets have to be know addressed by the companies. Okay. So they will be having some service level agreements. Okay. Like within uh one day these many tickets of critical nature have to be addressed or moderate nature have to be addressed in 2 days or less mo I mean low priority items can be uh cleared or addressed in 3 days like this. Okay. If you have these kind of service level agreements. So you will have to see uh how far you are able to meet those service level agreements. Okay. So to do that kind of monitoring also we can use performance analytics. So and also uh see in software companies you will be having these uh you know uh uh target deliverables right based on uh some time and uh uh project milestones there will be deliverables. Okay. So to monitor those kind of deliverables whether they are being done on time or not also you can use this uh performance Now customer analytics it is a huge area especially. So where do you think this can be used? Customer analytics. So customer analytics can be used telecom sectors, retail sector, aviation sector and social media platforms right? Retention of the old customers. So churn rate okay the way who are going away from the existing service provider to new service provider. So it happens in the telecom sector and uh internet and broadcasting sectors and you know TV channels those many things many other things even uh banking also it happens. Okay. So, so in these sectors, so this kind of analytics, customer analytics will be done by different uh parameters guys, different parameters say by age, okay, by gender, by region, by education, okay, and uh uh by uh you know income levels, okay. So by these many parameters and many more these are only few things I can just quote you. So there will be many more dimensions by which this uh customer segmentation will done. Okay. And then uh uh customer retention, new customer acquisitions, loyalty programs to the c customers, additional services, bundling of the services like in the telecom sector like you can add more more value added services like more data or more vice or SMS related things or additional uh free uh uh uh uh uh OTT platforms those kind of things can be given to you things for you analyzing the customer data and providing campaigns for say reduced uh interest rates for your uh uh credit cards or loans or you know uh giving higher margin for the loans those kind of things can okay so here critical decision making this is for customer retention and also loyalty programs you can use the customer analytics and market segmentation predictive analytics so in the predictive Analytics you can use like which customer is likely to churn and you can apply this uh analytics to uh retain a customer to provide loyalty programs to that. Okay. Yeah. Product mix preference. Yeah. Customer lifetime value also can be calculated. So enhancing prediction prediction of customer behavior. So if a customer is making the payments on time. So what do you think of that customer and one customer is not making payments on time another customer is making on-time payments. So definitely the customer who is not paying on time right because he might not be satisfied with the services. So we have discussed about types of analytics right. So what are the types of analytics? descriptive, okay, diagnostic, then predictive, then prescriptive. Okay. So, we have discussed about these four types of analytics. Now all these four can be applied on all the four se four areas like finance then customer analytics then performance analytics and then risk analytics. Okay so it is 4x4 so it is 16 possibilities are there. Ainash are you able to get it? Okay. So in the finance sector you can use as we have also discussed right in detail if you remember. So we can we can use descriptive analytics like what has happened. Okay. And why something has happened and what could happen in the future. Okay. What is the optimum one? Okay. So these can be used in all the these four types of analytics can be used in all these four areas of applications. Okay. And these four areas of application these are only you know few things that are said here. Okay. It's not restricted only these four types of analytics. You have sports analytics, you have travel analytics, you have know hospital analytics. There are so many things. So that is the main focus. So uh without taking a business decisions there is no point in you know doing the analytics right? Okay. So it is the business analyst who will be doing most of these things. But you know uh people will be used in organizations people will be used for multiple tasks. Okay. Sometimes data analysts also will be given these additional responsibilities. At times data scientists also will be doing but but in my opinion uh most of the time business analysts will be doing these kind of analytics. Uh risk analytics. So in the risk analytics, so this is mainly used in uh uh uh projects like if a project could run into risks like you know uh uh missing the deadlines or any uh you know u uh like you know supply chain related issues like suppresses not available okay those kind of risk analytics can be done here. So uh this forecasting can be used to predict the uncertaintities okay some kind of uncertainties so that project can be evaluated whether it it may be successful or not okay and here managing and mitigating the risks across business operations is another important aspect. So once you know the risks then we have to come up with the mitigation plans like how to uh reduce its impact. Okay. Suppose you have a situation where supplies are likely to be affected. Okay. So what will you do? Suppose you have only one supplier supplying a critical component. Okay. and supplier is in a situation where he cannot supply the material due to some external factors like war or earthquake or uh cyclone or any such situation natural calamities or man-made calamities. Okay, it could be due to any such things. So what will you do to meet your projected lines? So here this risk analytics is especially important from in the finance sector and also uh banking sector and then in the project management. Okay. So uh so what can go wrong? So like in terms of dairy, in terms of cost or in terms of timeliness, okay, those kind of things you need to look for and have plans to uh mitigate the effects or impacts. Now, so let us just have a look at the Excel file and various menus and ribbons available in the Excel. Okay. So as you all know, Excel is a very powerful spreadsheet software which is developed by Microsoft company in 1985. So to be precise, September 1985 and it is used in almost all industries guys. It's not restricted to any particular industry. So it can be used across the different sectors like manufacturing, power, island oil and gas, aviation, IT, retail, whatnot, hospitality, uh, uh, medical all the sectors can be used, all the sectors can use or they are using Excel. Okay. So it is used for data analysis, management and visualization. So here uh let's uh uh try to understand its key features. Okay. It is extensively used for business analysis. Okay. So data in Excel is used if if data is available in Excel file, it can be analyzed for you know finding out the uh uh uh uh descriptive analytics or diagnostic analytics like KPS tracking or visualizations. Okay. For so many different purposes data can be analyzed. So uh and the next thing is data storage. Okay. So Excel file as such is used for storing the data. So it can store up to 2 GB data maximum and it can have 1 million records 16,348 columns. Okay. So it acts as a data storage system as well and it can be used for reporting purpose. So this reporting purpose in the in the form of say uh charts, graphs and you know dashboards, summaries and pivot tables. Okay. So many such things can be used uh as part of reporting and educational purposes. So for training purposes for you know students to become data analysts or business analyst uh this Excel can be used heavily. Okay. Is this understood everyone? So other capabilities include like know it has got extensive analytical uh and statistical analysis like we can do uh no analysis or chquare test and also we can do hypothesis testing in the Excel. And it offers intive analytical features to model and analyze the company's data like you can create a lot of visualizations charts. Okay. And it's a tool of choice to start the journey of data analytics. So as we have discussed you can just use the basic features of piv tables or visualizations or you know summaries or group by there are so many other things which you can use to start the data analytics journey and it comes with in-memory technology which is compatible with millions of compatible with millions of bytes of data. So here especially this in inmemory technology is nothing but here we can use you know power p okay so power p is used for basically uh data transformation techniques okay and uh it has added advantage of flat learning curve that is it is easier to learn so it's not a complex software uh to learn okay it's easy to Okay. And it performs necessary analysis without dependent on specialized solutions. That is as it is easier to learn and uh most of the times like drag and drop or uh using some formulas. So you can easily do the analysis. Okay. And also other advantages of Excel as a business analytical tools is like it makes exploring data and deriving results easily. So we can explore the data that is you can uh uh use like pure tables or visualizations to see what is there inside the data. You can also clean the data, you can transform the data, you can uh prepare the data to uh do the analysis. Okay. And we have also have uh a capabilities like you can integrate a uh tools like charge GPT or many other tools. Okay, co-pilot can integrate into Excel and you can uh derive the insights very quickly. Okay, you can do the data analysis very quickly. Okay. So you can use powerful data analytics uh with a few clicks like we have you know u this uh data tables under what if analysis you have lot of uh uh uh analytical uh tools like you know scenario manager data tables okay and it serves as widely accessible BI tool business intelligence tool for diverse organizational needs. So you can create lot of visualizations, dashboards or reports using the Excel. Okay. So it empowers robust data analytics through advanced functions and formulas. So we have lot of formulas and functions inside uh Excel. Okay. Like we have finance related functions, engineering related functions, mathematical functions and we have logical functions, text functions, related functions. So a lot of functions are there. So based on our type of data that we handle so we can do the analysis accordingly. Okay. So as we have discussed we can integrate uh Excel with the chart GPT and a spreadsheet. So chart GPT is for text related uh uh like it is like a Q&A type of thing. Okay. Question and answering. So it is basically on the foundation of NL okay natural language processing. So you ask a question and it will try to retrieve the answer from the existing data and this spreadsheet AI is for machine learning purposes. Okay, like predictions and many other things. Okay. So these are the capabilities that we have in Excel. Okay. Now uh let us discuss about data cleaning. Okay. And process uh preparation. So guys, why do we need to do data cleaning and preparation? See, we have to start with why. Okay, anything we have to start with why? Why do we need to clean the data and prepare it? Now, just think of this business scenario. Okay, like you are analyzing large regional sales report for an upcoming executive meeting. So, as you explore the data, you notice customer names spelled differently. Okay. Dates entered in different format. Different formats and some sales figures are missing and few entries are repeated multiple times. Okay. So now if you want to make full sense of the data. So what kind of steps or activities do you do? So the kind of issues are highlighted here. Okay. So you notice customer names are misspelled or know they are spelled differently. dates entered in different missing and a few entries are repeated. Now, so in this uh module we will see how to organize the raw data sets. The learning objectives are to organize the raw data sets in a structured manner and apply various data cleaning techniques to correct errors, remove duplicates and handling handle missing values and to use various Excel functions to transform and standardize the data and to prepare the data and construct dynamic formulas with cell references to automate the calculations and reduce manual errors and integrate a gen like chart GPT and extract insights. from the uh data. Now let us discuss about importing and organizing data. So there are different data sources from which we extract the data. Right. Now we will see just see yeah so data importing. So Excel enables data importing and consolidate data from variety of data sources using data tab and get data option. So here if you look at one excel file. So in this you can see uh this is the file. Okay. So go to the data here. In the data are you able to see here get data. So in the data rib okay you will see get data in get and transform data. Okay. So this is the group name get and transform data. So under it you have the option to get data. Okay. So here you can see get data. So from file different files are there. Okay. So data can be stored in different file formats like Excel, text or CSV, XML, JSON, PDF and from folders and as well. Okay. So if the data is in is available in different Excel files, we can just open this. Okay. So uh without this I mean uh when we open blank files okay you can just open blank workbook also like this. Okay. So you can open blank workbook like this and then you can go to the data here and you can click on get data and from files you can just select from Excel file. Okay. So from Excel workbook you can go to the uh folder where your Excel files are there and then you can go to the data sets. Okay. So like this say uh demo tool. Okay. So we can select this file and click on import. So we'll see the data in a way. Okay. So are you able to see this? So you can just select this sheet one. So this is very similar to what we see in uh PowerBI. Okay. In PowerBI also we will be connecting to different data sources in similar manner. So it is very similar. See if you have opened the data uh from an Excel file that's okay. And if you want to extract from some other Excel file that is the uh in such scenarios we can extract the data. We can import the data using this get data. Are you all getting? Okay. So here let us just click on get data and from file from Excel workbook say let us take demo one and import. Okay. So just select the So here what we'll do is we can just preview these things. So this is the data. So this screen this navigator is pretty much similar to what we see in PowerBI also. Okay. So you can select this particular thing and you can load it. Okay. So processing queries. So here in the power query editor. Okay. So it is taken as a query. Each data set is taken as query. Are you able to see here? So this automatically converts the data into Excel table. So this is called Excel table. Okay. Okay. Now let us see how to import the data from a text file. Okay. So go to the data again and here from file you can select a text or CSV. And let me just go to the desktop. So here I have uh data set. Okay. Okay. So I'm having this data set small data set. Okay. So I'm loading this as okay. So are you able to see? So whenever we extract uh data from different data sources, so a new sheet is created for those things. Okay. And now uh we can try to extract from say uh another file JSON file. Okay. So let me just show you how JSON file looks like. Okay. So some of you may be entirely new to JSON file. Uh yes. So guys this is the JSON file. So here JSON file it is called you know key value p. Okay, it will be having data in the form of key value pair. Okay, so what is key and what is value? See in Excel file, how do we have the data in the Excel file? How do we have this data? Any data set. Okay, let us take this one. Okay, so we are having columns. These are the columns, right? And these are the rows. Okay, so we'll be having the data in the form of columns and rows. So this is called structured uh uh data format. JSON file is called semistructured. And unstructured formats we have uh you know uh this social media uh uh files like it could be audio file, video file or text free text or photographs uh images those kind of things are called unstructured. Okay. So basically we have these three types of data sources uh three categories. Okay. So it is called key value pair guys. Okay. So this key serves like a column name. So here are you able to see age, job, marital status, education, default. These are all called keys. Okay. They are nothing but column names. And these values are nothing but these data points. 58, management, married, territory, tertiary. No, these values are called these are called values. Okay. So now what is a record in JSON file? So this record is stored in the in inside this curly braces. Okay. So from this curly brace to this curly brace. This is one record or one row. Is it understood everyone? So this is a JSON file. So it is called key value pair. And in the Python language if you are aware it is called dictionary. Okay. So this kind of data arrangement is called dictionary in Python language. Okay. Okay. So let us just be aware of these things. And now you can see how does the next record look like. Okay. So next record after a comma next record again starts with the curly braces and ends with the curly braces. Okay. Now let us try to import this data set into Excel here. Okay. So guys here I'm just clicking on get data. Go to from file and select from JSON. Okay. And uh just yeah so I'm selecting the JSON file and importing it. So it directly opens in the power editor. So once it opens in the power query editor. So you can just click on this. Okay. So each record you can see the records. Are you able to see the records? Okay. So age these are all the now these are all the uh uh keys. Okay. And on the right side you can see the values. Okay. So you can uh just uncheck this. So you can click on record again. This is the second record. Okay. So this applied steps is the place where it will store all the data transformation steps. Okay. Now what we'll do is so we can convert this into a table. Okay. To table just click on this. So here select the delimiter. So delimiter is uh yeah comma okay and let us click okay here just a okay so what we'll do is let us just go to home here and let us close and load okay on the what uh on the top left side you have the close and load. So just close and load and here yeah close and load. Let us just use close and load just a so here we'll go to the power editor again. Yeah. Let us try to convert it into the uh table. Yeah. Now are you able to see here? It is in the form of table. Okay. Now what we can do is we can just close it and load it. If you want you can also change the uh names column names. So it is taking some time to load the data. Okay. Are you able to see the data in the form of uh columns and rows? Uh you can also get get the data from other things like you know PDFs or you know XML files and data source databases. Okay. if we have any database. Okay. So from SQL server, if you have SQL server installed in your uh laptops or computers, you can just connect to those things and you can extract the data. So here you will have to provide the SQL server name and you have the advanced options like you know you can write the queries as well. Okay. So this is pretty similar interface uh that we have uh in PowerBI. Okay. So do you want me to show this SQL server database? It might take a little while to connect to the database. Okay. Just a moment guys. Let me just show you this and we'll get into the next topic. Okay. So I have Microsoft SQL installed. uh in my computer. Now guys, we will see how to connect to the SQL server. Okay, so I have just opened my SQL server. Let me just connect with this. So in fact guys this you know connecting to different data sources. Uh just just a moment I will have to start this server. So this connecting to different data sources it is uh uh quite timet uh concept. Okay. So we have seen uh connecting to different uh data sources. So similar thing you will find in powerba as well. So the server is getting started here. The skill server Okay, it's running. Okay, now uh we can just connect to the server. Okay guys, so this is my SQL server, Microsoft SQL server. So in this I have few databases. So you can see these are the three databases that I have data science uh portfolio project tutorial DB. So let me open one uh database tutorial DB. So within this you can see there are different tables. Okay. uh Raind see it is not required at this stage for you people to have the SQL server. So if you want to get into advanced analytics or uh based on your interest you can install the SQL server. Now here you have this. Now there you can search in the internet from Microsoft website you can install this Microsoft SQL Server 2018 version or 19 version or even 2022 version there are free versions available so you can install those. Okay, you can just search in the Google. So here we have these tables guys. Okay. So we have uh tables like you know uh uh tickets, events, sample supertore, customers like this customer employees. Okay. So let us just click on this uh event right click and uh let us select the top thousand rows. Okay. So you will see a query is uh written for us and uh data is extracted from the SQL server SQL table. Okay. So it's executing. So are you able to see this? So we have these six records and this is the uh query SQL query. Okay. So if you want let us take another uh table. So here uh we'll select top,000 rows. Okay. Okay. So this is the thing you can see all the column names. Okay. Different column names from this table. And these are the uh records. Okay, is this clear up to here guys? So we have the database. We have the SQL server. Within SQL server, so it is like this. Okay, this is the SQL server. It is physical server. Okay, within this you have different databases. Okay. Okay. Like this databases data science portfolio project. Okay. And within each database you have different tables. Are you all getting? Okay, different tables. So within each table you have data like this in the form of columns and rows. Is that clear all of you? Now we are going to access this data. We are going to access this data from the SQL uh so from the Excel. Okay. Now so uh let us go to the Excel here. Okay. Let us uh try to connect to database. Okay. from SQL server database. Okay. Now here we will have to specify the SQL server uh name here. Okay. So my SQL server name is this opening back slash SQL space. So uh providing this database is optional and let us just click okay. Okay. Now I'm using this current credentials, Just a moment. Yes. Are you able to see this guys? So this is the server. So it is successfully connected from Excel file to SQL server. Successful connection has been established. Okay. Now you can see the databases three databases. Is this clear up to here all of you? Okay. Now you can just expand the You can just sorry database. You can just expand the database and within this you will find different tables. Okay. You can just preview this data here. Are you able to see this row ID, order ID in the sample supertore data? Okay. And here you can either load or uh you can transform. Okay. So if you think the data is clean enough and you need not have to do any transformation, you can just click on load. Or if you want to transform the data, if you think that data is having some inconsistencies or some errors, some data quality issues, you can just click on transform data. Okay. So now let us just click on load here. So this entire data will be loaded into this particular Excel sheet, new sheet. Okay. Are you want to see this case? Now so guys u let us try to understand role of uh excel in data management okay in effective data management. So first of all it is used to analyze the data. So we have lot of options for data analysis. Okay. Like you know uh uh uh sorting, summarizing or pivot tables or and also we have the uh uh uh options like you know uh uh analytics various analytics in the uh data set in the Excel file. Okay. Yes. uh charts creating visualizations different types of visualizations like line charts, bar charts or you know pie charts like this histograms. So we can create lot of visualizations and transforming the data sets. So Excel plays effective role or important role in transforming the data sets. We have just seen the power query editor and its features how to transform the data and summarizing information in the form of piv tables or uh uh by grouping or you know by subtotals those kind of things can be done in Excel. Okay. So in these ways Excel provides uh uh plays key role in effective data management. Now let us look at some of the data cleaning functions. Okay. So data cleaning. So uh earlier we have discussed various data quality issues, right? So some of the data quality issues are like you know uh uh missing values. Okay. So many of you are working in banking and IT sectors. So you might have handled lot of data sets in your day-to-day working. Right? So you can name some of the data quality issues like missing values. Okay. But then uh you will be having duplicate values. And then you'll be having format related issues, inconsistent formats especially for dates and numbers. Okay. Then you will be having errors in the data sets and sometimes irrelevant data would be there. Okay. So these kind of issues you'll be facing day in and day out. Okay. And apart from this uh many times it becomes the case that you may not have the required uh uh fields for your analysis. Okay. You will have to derive it from the existing fields existing data. Suppose uh you have date of birth in your field. Okay. Suppose you are handling the HR data of a company employability data. So where you have the data birth of employee. So these are the various data cleaning functions. Okay. Now let us uh look at them. Okay. Sorting and filtering, grouping, ungrouping, text to column conversion, sub totals and removal duplicates. Okay. So let us dive into the Excel. So here guys, uh we have demo one file. So all of you please focus here. Okay. Please do not do it. Okay. Uh please focus. Okay. So uh sorting what is sorting guys? What is sorting? It is arranging. Arranging the things in either ascending order or descending order. Right? Ascending order means from smallest to highest. Okay. So in school days Yeah. In school days, how do we used to stand in the assembly line? The shorter person will stand in the front and a bit taller person and again taller person, taller person like this. Right? We will we will be standing in the assembly line like this. Right? As per height. Okay. So this is ascending order. Okay. Is this clear? Then uh descending order. Descending order is nothing but it is from highest to lowest. Okay. So when the exam marks are given so how the people will be ranked in the classic case of any school or college. So when the exam marks are given how it will be uh ranked highest to lowest right? So the person who has got the highest marks he will be put on the list first then it will be reducing marks decreasing decreasing. So this is called descending order. Okay. Is this clear everyone? So in sorting uh sorting we'll be doing like this. Okay. So here under the data ribbon here you can see all of you under the data ribbon we have the option called sort. Is this clear everyone? Okay. Now we will see how to apply this sort. So guys, whenever you are uh uh you want to sort, okay, sort the data, you you can select a single column or you can select entire uh you know data set. Okay. So it is recommended to select the entire data set. Okay. So let us say here we have the sales amount. Now we want to sort in the descending order. We want to find out which is the uh uh highest sales. Okay. Or who is the person uh I mean who is the salesperson who has done highest sales. Okay. So now what do we want to do? How do we go about it? So when we want to find out who is the person who has made highest sales. So we have to sort the data in the which order ascending or descending ordering order. Right? Very good J. So what we'll do is let us just select this column. Okay. So let let us select this sales column. Okay. Sales amount column and let us click on sort. Okay. So it will give you this warning. So uh do you want to expand the selection? So let us select expand. Okay. So when we expand the selection all the records will be selected. Okay. So just uh this radio button needs to be selected and click on start. Okay. Now here you have the option to add different levels. Okay. So all the columns which are available there will be which are available in the data set will be available under sort by and then you can select the order either ascending order or descending order. Okay. So all of you please focus here. So you can just choose uh sales amount here. And here we want to arrange it in descending order. Okay. You can select largest to smallest. Okay. So you can click okay here. Now are you able to see all the records have been rearranged. Okay. So you can see the top sales. Highest sales as 1500 which is done by E. Okay. Next highest is Kol 1400. Next L is 1300 and again L is 1200 like this. Okay. So this is descending order. So uh uh we are selecting we want to see the business cases we want to see say this time we want to see who has done the lowest sales. Okay. So we want to find out that person salesperson who has done lowest sales and we want to uh give him proper training or more training so that he can do better sales. Okay. So uh we have selected the sales amount column and click on this sort option. Then you expand the selection to the entire table. Now you can see here. So here let us select the sales table. Okay. Column by which column you want to sort the data. Now we will select smallest to largest. So this is in the ascending order. Okay. So let us click okay. So you can see here Bob has made the lowest sales of 100. Okay. Next is Frank like So this is in the ascending order. We can also apply multiple multiple levels of sorting. So you can just click on this sort again. Okay. So now we have applied the uh sales amount. So we want to select by category. Okay. So category A to Z. Okay. And sales amount let us say largest to smallest. And let us click okay. So you can see here. So category first we have done sales amount highest to lowest. Okay. And then category. So within electronics you can see these are the sales. Okay. And then home goods and then electronics here. Okay. So next is the filter function. Okay. So filter function. What is the purpose of filter function guys? So suppose you have thousand records in a data set which belongs to say different countries say US and say UK and say uh India. Okay like this. Now you want to work on only data related to India. Just imagine okay now so we can just select the country as India and you can just work on that particular data set. Okay. So filters what they will do is they will restrict the data to whatever selection we have made. Okay. They will limit or restrict the data based on our selection. Is that clear everyone? Okay. Now how do we apply the filters? So uh by default we will be in the home tab. So you will have to come to the data tab here. Data ribbon. Okay. So are you able to see the filter here all of you? Okay. Just select any of the field and click on filter. Now you can see filter icon is available or applied to all the fields all the columns. Okay. Are you able to see this drop-own menus are applied to all the columns. Okay. Now you can apply the filters. Okay. So you can just see here suppose in this region you want to see the data related to only Europe. Okay. You can just unselect all the uh values here. You can select only Europe. Okay. And you can click okay. Are you able to see here only Europe related records are showing here. And within this if you want to apply another category, another filter. Okay. Suppose you want to see only data related to electronics. Okay. So then you can select the category and then you can see you can select electronics. So here we have applied multiple filters more than one filter. First we applied region filter for selecting data related to Europe and then we have selected electronics category. Okay. Now this is how you can apply multiple filters to see the records to see the data which you are interested in. Is that clear everyone? Okay. So you have different types of filters guys here. So based on the data type you have different filters okay like a text data type or date data type or numeric data type okay or true or false like a boolean data type based on that those things you have different uh uh filters okay so here for the sales amount so you can see number filters okay you can you can either uh use this checkbox selections okay or you can use number filters equal to or does not equal to greater than or greater than or equal to less than or equal to or in between these values. Suppose here you want to find out all the sales which are greater than 1,000. Okay. So uh you could just select the sales amount go to the number filter and here you can just use greater than. Okay. So you can provide greater than 1,000. You can also provide multiple arguments here but let us just do with one and click okay. So are you able to see here we are now seeing the data related to I mean uh data which is having more than 1,000 sales amount more than thousand. So like this you can apply uh numerical filters. Okay, you can clear the filter. Now on the dates also you can apply filters. Okay. So you can just pull on this drop-own menu and go to the date filter. You can see there are lot of things. Okay. Equal to before, after, between, tomorrow, today, last year, this year, next year. There are so many things are there. Okay. You can use any one of these things. So let us just select something like you know between between two dates. Okay. If you want to see the sales between two given dates. Okay. So you can just provide these things. Okay. Say we want to see the data related to say 1st January nothing say let us say between 31st January 2023 to uh say sorry it's all in January okay let us say 1st January say 15th January okay 2023 so let us click okay now are you able to See here guys, we have only two records in between these two given dates. Okay. So this is a date filter. Are you able to understand the filters of different types and uh we have already seen text filters. Okay. So text filters also you can see you can use this equal to, does not equal to or begins with, ends with or contains, does not contain or any other. Okay. other than this checkboxes. Shall we move to the next topic? So this is about the filters guys. Okay. So let us go to the grouping and ungrouping. Okay. So grouping and ungrouping. Uh see when we have huge data set. Okay. Uh let me just show you one data set here. when we have huge data sets and uh we want to know group based on certain categories. Okay. So we want to hide them. We do not want to show everything to the people and uh so in such scenarios we can use group okay grouping of the data. So this data set has 9,994 records. Okay. So what we will do is here guys let us just go to the homepage here. I mean home So here we have something called region. So you can just see here we have four different regions central, east, south and west. So what we will do is let us first you know sort the data based on the uh uh regions. Okay. So let us select uh this uh region. So before you apply any grouping guys, you will have to sort the data appropriately. Okay. Based on whatever column you are grouping. So based on that you have to sort the data first. So now uh so let us talk about the duplicates. So in this particular record we in particular data set we have row number 15 and 16 repeating. Okay that could be one uh duplicate record. Okay. So when we are looking at data set say uh we have this data set. Okay this data set let us just have a glance at this data set. Okay. So these are the values. We have the customer name, email and then comments. Okay. So when we want to clean from the you know data analyst point of view when we want to identify the duplicate records. So how do we go about it? Shall we select only one column say customer or email id or comments or how do we go about it? So how do we uh judge suppose can we just select this particular column and identify the duplicates and remove it. So here are you able to see remove duplicates? Let me just highlight it here. Are you able to see under the data button under data tools? So in the middle you have remove duplicates. Okay. So you can just select that and remove the duplicates. Okay. So you can just click on this. So here you have the option to select the columns in which which combination of columns you want to define as duplicate records. So you can select say only customer column or uh only customer name column or you want to multiple columns okay or all columns okay it's based on your business requirement based on that you can select but ideal thing is you need to select all the columns. So whenever you want to clean the data sets for duplicate records you have to select all the columns. Okay. So then you can click on okay here and here you need to select uh this my data has headers because you know we have the column names here. Okay. So you can just click on okay here. Now are you able to see here? So this data set this data set has got 40 duplicate uh records and uh they were found and removed. only 10 unique values are retained here. Okay, is that clear everyone? Okay, just imagine. So in a business situation, in a company situation, suppose you have a scheme in the in in a business, you have in your company say you have a provision to know reimburse the travel expenses for your employees. Okay, just imagine that. So when you are uh reimbursing that uh that travel expenses suppose by mistake it has been you know uh uh uh entered multiple times more than once that is maybe three times the employee has submitted the uh reimbursement uh uh data for the same travel. Okay. Now how do you uh uh from the business point of view how do you handle this? what impact does it have on the business? So if you do not clean the data, yeah, it will financial impact. If you do not clean the data, if you just, you know, uh uh process it in the same way as it is uh submitted to you, then company will reimburse thrice, right? So instead of once, it will reimburse thrice. So these kind of things happen might happen in in your payments especially you know uh payments to the uh suppliers or payments to service providers or employees or many many other situations. Okay. So let's now go to the next uh topic that is uh handling the text fun I mean uh text functions for cleaning the data. Okay. So we have got a lot of text functions like uh let me just show you some of them. So these are some of the you know uh text functions for cleaning the data. Okay. Trim function, upper, lower, proper function, substitution, clean function. Okay. So these are some of the uh functions which will help us in removing the anomalies. Okay. So here we have this comments column. So what we will do is we will just apply trim function. Okay. Here uh trim comments. Okay. So here you can just type the formula. So equal to so you can use the trim function and then you can provide the text. Okay. So this is the text and you can close it and press enter. So are you able to see here? So I'm just zooming it. So here after all good and comma there are you know multiple spaces two spaces are there. Okay. And in the trim comments we have only one space. So in between the one space has been removed. Now so if you want to apply this logic this formula to all the things what do we do? So it is called fill handle. So some of you might be aware or some of you might not be aware how to you know enable this fill handle. Are you able to see at the corner right corner here at the cell? So you can just focus here. Okay. So there is a fill handle. So in order to enable the field handle so go to the file here and options. So in the options uh you can select advanced options and are able to see this under advanced options enable fill handle and sele all you. So you need to just check this and click okay here. Okay. So for some of you if it is not enabled. So guys now uh let us just apply this uh uh cell drag. Okay. So let me just double click here. So you could see here. So all the extra spaces have been removed from this comments column. Okay. Okay. So next what we'll do is we'll look at upper lower and you know proper. So uh so upper what it will do? So it will provide all the text into upper cases right very good. Okay. So here you can use upper upper function and you can provide the a this cell reference. Okay. And close the parenthesis and press enter. Okay. So you could see everything is converted into upper case and you can just double click here. Okay. So all the comments are uh uh translated or not transferred into or…

Transcript truncated. Watch the full video for the complete content.