Excel Pivot Tables - Crash Course | EdSolver Learning | Skillshare

Excel Pivot Tables - Crash Course

EdSolver Learning, Lead with Learning

Excel Pivot Tables - Crash Course

EdSolver Learning, Lead with Learning

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (47m)
    • 1. Data Analytics with Excel Pivot Tables

      0:53
    • 2. Data Analysis and Pivot Tables

      3:10
    • 3. Overview about Store Dataset

      2:16
    • 4. Data Insights

      1:44
    • 5. Sales Profit Summary

      2:38
    • 6. Highlight the Data

      1:26
    • 7. Unprofitable Products

      2:08
    • 8. Create Crosstabs

      1:33
    • 9. Select Regions for Unprofitable Products

      4:20
    • 10. Real Time Analytics and Insights

      2:19
    • 11. Search Data for States with Slicers

      6:09
    • 12. Finding Trends

      1:30
    • 13. Find Data for Specific Date

      3:42
    • 14. Reporting Made Easy

      1:34
    • 15. Generate Reports with Pivot Tables

      4:26
    • 16. Marketing Analytics

      1:43
    • 17. Target your Advertisements

      5:19
    • 18. Become an Analyst

      0:21
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

30

Students

--

Projects

About This Class

Data Analytics is an industry demand skill in 21st century. Excel is the basic software and accessible to all. With pivot tables in Excel, you can analyze any data within short time.

 In this class, you will learn Excel Pivot Tables with a case study like example. This class follows a practical approach and focuses on teaching you how to get data insights and how to make some business decisions on the basis of these findings.

The sample data used in he course is a public data from Tableau Sample Superstore database. After completing this class, you will be able to use pivot tables like a master, using all functionality.

Meet Your Teacher

Teacher Profile Image

EdSolver Learning

Lead with Learning

Teacher

EdSolver Learning is just right platform for YOU, for your professional career development. We emphasis on bridging the academic-industry gap with our courses to make YOU ready for a good career. With us, YOU can improve your knowledge, enhance your skill and become more creative.

We provide training courses in to graduates, post-graduates and working professionals. We have a team of experts to provide YOU industry-demand skills in following areas.

Data Analytics

Computer Software and Systems

Financial Management

Business Management

Enterprise Solutions

Digital Marketing

Creative Designs

And much more......

 

Give yourself a wonderful experience of learning with the best education community. EdSolver Lear... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Data Analytics with Excel Pivot Tables: Hi. I welcome you to this course wherein I am going to teach you that how you can use Excel pivot tables to increase your data analytical skills. In this century, data analytics and data science are considered to be the best career for everyone. But there has to be some starting point from where you need to understand that what are the things that a data analyst has to do. Now in this entire course, we are just going to focus on one particular aspect that is Pivot Tables within Excel. We will see with the help of an example that how you can create, manage and find different data insights with the pivot tables. After this class, you will be able to analyze any kind of data given to you. And you will be able to find out the insights which can help you in making some decisions. I hope that you enjoy this class and you sign up for this class right now. 2. Data Analysis and Pivot Tables: Data analytics is going to be one of the best JOB enter the, for most of the people in the 21st century. It is a kind of green job for everyone. But as you already prepared for that. Well, in this course, we are going to show you with an example that how you can use the basic fundamentals of Excel pivot tables for data analytics. Assume herself in data analysis, who inspires to walk at a departmental store. You get hired and the owner of the store wants to meet you. When you meet him and he discusses something about his business. He wants to get some insights from his data and he wants to make certain decisions for his entire store. For this entire chain store. He asked you that, hey, can you please tell me what is the status about the business? And by looking at the data, can you tell me why profit is not increasing? Well, you tell that, oh, I'm sorry, I have no idea. I need to figure it out. He asked you to prepare a report and present it to within an R or within half an hour. This makes something confusions in your mind. By looking at his data, you find out that there are more than 10 thousand and trees and unit to provide reports and answers to him immediately. Now, these things are included in an everyday tasks of a data analyst. As a data analyst unit to focus on data management, getting some data insights, you need to help the owners in decision-making provide the visualization of the data with the business had unit to forecast the data for the future. And you also need to prepare certain reports and present it to the management and the owners. These are the basic tasks which each and every data analysts do. Now, if you don't know from where you need to start on, this course is just perfect for you. In this course, we are going to focus on Excel pivot tables. Now, these pivot tables help you organize the data and find out various insight from data. And you can even use it to get some specific details about your business. For this entire course, we are going to learn how to use pivot tables, how to modify them, how to get about a particular thing using these pivot tables and how you can generate various reports and submit to the owners. With this saying, we are going to focus on the data of this chain store, which is fun over four years period, and it has more than 10 thousand data entries. Let us start our journey of learning Excel pivot tables and let us try to learn PivotTables and become a masters in the same. 3. Overview about Store Dataset: Hi. Now first of all, let us understand this dataset on which we are going to work throughout this course. This is the dataset of the stool, which is running like a department store and it deals in 70 category of products. Now, this dataset is span over 9,900 entries and we get more than 20 columns to deal with. Let us see which are these columns. The row ID indicates the number of transactions. The second order ID, which is a specific ID for a particular transaction, then we have the ordered it and shipping it. Generally, these two are considered for the businesses and we also need to find that what is the average duration of shipment for each and every product. Then we have different shipping mode, second-class, standard glass, etcetera. Then we have the customer ID and associated customer's name. We're different segment which will do added on the basis of type of customer. That is, whether it is a consumer or a home or office, or a corporate client. Then we have the country. This dataset is all about the customers from United streets. We are the geographical data about city, state, postal code, and region. Then we have some product-specific data which are product ID, main category of product subcategory, and the name of the product. Lastly, we have some numeric data regarding sales, quantity, discounts and proper. Now with the help of these datasets, we are going to summarize and we are going to provide a report to our managers and bosses. In this entire goes. We're going to deal with several problems which are faced by the management and they want us to provide some data insights to them. Soon I'll be ready to use pivot tables and provide some wonderful data insights to the managers. 4. Data Insights: So now it is time that you start capturing some insights from the data available. By data insights, we mean that you are able to mine the data and get some delivered information on the basis of which you can make certain decisions and it becomes profitable to the business. When we talk about data insights, we need to think about following measure things in our mind. Visit thing about getting a detained site is to understand the key properties of your data. A lot of things are hidden behind the last datasets. You are going to find out the key important, relevant things on the basis of which into a business process. The next is to get a detail view. Instead of just a summary, you want to get a detailed view about a particular event, a particular product, and etcetera. And finally, you're going to take a business decision with the help of these data insights. Now, as a data analyst, you are going to start with preparing a short summary for this stored, let us create a summary table. With the help of a pivot table. We will drag certain necessary fields and then we will create a short summary report which we can present to the boss. Remember, you need to make sure that all the basic information's are included in your pivot table and you are able to explain with the help of your dataset. So now with our data, let us start creating or pause pivot table. 5. Sales Profit Summary: So now we are supposed to create and provide the sales profit summary to the managers. Now, when we look at this dataset, we can know that a lot of entries, and we need to summarize the data to make it the kind of people. For that. Instead of using a simple table, we're going to create a pivot table wherein we can use several categories. And we can even summarize the data in an easier way. Totaling up the profit NCEES is not the actual answer to this problem. Now let us try to solve this issue and let us create our first pivot table. How to insert a pivot table? We just need to go to the insert tab. Now from this Insert tab, we need to select on Pivot Table option. When you select the PivotTable option, it automatically opens up the dialogue box. And here we can see that all the data from this sheet is already selected. We just need to click on OK and it will create a blank PivotTable. Now, as part of our requirements, we can drag and drop fields into the pivot table area. Now, as per the requirements, we are supposed to provide them, the summary of profit and sales. For that, we can simply drag the profit tenses in the values. Now this just makes it a total of sales in property, but you also need to classify them into various categories. For that, we will select the column of category and we will drag it to the rows. So this creates a simple summary wherein we get the data as per the category that how much Cs is contributed towards the only Jew office supplies and technology. If we wish to get some more data inside. Along with the categories, we can also drag subcategories below the category. So now we get the subtotals for the categories, as well as for each of the sub category which we are selling. So this creates a small summary record which we can submit to the managers and they can decide upon these figures. 6. Highlight the Data: Well, good job. You have successfully created your first PivotTable. Now you go to your boss and present the data to him. When you go the, the ask a question, hey, which are the unprofitable products in our businesses? Well, while preparing the pivot table, we'll not highlighted the same. That is the first thing that you highlight some important key features from a pivot tables. It leads to your basic rule of highlighting important data. Now this let us, it contains the data of sales, profit, discounts and all the details about customers. So now, as what the store owner has asked us that he wants to know that which are the products which are unprofitable. So it means we need to find out the products which are making losses. For that, we can apply filters in our PivotTable and we can find out the negative values. But there is a better option than using filters is use conditional formatting. Now with the help of conditional formatting, you can highlight the relevant cells with different colors. You all need to do is just prepared a room for your dataset and Excel will automatically highlight that data for you. How to do that, let us find out. 7. Unprofitable Products: All right, so now to improve our profitability, we need to reduce the loss-making sectors from our business. That is why, from this data, we are going to find out that which are the subcategory of products which are making continuous losses for us. Here. Than dataset is small, however, it is a tedious task to manually find out each and every values from this table. Rather than this, we can simply select the entire column of profit. Then we can go to Home tab. Over there. We need to find out the values which are less than 0. It means that these products are making losses and due to that, our overall profitability reduces. Now, let us go to conditional formatting option. Then. We will use the highlighting cell who's in highlightings L rules. We will select the option of less than. So. We wish to highlight all the values which are less than 0. So technically, these are all over losses. We just have to click on hokey. From this, we can find out that the bookcases, tables, and supplies are the three categories which are making continuous losses for us. If we wish to find out. More detail is we can simply add any of the other categories into our filters. Let us see for the regions we want to find out that whether these three categories, our loss-making in each of the regions or not, we can simply drag the region into the filters column. Then we can select any of the region and we can find out the results for the entire dataset. 8. Create Crosstabs: Well-done. The store owner is satisfied with the answers. But now QGIS and he wants to get some more details about his business. He asked that, hey, can you tell me which are the regions where we can still sell this product? Right now, we'll just added the product category and details of sales and profit. But we do not know that whether these products which are making losses at, making losses in all the regions or in specific region only. So for that, we need to set up a new pivot table in which we can find out that whether these products are making losses in all the regions or there are some outliers which are making profit for us. If it is making profit, we're definitely not going to bend those products in that particular area. Now to get this details, we need to step forward and we need to prepare crosstabs. Now generally, crosstabs includes both. It is on Ross as well as columns. Till now, we'll just focus on the product category. In this, we will also add one dimension of the region. Now for this, let us say we will create a kind of cross-tab with multiple dimensions. Then we will apply filters and we will compare the data and we can visually display to the owner that these are the areas where you can still continue selling these products. Let us create our first crosstab. 9. Select Regions for Unprofitable Products: Now we need to create a record and provide delete is that whether these all loss-making sectors should be removed from our product range. Or there are some regions which are giving us profit with these products. Also. For that, one easy way is to use the region into the filters tab. Then we can toggle around for all other regions and we need to manually find out that whether we are making profit losses from this particular product. Let us say for bookcases. Now, manually, we need to filter out the central region. Then we need to confirm that whether the MOOC cases are making profits or losses. Then East region, then sounds. And then we will probably figure out further west region. Now, comparing all those becomes a tedious task. For that. Instead of doing this, you can use another trick. Let us make some changes. Go our PivotTable, we will keep the sum of sales and some of Robert as it is, but for temporary purpose, we will remove the subcategory. Now, we don't need the region in the filters bonds. We will drag the region to the column section. Now, we are just focusing on profit. We can keep the is or we can even remove the seeds from our pivot table to make it a clean looking table to us. Now, this is about a category level, but we need to find out at the subcategory level details. And we need to make sure that the subcategories which are making losses, we wish to remove them from that particular region. We just want the subcategories which are making losses. We can do it with two ways. We can simply select the subcategory and we can drag it to the Filter section. Then we can manually select all this three, which will making losses. But the better way is not directly use it in the filters. From this option. We can apply a value of return. Now, valuable TO will generally include only those subcategories which have led specific value. So for this case, we want a value filter. And we want to include the subcategories which are actually less, then 0. So for all the subcategories which held the profit less than 0, only those sub categories will be included in our pivot table. Then we just need to click on OK. Now that filter is applied to the field, but we are not included subcategory anywhere right now. Now let us let this up category into the rows section. And here we can see that only bookcases, tables, and supplies at selected. From this, we can see that most of the things are showing us the negative values. However, particularly bookcases in South Region and tables and supplies in west region are giving us some profits. So from this, we can conclude that we should discontinue the sales of MOOC cases, tables, and supplies. However, we should keep these products, particularly in the respective region. We should keep selling the book. This is in the south region. And we should keep selling tables and supplies in the west region to make us profitable business. 10. Real Time Analytics and Insights: Now you know how to create simple Pivot Tables. Now let us talk about some real-time analytics. By real-time analytics, we mean that you should be able to toggle around any of the object and you should be able to provide the answers to the owner at the time. It's. Now this is possible with pivot tables by adding some more features that we are going to see right now. For real-time analytics, we need to understand that which are some interactive elements which we can associate with our pivot table, like slicers and timers. Now these are generally used to drill down or to focus on some granular level of details. For example, for our entire business, if we wish to find out the sales of a particular city or even a particular customers. For that. Apart from filtering, we can use some information for realtime analogies, like slicers. In slicer, we can navigate to any of the option quickly and we can find out our answers from the pivot table itself. After you have provided all the details to the owner. Now it gives you another task. Well, now we ask you to prepare state wise import. Now for this, first of all, we are going to use Slicer. Now slicer will basically divide all of your data into different categories. You can select on any of the option and the display area of PivotTable will replicate the lead taste of that particular option. Only. For this, we need to create a slicer for states so that we can select any of the state. And in the pivot table display, we get the relevant values of that particular state. Now regarding slicers, we will see how we can use it effectively for navigation. How we can apply multiple slicers to our data so that we can get some granular details for a particular state, or even with multiple options for that particular state. Let us use these slicers to provide answers to any kind of questions of owner. 11. Search Data for States with Slicers: Okay, so now you want to present your data insights to your managers. But there has on questions and they want that you analyze all the data on the basis of each of the state and you present it to them. So for that, you need to create a kind of interactive pivot table so that you can answer all the queries of your manager. Now for this, we generally use slicers and filters. Let us create a new pivot table with the same data. For that, we will go to Insert tab and we will select on pivot table. Then we just need to click on OK. Now this time, we will include the quantity of sales and profit in our values. Before that, let us drag the category of product, ends up category to the row. Now quickly, we can insert the quantities, sales and the profit that we own. Now, we wish to analyze it on each of this T. One way to do it is to select a state and drag it to the Filter step. But when we drag it to the Filter step, it becomes too much difficult to navigate all the time and present the same you are managers. So for that, instead of filters, we can create a slicer. While creating a slicer, we need to go the Analyze option under the pivot table tools. And we can select on Insert Slicer. From this, we can select any of the field on which we want to create a slicer. For this case, we are going to select the state. Now this is again a scrolling slicer and it could be difficult. So for that, let us increase the number of columns and resize our Slicer option. So this looks clean and easy one. So now if you are presenting your dataset and the manager wish to know that what are the sales of a particular product in any of these states? Let us say for California, we just need to click on California and all the relevant data to that particular state will be displayed. Let us talk about Florida. Here we can see that most of our products are making losses. So we need to make sure that we highlight these points to the person so that they can make correct decisions on the basis of this data. Let us talk about Georgia. In this case, we are almost making profit in each of the product category. So we can see that compared to Florida, Georgia IS a profitable state. Let us see. We want to see the combined sales and profit off California and Georgia. For that, we need to enable the multiple select option. Now, whichever states we will select, the sum of those states will be displayed in our pivot table. Let us say, along with Georgia and California, we also wish to add details about Indiana. We just need to click on Indiana and the summary of sales, profit and quantity of dead state will be added. And the pivot table will show that totals of all these three states. If we wish to clear this out and we wish to get back the details of entire country, we just need to peel the filtering option from this. Now, we can create multiple slicers as well. Let us go back to our Pivot Table. In the analyses section, we'll insert when most slicer. Let us see we wish to add a slicer on this segment. Then we will click on OK. Now we have three segment, consumer, corporate, and homophilous. What if your manager wants to know that? What is the sales or consumer products in California? All you need to do is just select on consumer and select on California from these states option. Now, this I have the sales and profit of consumer products in California. In this way, you can even add up multiple slicer and provide any result or any answers to the managers. This also makes it very easy to find out even the minute details which you cannot simply find out from these kind of large, let us. So this technique is very useful when you are going to drill down to the bottom level and you want to find out some specific details from your dataset. 12. Finding Trends: After seeing your state wise presentation, the owner is curious to know the recent trends in sales. He calls you and he asked that, hey, what about our last year's performance? How much says we generated or whether the EEOC in any kind of increasing profit or not. Now, to explain these kinds of trends, we can use a date timeline. Now this is almost similar to slicers and filters, but this is specifically used for the date element. It is only useful if you are having any column of date in your data. You also need to make sure that it is sorted and saved in the format of the date. With the help of this, you can create a timeline. You get different options for different months, quarters and yours, and even a particular day. You can find out the detail of any single day of the past data with the help of just a few clicks on these date timeline. Now with this data, let us create one date timeline and then we are going to select any date for any date range. As for the questions asked by our owner, we will be able to provide an answer to any question regarding any of the events of the past. Let us create the date timeline. 13. Find Data for Specific Date: So now we are ready with their interactive less road. But what if someone asked you that, hey, tell me the series of furniture in California in the your 215 or hey, I want to know that how what c is we generated in the month of May in 2017? Well, for that, along with the slides, so you can even put a date timeline into our dashboard. Now for that, we need to select anything in our pivot table. Then we get the option of inserting a timeline. Now, the timeline will be only applied if there is any kind of date column in your dataset. It means that if you inserted the date, but you have not kept it in a proper date format, it won't recognize this. Now, in this case, we already have two columns, the Order Date and shipping need. For this. Let us select our order date because that will be considered as the date of scenes. And we wish to know that in a particular month or in a particular year, how much sales field generated. Then we will click on OK. Now this will create a date timeline which you can use and navigate to any data point. Let us see. You just wish to find out the c is of November 2019. You can select the option of months from this option, and you can drag it to the November 2019. So now whatever data is displayed in your pivot table are the sales and profit of November 2019. What if you just wish to find out the data of June 2017 til June 2019, you can do that as well. You need to drag this the beginning point to the June 2017. And from June 2017, we are going to drag the ending point around June 2019. So now this are the total of sales and profit off our company right? From June 2017 til June 290. It is not so that you can only do it for months for a broader prospect. We can even convert it to yours. Now let us clear the filter and we just select on the year 2019, we can select any of the your or combination in the data series. So in this way, you can even find out the answers from the data which are useful to you and which you can apply for making a proper business decision. With this date timeline, you can find out the data on the business of yours, maybe quarters, months, and even days. You just wish to find out the c is in the last five days, you can't even do that. So this will provide a complete control who your dashboard, and you can find out any kind of details from your data. 14. Reporting Made Easy: So now we have provided all the answers with some interactive pivot tables. But now the owner asked you to prepare a report for each and every customer of the entire business. You can assume that for such a large dataset, preparing a report for each and every customer is a daunting task. And it is obvious that you will get frustrated that upper All those, why you need to create a record for every customer? Well, if you think that it is going to be difficult than you are actually wrong. Now, creating reports with the help of pivot tables is very easy. The reason is that however larger dataset is you can create any kind of reports just with the help of few plagues. Now to create reports with the help of pivot tables, all you need to do is just two steps. Pick up any of the field and drag it to the filters, and then simply click on the report filter basis. So for this case, we are going to prepare the report for each and every customer's. It is a task of analysis to find out that weather each of the customers are profitable to us or not. But to do so, we need to create different Excel sheets so that we can get an account summary of a particular customer. Now let us see how we can do it with pivot tables with just a few clicks. 15. Generate Reports with Pivot Tables: Hi, generating reports is one of the very important aspect of flow data analytics. Usually you get the data in bulk, but you wish to get the summaries as well as you also wish to create some reports out of this. Let us see from this last dataset, you wish to create the reports for each of your customers. And you wish to find out that each of your customers has what proportion of sales into your business, how much profit do on from that particular customer, and what quantity of products they buy from you. Just imagine how difficult it is to find out, sort, and prepare a report on the basis of this. But with the help of pivot tables, that is one amazing feature which you can use to create reports with just a few clicks. For that, let us our new pivot table. And in that, we are going to create the customer reports. We will begin with the insert tab. In that we will click on pivot table and let us click on OK. Now, for the details regarding customers, we wish to know that what are the quantity of products purchased from us? Which product they are buying from us? What is the amount of sales and how much profit do we earn from that particular customer? So for that, let us drag the product name into a rose. A lot of products. Let us drag Sales, profit, and quantity of products that they buy from us. So these are the basic details. Now, we wish to create this kind of table for a particular customer and we wanted for all our customers. We will simply drag the details of customer ID or customer name and we can drag it to filters. Let us say we want to prepare a report and we want the name of all the customers in our files. We will select the Customer Name and drag it to filters. You do not need to manually filter each and every one of them, like selecting one. Then you can copy the same independent IPO. But instead of doing this manually, you can do it all at once. All you need to do is move to the Pivot Table Options from this unit to select on show report filter bases. So whichever variable you have kept in the filters, it will create a different phase for each of the values given in that variable. So in this case we have our customer names. So when we click on this option, we can select the Customer Name and we just need to click on ok. So now this will take a little bit of time, but afterwards, we will be able to see the details of all of our customers separately like this. Just in a matter of few seconds, we get all this sheets which indicates the details of your customers and what are the products that they purchased from you. You can find out for each customer's that whether that particular customer is profitable for our business purpose or not. You can even take out the pins if you are maintaining the accounts of each of the customer and with the help of just one click, you can create the reports with the help of pivot tables. 16. Marketing Analytics: So now we are ready to use pivot tables as in when you need. So are you ready for our next task? So now the owner request you to help him in some marketing decisions when you are actually not directly link with the marketing department. But there are some decisions which needs to be based on the past data. The team is planning to create some advertisements for consumer segment and the owner wants your help in identifying that which region should the target to get more returns. Doing that with pivot tables is going to be interesting tasks because for those, you're going to learn some new amazing features. Now let us talk about some value calculations. So far, whatever we have seen, we'll just simply drag and drop all the values over pivot tables and met some necessary changes in our pivot table. But what if we need the values in some different formats? That can be done with the help of pivot tables. Now with the help of value calculations, we are going to find the insights about the market share. We already have the data of sales and we're going to convert it to other values so that we can easily compare that, which is the region which is grabbing the maximum market share. And we're going to use this to justify our decisions. That owner should accept our decision, which will give him better profits and returns. Now let us focus on our pivot tables and create some amazing stuff with the help of value calculations. 17. Target your Advertisements: Okay, so now you want to know that we are going to be the regions where you should make some new advertisements to attract their customers from consumer segment goods. Now, for this, you need to select certain parameters from these datasets and unit to find out that whether you are getting some good proportion of Cs or profit or item sold from that particular region. For this, let us insert a new pivot table and let us start analyzing it. We will insert a Blank pivot table. In this table, we are going to drag regions and segment in rows and columns. So let us select the segment and drag it to the column. Now let us select the region and drag it to the rows. Now, let us try out with the data of sales. We will select the data of sales and we will drag it to the values. Now, these are the huge numbers and you cannot easily identified that whether you are getting some sizeable proportion from that particular market or not. Now to know that you need to change the properties of the data. For that, we will select this sum of sales and we will convert it to a percentage form. We will go to value-based settings from this unit to click on Show Values As instead of simply showing the sum of sales, we will convert it to the person days of grand goto. Now what this will make by this and ten sales amount will be converted into percentages. And you will be able to know that what percent of your entire sales comes from that particular region. For the calculation purpose, let us select the percentage of grand total and we will click on OK. So now this is completely simplified and we can easily find out that which are the good regions to target your advertisements right? Now let us compare. We want to target consumer segment. For the central region. We have somewhere around 11% of our sales of our store. Then from the east region meal, almost 15%, 9 percent from the South Region, and almost 16% from the west region. Now there are some confusions because all the regions are having almost similar or near to each other numbers, it is quite difficult to make a proper decision and target a particular region for your advertisements. Now, along with sales, you can even analyze the values of properties. Let us drag the profits go or values. Again, we will do the scene. We will convert these numbers and we will add one calculation. And that will be the percent of grand total. So now here we can see that from all of our proffered, almost 20% of Robert comes from the consumer segment from west region. So this is very much a sizable proportion to make a clear decision. When we compare it to other regions. The South region is merely at 9.40%. East region is again sizable of 2.38, 14%, but it is not as good as vest region. When we talk about central region, it is very low, which is only 3%. So now we are talking about advertisements, which ethic cost. We are also going to hookers that if we are incurring some cost, you also wish to own some profit out of it. So instead of deciding on the basis of sales, you can decide on the basis of profit. Out of the entire profit of a company, you get almost 20% of profit from the consumer segment from west region. So your advertisement should be more focused to the people of west region to attract more and more customers by different ways. 18. Become an Analyst: Well then, now the store is really impressed with your skills and he has got a lot of help from your analytical skills. I hope that you have learned every aspect of using pivot tables for data analysis. This is going to be useful for you in your entire journey as an analyst.