Pivot Tables Crash Course #pivottables #exceltutorial #exceldashboard

hello everyone and welcome to this crash course on pivot tables pivot tables are one of the most powerful Tools in Microsoft Excel but many Excel users don't know how to unlock its full potential the aim of this course is to ensure you're not one of them this course takes you from Basics to advance with pivot table functionalities let's have a sneak peek into what's inside this course we will start by studying why do we need pivot tables next we'll get our hands dirty and set up our first few pivot tables we will learn about slicers and timelines how to disconnect and reconnect slicers we will analyze what to do when our source data changes we will see how to customize our tables and after that we will get a bit analytical and learn about summary calculation sorting and filtering the data calculated Fields calculated items and so much more in the end we will set up an interactive dashboard using nothing but pivot tables this is the only course you need to learn this powerful feature from A to Z so I hope you're excited and I look forward to seeing you to start off let's understand why do you need pivot tables from the beginning imagine you have following data set it shows sales of a fictional Superstore from the year 2014 to 2017. let's say your manager has asked you to summarize this data to display sales by region now one way to do that is to set up a new summary table write the names of regions one by one and then create the required analysis mainly using the sumiformula this approach is quite time consuming furthermore it's also not the most adaptable one for example if your manager later on asks you to remove the region and show the sales by category you will need to change the row headers and then reconfigure the formula that will take up a lot of your time an efficient way to achieve this is to use pivot tables with a pivot table you can complete the same summary with a click of view buttons plus it's more versatile if you get a request to make some changes tomorrow you can process that very efficiently now let's get our hands dirty with this fantastic tool we will start with A Simple and Clean data set that is the superstore data set we just saw to set up a pivot table select the entire data by pressing Ctrl a then click on the insert tab then pivot table and select from table range alternatively you can also use the keyboard shortcut alt nvt after you have selected the entire data set once you do this a screen will appear asking you where you want to place your pivot table you can go with the new worksheet or provide a location of your choice within an existing worksheet I will go with the new worksheet for now once you click OK Excel will add a new sheet that contains an empty pivot table on the right hand side you will see a pivot table Fields pane this pane will help you build your pivot table a table has four areas the rows area shows the headings that go downwards on the left side of the pivot table The Columns area shows the headings that stretch across the top of the columns the values area is the bottom right portion that contains values and the filter is the drop down field we have at the top dropping a field in this area will enable you to filter your data based on that field note that you will also see these four areas in the pivot table Fields pane at the top of the pane you will see the field list this area shows the headers of all the columns in your data set you can set up your pivot table by dragging the fields from the field this to any of the areas but how do you decide which fields to drag in which areas start by answering what you want to measure and how you want to see it let's assume our manager wants to see sales by region and category such that region names are along the row headers and category names are across the top of columns and sales values are inside the table for that I will drag the region to the rows area category to the columns area and sales to the values and voila we have our pivot table ready now let's say you've been asked by your boss that in addition to region he also wants to see sales by state all you need to do is drag the State field in the rows area right under the region and there you go we have the analysis ready on let's say he says that instead of categories in the column he wants to see sales by year well not a problem you will just track the category out of the column area and put the date field which is in our case the order date into the column area now imagine doing all of this with manual formulas that will be a nightmare this example explains how pivot tables can make life much easier let's explore some more features of pivot table these are some things which are not even possible if you make summaries manually let's say your boss is back and now he's saying that he wants to see sales by region and here but only for the furniture category well thank God we have pivot table otherwise this would have been quite difficult you will first create a sales by region table like we did before in current situation all you need to do is drag the State field out of the Rose area and there we go we have the sales by region table now you will drag the category field into the filter area you will notice that a drop down option will appear just above the pivot table in this drop down you will select the furniture option and then your report will be filtered only for the furniture category likewise you can select any other category that you like on a side note you can change the format of numbers by right-clicking anywhere in the table go to number format and then select format of your choice I will go with simple number format and add a thousand separator returning to filters you can filter your report via another feature called slicer first I will remove the category field from the filter area then right click the category field in the field list and select add a slicer this will give me a slicer with buttons for each category in the data set now I can select any category or combination of categories from the slicer to filter my pivot table so what is the difference between slicer and a filter slicer can impact multiple pivot tables whereas a filter will only affect a particular pivot table let's understand this with the help of an example assume your manager now wants to see two tables one showing sales by region and another separate table showing sales by subcategory and he wants both of these tables to be filtered only for the furniture category let's start with a fresh sheet for this I will first set up a table that shows sales by region and year this time I will put my pivot table on an existing worksheet as I already have an empty sheet available I will drag the region field to the rows Area Sales to the values area and ordered it to the columns area I will name this table as sales by region as a good practice always name your pivot tables as soon as you create them this will help you in the long run next I will add category field as a slicer to do this I will right click the category field and select add a slicer I will click on a few buttons to make sure the slicer is working and it seems like yes it is now I will create a copy of this pivot table in the second pivot table I will remove the region field and replace it with the subcategory field I will name this pivot table as sales by subcategory now you will see that when I click on furniture in the slicer it will filter both the tables for the furniture category this is not possible if you use the filter areas if you put a field in a filter area of any pivot table it will only impact that particular table so does this mean that whenever you put in a slicer it will impact all the pivot tables you have in your workbook no you can actually disconnect a slicer from a particular pivot table let's say you have now been asked that in addition to the two existing tables your manager wants to see another table showing sales by ship mode this third table should not be filtered for any category it should always show the total number well yes we can do that too I will create a third pivot table and put ship mode in the rows area of that table I will name this table as sales by ship mode now you'll see that by default the slicer will affect all the three tables I want to disconnect this slicer from the sales by ship mode table so that it only impacts the first two tables and not the third one to do that I will select the slicer click on the slicer Tab and then click on report connections this will show me all the pivot tables to which my slicer is connected I will uncheck the sales by ship mode table and then click on OK now when I click on any button in the slicer it will impact only the first two tables and not the third one there is another way to achieve the same thing let's say I also want to disconnect the second table from my slicer one way which we already saw is to go to the slicer Tab and then filter the report connections second option is to click anywhere in the pivot table you want to disconnect go to pivot table analyze and then click on report connections this will show you list of all the slicers to which your pivot table is connected right now our pivot table is connected to the category slicer I will uncheck it and click on OK now when I operate the slicer it will only impact the first pivot table this is an excellent feature of pivot tables and can help you create some great dashboards if you want to learn how to create an interactive dashboard in five simple steps using this feature you can check out my video by clicking in the link which should appear at the top right and if the link doesn't appear I'll put the link in description anyway let's continue our discussion on slicers slicers are of two types first is the standard one which we just saw second is the timeline slicer timeline slicer you can say is a slicer for date values therefore you need at least one date field in your data set to work with this I have this pivot table I have the region field in the rows area the order date in the columns area and the sales and the values area the procedure to add a timeline is the same as the slicer I will right click on the date field and in this case it's the order date and add it as a timeline this will give me a timeline slicer now I can filter my data for any date range I like you can say the timeline is a slicer but in a visually more appealing format next we will talk about data source changes let's assume you have created this pivot table it shows sales by region and month with region field in the rows area order date in the columns area and sales in the values area everything is good but all of a sudden someone shows up and tells you that this data on which you have created a pivot table is no longer valid and there are some updates to this Source data then what will you do you need to ensure your pivot table is most up-to-date how you will handle this situation will depend upon what type of update is it is there an addition of new rows and columns or there is just an update to some numbers within the same range let's assume there is an update to a number within the same range for example you are told that the sales number in the first row should be 10 million dollars rather than just 261. that's the case then updating the pivot table is very simple just go to any cell in your pivot table right click and select refresh and your number should get updated I will press Ctrl Z to go back to original position now let's say you've been told that there is some additional data available your current data is only up until row 9994 and now there is an addition of few more rows such that your data has expanded to row number one zero zero zero six in this case to update your pivot table you will need to go to pivot table analyze and select change data source Also let's keep an eye on the total number as this will help us ensure if our pivot table has been updated once you click on change data source a window will pop up showing the range captured by your pivot table currently it's only capturing up until row 9995 we will modify this range to capture the data up until row 1006. once we click on OK your pivot table should get updated as you can see the total value of the pivot table has changed next we will talk about customizing the pivot table let's start with the formatting I have a pivot table with subcategories in the rows area audio date in the columns area and sales in the values area to change the overall appearance of your pivot table select any cell within your pivot table then go to design tab over there under the pivot table style section you will see many options to choose from select the option you like and your pivot table will change accordingly now let's say you do not like any Styles available by default this seeking off like I would truly appreciate if you could like this video And subscribe to my channel coming back let's say you aren't happy with any of the default Styles then no problem you can create one of your own to do that you will first need to duplicate any of the existing stalls right click on any of the Styles and select duplicate give it a name of your choice I will give it a name of our pivot Style now you will see that a new style would have been created under the pivot table style section you can right click on that style and select modify here you can do as many modifications as you want let's say I want the font color of my row headers to be orange for that I will go to header row click on format and there I can change the font color to Orange and then click on OK you will see that nothing happens to my pivot table this is because the table is still on the default style to give my table the new style I created I will again select any cell within my pivot table go to design and select the custom style I just made you will see that the header row is now in the orange font I will again right click on the customer style and select modify you can see there are multitude of options available here you can change the style as much as you want I won't be able to go through each and every option since this is a crash course but I suggest you try out these options yourself they're pretty self-explanatory if you still need help to design the table in a formatting of your choice please let me know in the comment section or message me on my Instagram and I'll try to help you out as soon as possible similarly the formatting process for the slicer works the same way I will go back to default formatting by pressing Ctrl Z I will add category field as a slicer to this table with the slicer selected when I go to the slicer tab I can see different slicer Styles available here and if I want to create a slicer style of my own I will duplicate any existing style and then modify it in the same way we did for the pivot table in juggle width is the pivot table layout I have a pivot table here with subcategory in the rows area I will add category field to the rows area as well when you set up a pivot table it will appear in a compact layout by default which looks like this if you have multiple fields in the rows area like we have here compact layout will stack them all in the same column and give you options to expand or collapse the fields by pressing the plus sign other layout options available are outline and tabular with the pivot table selected go to design Tab and then click on report layout here you will see different layout options outline layout puts each field in the rows area into separate columns note that in the outline the subtotals are at the top the tabular layout also puts the fuels into different columns but displays the subtotals at the bottom instead of at the top with the tabular and outline layout you can also select if you want to repeat the item labels or not also if you want to get rid of the subtotals you can right click on any cell in the first field and uncheck the subtotal option which layout you want to go for will depend on your preference and the task at hand the layout I find helpful is the tabular one with no subtotals and item labels repeated this is because I usually apply many formulas on my Pivot tables to pick data for dashboard and further analysis and from this perspective tabular layout works the best later in this course when we will build a dashboard you will see how tabular format helps in this situation so this means that every time I set up a pivot table I have to go and change the layout to suit my preference manually well thankfully no let's say I'm comfortable with this layout and I want that whenever I create a pivot table it should be in this particular layout to do that I can go to file click on options then data and then select edit the default layout here I can reference the pivot table whose layout I want to be applied by default I will select any cell within this pivot table click import and then click on OK now the next time I create a pivot table it should come in the layout of my choice so this was the major feature under the layout options there are some other options available as you can see here you can add a blank line after each category add or remove the grand totals or do the same with subtotals play around with these options and I'm sure you will get the hang of how they work and what they do next let's talk about summary calculations I will create a fresh pivot table I will drag the subcategory to the rows area and the sales field to the values area when you set up a pivot table and drag any field to the values area the pivot table will either sum those values or give a count of them if you drag a field with a numeric data the pivot table will add those numbers if you drag a field with descriptive data for example region pivot table will show the count of that field but that's not the only option you're stuck with you can opt to summarize your data differently I will first undo and go back to my previous position where I had sales in the values area Now by default pivot table is summing these sales values now let's say you want to see the average of the sales rather than the sum right click anywhere in the value field go to summarize values by here you have various options available I will select average and you will see that the pivot table will average the sales instead of summing them I will press Ctrl Z to undo it few other helpful options available are under the show values as feature let's say you want to see percentage of each category sales to the total you can get that by going to the show values as and select percentage of grand total now let's say you want to see the total sales in the First Column percentage of grand total in the second column and the running total in the third column you can do that too first I will go back to previous position where it was showing me sales as a sum and then I will drag two more instances of sales field into the values area once you do this you will see that you will have three columns for sales each of these columns will show sum of sales by default for the first one I will leave it as it is for the second one I will right click on it go to show values as and click percentage of grand total similarly for the third column I will go with the running total in and select subcategory and you will see that we will get the desired outcome again there are various options available under the show values as feature I suggest you try them out to get some Hands-On practice also I can rename the fields by simply typing the name of my choice next we will learn about filtering and sorting the data I will first remove the two additional sales field we have in the values area next I will add another numeric field in the values area that is the quantity by default the subcategories are sorted alphabetically from A to Z let's say I want to sort the subcategories in descending order of sales to do that I will click the drop down which appears on the subcategory field header then click on more sort options go to descending and select sum of sales in the drop down and now our subcategories are sorted in the descending order of sales to go back to the default I can either press Ctrl Z on my keyboard or select sort A to Z from the options available next let's say you want to filter the subcategory values you will again click on the drop down now one option is to manually filter the subcategories you want to see in your report from the drop down available at the bottom but let's assume I need to filter the report to show only the top 8 subcategories by sales for that I will go to the value filters and then select top 10. a dialog box will open where I can specify the filter mechanics I want to filter top 8 items by sum of sales so I will specify that then click on OK and this should give me the top 8 items as with other areas you can filter and sort your pivot data in various ways they're pretty straightforward so I encourage you to try them out next we will learn about data grouping grouping is a handy feature which can help you save time here we have a table showing sales by subcategory let's say I want to see sales by category although in our data we have the category field available for the sake of this example imagine you don't have it in such a situation we can group The subcategories into categories very easily I will select a few subcategories that I want to group with these subcategories selected I will go to the pivot table analyze and click group selection and now we have the group created similarly I can create groups for the rest of the subcategories as well to view these scripts better I will switch to the compact layout for now now you can see the subcategories that are clubbed under each group I can also rename these groups to whatever name I like for example here I will rename them as category 1 Category 2 and category 3. if I want to go back to default position I will click anywhere in the field and select ungroup a watch out here is that when you create a group all the pivot tables connected to the same data source or the same pivot cache will inherit that grouping let me show you why an example I will first create a copy of my pivot table and to elaborate I will put some other fields in the values area let's say I will put quantity and profit Fields instead of sales this is just to elaborate that these two pivot tables are entirely different now if I create a group in the first pivot table you will see that the second pivot table will inherit that grouping too if you are using groups in your pivot tables this is something you should be aware of so you can understand the impact these groups can have on your wider Excel workbook grouping is not limited to descriptive Fields you can also do grouping with numeric fields too this is particularly helpful when you want to do some statistical analysis let's say I want to find out how many orders were in the range of 1000 to 2000 dollars first I will delete the second pivot table we don't need that anymore then I will put the sales field into the rows area then with the sales field selected I will go to group field a dialog box will pop up where I can select the high and low ends of my group and specify the increments I want to make six groups with an increment of 1000 each so on the low end I will put 0 on the high end I will put 5000 and in the increment or interval I will put 1000. as you can see the sales field has been now clubbed into six group each showing a particular range next when I drag the order ID to the values area I will get the count of orders by each group since order ID is a descriptive field Excel gives me the count of this field rather than the sum now I can see only 328 orders out of total of 999 four orders were for the value of one thousand to two thousand dollars likewise grouping can be done with the date fields as well I will first remove the order ID from the values area ungroup the sales field and then put the sales field into values area so Excel groups the date values automatically anytime they are put into any of the areas let's learn it by an example first I will drag the order date to the rows area now let's quickly go to the source data here you can see that the order date field has individual dates indicating a particular day for example 8 November 2016 Etc but when I drag the order date into the rows area of my pivot table Excel by default groups the order date field in two years quarter and month if I want to change this grouping I can do that too with the date field selected I will go to pivot table analyze and select group field a dialog box will pop up where I can select any level of grouping I want let's say in addition to years quarters and months I want to see the day as well I will select a and click on ok now I can view the details down to the data Day level moving on we will learn about calculated fields and calculated items let's start with the calculated Fields here I have a pivot table that shows sales and profit by subcategories now I have subcategories in the rows Area Sales and profit in the values area now sometimes you might want to expand your analysis to include fields that are not there in your original data source for example here in my data I have numbers for sales and profit but I do not have number for cost and let's say I want to see cost by subcategory given that I have numbers for profit and sales I can calculate the cost numbers by subtracting profit from sales to tackle this problem there are broadly two ways one is to create a manual calculation outside of the pivot table and the second is to set up a calculated field inside of the pivot table let's try out the first option in cell E4 I will write the formula B4 minus C4 then drag this formula down and there we go we have numbers for cost by a subcategory the downside with this approach is that if your pivot table moves or gets modified your calculation might become incorrect for example if I copy this pivot table to some other location and then delete the first one by mistake my calculation might become useless or let's say if someone modifies our pivot table for example by adding the segment field in the rows area then again our calculation might not be complete from this perspective calculated fields are great as they are more adaptable let's try to achieve the same with the calculated field to create a calculated field I will go to pivot table analyze then go to filter items and sets and then click on calculated field in the dialog box that shows up I can give a name to my field and specify the formula based on which I want this field to be calculated once I click OK a new field will show up in my pivot table note that even if I remove the sales and profit fields from my pivot table the cost field will still work this is because the cost field is set up as a new field in our pivot cache and therefore calculated fields are more adaptable now let's talk about calculated items to understand the difference between calculated field and calculated item let's take the following example imagine this is your Source data table when you create a calculated field in your pivot table it's like you are creating a new column in your data on the other hand calculated item is equivalent of creating a new row in your data let's work with an example to understand this further to keep it simple I will take a new source data this data is similar to The SuperStore data set we were using just instead of order date this data set has a month field I will create a pivot table of this data put months into rows area and sales into values area now we have a sales by month pivot table now let's say I want to see an average of half one sales which means that average of sales from Jan to June an average of half two sales then I can use a calculated item for that to create a calculated item I will go to pivot table analyze and click on calculated item I will give it a name of half one and then give it a formula of average from Jan to June once I click OK you will see a new row in the months field same way I can create a calculated item for half 2. foreign groups and calculated Fields calculated items will also impact other pivot tables connected to the same data source for example if I create a new pivot table from the same data and put months in the rows area you will see half one and half 2 will be there this is because both the new pivot table and the first one are connected to the same pivot cache now I have mentioned the word pivot cache quite a few times and I think it will be unfair if I cruise through this course without explaining what it is so let's spend a few seconds understanding pivot cache imagine this is your raw data when you set up a pivot table on top of it Excel creates a copy of your Source data that copy is called pivot cache the final pivot table which you see is connected to a pivot cache when you set up a calculated field calculated item or groups they are set up in the pivot cache Therefore your Source data does not get impacted now when you create a copy of your first table or a new one from the same data source that new pivot table will be linked to the same pivot cache to save memory if this sounds complicated don't worry about it just remember that when you create a group calculated fields or calculated items they will impact all the pivot tables linked to the same data source next we will have a quick look at pivot charts I will go to my Superstore data set select the entire data click on the insert Tab and this time I will select pivot chart I will set it up on a new worksheet pivot charts work in the same way like pivot tables instead of rows and columns area you have access and legends area I will drag the order date to the access Area Sales to the values area that will give me a column chart by year if I want to drill down to quarters I can press the plus sign here if I want to break down the bars into categories I will drag category field in the Legends area if I put the segment in the filter area that will help me filter the chart by segment pivot charts are a bit inflexible when it comes to formatting and due to that reason I usually don't use pivot charts much instead I set up a pivot table and then create a standard Excel chart based on the data from there we will do this in our next section where we will set up a mini dashboard before we start I want to give a quick heads up and that is we will not be covering the formatting of the dashboard in a lot of detail purpose of this course is to show you how to set up a dashboard using pivot tables therefore I will fast forward the video on sections where I'm working on the format piece if you want to learn how to set up a dashboard in detail you can check out my other video and I'll put the link in the description so let's start I will first delete all the sheets we had created over the last few sections then I will start with a blank sheet and set up some shapes [Music] [Music] next I will create a pivot table from my Superstore data set I will put subcategory into rows Area Sales in the values area next I will link the values to cells outside of the pivot table and on those cells I will set up a bar chart now I will copy this pivot table down in the second pivot table I will show sales by month and year for that I will put the order date into the columns area and show years and then drag another instance of the order date field into the rows area and show months [Music] similar to how we did above I will link the data to Outer cells and create a line chart on that [Music] next I will cut the charts from the working sheet and paste them onto the dashboard sheet and then do some formatting [Music] nice [Music] thank you next I will set up the category and segment slicers and bring them to the dashboard as well and here you go our interactive dashboard is ready before we finish this course I wanted to talk about an important practical issue you might face when working with pivot tables in the last few exercises we created pivot tables based on the superstore data set now this data is arranged in a very nice format it has no empty columns or no empty rows and the data is in a tabular structure it's ready to go into a pivot table but when working in real world scenario it's unlikely that you will get data in such a clean condition mostly you will get data that looks something like this this is how most of the erps and systems produce data and it's actually good for naked eye everything is easily visible but it's not in a format that can go into a pivot table in such a situation you will first need to transform your data so it's pivot table friendly you can do this data transformation manually by deleting columns and rows and then copying the item labels but assuming you have to do this exercise frequently it will take up a lot of your time the way is to use power query with power query you can automate the data cleaning and transformation workflows so once you're comfortable with pivot tables you should explore power query as an extra step with that I hope you enjoyed this course if you did please like this video And subscribe to my channel have a good rest of the day

Channel: Skillnator

Views: 13,301

Keywords: #exceltips, #exceldashboards, #microsoftexcel

Id: WlJpzth68os

Length: 41min 1sec (2461 seconds)

Published: Tue Mar 28 2023