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

Today businesses do not just rely on guesswork. They rely on data to make better decisions, improve processes and solve real problems. And that is where business analytics becomes so important. Business analytics helps companies understand data, find patterns, solve business challenges, and make better decisions. From companies to startups, professionals with business analytics skills are in high demand because they help turn data into actions. Welcome to the business analytics course. In this course, you will learn how business analytics work and how tools like Excel, PowerBI, SQL, and AI can help you make better business decisions. Whether you are just starting out or want to improve your skills, this course will give you practical knowledge that you can use in real world projects. First, we will understand the basics of business analytics and why it is important in today's business world. Then we will explore the different types of analytics and see how businesses use data to solve problems and create value. Next, we will learn how to work with data in Excel, including cleaning data, formatting it, and preparing it for the analysis. We will also cover useful Excel features like formulas, functions, lookups, private tables, and dashboards that will help you turn raw data into meaningful insights. As we move forward, you will also learn how AI tools can support your work, speed up your analysis, and make reporting and decision making much more smarter. Throughout the course you will learn on practical examples, guided activities and real world use cases so that you can understand how business analytics is actually used in the companies. So if you want to build a strong career in this field, this course will help you develop the right foundation and confidence. So if you're ready to learn how to combine analytics with the power of AI, let's get you started. If you're interested in boosting your career in business analysis, do not forget to check out our powered business analyst course. This course is perfect for professionals looking to enhance their skills with the latest tools like PowerBI, Excel, and SQL all while gaining hands-on experience with the real world projects. You will also learn how to leverage generative AI for smarter, faster decision making. Our program is IABA, BEBA, V3 aligned and help you prepare for certifications like CBAP and CCBA. You will also engage with 10 plus industry projects, 40 plus practical activities and benefit from live online sessions led by experts. Plus, with Simply Learns job assist, you will get the support you need to land your next big road. Hurry up and enroll now. The course link is mentioned below. Here's a quick quiz before we begin. Which of the following is the main goal of business analytics? Your options are option A to design websites, option B to use data for better business decisions, option C to write software code or the option D to manage employee salaries. Excel with AI. Right? And I want to start with this question for for all of you. The thing is Excel is such [snorts] an old tool. 1985 when Microsoft introduced Excel. Okay. And shall I tell you some fun fact? You will not believe. Excel was first introduced for Mac. Did you know this? Microsoft introduced Excel for Mac and not Windows. Right? So that's a fun fact you can say since since we started since I started with some fun fact I'll also in add some more interesting facts that would really leave you uh I mean uh that would make make this session more interesting right so introduce for Mac first now since many of you will be working on excel do you know and that's why I mean the beauty about this tool the beauty of this tool is Many of us work on this tool day in day out. It is the first tool that is expected to be and and you are expected to know this tool when you land up in a corporate job, right? You are expected that you would know Excel or if you don't know there would be just quick training so that you can just get up to speed. But just give me a second. This is the first tool that you would be expected to work on. Not even Ppts, not even Word doc. But this tool you cannot escape if you really want to work. Okay. So, next thing is since many of you know and you you are working you have been working on Excel. Do you know you can work very efficiently and very fast in Excel with shortcuts, right? Do you know how many shortcuts are there in Excel? Do you know how many shortcuts are there in Excel? Close to 500. 500 plus shortcuts are there. It means if you start learning one shortcut in a day, probably it will take you one and a half years just to learn the shortcuts. So I mean and despite of working decades and decades on Excel, nobody I mean it is not expected. It is humanly not possible that you learn you you are able to have a good grip on all the shortcuts. you know the shortcuts which you use very often which you you which you work and everyone bases their work they they they develop a I mean a grip on some shortcuts 20 25 30 40 50 maybe right but 50 shortcuts if you know 50 shortcuts I can assure you you can work in Excel with a speed of at least 5x without if you if you don't know Excel and if you know Excel not 5x but definitely 3 to 4x right that's the power of shortcuts right so our right from the word go my focus is going to be on it that we should there could be some shortcuts in this training that even I don't know but we are going to I'm going to definitely help you learn those shortcuts I mean I know a lot of shortcuts but we are going to try and learn as many shortcuts as possible and learning the Shortcuts means not touching the mouse. If you what what's the what's the fun of learning the shortcuts? Not touching the mouse. Because if you touch the mouse, you are wasting milliseconds. Then you're picking the mouse, then you're scrolling and then you are maybe performing or some action or right. So learning the shortcut means saving on time. Hence the shortcut, right? Not touching the mouse. And you know this is one of the benchmark this is one of the parameters if you really want to check whether how good are you with Excel you can easily check for yourself the more you are not touching the mouse the more you're working with shortcuts means the more good are you with another interesting thing uh another very interesting thing and I would show you rather you know that by I can name I mean by change I can change the name of the sheet. Yes, all of you know we can change the name of the sheet like right sheet. Okay. Uh Excel with AI right try naming it as history. Try naming it as history. It's a reserve name. You will not be able to save it. able to save a name history to a sheet. Isn't that amazing? Did you know about it? But yeah though I mean it's a very quick I mean it's a very t I mean very short thing but yeah interesting that you cannot name any sheet history. Okay. Next thing this you can you can easily calculate but do you know a default Excel sheet has how many rows? See 1 million 48,576 rows. Okay. So this you can easily know rows 1 million 48,576 rows. Okay. You know how many columns are there? 16,384. How did I do? See, got it. Count. Can you see at the bottom? Count sum 16,384 columns. So now the big question, how many cells are there? Cells is an intersection of a row and column. You can multiply them. it will come out to be some 17 billion columns. 17 billion cells. Okay. 17 billion cells are there. So if you just start filling one cell in 1 second, it will take you 545 years to fill a Excel sheet. Astonishing. 545 years to fill this Excel sheet with if you fill one cell per second. It is the one of the most Excel in terms of popularity. It is the most popular most used and most popular tool in the world. I can say it safely. Roughly 16% of the world's population have used Excel sometime or the other. Another interesting fact okay. Okay. Another thing another interesting thing we know that in Excel if I I write right now I just wrote something I can undo it. Can you see I'm undoing it. How do you undo any any what is the shortcut for shortcut for undoing? Ctrl Z. Very good. Those who work, they know, right? Those who don't work, we will definitely learn this. Ctrl Z will undo your action. If you have not saved any previous action, you can undo 100 previous actions. Okay. Another interesting fact, you can undo 100 previous actions if you do not save anything in between. Now I have written something and I do a control S. Now it's gone. Now I can't undo. See? Oh, it still does. But yeah, there would be a limit. Yeah. Yeah, it is doing. Yeah. So even pressing Ctrl S, you can undo. Okay. I see. I also keep forgetting. So fine. I always thought that once you save it then you cannot undo. But yeah fine every day you learn right? Okay fine. Next thing we we all know uh it's a it's a software. So any idea how many lines of code were written for Excel and when it gets upgraded I mean it was written it was released in 85. Since then there have been upgrades and upgrades on Excel. The latest version of Excel has more than 30 lines of code. Okay, more than 30 million lines of code. And this has increased with the latest release of AI features this off late. Okay. So, of late there have been a string of releases. They have been immensely as we all know that the focus I mean AI is taking the world like a storm. We all know right? So, Excel is not leaving Microsoft is not leaving the stone unturned otherwise Excel would have been uh destroyed or it would have become outdated by now. But still Excel holds its place. Still, Excel is the first go-to software that you are expected to learn when you go to any organization or you work right. It's because Excel Microsoft is leaving no stone unturned to make this Excel as upskilled. I mean we also upskill. So, so is Excel upskilling. Microsoft is upskilling Excel. It is imparting it is I mean imparting new and new features. Lots and lots of features have been recently added especially with regards to AI. Lot of AI based features. We are going to discuss those in this training. Right? So lot of AI based features have been introduced in Excel 365. I I am hoping that uh all of you should have 3 office 365 I mean Excel 365 because the the AI based features are in office 365 primarily right so we will see what those features are in the coming uh sessions okay so that's pretty much I think yeah couple of more yeah one more interesting fact that I can recall is uh you know we we can write dates also in Excel right January 23rd January 2024 okay and this is a date right we all know that you can write dates right so in Excel do you know the range of dates the earliest date that you can write anyone It's not very far behind. I mean, it's not too far in the history. It's correct. One one 1900. This is the This is See, it transformed into a date format. But if I write 1 1899, see it did not transform into a date. Okay? It did not transform into a date. So the earliest date that you can write and that Excel can accept is 1 1900 and the latest date any [clears throat] idea 31st December 9,999. Okay. So that gives you an idea of the dates Excel can accept, right? The timeline that you can forecast. Okay, great. So that's this this was about the fun facts, right? And I just wanted to start with the fun fact. So so I mean it should really that is kind of a hook, right? It should I want all of you to be attentive in this training. Okay. So I hope you like this session and so what we'll do is now this particular part of the session not the session the session is still there right this particular part I hope you liked it you like the interesting facts okay now what we'll do is see in excel what we have to do is we we are going to deal with data right we are going to deal with data so I want you to I want to discuss couple of terminologies Right? Some terminologies related to data because it is the data that we are going to deal with in Excel. Right? And your expectation in Excel should be that you should be able to work with data as quick as possible. Right? Because Excel there are okay having said having all said all these good words about Excel still there are limitations with Excel. It's not that Excel is like the best tool. There are limitations with Excel. But for a newcomer, for a person who is doing daily work with uh small data sets, medium data sets, right? Excel is a wonderful tool. They have upgraded a lot. You can perform any statistical analysis, right? Which we are going to see, right? and statistical analysis in Excel. Create visualizations, right? Do any type of number crunching, create reports, very interesting reporting you can do and in a very powerful way you can create even dashboards also on Excel, right? And though dashboard creating dashboard is like getting a bit outdated because now you have specific dashboard tools right and we will also see a tool in Excel which is called power query editor is a very powerful part of Excel mostly we don't use it in Excel even if you know Excel we don't use power query editor in Excel we because power query editor uh I don't know somehow Microsoft did not market it much uh with Excel but power query editor is a indispensable tool indispensable part of PowerBI so if you learn PowerBI so we will see Power Query editor also in our training okay so yeah mostly that's what I'm saying but yeah if you are working in Excel you can still work you can still learn power query editor and we'll see we'll see it how it works in Excel. Okay. So, as I said, most of us would be working on Excel. Uh, and when we work on Excel, it's all about data, right? Correct. It's all about data. So, what we'll do is we'll I'm going to share this PPT with all of you. Right? Let's quickly glance through some concepts related to data. We'll not spend much time. Okay? We'll glance through some concepts but we'll just spend some time and understand the basic concepts of data. Okay? Because Excel is in itself it's a business intelligence tool. Notice the first word it's the business intelligence tool, data analytics tool and data analysis can be done on Excel. So, so it's I mean logically also it makes sense that we understand all these terms and now with AI being integrated and if you see I'll just show you a couple of things that Excel has recently done right they have integrated Python they have integrated Python into the wait I'll just show Can you see they have integrated Python okay then they have come up with extremely powerful forecasting techniques see I'll show you forecasting forecast we'll see this also right how can you forecast some very powerful features of data analysis and analyze data and they have integrated co-pilot also. I'm sure all of you know copilot right? Okay. So they have similar to charge GPT. Yes, it's a yeah it's a AI tool by Microsoft. it the at the back end charge GPT is I mean it works on charge GPT only but yeah Microsoft uh got this tool for their own products right and when they invested like 10 billion I think now they've invested much more in u openai so I think that was a deal that they would uh come up with copilot and at the back end uh it's uh chart GP only okay fine so we will yeah coming back that's why since AI the focus of this training is also excel with AI we are going to learn some terms some understanding of what is AI what are the different terms of AI right what are the different terminologies in the world of data and AI okay so let's start with BI business intelligence it involves the use of tools and techniques to transform raw data into actions. Uh I should have actually asked you but nevertheless I I just slipped. So what do you think about what is BI? It is the use of tools and techniques to transform the raw data into actionable insights for strategic business decisions. But the very important thing about BI I'm sure all of you would have heard of of this term but the very powerful very important thing about BI BI is the use of computing technologies okay it's the use of computing technologies okay so BI business intelligence is the use of as I said computing technologies very important tools and techniques means computing technologies. Okay. Now, what is the benefit of BI? It focuses on I mean it helps you take strategic business decision, right? Focuses on monitoring and optimizing business processes with the help of data warehousing is a BI technique. Data quering is a BI technique. data analysis, reporting, dashboard, statistical models, machine learning, deep learning, all this is the bigger bigger domain bigger you can say the universe of all I mean the entire machine learning data science data analytics is a part of BI is that clear so BI is a umbrella you can say it's umbrella term okay next I'm sure all of you would have heard of data analys analytics and data science. What is the main difference between data science and data analytics? So data analytics is all about analyzing historical data and data science is all about future predictions. Basis. Okay. Important thing is basis. Basis historical data you predict future right predictions and forecasting. Okay. Basis historical data you predict and forecast. And when you predict and forecast you apply I mean you work with ML DL is deep learning neural networks and these days NLP geni all this is data science clear these are very high level models these are I mean very sophisticated machine learning models and when I say machine learning models I mean stat I mean you can take it as statistical models only Okay, clear. The main difference is clear to all of you, right? So now since the difference is clear, let me quickly take you through some details. Data analytics is analyzing data to drive meaningful insights and support business decision-m. So since we are starting we are starting to understand data analytics and data science, right? open this data set and we will parallely learn looking at the data we will learn couple of concepts. Okay. So this is a data set if you ask me uh yeah right so this is a data set for 4 years right four years this data set has how many transactions? 9994 transactions. Can you see right? This is row ID. 9994 transactions are there across 21 variables. Do you think this is I mean people those who are new to data do you think this is a big data? Do you think this is a huge data set? Because in the world of big data this is a small data set. Actually it's a small data set. This data is easily handled by Excel. This data can be easily handled by Excel. So any data set that can be easily handled by Excel it's a small data set in a big data world. Okay. So this is not a very big data but still it if humanly humanly it is not possible for us to just glance through and get an understanding of this data right. So I have given you this data set. So it would require some capabilities of Excel to to understand this data. Okay. So we will see. So 10,000 rows I mean 9994 rows across 21 variables. This data set has 21 variables. And this is a dummy data set for e-com for a e-commerce player. for a e-commerce player who has been selling something like Amazon who has been selling uh products across 17 subcategories and three categories and across four years right so across four years this data set so now tell me if I ask you to analyze this data if I ask you to analyze this data see how many years 4 years 14 15 16 17 Okay, if I ask you to analyze this data, analyze sample supertore, let me call it SS. Okay. So, will you be performing data analytics or data science? Data analytics. Why? Because it's a historical data. These are facts. So, data analytics deal with facts. Okay? Now if I ask you basis this data four years try to forecast the sales for next 3 years whatever model you want to apply then I am in a domain of data science. Okay, clear. Forecast revenues or sales, right? So that is data science. Clear? So now that we have touched upon a very interesting concept of data analytics and data science and you are clear about it also, let me read through just some very important parts of data analytics. Right? Data analytics is about analyzing data sets to derive meaningful insights that support business decision making. Have you heard of the term datadriven decision? Datadriven decision. Have you heard of the term datadriven decision? Yes, all of you. Very good. Datadriven decisions means decisions taken in companies or otherwise after analyzing data you know and this has recently not recently I would say now it's been more than a decade right it's been a decade or so that now companies take datadriven decision right and what is the impact of datadriven decision and can anyone tell The decisions are more scientific, Less biased because they are supported lesser biased. All of you agree with me? Anyone who does not agree? More scientific, lesser biased, fact-based decisions. Right? Very good. Fact based decisions any any further any any any more benefit any more benefit of datadriven decision think of scientific fact-based less bias can planning can be done better right deeper analysis is possible you can get more and more data you can I mean yes very good this is a very good point deeper analysis right I also agree with it. I was just struggling with the word. Yes, more accurate. Very good. Planning can be done. Deeper analysis means I can keep on accumulating more and more data. I mean now our systems are so powerful. They can keep on accumulating more and more data and our systems are so powerful that they can use it. Right? So deeper analysis, more accurate. Very good. More accur. See, I I had put put you on the spot and you're responding so well, right? Very good. More accurate. Very good. Can be confident to invest more in specific fields. That's a Yeah, you're so that's like more accurate only, isn't it? Based on prediction, decisions cannot be made. I mean, you're right. You're right. But not all decisions can be made made uh based on prediction. No, right. it I mean if I have to analyze historical data and I say okay I want to discontinue some product that's not a data uh that's not a decision based on his uh prediction okay mill okay when datadriven decision are taken these days right how were the decisions how were mature decisions or better decisions taken before datadriven decisions Very good. Experience and gut, right? Gut and experience. Gut base. All of you agree. So what so what is data doing? Data is effectively data is replacing experience and gut. And that is why we say that these days people those who are more experienced they are more on the I mean receiving side right because their experience is not does not matter much these days and a decision that was earlier taken by a 20-year-old experienced guy 20 year experienced guy today can be very easily taken by a person who is probably 8 or 10 years old. Do you agree with me? Just think for a minute and just think on this what I just mentioned. Just think and just look at all around you what is happening. Right? Experience is actually now a liability for most organizations barring very few leaders. barring very few leaders and that two AI is I mean after them okay so that's that's the impact that's the way it in fact it has data has completely changed the way we work earlier the way it changed the way we take decisions and now with AI it has okay that has been the impact of data Okay, now that we have uh data analytics, no uh data analysis and business analysis, right? Is when you are actually analyzing data, data analyst people, they analyze data. Business analysis data. Business analysis analyst people they would also analyze some non-numeric uh data also right non-numeric like business functions business processes right systems correct right so they would analyze nondata base nondata related you I can say what can you say non-data related uh what should I say non-data related data or no non-data related uh what can you say uh information no non-data related yeah components yeah you can say they can they make decisions or yeah nondatar components Or non-data related I'm not getting the word but I think yeah strategic decisions. Yes. Non data related decisions also you can say right they are involved in non-datal related decisions and these data analysts they are they completely rely on data. So these days [clears throat and cough] data is data is not working with data is not just an option it's mandatory data analytics in itself data analytics and data science right it is a separate department also right But this data science and data analytics is ingrained. It is in it has been infused in every other department like HR, marketing, finance. Okay, you agree right? So data is I mean and to the extent I'm sure all of you would have heard of data is the new oil for business. We say that right data is the new oil for business. It's a old now I mean it's a decade old like 15 years old but still holds very true. Okay. Now let's now move to something really interesting. Okay. Yeah. Data science. Okay. Yeah. Typically uh data analytics some some things some uh activities that constitutes some actions that constitute data analytics. Descriptive analytics. Descriptive anal analytics analysis means when you're describing a activity or a act or some some happening in the past business transaction, right? Launching a new product, scrapping a new product, marketing efforts, right? Adding more employees. All these are descriptive analytics because they happened in the past. Exploratory data analysis EDN will go to the Excel. See if I give you this data, right? I give you this data. It's a new data for most of you, right? So I ask you to do descriptive analysis. Do a descriptive analysis. You know what is descriptive analysis? Analyzing this data. Right? I just give you this big word this I mean all of a sudden I say okay do a descriptive analysis of this data of sample supertore right what will you do if I give you this data what will you do descriptive you have to describe you have to describe this data set what will you do can you tell me anyone wants to contribute I would be more than happy to hear because I have shared this data take a minute spend a minute on it and tell me if I ask you to do descriptive analysis in next just I give you only 10 minutes I mean it's not that I'm giving you 10 minutes for this answer I tell you I give you 10 minutes give me a descriptive analysis of this data what will you do I think most of those who are in India they would know called uh Amir Khan's movie um three idiots Yes. Yes. Do you remember? Yes. And I am sure now you would have got that what I'm going to say. Many of you I just asked you a question and you started responding. Isn't it? Did you try to understand the data? I gave you a glimpse. Did you try to understand the data? Okay. How many transactions? What are the variables? Do I know the variables? Do you know what is ship mode? Do you know what is a segment? Do you know? Okay, city, state, postal code, you would know. Do you know what are the subcategories? What is the discount? Do we have this cost? You did not try to you did not try to get familiarized with the data. That is the first thing that you would do whenever you have a data. Isn't it? Do you think don't you think that should be done? If you do not get familiarized with the data whatever analysis you do in haste or you start doing it in like minutes or it is not going to be correct. Exploring data don't you think that is important? Understanding data getting familiarized with the data. Let me write these words. Very important, right? Explore. Exploring data. Familiarizing with data. What else did I There there's another understanding the data, isn't it? exploring data familiarizing with the data how many rows how many transaction are there any NAS don't you think we should know if there are some NA values if there are some NA values in sales how will you able to get this all these things that you were telling me I need to know if this data is clean or are there some NAS in this data don't you think yes So getting familiarized with the data, exploring data, understanding data is called exploratory data analysis or it's called EDA. Is that clear? What is EDA? Now, now what will you do? What? What? And also yeah one more thing to get to get familiarized with the data understand the data dictionary. See data dictionary is a central repository of information about data such as the meaning of the variables relationship to other data original origin use sorry origin usage format like like I don't know sales is in millions or it's in dollars is it in dollars or rupees or is it in some other currency I don't know isn't it. This all will be there in data dictionary. All these variables are defined in detail in data dictionary like ship mode. What is ship mode? How will I know unless and until somebody will tell me do you agree with me? So data dictionary understanding data dictionary is also you can say it's part of EDA only explore you are exploring data right. What else can we do to explore data like I'll tell you the easiest way right if you have a data it okay quickly easiest way just alt down button just know okay four ship modes fine so many customer ID I don't I mean I will not remember so many customer name I will not remember three customer segments three segments consumer corporate home should don't you think I to know what these three segments cater to. So different sheets have different data dictionaries. Different data set have different dictionaries. Data dictionaries not sheet. This is a data. See this is a single data returns. It also has return it has and this is incomplete right. So this is they have given a flavor of okay you don't get the data always like complete data. So this is like a people uh sheet which is incomplete. Okay, returns is still complete. It gives you a complete returns uh information about the returns. Okay, but our main main table is this first sheet orders. So, we should have a data dictionary. Okay, then yeah, I was talking about the exploratory. How do I explore? Okay, three segments only one country. Isn't that interesting? I I never knew it. You I mean you you never knew it but you started calculating it is only one country. So can I guess the now can I guess the currency? Of course. See as I start doing a EDA I start getting deeper and not deeper but start getting a hold on the data start to understand the data. Do you think just getting to understand the country I could easily get okay what was the currency before that I was not able to understand that okay then state okay city okay too many I don't want to spend much time so what I'm doing is this is EDA exploring I'm exploring data so I become more familiarized with the data I understand the data right I start understanding the data how many regions four east central Central, Southwest, West, there is no [clears throat] north. Okay. Fine. Right. How many categories? Three. How many subcategories? 17. Starting from accessories, appliances, arts, tables. Okay. Then comes some more things. Product name. I know there would be too many. But if I want to know how many, if I want to know how many products were sold in these four years, how will I know? If I want to know how many products were sold for, how many different products were sold? Quarter. Shift down arrow. So what will it do? Shift down arrow. Shift down arrow will Okay, I'll I'll select then count this thing. Yes, that is what I wanted to hear. But how will you come to know the distinct products? There is a feature in Excel pivot or there's a feature in Excel which helps you count the distinct values. Okay? Right? So that way I will get to know the distinct products. Okay? So if you see if you want I can quickly tell you see how will I do it. I'll just copy paste it here. And I will go to data this one this feature remove duplicates. Let's see this many unique values remain. So how many products? 1850 products. Got it? What am I doing? Am I analyzing Am I am I analyzing the data? No. I'm doing exploratory data analysis. Clear? Is that clear? Make sense to all of you? Is it making sense to all of you? And all of you agree with me? Now let's look at quantitative variables. Now quantitative variables. Let's look at quantitative variables. How do I do EDA for them? Anyone? Anyone wants to now attempt? I am not saying how do I do a EDA for quantitative variables. Yes. Anyone? Okay. One way is I sort this data set smallest to largest. See by sales. Notice this arrow comes up, right? We will be learning all this but I'm just showing you sales 4444 is the smallest sale 22,638 is the highest sales value. Similarly I can do it for quantity smallest to largest 1 to 14 discount smallest to largest 0 to.8 8 then profit - 6 5992 8399 gives you a lot of insight about the data. Does this does this give you a lot of insight about the data? Right. What else can I do? What else can I do? See, sum gives me total sales. Average sales value would be very interesting to know. Let me show you average sales value. You would not believe. I mean, let me first do a smallest to largest. See, largest sales value is 22,638. Let's do average and see. It will be astonishing. Can anyone guess without calculating what should be the average sales value? 229. Average 229. Similarly, average profit 28. What is the average profitability? 12%. This is what is exploratory data analysis. Making sense to all of you? Yes, I am just doing I'm just trying to analyze understand the data. Okay. I can dig deeper also like understand the sum of sales with respect to each category subcategory that is also exploratory data analysis okay but I'm getting deeper with it I mean so okay sum average then standard deviation variance do you know what is standard deviation variance minimum maximum then count then skewess I don't know if you know skewess or not skewess then all this is exploratory data analysis okay and in excel see there are so many formulas you'll have to learn no not required now in Excel there is this tool called data analysis can you see okay I I'll just show we will come to this but I just want to show you in this click on it you will see something called descriptive statistics this gives you everything all that you want in a single go. Okay, we will see this right now. Let's not do it. Right, it's very easy. Don't worry, Rupali. This is the easiest tool that you would work with. Okay, if you are in the field of data, this is the easiest tool you will work with. But yes, having said that, it does not mean that you will not practice it that you will just listen to my session and you will just I mean become an expert. No, no, no, no, no. The more you work on Excel, the more you become proficient and the lesser you start working with some functions, you will forget it. I mean it happens. You are you are on a journey when you're working with Excel. Perennially, you are working with Excel. But you keep on I keep forgetting some functions. I keep learning new functions. That happens because some functions I started to work with new data and some new functions and I stopped working with some functions. So I I stopped working I forgot those after a month, two month, 3 months. Okay. So it's a journey when you work with Excel. Okay. Okay. So I think pretty much the first part of the training. Uh now let's quickly understand some more terms, right? And then this evolution of data is very interesting, right? So data analysis. Yeah, there is another term I I discussed data analysis and business analysis, right? But we'll also discuss what is the difference between data analysis and data analytics. Okay? And when you're working with Excel, this thin line you should know data analysis and data analytics. We'll discuss this quickly. We were in EDA. I showed you what is EDA. Data cleaning and transformation. Data cleaning is when you see any data when you get any data that data may not be fit for your analysis. It has to be clean. In fact, most of the time it is not fit for your analysis that you want to do. You have to perform transformations, data cleaning and this part EDA data cleaning and data transformation even today it occupies even after AI is being integrated. So well into this data value chain even today this EDA exploratory data sorry EDA data cleaning and transformation occupies the maximum time in terms of the data value chain approximately 50% of your time that you spend on your data you spend on these two things is that clear then you do data analysis, visualizations and lastly if you are uh if you want to add models I mean if you want to create models then you work on models okay but this still in terms of time 50% still earlier it used to be 60% but with AI things have become really uh efficient right so 50% is still you spend time on these activities Okay. And I'm talking about big data not small data sets. Okay. I'm talking about big data set. You have to spend lot of time on these. Then comes ETL. ETL means extract, transform, load means how do you extract the data set from uh databases, transform them and then how do you load them into the data model or data visualization tool. Okay. All this is part of data analytics and data science. I've already discussed in brief multiddisciplinary field. Yeah, for data science it is a multidisciplinary field. What do you mean by multiddisiplinary field? It involves you to have a good understanding about statistics and mathematics, programming skills and domain expertise. Okay, domain expertise is business like uh domain knowledge means if you are from insurance industry, you should have insurance knowledge. If you're from e-commerce industry, you should have understanding of e-commerce. That is called domain knowledge. Is that clear? And the intersection of all these three you can see is data science. Clear? And data science involves predictive models, big data processing, predictive forecasting models, all this. Okay. Now coming to data analysis and data analytics. These two terms are used interchangeably and many people they don't understand this the difference. So very very very thin line of difference but yeah [clears throat] after this discussion you would know it and you should know it because being a data professional you should have a good understanding of these terms. Okay. So data analysis is typically fo focuses on examining and interpreting existing data to draw conclusions and answer specific questions. specific questions, very small questions, right? Specific, you examine data. Like example, if I say, okay, I give you this data and I say, give me the total sales or give me the category wise sales. It's a very specific question. I'm asking you a very transactional question. Give me the category wise sales. Very easy. What will you do? So if I ask you okay give me the total sales or give me the sales as per categories. So it's a very specific question very I mean this is data analysis but if I ask you okay help me understand the sales pattern or the sales trend for these three subcate these categories. help me understand the sales trend over the period of time for these three sales categories, right? encompassing the entire process of collecting, processing, analyzing, de deriving insights from the data and yeah also let me add help me understand the sales trend and the growth in sales growth y growth rate keer for the sales for these three categories on for the years or help me understand the quarterly quarter-wise sales trend and the keer keer is compounded annual growth rate. Now that's a very that's not a that's not data analysis, it's data analytics, right? Why I am trying to take a decision that helps you to in take informed decision but this was a ad hoc. Okay, give me sales ad hoc question. It it was just a transactional view. Are you getting the difference? First let me hear it from all of you. So it is more of a this is more of transactional this is more of at a very bigger level I mean end to end okay data analysis is typically used to answer standalone specific questions data analytics aims to cover patterns [snorts] relationship and insights so I can add some more analysis okay help me understand the sales trend for these three categories, right? Quarterly sales trend also help me know the growth rate of profit for the same period right or sorry sorry not profit profitability profitability is profit ratio okay so I'm understand that here I'm trying to make some decision but in that ad hoc I don't know what I'm trying to do what the if the person I'm helping or the person I'm giving that information I don't know what he's trying to do I mean this is the main difference between data analysis and data analytics is this clear okay next structured and unstructured data not at this level but maybe at a later level you would understand this you would know it better I'll just wrap it up in just just to give you a glimpse what is structured data this is structured data rows and columns properly neat labelled row columns This is structured data. Unstructured data. This is unstructured data. PTS, slides, emails, all this is unstructured data. Free form free form format with predefined data model. Is that okay? Difference is clear. Emails, image, image, video, audio, all this is unstructured data. Structured data, customer records, financial transactions, sensor logs, etc. are structured data. Okay, for now this is more than enough. Now coming to the slide that I really wanted to discuss and I would spend some time to discuss this descriptive statistics or descriptive analytics or descriptive yeah these are the two ways you fi you would find this descriptive statistics descriptive analytics sometimes it is there is another word that it is used descriptive modeling no not descriptive modeling okay descriptive analytics okay descript Descriptive analytics approach. Yeah, these are approaches. Yes. Right. Uh yeah, correct. You can say that descriptive approach, right? Evolution of data, evolution of value from data. Notice this plank. First of all, hindsight, insight, foresight. And notice this dis this division that I made. It's very very important. Are you getting it? I think if if you have understood data analytics and data science well you can very easily understand this visualization or this picture data analytics is all about descriptive trying to understand what happened and why did it happen example let's take an example so that you also understand it with the data right so what we'll do is we'll go to that sample supertore and I will ask you okay I cannot ask because it's on Excel and some of you don't know Excel so I don't want to give you excise right now but I'll show you right descriptive analytics what happened example let me show you with the help of example what happened I want to know the quarterly sales right or let's say not quarterly See uh yearly sales only. Let's stick to yearly otherwise I'll waste time. So date yearly sales. Oh we have this quarter. Fine. This is called pivot. Those for those who don't know I'm creating a pivot and we have we we have this in our curriculum. So don't worry. I'm just showing you. Okay, yearly sales and profit, right? This is my yearly sales and profit. This is what I did. I described what happened, right? Okay. This is how my sales happened. 4 million 4 million 6 million and sorry not 4 million uh 4 million 4 million 6.6 million.7 million. So in all 2.2 2 million. So this is historical data. This is descriptive statistics. What happened at explain? Why did it happen? Okay. Why is I want to know which region or which yeah which region contributed to these these sales? Why? Right. That's actually not why it's how. Right. I mean which region? Yeah, you can take it as like which region contributed why did the sales increase right? So you can definitely in terms of region it is a Y only which region uh led to this increment in sales or which product why did the sale increase right which category which subcategory led to the increase of the sales right why okay why did it happen diagnostic I'm going into the diagnostic mode right let me if you want I can show you quickly like I am not going to go into much depth category Right. Okay. Category. Yeah. Right. So, which categories led to the sale? Right. It it can show me quickly. Right. Why? I'm getting into the diagnostic mode where what led to the increase in the sales. Okay. Furniture I would see. Technology probably technology is the answer, right? Whatever. Okay. Clear. Diagnostic. I am analyzing the historical data but to try but trying to find out some answers. Okay. Then if I use this historical data to predict to predict okay to start predicting okay help me predict the sales for 18 19 and 20 using this these four years I am I will have to use some machine learning models data science models models right statistical models and that would be that would be predictive I'm getting into that predict and notice the difficulty level also increases right information to optimization I'm moving towards optimization okay what will happen and lastly prescriptive prescriptive means now I know that okay my sales are expected to rise this much as a data scientist as a data specialist I would give some prescription to the business I would give some prescription to the business okay got it means how can we make this sales happen the sales that you are seeing that the model is indicating that that sales can happen now what resources I should gather how much finances I would need how much employees I need right what are the things what are the products I might have to scrap prescribing fine okay so are the four pillars of analysis you can call it pillars or four different techniques of right or the evolution you can call it evolution whatever Right? Is it clear? So evolution, we've seen the evolution of data. Okay. Next, it's visible now. Fine. Fine. So this evolution is also linked to this triangle or this uh you can call this as a yeah triangle only or pyramid. It's called D D D D D D D D D D D D D D D D D D D D D I K AW, data to information to knowledge to wisdom. Okay, as I try to go deeper into the data, I try to start finding relationship, start analyzing the relationship, start finding the underlining patterns. I move from data to information to knowledge to wisdom. So [clears throat] now that you have understood this, uh I don't want to spend more time on these AI terms, right? Artificial intelligence, machine learning, deep learning. I don't want to spend more time here rather I would want to spend some time on this data life cycle and big data. Okay. And then I think we will so let's quickly go to this one the data life cycle. Okay. So you should know what is how a data life cycle is structured because in which part when you working on Excel which part are you working with you should understand that okay that's why we should know the at least at a higher level what is the data data life cycle looks like. Okay. So data life cycle starts from storing of data. When the data is produced by the systems, you you do a transaction, you do a transaction on Amazon. Okay. For Amazon, the data is instantly produced when you do a transaction and it gets stored in some storage system like their data sources or you can say data databases sorry right databases right so when the data gets stored the data life cycle actually data is produced and stored there okay so data life cycles Starts with data storage, data acquisition and extraction from various sources. Deals with storage, management and access of raw data, data warehousing, identifying and exploring various data sources. Deals with structured and unstructured. So you store structured data also. You also store unstructured data also in the databases. There are different types of databases for structured and unstructured. So right. So there are different types of structured and unstruct databases for structured as well as unstructured. Okay, clear. So now once you have stored the data in databases and you know just to let you know right just to I mean I'll delete this just to let you know the the icon for databases I don't know many of you would would have seen is this like a cylinder have you seen such icon in the world of data this means databases. Okay. So this is a icon for that the storage of data. Okay. Next is about [clears throat] data engineering. Now what does data engineering means? Data engineering means when you are data engineers are people those who understand the structure of the databases they with data warehousing data warehouses right there are some more terms which I don't want you to I mean don't want to explain in detail because those are data ms you would have heard of a data links So all these are terms related to data source databases and the way data is stored with different structures like data mod data links data these days there is another term that is being used uh data links data mods I don't know yeah right so not getting it right so data engineers are data engineering is a field from which in which you are You understand the structure of the data. You are able to convert data into structured table format. You are basically able to play with the data at a at a data structure level. Okay. At a data source level, you can transform add new variables, right? Transforming means adding new variables. Cleaning, right? Quality of the data. You are responsible for the quality of the data and data engineering is a very very important role, very powerful role. Okay? Because you know the data very well. You know how the data is stored, what kind of data is coming, then what how to store that data into which type of databases all that you know. Okay. So this is called data engineering. Right? Then comes the tools like Excel, Tableau, PowerBI, Python. Look at it. ADA, right? Which helps you summarize. Once you have extracted data, once you have extracted data from the databases, right? You start working on data. Now data is with you. Reporting. The third part visualization to communicate findings right summarizing just the way I showed you pivots pivot tables are the summarization tools okay data analysis then visualizations charts graph pivots all this is reporting all this is reporting fourth part is called data modeling thing. Now see with Excel, Excel lies here. Excel lies here. Okay. Now I would say Excel lies here. Some part of forecasting can be done but very very brief. Right? So I would say Excel should lie like this. If this is a value chain, some part of forecasting but majority of data analysis and reporting. Clear? Okay. So, data modeling. So, most 90% 95% of the work Excel can be used for is here. A little bit it spills over to the data modeling. Data modeling is all about analyzing, discovering insight patterns and forecasting, statistical analysis, inferential analysis to assess statistical significance. This is more important. That's more technical. I don't want to touch this right now. But predictive and prescriptive analytics, we did we just did predictive and prescriptive. Yes, we did that just now. So that part is data modeling. Okay, that is data modeling, prescribing, predicting, forecasting using machine learning models. In fact, data science you can say all data data science starts here. Data analytics here and this is data engineering. Okay, this is all data engineering. Clear? And lastly is actionable insights. Once you have predict done the prediction, once you have done the prediction, right? You start to take decisions, You start taking decisions. You take actionable. You you derive insights which are actionable, right? proactive decision making and its evaluation. Okay. Use insights, knowledge and intelligence gained from analytics to enable decision makers to to take datadriven decision. Notice the word datadriven decision. I'll give you an example of a data life cycle. I'll discuss a data life cycle rather. Right? So let me discuss the example with the help of data life cycle. Okay ecommerce player like Amazon. Okay. So I'll give you an example of now let's look at the data cycle of a data life cycle of a typical e-commerce player like Amazon. Okay. You know on a typical day how many transactions are made on Amazon and especially when it comes to like days like which are coming like big billion days and all millions and millions of transactions happen on a single day. Yes. All of you agree right millions and millions of transaction happen every day. Now it's not easy to manage the scale of the data. It's not that easy. Believe you me. When you are dealing with that massive scale, it's really really you have to have your systems in place. You have to have your all the things in place. Otherwise looks like a event like a big billion day or a what is what that what does Amazon call it a great India sale I think great India sale right millions and millions of transactions are happen right India festival right in a single day and it is not 1 2 3 million probably 40 50, 60 million, 100 million also. Sometimes transactions happen during this. Okay. Millions and millions of transaction happen. So when you do a transaction on Amazon, can anyone tell me how many data points would be captured when you would be captured or would Amazon capture when you do a transaction on Amazon or maybe a flip card? Anyone? How many data points are captured when you do a simple transaction let's say of for 10 rupee or a dollar your name your location the price the product detail right then now you got it what are the data points I mean these only see these are the these are the data points now each transaction in this dummy data set is capturing 21 Right? And this is the bare minimum. You would not believe 300. 300 data points they capture each transaction that you make. I can name a few like these 21 you already I mean you got it right. Apart from this many other color of the product your location I mean your coordinates longitude latitude did you make any further transaction I mean previous transaction I can't even think of I have not worked on any e-commerce industry I can't think of 300 data points they capture approximately of course this keep on changing I mean with year passing in each transaction millions of transaction ction 300 data points. So can you think of the scale of data they manage? So the data that that scale with that scale they need very very big Okay. So this is your first part of the data life cycle. The transactions that you make they will get saved in the databases. Okay. Clear? First part first part of data sourcing data data gets saved. Now how do you manage such kind of data? I mean such huge data you have to some the data engineers I'm not from data engineering side but they maintain how do you optimally maintain that data maybe you create some additional variables so that they are the data is structured properly right you convert you divide the data into structure and unstructure right some some data have some variables you rename it right so I don't know I don't know much on data engineering side but this is all data engineers do right they maintain the databases okay and when I say maintain the databases means when you have to as a data analyst you have to analyze data you would go to a data engineer and ask for okay help me pull this data so they maintain the databases are respons responsible. So all the people in the I mean those people those who maintain the databases those who would give data on request to the analysts in Amazon they would fall in the second part that is data engineering. Okay. Now you are working as an analyst. You got the data you placed a request. You got the data and you let's say you wanted to this is the data you got it right you wanted to analyze the transactions for the year 14 15 16 17 this is the data you got it now you were your role was to identify the top selling products the top selling customers and the the regions or the states that are giving me losses. This was your analysis that was to be done. You got it? Did you get it? Three things you were asked to analyze by your manager which which are the top selling products which are the most profitable products and most and the products that give that are giving me the most losses and the region let's say region or states. Okay. Now you will do those analysis. Okay. Analyzing and you are asked to create a report. Reporting. Okay. Third part date reporting. EDA. Of course before that you will not straight away start doing it. Now I told you you will have to first do EDA summarization data analysis descriptive statistics. What is descriptive stat? We'll see trend, charts, graph and creating dashboard. Right? Clear. Next. Now you have made some analysis but your as you present these analysis as you present these analysis your manager ask you okay also help me plan my campaign. also help me plan my marketing campaign for the customers who are for let's say for five customer segments divide my entire customer base into five segments so that I can plan five different campaigns for them are you getting it clear now how do you cluster cluster them. How do you cluster them? Clustering is a machine learning algorithm. It's a unstructured sorry yeah unstructured machine learning algorithm. Okay, sorry unsupervised I'm saying unstructured. Unsupervised machine learning algorithm means I told you a bit little bit about it supervised and unsupervised. Did I tell? No. Did I cover supervised and unsupervised? Oh, I covered structured and unstructured data. Not supervised or unsupervised. Okay. Okay. Just in short, it is a machine learning algorithm that helps you not I'll not use the word unsupervised and supervised not beyond the scope, right? So it's a unsuper uh it's a machine learning algorithm that helps you cl create cluster for your customers for millions and millions of customers. It creates c clusters and those clusters are like for the customers who have similar feature or similar characteristics. Okay, this is um this is something that a machine learning algorithm can do. Okay, so it clustered or another way another example could be so one is clustering. Second is the your manager ask to help him forecast the sales trend for the next year so that they can do capacity planning, they can do resource planning. Okay, for that you would need to do you will you are now you are in the fourth part of the data life cycle m data modeling. Okay, you would do predictive predictive analytics, prescriptive, right? You will predict basis those models. Okay, is it clear now? And then last is of course that that your manager would do bases the data that you share. He would take actionable insights. I mean he would take action basis those insights and he would do the decision making e-commerce company similarly for banking similarly for insurance similarly for I mean every industry telecom every industry would have their own data I mean the data life cycle would be similar same but yeah of course an example could be different Okay. Okay. Now that we have understood the data life cycle, right? Now let's understand another very interesting part and that's going to be the last section for today most likely applications of business analytics. Okay. Or you can say data analytics, business analytics. See data analytics and business analytics. What I told you was a strict definition, right? But they are used see as I said business analytics or data analytics people they business even business analytics people they do lot of number crunching but they are more inclined towards written written content also. But data analytics are strictly right they are strictly working on data crunching. Okay. So applications in different industry right can you think of a let's say uh or let me just reframe it. Can you think of the sections of business where analytics is used? I want you to name those those business you can say business divisions or whatever you can say um parts of business or sections of business or business process not processes but yeah parts of organizations where analytics is used. HR, sales, operations, what else? Supply chain, quality. Excellent. Quality is a very very important finance. How can we miss finance? Right? Then one thing is missing marketing. Right? And what else? Yeah, primarily these, right? Primarily these are the big yeah customer support that comes as a part of marketing or sales only, right? But yeah, of course each and every of these are the big heads. Each and every of these section would have a sub head also. Okay. like customer support as you said customer support okay so customer support would fall either sales or marketing isn't it marketing CS okay then what can come under marketing any subhead like campaigns right campaigns campaign management okay then what else can come under marketing performance marketing What else can come under marketing? Anyone from marketing can help consumer support, campaigns, customer acquisition, digital marketing, very important, right? Customer acquisition. Correct. Advertising. Yes. advertisement advertising right so you can see each and every not just business trend analysis how is marketing I mean trend analysis can be in finance also operations also sales also right so but yeah these are the big I mean heads of business and then there subheads okay technology we missed out So these are mostly the big business parts of the business or you can say the business departments heads whatever. Right now each of them have their own way of using analytics. Each of them like have their own field of analytics financial analytics right? Yes. Budgeting very important budgeting. Budgets targets isn't it? Allocations. Yes. Risk management. Excellent. That is why I wanted to really include financial risk. financial risk. Okay. Then expenses, Investment. Nobody mentioned investment, All these are part of cost and control. Yes, very true. Cost I did I miss that expense I mentioned? Right. cost and controls very important cost and controls I mean P&L yes right P&L and all that comes into it right so why do you think it's important to because at the end of the day everything is about money isn't it to to manage financial risk excellent right fraud to to avoid Avoid for frauds, right? Avoid frauds to manage financial risks, right? So similarly just like this financial analytics you would have HR analytics or yeah HR analytics we call it human resource analytics right so what I'm saying is every field no there is no field that is saved from analytics that that exist in isolation to analytics. Okay. Resource allocation, very good. Payrolls, excellent. employee attrition, employee recruitment, right? All these are see all these are bases based on analytics, isn't it? All these are numbers. You are forecasting, you are planning, you are b I mean employee benefits, right? All these are numbers, okay? Analytics. Similarly, sales, okay? And all in fact, right? All of them. Now I want to specifically cover one risk analytics. There is this field there is this field called risk analytics, What do you think is risk analytics? It aims to minimize the business yeah business and financial risks. Isn't financial risk is like covered by finance only sometimes finance but yeah there is a overlap if there is a risk department okay aims to minimize business and financial risks okay we using data important thing is using or analyzing okay understanding the anomaly that are happening. Are what are there triggers to frauds that we are able to identify before time before time that is analytics right? Are you getting it? The uncertaintity that business faces, uncertainty of business, right? That is also handled by risk. Credit risk analytics, credit risks, analytics, market risk analytics, right? Especially for banking they they need this so that they they they take decision. Yes. Civil score they take decisions how a customer would repay a loan. They take decisions to pay or to give loans to customer or not. Right? Again why I'm why I'm discussing all these these are all applications and at a very in organizations from small to mediumsiz organization these days in fact I would lot of this work is done on Excel lot of work is done on Excel even in very big organizations still lot of work is done on Excel despite of so many tools now in the market like data visualization tool and all still Excel remains very very prevalent. Okay. So this is all I think and yeah one last type of analytics I would really want to discuss performance analytics. Okay. And I think this is the last part of theory, right? And we will after this we'll start with some I think we will start with some we'll move on to the Excel and we'll start understanding what is Excel sheet. Okay. When you measure the performance right you measure the performance Yes. Important KPIs, right? Important KPIs against their target. Okay. Yes. Correct. KPIs and metrics. You compare the performance of important. Right. So you here you you're focusing on efficiency, productivity, right? And achieving goals. These are my target. These are these are the big things on my mind when I talk about performance analytics. Can you give me some examples of performance analytics? any any need not be business any any performance analytics for example delivery times right for a company like Zomato right delivery time what is the average delivery time of a month in a month if it if it increases they it means they are not doing in the delivery they are not doing well in the delivery okay so they have their targets target target delivery time. They keep comparing okay employee KPIs right like how well the employee is performing does the employee need training all these are performance analytics ola yes very good yes right then for a customer care average call handling time average handling time to pro provide solutions right all this is performance mark performance analytics Okay. So that's pretty much about the theory part and I think now we will move on to some some we'll start right we'll start understanding the layout of a excel sheet and let me just mention just type excel and you will open the excel sheet like this. Okay all of you can open. Okay, one more thing I think see this is a advanced Excel course. When I say advanced means it involves AI also. So I will give you a idea I mean we are going to cover AI towards the last sections right AI AI part of Excel. uh we will start from the very basics and we'll cover AI towards the end right but I will give you an idea because for some AI based functionalities I'm going to show you is you see this this is a excel sheet that you that I have open all of you would have opened it my current my mine is 365 office 365 see it's not expensive the off see for AI features yes you would have to but you Can I think if you can manage for 1 month, 1 month is costing around 7800 rupees, right? We can if you can just buy for one month, you can practice also. But I I think I will rather I will just check and get back to you with a communication. Okay, maybe I don't know maybe uh there's a lab uh right so we'll just get back to you on that. Okay, Web is free. I think it is free. Uh you mean web 365 right? Yeah, there is a Excel 365 also has a web version also but I think it's not free and web excel uh it would not have the AI features also it's a bit slow also know being a web being a cloud-based it becomes slow also okay right so what I'm going to do is let's start with some uh what should we start? Okay, I'll give you a activity sheet, right? I'll give you a I'll share a sheet with all of you in class notes. We have seen most of the features are logged in. Yeah, the desk desk app. Yeah, that's what I'm saying. Uh I'll just wait. I'll give you we'll give you a communication. Okay, don't worry. Okay. [sighs and gasps] Uh let's start with day one activities and we are going to start with these some activities. Okay. And these are there are some instructions you'll have to follow them. Okay. And we will I'll we'll do these activities along. Okay. So this will involve lot of things conditional formatting activities. Okay. some let me just delete this some of fine some of these are there still from the previous share the link which link which link do you want me to share I don't know did I mention some link the practice practice I'm going to share this these these sheets don't worry. Okay. So let's start with the first activity and I I'll share this sheet with all of you. Okay. So let's quickly understand the structure of Excel. See people those who are new completely new to Excel see you can see this this is called a spreadsheet. This area which is which you see is called a spreadsheet. Can you please upload the same on the LMS? No, I cannot I cannot kunal because this is not the official uh so I don't think I will be able to okay this is just that I have discussed it I'm sharing but this is not to be I think I will not be able to put it there okay right okay this these are not official I mean from simply learn okay this is because I I discussed some topics so I've shared it okay fine so quickly let's look at the structure of Excel. This is called the spreadsheet. Each intersection of rows and columns is called cell. We know it, right? And if you see if this is the cell, what is the name of this cell? E4. Clear? Are you able to get this all of you? This is called cell reference. E4 is the cell reference, right? Cell name you can say or cell reference. Cell E4. This is called cell referencing. Is it clear? Right. On top of this, you have the menu and you call this as ribbon. What is it called? Ribbon. That menu that you see on the top, right? This is called ribbon. Oh, wait. Yeah, this is called I don't know what is this. Wait. Oh. Pick marks are happening. Sorry. This entire thing is called ribbon, right? And these are called tabs. Okay. Home tab, insert tab, draw, page layout, formula and all these are called tabs. These are ribbons. Within these ribbons, within these ribbons, you have something called, can you tell me what are these called? Anyone those who work, I know most of you work on Excel. What are these called? Groups. These are called groups. So this is called a group. Okay. Ribbons are divided into groups. Can you see alignment, font, number, style, cell, editing, all these are groups. Clear? And then each of these icons, these are called commands. Is that clear? clear to all of you right so let me quickly name them and yeah yeah I'll repeat no problem see first on top is the uh tabs right like tabs you see home insert draw these are tabs okay then within tab tabs you have you each tab opens a ribbon. Each tab opens a ribbon. Clear? This is called ribbon. This gray area is called ribbon. Okay. So each tab opens a ribbon. Then each ribbon has some groups. What are the groups that you see right now in the home tab? clipboard font alignment etc. Is it clear? And then each group has commands. Okay. What are the commands that you see this one? See this is a command. Center font may this bold italics. These are commands. Okay. So let me put it like this. Bold, italics, underline, all these are commands. Is it clear now? Clear to all of you? Okay. So, let's start by un start and I want this now from now onwards everything is hands-on. Okay. So whatever I how do you select a cell? How do you select a cell? Okay, see one way is bringing the cursor. If suppose I want to select this cell, I will bring the cursor to that cell. Okay. So using shift arrow selecting a cell use shift arrow shift plus arrow right shift is also not required. Sometimes only arrow or shift arrow will yeah if you want to select multiple cells. So selecting a single cell single cell is simply arrow. Okay. You move arrow like I'm moving you select a single cell. Selecting multiple cells. Multiple cells is more than one cells. I have to select like these two. So I will press shift and arrow. Can you do this? I want I'm hoping all of you now it's completely hands- on. No more theory. It's gone. Theory is gone. Right? Okay. Can you do this? Keep the shift pressed and arrows. You will see multiple cells are being selected. Clear. Selecting a entire column. Can you tell me what is the shortcut? Anyone those who work selecting a column and I want all of you to do this. It's very important. Right? shortcuts we are already starting. Okay, I want you to be doing this. Okay, selecting a column like this. What is the shortcut? People, those who work on Excel, please quickly those who work with Excel clicking the cursor on the column name again. Then how will you click? You will use mouse. I don't want you to move to use mouse. I want you to be a expert with Excel. If you are using a mouse, you are not an expert. Yes. Control + space bar or Yeah. Control + spacear, right? Is it control + safear? Shift plus spacear is uh row. Control + spacear is selecting column. All of you could do it please quickly. I want to cover these uh shortcuts. Yes. Selecting a row quickly. Selecting a row. Shift plus spacear. Please those who are new try this. Shift plus spacear. It will select the entire row. Okay. Done. Now selecting multiple columns. Selecting a column. Selecting multiple Anyone before I write you should tell me. Selecting multiple columns. Anyone? Yes. First there are two. Control + space will select that column. Then shift + side arrow. Do it. Control + space. Shift + left or right arrow. Can you do it quickly? Very good. Those who are able to do, very good. And those who are not able to do, please let me know right away. If you're And I'm assuming my assumption will be if you are not stopping me means you are getting it. You are able to do it. Okay. If you're not, if you are stuck or you want me to repeat, you have to tell me. Otherwise, I will assume it is done. You are able to do it. Okay. Selecting multiple rows quickly. Again, shift plus space and then shift plus arrow to try. do it. Are you able to do selecting multiple rows? Selecting the entire sheet, control A, control A but you should have you should be there in a empty cell. Remember this control A but you should be there in an empty cell. So if I'm here control A C if I'm on a cell which is not empty A will highlight or will select only the surrounded cells or the cells that are having see data. So you should be in an empty cell. Then you select control A. See, so what happens is how does control A work? Basically, it should get one complete row and one complete column three. See, at least either of these see if it does not get either of these. See, can you see if if I'm here, I press Ctrl A, it is getting the entire complete row or a entire complete column. It will select. If it is not getting either of this, see what is happening here. It is neither getting a complete row nor getting a complete column. So it will restrict. Okay. Next thing. Do you see this? This this part. Can you see this just under the home? This is called what is it called? The name box I think. Right. Cell name. N name box. I think I can name this cell. See this is B25. Now I can name it as let's say the puck. Now see this cell is named as the puck. Clear. So this is called name box. I can of course at the if I want to remove this name now I'll go to formulas and then go to name manager. There would be a name manager here. Just see do you find a name manager or they have maybe changed the so name manager would be there I don't know now it's not there in formulas earlier it used to be there uh recently used yeah it was there in earlier it's there in maybe it's there in earlier I' I've like discussed this very after a lot of I mean lot of trainings but yeah so so if I want to remove I can remove this name so it's not that now it is like permanent I can so this is name manager okay right we did some basic things we started hands-on uh understanding the layout of excel and what can be I mean how do you select a single cell. How do you select a single column? Right. Right. Selecting the entire sheet also we did. Okay. Just that uh just a couple of very easy uh more some more very easy commands that we did. Right? We learned how to select. Right? Let me add some more shortcuts because these shortcuts are really going to uh help us and right now we were selecting the cells right now adding cells right in what way you can add cells right so first let's see adding a row Right. Adding a row. First you select the entire row then shift control + okay I I'll I'll show you. Let's say see A B C D. Right. Now if you have to add a row after a shift and space what will it do? Shift and space will select the entire row. Then shift control plus. Okay. So adding a row is in two parts. First is shift + space. 10 shift control plus control plus done. Adding a row is done. Okay. Adding a column. Now how do you add a column? Let me again show you. A C B. Right? I have to add a column here after A. First I'm going to select the entire column with control + space. So this one same control + space. And then as a second part I will do same shift control plus. Okay. Same shift control plus. I want to add some let me just show that let's say this is the table and I want to add some cells here right in between these so I'll just select and insert right but this does not I mean I don't know the shortcut of it there would be some shortcut definitely but I don't know it is not much used. Okay. Insert shift cell down or enter or shift cell right. Right. So if I do a shift right. See you could add that. Yeah. So control shift this. Control shift and then Okay. I Okay. Control plus shift. I did that but nothing happened. No. Control shift + okay this insert will come fine control shift plus is the shortcut and then you can select control shift plus and then you can select this right so this is just adding some bare minimum some cells that you want maybe I want to add only two cells here so control shift plus and then you want to shift these cells right or down if I put down see it will move it will create only the two cells. Okay. So, control + shift + plus and then you can make a selection. Now, we have learned how to add a row, how to add a column, right? Let's see how we can delete a row and delete a column. Do you know this is the row I want to delete. First I will select this row. Shift + space and thent control minus. Okay. So I'll write deleting a row. Shift plus space. Yes. Shift plus space. and then control plus minus. Similarly, deleting a column, everything remains same. Control + space and thent controll + minus just remove shift deleting a row deleting a column. So these are some basic functions that you should know right and I mean you should know these basic shortcuts and it will really help you move fast. Apart from that some very basic shortcuts. Some more shortcuts are control C. Do you know what it is? Mostly you know was those who are new. I'm just copy. Okay. Copy. I see. Ctrl C copy and Ctrl V paste. So Ctrl Ctrl Ctrl Ctrl + V. Very important. Similarly, Ctrl X Ctrl X is cut and then cut and paste also works. Ctrl X control + V. Okay. So, so I think with regards to the initial shortcuts, we should be good. And uh now let's move on to some exercises that we will do, right? start working on these exercises and they'll help us in. So some activities we will perform. Remember our value chain data value chain. Do you remember our data value chain? If you have extracted data, what what is the first thing that you do? When if you have got the data from databases, what is the first thing that you do? Data cleaning. Remember, you'll clean the data, right? Yeah. EDA, EDA will come. Yeah. EDA, I mean cleaning is a part of EDA is a bigger activity, right? But you will first see how you can clean the data, do formatting, right? So these exercise that we are going to do they are going to focus more on just I'll just show you the flow of the presentation I mean this is just presentation you can go through it data cleaning functions okay we are going to first see data cleaning functions okay what data cleaning activities and we'll perform these activities and then we'll see we are going to cover all these today right sorting filter text to column these are some of the data cleaning activities we'll Right. So let's start let's start with these activities. We'll go slow. I will guide you. Don't worry. Right? But these activities are really really important to understand how you do formatting. How do you do cleaning? Right? All that formatting and cleaning are going to be performed here. Right? So I have given some you can say some instructions that you can follow right. So like go to this first activity. Go to activity one. What is expected? Formatting. All you have to do is format. Right? You have to perform these steps one by one. Okay. You can perform these steps one by one. Right? Auto fit column width. Now see this is I think your it should uh appear like this. your or if it is not appearing your columns are not appearing like this just make it short in width right make the width small sir I used shortcut for deleting row but…

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