Excel Data Analytics Tutorial 2026 | Essential Skills For Data Analysis In Excel | Simplilearn

Key Takeaways

[Music] Hi there, welcome to simply learn. Imagine hundreds of thousands of rows of complex data and very little time to extract the detailed insights. Imagine a tool that helps you break down this complex problem into simple steps and not just help you to extract the insights but also give you interactive and responsive realtime dashboards with detailed visualizations. Yes, you guessed it right. We are talking about one such basic tool for data analytics and that is none other than Microsoft Excel. 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 so that you get notified whenever we host. Also, just that you know, if you want to upskill yourself and master data science and data analytics to land in your dream job or grow in your career, then you must explore Simple Learn's cohort of various data science and data analytics programs. Simply learn offers various certifications, masters and post-graduate programs in collaboration with worldleading universities. For example, the post-graduate program with simply learn from Purdue University offers you live sessions with latest AI trends such as generative AI, prompt engineering, explainable AI and job assess will help you get noticed by top hiring companies, hands-on projects across the data analytics life cycle with generative AI and a lot more. Through our courses, you will gain knowledge and work ready expertise in skills like data ethics, data analytics, SQL, Python, Tableau, PowerBI, and generative AI. And along with that, more than 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 a data analytics role as a fresher or moved on a higher paying job and profile. If you are passionate in taking your first step in building a successful career in this domain, then make sure to check out the link in the pinned comment and description box below to find a data science and data analytics program that fits your experience and areas of interest. So without further delay, let's get started with the agenda for today's session. In today's video, we will be learning about Excel fundamentals. We will have a brief introduction to data analytics and what are the various types of data analytics. Then we will find the relevance of Excel in terms of today's data analytics. Hi. Next, we will have a quick overview of Excel interface and then we will directly jump into the sales analytics project with Excel. So, I hope I made myself clear with the agenda. Now, let's get started. Before that, let's have a small quiz. Which of the following is used in Excel to find a matching value? And your options are sum if function, VLOOKUP function, count if function, and lastly, the concat function. If you know the answer, please let us know in the comment section below. So, one thing is for sure, the humble Microsoft Excel still stands the most trusted tools for data analysis. Even in the age of AI and advanced analytics platforms, that's a tough competition. So, let's get started. What is data analytics and what is its purpose? So, let's start with a simple question. You have some data and you need to extract insights and that's the purpose of data analytics for basic. At its core, data analytics is a detailed process of examining raw data to uncover insights, patterns, and trends that support better decision making. Whether it's tracking sales performance, understanding customer behavior, or predicting the future demands, data analytics helps transform numbers into actionable intelligence. The purpose of data analytics is not just to collect data, but to make some sense out of it. So to turn confusion into clarity and help organizations make smarter datadriven decisions, data analytics is a key step. Now let's dive into the types of data analysis. Now data analytics isn't one sizefits all. It comes in four major types each answering a different question. So let's go through them. Firstly, descriptive analytics. So this is basically about what happened and this is the foundation. It looks at historical data like sales reports or monthly expenses. To summarize, it's like it looks at what already has occurred and finished. Now comes the second type diagnostic analytics. So this basically deals with why did it happen. Here we dive deeper. Diagnostic analytics helps us find causes behind the trends. For example, why sales drop or why did a campaign underperform? Now comes the third type, predictive analytics. This deals with what might happen next. Using statistical models and forecasting techniques, predictive analytics helps anticipate future trends such as predicting the next quarter sales or customer churn. Now comes the last type, prescriptive analytics. What can we do about it is its question. Finally, prescriptive analytics goes one step further. It recommends possible actions. It helps answer given what we know. What should we do next? Think of it as moving from insight to an action. So that was about the types of data analytics. Now why Excel is still relevant for data analysis. Now you might be wondering with so many modern tools like PowerBI, Python, Tableau along with the power of generative AI, why is Excel still relevant for data analysis? Well, the answer lies in its power to balance the simplicity and power. So the first one, the first reason, accessibility and ease of use. Excel is one of the most widely available tools on the planet. Anyone with basic computer knowledge can start analyzing data. No coding required. It's userfriendly, intuitive, and perfect for quick insights. Now the second reason powerful analytical capabilities behind this simple interface lies a powerhouse Excel supports formulas pivot tables charts conditional formatting power query and even builtin AI tools. You can clean transform and visualize data seamlessly. Now comes your third reason integration and compatibility. Excel easily connects with other tools like databases, PowerBI, Google Sheets or even APIs making it greater bridge between the basic and advanced analytics. Now comes your fourth reason real world use cases across industries from finance to HR from sales to marketing professionals still rely on Excel for quick exploratory analysis reporting and visualization. For instance, a marketing analyst might use Excel to analyze and performance or calculate return on investment before presenting the findings to the stakeholders. In short, Excel remains the Swiss army knife of data analytics. Simple enough for beginners yet powerful enough for professionals. So that was your quick introduction to data analytics and why Excel continues to be a corner store for analysts around the world. In the next module, we will dive into Excel interface and explore the key functions that make it such a reliable tool for any datadriven professional. So, some of the key functions are trim function, clean function, text function, average function, median, standard deviation, index match, lookup and a lot more. So, we will be exploring each of these in detail in a demonstration session. Now that we have a basic understanding, let's get going to the practical demonstration that will not just introduce you to the basic formulas of data cleaning and transformation, but also will help you build a fullyfledged interactive sales dashboard. So let's get going. So now we are on the demonstration module for Excel data analytics. So let's go through the data first. As you can see on my screen, we have a simple sales data set. We have order ID, order date, delivery date, customer ID and his details. And followed by that we have the location details like state, region of which state are we delivering to and the product details from which category does it belong to and what is the specific product that they have ordered and apart from that here we have some traffic, price and sales related data set. Right? So this is the data set that we will be working today. This is as simple as it looks like. Now let's come up with some basic Excel formulas or functions that you will be applying on a daily basis in case of a data analysis project. So here you can see we have a customer name. Okay, let's do an example so that I can show you how it looks like. So here we will be applying a simple and application. So here what we have did is we have combined the first name and the second name together to replicate a scenario where you will find the full name of the customer and you will have to split it. Right? So at this scenario what you do is you just select the entire column and go to the data section here and then you go to the option text to columns. So just go to the delimited option. By default you have a tab option here but instead of tab we have space. So you just need to select the space and then select on next. And here if you want some data formatting done you can also proceed with that. So far it's a text one. So I'd go with general and then finish it off. So data is already there. Do you want to replace it for now? Yes. Right. So here we have the first name and the last name together. So we will not need this anymore. So we'll delete it. So we have the data sorted here. Now apart from that if you would like to go ahead with some data formatting issues. Let's say here we have the date format in custom. But in case if you wanted to have that in a dedicated uh date format, you can also do with that. And apart from that let's go through the ribbons here. Apart from that, here we have the home section where you can select the specific font, size of the text, alignment in center, right or left. And here you have the data type selection. You can modify your data types. And here you can see in the styles section, you have conditional formatting which we will be exploring soon. And you can also insert new cells, delete existing ones or do some formatting to them. And here you have some draw page layouts. So if you're printing a page then you can just uh go ahead with the page layout and find out the best margins suitable for your data and adjust them based on this particular height width and scale and then print it. And here you have the formula sections where you have the aggregate functions like sum, average, count and next you have the data tab where you can apply some advanced filters. We have already explored text to columns and you have data validation which we'll be exploring further and then what of analysis you already have a dedicated video on the simply learn channel you can explore that as well and then apart from excel data if you want to import data from a database cloud storage or any other sources you can proceed with this so here we have an option for web as well if you want to connect your website data to the Excel workbook that you're working with you can just go ahead and apply the website URL in this particular location and you're ready to export the data from your website and import it on the Excel workbook you're working with. Then you have the review bar here. We can just check the spell checks and everything. And here you have the Power Pivot window as well. So we'll be exploring Power Pivot as well. Now going back to the home section. So let's add some custom formulations. So here let's apply a if condition and the logical test will be if this particular quantity is greater than five then the value will be a premium order else it will be a regular order. Now hit enter and there you find the premium orders and regular orders. So this was one of the logical functions that we will be using and apart from that you can also do some data lookup functions. Let's say we have the customer data here. Let me add the customer data to a next tab. Let me paste it here. And I want to know which particular product have they ordered. So let me place the product here and expand this a little. So I don't know which product they have ordered. So what I'll do is I'll apply a simple and then provide the lookup array that I want to look in and the return product or return array which is a product and enter. And now I know what product have they ordered. If I expand this, I'll have the details for all the customers. So this is how you will use the lookup functions in a requirement as well. So let's add uh boxes for this as well and go back. Now we've explored lookup functions. Now I want to apply formatting, right? Let's go to conditional formatting and I want to highlight or let's go with the product column, quantity column. So I want to highlight each and every cell that has a premium order. So I'd go with a simple uh condition here which is greater than five. Then fill it with green color and then press okay. Now what I can do is I can sort or I can filter out the premium products here using the data function and I can apply a filter and modify the filter to filter it by color. So wherever there is a green color select them only. So here I have filtered out all the premium orders. Now you can eliminate the filter if you don't want it. clear from the quantity and you can totally eliminate the conditional formatting as well. So go back to home and conditional formatting and you can just clear all the rules from the entire sheet and you'll have the normal sheet back again. So now let's also explore the function called data validation. So let me add order type or let's let's choose a new column al together. So here is a list of unique elements. These are the single entries and here let's try to add data validation. So I'll select data validation and here instead of any value I'll choose a list and then you must enter the source. So go to this particular sheet and select the source. Apply okay and your data validation is ready. You can just select from the list. So apart from this you can also choose any value whole number decimal number date time and custom data types as well. Then you can also throw up some error saying there is an invalid entry or you have chosen a wrong value. So that's how data validation works in real time. Now let me delete this and delete this as well. Now here let's explore the sorting option. So what you can do is let's say you have irregularly organized data and you can either organize it from the largest to smallest or smallest to largest. So that's one option you can explore. Now let's go to our pivot or pivot tables. So let's say I want to install a pivot table. So let me delete this sheet. I don't want this one. Now we have the entire data set ready. We'll select the entire data and choose the power pivot or you can just go to home insert pivot table from the provided range in the new worksheet or in the existing worksheet. So I'll go with a new worksheet. Now here let's say I wanted to find out statewise sales. So what I'll do is drag and drop state into the rows and then sales into values. So you can see automatically it has added an aggregate function and summarized the total sales happening in each state. So this is one of the data tables or the power pivot tables that we will be using for our dashboard stay sales state based ones. Let's go back to the original sheet and insert another pivot table. Again go back to the new table product level sales and to the home again and lastly the region wise sales. Now let's try to insert some pivot charts. So let's select the data and go to pivot table analyze and here we have the pivot chart and I'll go with a bar graph. Okay, you can also customize this one state level sales. This is category level. Now we will change the title to category level sales. Done this one. So go to pivot table analyze and then I would go with a donut chart and I'll name it as product level. This is region level analysis for sales. And lastly we will use this particular one pivot chart and we will be using a line graph. So small changes. So we can name it as month-on-month sales. Now what we will do is we will insert a new sheet and call this as dashboard. And here we will use all the pivot tables and pivot charts and add them onto the dashboard. First let's me let me add it here. So our dashboard is completely ready. But is it complete? No. It lacks interactivity. So to add interactivity to our dashboard, what we will be doing is we'll take the help of slices. As we discussed before, we'll be adding slices from this option. But before that, let's go to home and combine all this tables and add a name to it as sales dashboard. Add a background to this particular one. And you can change the color text. That looks fine. Now, what's missing is the slices. So, we will shorten this particular visualization on this one to make room for our slices. There you go. Now, we will go to the pivot analyze and here we will insert slices. So what we need is a state slicer, region slicer, category slicer and product slicer and enter them. So uh here we have added all the slicer we need. Now what we will do is adjust these sizes. That's fine. Now arrange them into the dashboard. That's our first slicer. This is our second slicer. Third and the last one. So we have adjusted all the slices but still we have a problem. If I select product it will just show me the product related details but there is no change no interactivity in the entire dashboard. So the problem is the connection. So now for that what you need to do is select report connection option by right clicking the dashboard or the slicer dashboard and then select all the pivot tables so that there is a connectivity between them. So similarly you need to report connections to all the other slices available. That'll take a moment. There you go. So all the data visualizations and slices are now interconnected. So whenever you select any of the options available, the entire dashboard will reflect the changes. For example, if I select the region east, it will show me all the eastern regions of all the states and all the products and the categories sold on month-on-mon basis that happened in that particular event. Now this is how you work on Excel data analysis. And with that we have reached to the end of this session on Excel data analytics tutorial for beginners. Should you need any assistance or any other resources used in the session like PowerPoint presentation or the data set or the code documentations then please do let us know in the comment section below and our team of experts will be happy to assist you at the earliest. Until next time, thank you for watching and stay tuned for more from Simply Learn.