Data Analytics Full Course 2026 [FREE COURSE] | Data Analytics Projects For Beginners | Simplilearn

Key Takeaways

[music] Hi there, welcome to Simply Learns YouTube channel. Imagine hundreds and thousands of rows of complex data and very little time to extract insights. Scary, right? But what if I say that's the playground of data analysts and the game that they really love. Data analytics is the skill of turning raw data into clear, actionable insights that drive smarter decisions. In today's datadriven world, businesses don't rely on guesswork. They rely on patterns, trends, and evidence hidden inside data. This tutorial is designed for beginners, breaking down complex analytic concepts into simple practical ideas you can actually apply. That said, if these are the type of videos you'd like to watch, then hit that like and subscribe buttons along with the bell to get notified whenever we host. Also just that you know if you want to upskill yourself master data science and data analytics skills to land in your dream job or grow in your career then you must explore Sinclarn's cohort of various data science and data analytics programs. Sinclan offers various master certifications and post-graduate programs along with collaboration with some of the world's leading universities like per university I bui and many more. Through our courses, you will gain knowledge and bury expertise in skills like Python, Tableau, PowerBI, Gender VI, and over a dozen others. That's not all. You also get the opportunity to work on multiple projects led by industry experts working in top tier data and product companies. After completing these courses, thousands of learners have transitioned into a data science or data analytics role as a fresher or moved on to a higher paying job and profile. If you are passionate about making your career in this field, then make sure to check out the link in the pin comment and description box below to find a data science and data analytics program that fits your experience and areas of interest. Now, without further delay, let's get started. In this video, we will get started with the fundamentals of Excel, SQL, PowerBI, and Tableau. Now, let's get started with a small quiz. What is the main goal of data cleaning? Option A, increase the data size. Option B, remove errors and inconsistencies. Option C, encrypt the data. And the last option, archive the old data. Now, please do let us know your answers in the comment section below. And with that, let's get started. So, now let's get started with Excel. So, here you can see some rows and some columns. Let's understand from the basics of the pros and cons and then we can understand the basic fundamentals on Excel which we will be using on a day-to-day basis. If you are proceeding with data analytics some of the basics are data validation, conditional formatting, data cleaning, pivot tables, lookups, aggregate functions and also before we dive into analytics we have an inbuilt analyze function in Excel. If you can see something on the right hand top corner of the home tab, you will find something called as analyze data. Don't uh look into this addin. So this is one of the copiles that we have added in. But before that, you'll just have analyze data. So this is something from Microsoft which can be considered of as an AI analyzing tool. Once you provide this data tool with the data set that you have on your Excel worksheet, it will consider it as its input and it will just build some quick pivot tables for you and it will be more like an interactive session where you can just ask some basic simple analytical questions to this analyze data tool and it will give you instant results. So now let's get started. So in Excel if you are entering data then this is the row and this is the column and every column will have its own column header. So based on the column header you will be uh writing some queries with tables in the future. Now let's understand that fundamental. So now apart from that you will be having home option here you'll be having all the font uh here you'll be having all that font settings and you here you'll be having alignment and this section where you have the number group this is really important so here you will get to decide the data type of the data that you're having in so of the columns in your data set might be having rate data type it might be having salary data type it might be having employee ID data type right so all these three or multiple will have a different different dedicated data sets. Let's say if you're going to employ number then you might be having a combination of alpha numeric or you can have just number right so if it is alpha numeric then you can go with character data type if it is just number then you'll have to go with a whole number and if it comes to phone number then it's definitely and purely a whole number right so you need to focus on that you cannot just keep that phone number in text format do you right and apart from that if you come into the date section you might be having join date you might be having the last working day or etc. It can be anything right? If it is related to date then make sure that you are choosing the date form right and you might also have some other information. So the basic foundation on data types is make sure that you have the right data type for the right data. If you mess up then it is going to be really having a tough if you mess up then trust me in the analysis phase you're going to have a tough time. So that's the reason you need to focus on data types first. Don't worry we will go through that one step at a time and when we cancel and close this section you will have conditional formatting here which we will be exploring down the line. So conditional formatting is something similar to data formatting. So let's say you have some student data set and you wanted to highlight let's say if you have 10 students it's really easy for you to identify which student has the passing marks and which student is flung out right but in case if you have about 100 students or let's say thousand students in that scenario you might have a tough time to identify or point out the students with failed marks or the passing marks or you know categorization into first class second class and uh you know something like failed candidate. So in such scenarios conditional formatting which you can see in the home tab and style section this particular option comes really handy. And apart from that you have a basic foundation uh steps where you can insert a row, delete a row or format. So you can do this on the go and uh here you can see some aggregate functions right. So you have some average count mean max math. So we will be having this aggregate function in this particular tab. You already have some data set here. So we will be explaining that here. And apart from that coming back to the data sheet. So here you have some insert options where you can insert the pivot table. So here we have a basic explanation for pivot table as well. So this is just the foundation step. So we will have this for a couple of minutes of discussion. Once we are good with this, we will dive into a sales data set which has about 10 to 12,000 rows of uh data. And we will also understand power query where we will combine the yearon-year data together into one consolidated spreadsheet and then continue with it. So there's a lot to learn in this. So stay tuned. Now uh coming back to the insert option here we have pivot table recommended pivot tables and if you are going with something like data visualization then you also have charts here. If we go through this. So we have line graph, we have bar graphs, maps, pivot charts, a lot of them. And here you have spark lines and apart from that here you have timeline filters and slicers. So we will be going through this uh timeline filters and slicers. In the last phase there we will have the dashboard of the sales data that we have created. So what we will do is just analyze the sales data, create some pivot tables, then pivot charts, then consolidate all the pivot charts in one single dashboard and we will have slices into it and then we will filter. Let's say uh we wanted sales for a specific product or a specific brand. Then we will know what kind of needs are generated, what kind of uh you know conversions we have, how many number of orders we did get and how many number of orders did we get and how many number of customers we have a lot of those things. So apart from that if you go into draw this is basic foundation. So here you'll have some options to draw. If you wanted to highlight something in your presentation when you're presenting your data or dashboard to your clients and if you wanted to highlight something then you can use these pen options to just you know write something on the screen something like that. And here you have uh page layout if you wanted to print your data set or if you wanted to print your dashboard then you'll go with page layout here and you have the basic formulas that we already went through which is aggregate functions and rest we have the data. This tab is also really important for us. Here you have the option of getting data onto your Excel dashboard or Excel workbook. Now why get data is really important. Do I really need to use Excel to get data? Yes, because Excel happens to be that one Swiss 9 still existing in every data analyst pocket because this is one such friendly tool or one tool which helps you with all the requirements. you can start cleaning your data. You can make some important changes on your data. It's like a quick handy tool for you. So, Excel is still one of very important tools that you need to have in your uh arsenal if you're going with the fight with data analytics. So, here you can get data from a wide range of resources coming from Excel workbook. It can be text CSV, it can be Excel, JSON, PDF or a folder as well. And if you're having your data on some database then you can also go with database as well. And don't worry speaking of databases we will also export our final sales data onto SQL and uh specifically MySQL Wordbench and we will also perform some data analytics uh using the MySQL platform as well. And if you're working with cloud storage like Azure, you can also connect that from Azure to your Excel platform and pop platforms as well. And apart from that you have other resources like OTPC web etc. You can also do some web scraping and load the data from your websites to your platform as well. Now you can also directly launch your power query from here. And apart from that if you are having multiple sources of data then you can just click on the refresh all button and automatically your dashboard and your data um you know sources will update everything in one single click. And apart from that if you are focusing into filters then let's say you wanted to uh identify the sales happening in one single state. Let's say we have all the states of a country and you wanted to find out the sal one single country then you can use the filter option to filter all the states except one that you want and then remove all of those. So that is what something you can do with filter. So here we have it and uh if you swipe down you have uh data tools group here you have data validation and you have uh data uh so here you can remove the duplicates data data duplication is one common thing and if you wanted to remove all the duplicates in one single go then remove duplicates option that you have will be very handy and here we have uh text to columns option. So text to columns comes handy when you wanted to uh you know kind of split the data into different parts. Uh let's say you have customer first name and customer last name and you wanted to separate them as two different columns from one single column. Then you can go with text to columns and uh you can also provide the d limiter whether if it is a space daily limiter hyphen d limiter based on that day limiter it will split the data and uh in the next group you have the forecast step. If you have data for this current uh month or this current week or this current year and you wanted to see how the data could be performing or the uh future results then you can go with what of analysis and provide some steps to it and you can get the projected data right not just that if you wanted to let's say know the marks of a student uh what is uh what could happen if uh let's say you have five subjects in total to learn and you got this course for four subjects and you have reached that limit point right to either pass or fail. Now to identify what number of marks you need to get in that one subject you can do a whatif analysis and it'll give you let's say you will pass if you get 65 marks in that final subject and uh if you get below that you'll flung the exam if you get above that you'll pass the exam. So you have logic in your mind right and if you're having a different use case and a bigger digit not just two number digits then you can go with quart of analysis and use it in real business use case and you'll identify what would be the target for me to you know achieve that target. Let's say like 6,000 uh traffic you need to get onto your website to reach that target. You can do that with what of analysis and you have the data analysis shortcut here. So you can uh uh use that and since we already went through it you can use that uh logo to generate some automated power pivot tables for you and get the analysis right that's what you can do with data there's another shortcut here and apart from that here you have review which we use and view and automate if you're going with automations and Excel VBA this is the perfect place for you and developer which is also related to visual basics and VBA and this is the proper pivot where we will use some uh pivot options and create some data models and etc. So these are the basics you need to know before you get started with Excel. Now that we have a comfortable understanding on how rows and columns are used in Excel, how you insert data, how you create some uh formulas in aggregation and apart from that how you create some data analysis charts etc. And apart from that you have some data validation foundations, condition formatting etc. Right now we will get started with hands-on. So here we have uh data validation. Let's understand how that reboot works. So here let's say we have uh a small data table and here we are manually uh adding the data. So let's say imagine this is your sales data set and here you need to add uh the sales related data. Right now I receive a new order which is 105 and the product I'm selling could be one of the three options which is tablet, phone or laptop. Let's say I make a sale of tablet. So I have to manually write tablet. And then how many number of tablets did I sold? I think I'll be selling about two. And then what's the price? So I think I'll be getting around $800 for this. And I sold it in the central region. This is okay for I think I have 18 rows here including uh excluding the row headers. So this is the row header. So excluding the row header, I'll be having about 18 entries. Now what if I have 18,000 entries? That's a little confusing and scary, right? And this could be a little tough as well. Now for such instance, you can use some automation and you can use some built-in functions of Excel. So let's start with the order ID. Now let me select these two. And here you can see a small logo has been highlighted. If I expand my screen, you can see a small logo, right? This is called flashful. If I just click on it, let let me go back. So, it's like I'm training this particular tool that I have a series entry. If I click on it, it will load the rest of the data entries to an automatic number, right? in the increasing order 1 2 3 4 5 6 7 8 9 10 so on right and in similar way I can't do this with these entries now for that I can go with data validation so I'm basically selling three different types of items in my store so I'll have this around here and I have five regions so I'll have them around here norththeast and central so what are you doing why are we getting this uh you know type of thing here and what's the importance of this? So, let me explain that. Now, I'm just trying to create a drop-down option where I can just select the item that I'm selecting. So, for that, I'll go to data option and here I have the data validation option. So, what I want to do is uh I I have a lot of options here. You want to go with any value, you can go with any value, a whole number, decimal, list, comment to list. I want to proceed with list because I already have a list of elements I want to work with. Now I want to provide the source. So where's the source to this? So I can select these cells as my source. And you can also have an input message. So you can provide an option which says select from the drop-down. Yeah, this could be my message and this could be my products and the error alert as well. So we have our uh repeat. So here we have our drop down right. So based on the drop down you can select the items. Similarly we will drag and drop this this particular whole length. Uh what we can do is we can right now select everything and we have an empty drop down here and then just copy this and drag and drop to the end. So we have the data validation applied for all our sales. Now whenever I make a sale, I just simply select the item that I've sold. Add the pricing. So what's the price for a phone? Could be $200. Let's say I've made about uh $400 here by select phones to the uh central or west region. So we'll also go through that region as well. Now here we have another list. Let's say I sold a tablet and uh $400 would be the price of one single uh tablet. So I'll sell one tablet here and the $400 price tag here. Now can we do the same for the region as well? Yes, we can do that. So uh it's a simple process again go through the data validation and here we have the data validation option and here we go through the settings and since any value will be our list. So where's the source? This is the source for my region and you can have the title. So uh you can have the title as zoom and please select from the drop-down. There you go. and error alert from the drop-down. Now we are good to go. Now you can also drag and drop this to all the uh cells we have here. And from the simple dropdown you can see there's a highlight here and this particular cell is related to zone. Please select from the drop-own alone and can be anything north, south, east, west and if I write something else let's say I want to write it as um some random name I'll drop it as excel and hit enter I'll have an error now you can retry and you can go cancel and then you will go through the region right now what this does is this has wonderful benefits you're dealing with a small data set a sample on my screen right now. But if you're dealing with a bigger data set, let's say thousands together, 10,000 or even lacks of employees and you have a collection of uh colleagues or employees or your reporties, let's say 5 to 10 or even more and they are supposed to enter the data for you on your database, then this really comes handy. This awards that data corruption or inconsistencies in the data, right? You just get them an option. They just make a sale and select something from the option which is right and then you will have this data ready for analytics. It kind of minimizes the effort on data cleaning aspect. Right now we will go into the next stage which is about conditional formatting. Remember the example that we discussed about students data set. We have something similar here. We have about 30 students and their scores in their subjects which is math, physics, chemistry, biology, uh English and sports. So a lot of them have cleared their exams and they have passed in their exams. There are a couple of students which did not. Something like uh we have an example here Karan Maha who have might have scored 29 marks in math and 35 in physics 33 in chemistry and so on. So there are a couple of mock students who might have passed, who might have flunked, right? So you can just uh eyeball it and mark them. So I want to eyeball this particular section uh mark it as red color indicating that this particular candidate has failed in the exams. Right? I want to do that to all these students since I have just 30. If I had about 300, what if I had 3,000 students? Will I manually check and add them? No, even if I work with only just 30 students, manually eyeballing each and every single student could be painstaking and time consuming, right? And from a business perspective, let's say you have a wide variety of uh products, a wide arsenal of products uh that are performing serious and everything, right? And there you get a moment where you have to present your sales insights to your client. And if you are manually eyeballing every single sales performance and highlighting it and what's uh what let's imagine that your client identified something which you missed could be really problematic in real time right so to avoid that do you have any tool in Excel which can you know automatically uh look at each and every single cell for you and does the thing what you're trying to do in just moments or even seconds. Yes, we do have. So, that is nothing but uh conditional formatting. So, before that, let's go through this small shortcut which is alt hoi which will automatically adjust the uh cell width and height. So, we have everything in order. And uh now we can go with conditional formatting. For that you just need to highlight all the cell elements and go through conditional formatting. I want to highlight the cells in some color which is less than uh a dedicated number which is 35 and I can also get to choose the color red border, red fill. So I'll go with the first one which is red fill. Enter. And there you go. Now all the cells where the score is less than 35 is automatically highlighted for you. Right? Everything in repeat. So everything is in order. Now this will be really helpful in real time and uh conditional formatting is not just about identifying a number which is greater than or less than and I you know highlighting it with a color. It's a lot more than that. You can also include some logical conditions. You can also use some mathematical conditions. You can also use some complex formulations and then let conditional formatting decide the right decision for you. For now we will just um you know go through the uh fundamentals basic examples of understanding how conditional formatting works in real life. That was a brief example for it. But in case if you wanted to explore a lot about conditional formatting, we have a dedicated tutorial on condition formatting where we have explored each and every possible permutation and combination through condition formatting and it will be a great learning experience for you. So we will link it in the description box below and you can also search that on channel. Now going to the next phase in this particular tutorial which is about data cleaning. Now data cleaning could be a little too early for you or even it can be the spot-on position for you right now to learn about it. So what's the need for data cleaning could be the question right? We had a good uh foundational experience on data cleaning in the section where we discussed about data validation where you will have your employees or team members adding data for you right in real time. One data analyst might have a different perspective of vision for data cleaning or data positioning aspect and another might have a different perspective. Right? If I uh expand this table here, you can see uh if I expand again, some of the entries are a little too different from each other. If I consider date joint column, someone has entered DD, mm and y by. So the first one dd 12. Second one mm01. Last one y by and y which is 2023 in terms of excel or in terms of data analytics uh you mention the month as m you mention the date as d and you mention y as year right so here if you see the first cell entry is proper according to us maybe and the second cell entry is you know having a delimiter separated by slash right and the third one is having y and y y format with M M or could be DD as well. We don't know. Two can be uh the month of February or two can be a date and 10 can be a date or the month of October. Right? There's a lot of confusion. Now the fourth one looks a little more pleasing. So it has 10th date and month is February and 23. Again if you deep dive 23 can be a date. It should be a date. So uh yeah we don't have a 23rd month but yeah in case if it were three then it could have been confusing would that be the month of March or 10 would that be month of October and the date is three right so these kind of inconsistencies can happen in real time so before we proceed with any kind of analytics data cleaning or making the data ready enough to proceed with analytics happens to be the most important stage and here you have amount paid column. So some of them are already mentioned with a currency. So here you can see euro and some of them are you know having some space which can be considered as an inconsistency right and uh apart from that here you have the first name and last name. There are most of the scenarios. Let's say ticket booking. If you're booking with an airplane repeat, let's say ticket booking. Let's say you're going with a line ticket booking. Then always the second name comes first and the first name comes after. So let's say let's go with Rahul Wa or Rahul Kumar, right? And in the Aine booking system, Kumar comes first, then comes Rahul or maybe the vice versa. Basically the thing is you want name to be split into first name and second right. So here someone has chosen comma as a delimiter. Someone has chosen hyphen as a del limiter. Someone has chosen space as a delimter. Right? There is some inconsistency. Same with the phone numbers. So there are places where they have written hyphen. There are cases where they have written brackets and a lot of problematic thing here as well. Now city and country. So here we have uh city and country combined in one place. But I want them to be separate. City as a separate column and country as a separate column. Right? So these are the foundational basic examples for data cleaning. So let's get started to understand how it works in real time. But don't worry in our sales dashboard we will have a little more complex data cleaning operations where we will change the data type uh happen to include some mathematical uh calculations create new columns split the data and uh you also have some date related operations where we will be u formatting the date and then we will change the format from mmd y to y by ddm making it completely ready for SQL data analysis and we will also have some pivot table anal analysis a lot more but now let's understand the basics of it to to basically get the gist of it or wrap head around it right now I will remove this comma and make it as space and I will remove this hyphen and have the uh you know space there now I can't do this uh for every name right uh let's say I want to eliminate uh the bracket here so I can replace it find and replace one has been made and here another bracket is there so I'll do that as well replace all and I want to remove hyphen and replace all and there you go and you can also manually get into this particular one and remove the space now you have numerical values and if you go through this data and if you go through this uh data column and into the data type it is general But we want more formats and I want to select text because in case I want to run some text matching operations. Find me the details of the customer whose phone number is then it is basically a text matching operation. Right? So in that scenario I want this data type to be in the text format. So I'll keep it that way. Right? And here I want to insert a new column so that I can go with text split operation. So here I'll go with data and here text to columns and go to next and my del limiter would be hyphen and then next and here I can see a quick preview. So this would be my city, this could be my country and then finish. Do you want to replace it? Yes. So here I have cities and here I have countries. Good to go. And here I have numerical values. So I want to eliminate the space and I want to provide a dedicated currency format to all the numbers. Since one of them is already having a euro, I want to add euro for rest of the ones as well. So I would like to go with currency. And here I have the symbols. I'll go with the euro. Might take a little while. So B, C, D, and E. There you go. So I have euro here. Okay. And I have all the currency formats. Rest is date. This might take a little uh confusion. So first we will do this in two stages. Uh since we have dot slash and everything we will do it in two stages. Uh we will go through data and text to columns. Here we will go with delimiter and here. Here we will unselect everything. We'll have nothing here. And next is here we have to select uh 130 or DMI whichever suits you best and finish. Let's do that once again. I think I missed something. Yeah, I was supposed to go with month, date and year and finish. Right now what we will do is replace this with good data format here. I choose year, month and date. This is so far done almost. I'll replace the tops with hyphen and we are good to go. So here we have cleaned data ready for analysis. We can save it. And now here we are at let me delete this unwanted sheet. And now we are ready with data cleaning. And now we come to the next part of uh data analysis basics which happens to be the pivot tables. Right? Now pivot tables as the name says pivot. Right? they make a pivotal point in data analysis. So we already went through the data validation part here where we had some product and sales related information. Right? Now let's say I have this data table here which is also related to sales. We have some salesersons region like northsoutheast west and product details units. So all those details now I want to find out what is the uh number of sales we did in a specific region or let's say I want to find out the uh sale figures for the employees we have Rahul Mo Priya and everyone I want to find out what's the number of sale or what's the revenue that they generated to my organization and let's say I wanted to deal with product related data how many number of products that we sold in the laptop segment or what's the total revenue we generated via laptop product right all those details you cannot just manually check all those on the worksheet and get the details right so we have one such tool to help us with which is pivot table so it's really simple you go to the insert option we discussed this before how to get to the pivot tables right so we just need to go to insert and here we select the pivot table option so here we have two more other options where you can insert the pivot table in the same worksheet or you can choose to uh create a new worksheet al together. I'll go with the new worksheet and create. Yes. So we have a skeleton here for pivot table. So to get the results, it's really simple. It's just a drag and drop operation. Let's say I want to get the region wise sales. So I'll drag and drop regions into the rows. And now I want to find out the uh unit price, right? So I want to drag and drop unit price to aggregation. So automatically the aggregation function is set to sum and Excel is intelligent enough that I'm looking for some operation. So I want to find out the total sales, right? So it gave me the total sales. Now instead of region let's say I want to find out saleserson I simply drag and drop saleserson into the rows. And if I don't want that so we have the details here. Each and every one of them have equally performed. So they made $1,900 sale. And I don't want salesperson. Now I'll just drag and remove the saleserson out of this box. And I want to find out the product level. So I'll drag and drop product into the rules. Now we have uh the product level sales. So laptop gave us $3,600. Phone gave us $2,400. Tabs gave us $1,600. Right? So this could come really handy in terms of real bullet analytics. And you can also create some uh you know pivot charts as well. So you have the pivot chart here and you can choose any kind. I'll go with a pie chart and press okay. And you have a pie chart here. You can name it as product sales and uh you can also choose uh you know a dedicated option and you'll have all of those in one go. So this is really important when we are dealing with dashboards. So we'll have a similar approach. We'll create different different tables and then their respective the charts right. So this could be handbased. So this is a basic understanding of it. Now we can name it as pivot uh chart. Now comes lookup right. So let me have this table positioned somewhere here. Now what is lookup or why is lookup really important for us? So most of the times just like we had the data here. Let me close this. So most of the times data will not be positioned in such a way that all the details are in one place. So we have a different reason for that that is called data modeling. So in you can understand much about data modeling when we land into SQL, PowerBI and Tableau. So in real times uh let me go back to the same position. So in real times we have separate positions or separate places where we store the data. For example in my sheet you have employees uh data table in one place. You have employee name, employee name, department code. This acts as a foreign key here. So we'll have a briefing on foreign keys and uh primary keys in SQL. So we have department code, we have product code and unit sold. Similarly we have a different table for departments. We have department code and department name and another table for products where we have product code, product name, product uh pricing, right? So the data will be scattered. So each and every data table will be interconnected with each other via data connections or data relations. So these are done via schemas. One of the popular one is star schema. We have a lot other varieties as well and we will have one to one, one to many, many to one, many to many relationships as well. we will go through them in SQL. So basically we will try to store data in different locations or different tables and then person strings and establish a connection with each and every other table right and then we will segregate all of them to one place via relations and perform data analytics now let's take a use case I have a third table where I want to find out uh the employee related information right now if I go through employee data table I find out the department code and the product code right not the actual literal name of the department or the actual literal name product that we sold and what's the sale he made right I don't have that let's take that as a use case and find out what's the department name and product name so we can do that via vookup xookup hookup right a lot of options there but we will go with xookup alone because uh vertical lookup and hookup are a little too obsolete right now. Not completely obsolete, but XLOOKUP is a little a step ahead or advanced. It's a combination of both, right? And let's also discuss some quick limitations. VLOOKUP looks from left to right. Let's say uh the lookup is lookup values in the left position and your result is in the right position, then it looks from left to right. But if it's the other way around, if your lookup is in the left and the result is in the right, a lookup will never work. Similar with H lookup top and down approach but if it is X lookup you just have to give the lookup value and the result value it will give you the result regardless of the positions of the lookup array and the result array. So to save time and have a stronger uh dominating approach in data analysis let's go with XLOOKUP alone. But in case if you want to really explore the lookup functions, we have a dedicated tutorial where we will discuss VLOOKUP, HLOOKUP and XLOOKUP all together and we will have hands-on experience where we'll definitely understand how it works in all the scenarios. Now without much uh ado let's go with XOOKUP. So here I want to find out department name. So I'll write a simple XLOOKUP formula here and I want to provide the lookup array right. So this is my lookup value and where is my lookup array? So this is my lookup array. So uh yeah, we were also supposed to uh have this in my uh table. So let's have it. Yeah, now it makes sense. So I want to find out the department name, right? Also I wanted to have product details. So I'll have this as well. To find out them I need this J. Now to find out the department name I want a lookup value. This is my lookup value and this is my lookup array and this is my result array. There you go. Now you can simply expand this to all the sections and you will have all the department names. So Rahul Sharma belongs to sales and uh he also sold a product right now we want the product details. So similarly x lookup and I want to look for this particular product and this product details are here. So this is the lookup column or lookup array and this is my result array. Hit enter and this is the item. Now similarly if I want to find out the pricing details I'll go to text lookup and this is my lookup array and this is my that was my lookup value. This is my lookup array and this is my result array. Enter. There you go. Now we have a perfectly uh analysis ready data table. So we have Rahu Sharma. he belongs to you know certain department and he belongs to uh or he sold a particular product and this is the revenue he generated right so this is how you use xookup in particular time and apart from that we can now deep dive into aggregate so basically lookups help you segregate all the data from different tables together in one place or you can also fetch for some missing data basically they act like search and get the data and apart from this we can Now deep dive into aggregates. So aggregates help you with some basic mathematical functions like sum uh divide uh total average etc. And apart from that you can also find me in median mode max minimum right. So here we have uh some examples. So for average uh let me remove this. So let's say I want to find out the average pricing. So I'll just write down average of unit price. And there you go. And uh let's say I want to find out total uh revenue that I generated. So in that case I'll use sum function and give this particular array as the input and uh I'll also try to find out maximum uh pricing of a certain product where I'll use max function and now I'll get this particular area find out which is the maximum product. So laptop has been the maximum product so far and if I want to go with the minimum then I'll give it is minimum of this particular array and I'll get the minimum pricing and so on right and so on. So these are the basic aggregate functions that you can use on daily basis and now with that we will make a transition to the next function in our list for today which is analyze. So analyze acts as that one automation tool which is inbuilt in your Excel which gives you the uh pivot tables which it will uh create by itself and you can just have a so analyze does this thing where it automatically creates uh pivot tables for you and calculates the things for you and gives you results in uh a conversation like style. So select the entire data set go to analyze and you will have uh all the pivot tables readily created for you. So there you go. You have sales amount by region, discount sales amount uh and you have a beautiful line and bar graph together. Here you have a bar graph uh which shows you region wise sales and uh time based analysis as well. Apart from this, if you want to have a conversation with your what uh water analysis or analyze data in Excel, let's say I want to find out what's the total amount of sale done by Rahul. So I'll just write down Rahul total sales. It'll give me all the sales which Rahul performed. So he performed sales in the north region. I think he's handling north region. Apart from that if I expand this particular table to an extent then I will have a few more details. So we sold about electronics items category. He sold about one laptop, one phone and one tablet. And yeah, I think he sold two units of laptops, four phones and five tablets and units price and also the revenue that he generated. So that's all about the basic functionalities of Microsoft Excel so far. So with that you have understood what are the main day-to-day applications that you do along with data analytics when you are on Excel. So you'll be performing some data validation, conditional formatting, data cleaning, creating pivot tables, pivot charts, right? And you'll also use some lookup functions like VLOOKUP, HLOOKUP and XLOOKUP. I would recommend you to focus more on XLOOKUP than other two. So because X lookup is totally advanced and it is a combination of both VI and H together and it'll save you a lot of time and the limitations are very less compared to the other two. And apart from that you'll also have some aggregate functions and mathematical functions where you will apply some basic fundamental mathematics subtract, multiply, add, write, divide, etc. and aggregate like maximum amount, minimum amount and uh total revenue to generate average revenue by zone etc. So so far so good we have a comfortable understanding of Excel fundamentals that you will be using on a daily basis. Now we will make a huge transition to the next part where we will have some realtime sales data from the year 2021 to all the way up to 2026 and we will have separate Excel files with that year based and then we will try to uh you know uh add them all to one place and uh do some performance analysis etc. So let's go with it now with that we will close this sheet and this particular sheet will be saved for you. This particular sheet will be saved for you just in case if you want to follow along or if you want to have a deep down on this and I will provide the sheet for you. So that will be saved. Now let's go through the data sets. So u here we have uh sales data from 2021 to all the way up to 2026. Let's have a quick briefing of the data set. So we will be having uh okay let's use that quick shortcut alt hoi to adjust the column width. So we have order ID, we have order date, delivery date, order status. So consider this as a social media marketing analytics. So we have a wide variety of platforms. We have Instagram, Facebook, Snapchat, we will have YouTube and Facebook. So we will be running some campaigns on all those platforms and based on those uh campaign platforms we will have generated some traffic here. So we have about uh so much traffic in the year 2021. And out of so much traffic, we extracted about so many leads. And if you see on my screen, we have so many converted orders and those orders have gone into uh given us some revenue and given us some profits, right? And uh this particular section will be about the uh you know funnel data and uh this could be our orders data and uh this could be our uh customer's data where we have customers ID and uh name of a customer first name second name email of the customer the location state region pin code number and also phone number right so these are the basic things we will be having don't worry we will be also uh performing performing some data cleaning operations here. This particular phone number if you observe we have plus one which is a country code. Along with that we will also have some hyphens. So we will be cleaning that all. And in case if you're wondering can we make that into a country code. Yes we can convert that into a country code based on the country code provided here. Since you're dealing with only the US-based data I think uh the country code is not specifically required here. In case if you want to go ahead with some logical operations, you can also include the logical operations and identify which country code it belongs to and add the relevant country code as well. Now this is the quick walk through of our data set. Now let's proceed uh with the next uh section where we will have a new sheet created and uh remember about the data importing operations that we discussed about we will be importing the data from a folder now. So we have saved all this data in the new DA file. So here we have it. We will make use of the power query in Excel and segregate all the data together. Apply some data cleaning operations and steps and then once the data is ready we will proceed with analytics. Now uh let's go to data and here we will get data from a folder and navigate to the folder. So our folder is in download loops. So new DA and power query is up. So we want to perform some data manipulation here. So we will go with data transformation. So here is our power query. Now you can click on this particular expand button where we will combine and expand the folders all together or the files all together the workbooks we had dealt with. It will take a little time. And now this is your preview. So you will have order ID, order date. So you can already see the order date has been transformed to a different data type. So we will have to do that. And we will also have the customer details. But we will have customer ID, first name, last name combined together. We will have it split. And then if you scroll further, we have the phone numbers. And uh next we have uh the traffic detail or the funnel detail where uh the revenue and profit are not formatted to currency. We will format them to currency. So few changes that we will be doing. So let's click on okay and we will have the power query window opened for us shortly. So there you go. So it has already applied some steps for us. filtered, hidden files, invoke custom function and all those things. In case if you don't want any of these changes to be approved, you can just click on into and that particular change will be taken off from the list. But I'll continue with what we have so far. I want to perform something from my own. So firstly, I don't want this file name here. I'll remove that. And we have audit dates here. So what I'll do is quickly change the type to date. and we will have the DDMM by format and uh apart from that here we have customer details so I'll quickly split this so it's a little more fast in power query so here I'll provide the delimiter and click okay and I'll name this as customer ID and this as uh customer first name and this as customer last name and uh Now we have the phone number. Before you change the data type of phone number, I'd like to make some changes to this. So what do is I'll replace the values. So since I have + one here, I don't want plus one. So I'll just replace this with anything and everything or nothing. Right? And now again I'll replace the values. And here we have hyphen left out nothing again. So now we have proper phone numbers. Then we will deep dive into the last section where we have quantity, traffic, orders. Yeah, this is the one. Now I want to select these two and uh change the data type to currency and uh this is now in dollars format. And I also want to add a new column here which will tell me about the uh cost price. So I'll name this as cost price and I will add revenue into the column and profit into the column. So subtraction of revenue and profit should give me cost to company. So this is the one. I'll also change this type to currency and all good to go. Now let's have a quick briefing. So we have order ID. We have the order date, delivery date, order status, uh platform, customer ID, customer name, last name, email state, region, pin code, door number, phone number, and subcategory, category, product, quantity, traffic, leads, orders, revenue, profit, and cost price. So far so good. Now what I will do is go back to home, close and load the data. So the data will be now loaded and here you have the quick query connections and everything that you wanted in one place and uh here you have the table design. I'd like to go with the basic design what we always used to have and I'll remove the filter. We'll go to filter and sort and clear the filter. And if you come back here you have this in the form of date. uh and I don't want that to be in this format. I'll go to year, month and date format which is SQL friendly for us in future and and everything is in order. Now you can also do some uh formatting if you want but I'm good to go with this and I'll save this as before saving let's go back and remove any other sheets. So this sheet was the original one which we don't want. This is the one which we care about. So we will save this as downloads and in here we have created a new file new da we'll save it as book two and also we will save it in the form of a CSV file because we need CST SQL. There you go. Now it's all good to go. Now what we will do is we will open this particular spreadsheet once again and okay I think I opened the CSV file. We were supposed to open the Excel workbook. Now we can continue with some pivot table analysis. There you go. We can select all the data and now go with uh insert pivot option. So I'll create a new pivot table. So here I want platform wise analysis. So I'll have platform here and the total number of orders that I have received. or the revenue that I have received and it will be here. It'll give me a platform wise analysis and then we will create some pivot table chart which can be this one and leave it here and call it as platform based revenue. Right? And now we'll go back to this particular one. We wi