Excel Pivot Tables - Beginners to Advanced! Your ultimate guide to Pivot Tables! | Abdelrahman Abdou | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel Pivot Tables - Beginners to Advanced! Your ultimate guide to Pivot Tables!

teacher avatar Abdelrahman Abdou, Data Analyst & Excel Lover!

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

121 Lessons (5h 53m)
    • 1. Course Introduction

      1:54
    • 2. Please Follow Me

      0:17
    • 3. 2-1 Tabular Format

      2:06
    • 4. 2-2 No Gaps

      2:42
    • 5. 2-3 Number Formatting

      1:56
    • 6. 2-4 Creating Excel Tables

      3:30
    • 7. 2-5 Cleaning Data using TRIM()

      3:05
    • 8. 2-6 Creating our First Pivot Table

      3:21
    • 9. 2-7 Field List

      1:59
    • 10. 2-8 Field List Areas

      4:42
    • 11. 2-9 Drilling down on your Data

      1:39
    • 12. 2-10 Sort Field List A to Z

      0:55
    • 13. 2-11 Double Clicking Labels to show more fields

      1:37
    • 14. 2-12 Defer Layout Updates

      1:40
    • 15. 2-13 Pivot Cache

      1:47
    • 16. 2-14 Refresh

      2:29
    • 17. 2-15 Refresh All

      1:45
    • 18. 2-16 Connect to and Refresh External Data

      2:17
    • 19. 2-17 Importing from Access

      1:41
    • 20. 2-18 Changing your Data Source

      2:58
    • 21. 2-19 Clear and Clear Filters

      1:03
    • 22. 2-19 Select and Format

      4:39
    • 23. 2-21 Moving a Pivot Table

      0:59
    • 24. 2-22 Pivot Table Styles

      2:39
    • 25. 2-23 Custom Pivot Table Styles

      3:57
    • 26. 2-24 Subtotals

      1:54
    • 27. 2-25 Grand Totals

      1:10
    • 28. 2-26 Report Layout

      4:41
    • 29. 2-27 Blank Rows

      0:37
    • 30. 2-28 Expand and Collapse Buttons

      5:04
    • 31. 2-29 Move and Remove Fields and Items

      3:06
    • 32. 2-30 Show and hide Field List

      0:37
    • 33. Show and hide Field Headers

      0:21
    • 34. Number Formatting

      1:22
    • 35. Changing Field Names

      1:43
    • 36. Presetting Number Formatting

      2:06
    • 37. Moving around multiple aggregation fields

      1:47
    • 38. Indenting rows in compact form

      1:27
    • 39. Changing the layout of a report filter

      2:06
    • 40. 2

      0:58
    • 41. Formatting empty values

      0:46
    • 42. Keeping column widths the same after refreshing

      0:42
    • 43. Autorefreshing pivot tables upon opening workbooks

      0:53
    • 44. Printing Pivot tables

      2:01
    • 45. Report Filter on Multiple Pages

      2:22
    • 46. Exploring different aggregation options

      3:36
    • 47. Adding multiple subtotals

      1:43
    • 48. Percentage of grand totals

      1:39
    • 49. Percentage of Column Total

      1:38
    • 50. Percentage of Row Total

      2:05
    • 51. Percentage of Calculation

      3:47
    • 52. Percentage of parent row total

      3:24
    • 53. Percentage of parent column total

      2:07
    • 54. Percentage of Parent Total

      5:35
    • 55. Difference From Calculation

      6:51
    • 56. Percentage of Difference From

      5:47
    • 57. Running Total In

      4:04
    • 58. Percentage of Running Total In

      2:29
    • 59. Ranking Values in Pivot Tables

      3:09
    • 60. index Calculation

      5:36
    • 61. Grouping Dates

      3:15
    • 62. Grouping by ranges

      2:32
    • 63. Grouping by Text Fields

      2:43
    • 64. Grouping by Time

      3:37
    • 65. Grouping by Half years

      1:44
    • 66. Grouping by a Date Starting on a Monday

      1:56
    • 67. Grouping by a Custom Date

      2:25
    • 68. Grouping by Fiscal Years and Fiscal Quarters

      6:30
    • 69. Errors when trying to group by Dates

      3:11
    • 70. Grouping Pivot tables from the same data source

      4:40
    • 71. Showing grouped dates with no data

      1:41
    • 72. Sorting by Smallest or Largest

      2:17
    • 73. Sorting an Item Row

      1:54
    • 74. Sorting Manually

      1:15
    • 75. Sorting using a Custom List

      3:17
    • 76. Overriding a Custom List

      1:13
    • 77. Sorting Fields and Values in two different orders

      0:47
    • 78. Sorting a newly added item to a field

      1:15
    • 79. Filtering by Dates

      8:51
    • 80. Filtering by Text Values

      3:19
    • 81. Filtering Numerical Text Values

      2:12
    • 82. Filtering by Values

      1:21
    • 83. Top or Bottom 10 Items

      1:24
    • 84. Top or Bottom Percent

      1:07
    • 85. Top or Bottom Sum

      1:29
    • 86. Filtering by Report Filter

      3:40
    • 87. Different ways to activate filters

      2:15
    • 88. Filteration using WildCards

      4:58
    • 89. Filtering by Multiple Fields

      2:17
    • 90. Applying multiple filters per field

      3:28
    • 91. Choosing which value field to filter with

      2:28
    • 92. including a new item in a manual filter

      1:44
    • 93. applying filters to column field values

      1:36
    • 94. Inserting a Slicer

      4:57
    • 95. Slicer Styles

      1:21
    • 96. Creating a Custom Style

      2:55
    • 97. Copying a Custom Slicer Style to another workbook

      1:21
    • 98. Slicer Settings

      6:53
    • 99. Slicer Size and Position options

      2:30
    • 100. Slicer Connections

      3:07
    • 101. Filtering a Slicer

      1:28
    • 102. Timelines

      3:23
    • 103. Protecting sheets without a slicer

      3:21
    • 104. Inserting a Calculated Field

      3:37
    • 105. Creating calculated fields from other calculated fields

      2:06
    • 106. Modifying a Calculated Field

      2:18
    • 107. Formulas in Calculated Fields

      2:55
    • 108. Calculated Items

      6:20
    • 109. Inserting a Pivot Chart

      4:41
    • 110. Different types of Pivot charts

      2:52
    • 111. Column chart example

      4:45
    • 112. Pie chart example

      2:54
    • 113. Bar chart example

      3:26
    • 114. Charts you can't create

      1:16
    • 115. Fixing a Pivot chart width

      1:53
    • 116. Moving pivot charts

      2:26
    • 117. Case Study - Population by Mother Tongue in Canada - Pivot Tables

      9:47
    • 118. Case Study - Population by Mother Tongue in Canada - Charts

      7:29
    • 119. Case Study - Employee by Industry

      11:23
    • 120. Case Study - How Many hours people work by Industry

      16:20
    • 121. Thanks for Watching

      0:18
  • --
  • 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.

96

Students

--

Projects

About This Class

In this course/class, you will learn about all the secrets in Pivot Tables in detail.
This course is designed to be your ultimate go-to resource when it comes to Pivot tables.
We explain the basics of Pivot tables from the very beginning all the way to the advanced features including sorting, Filtering, and using Slicers.

We also have 3 case studies at the end of the course to demonstrate how useful Pivot tables can be in conducting real-world data analysis.
We also include assignments at the end so as to be able to apply and practice what you learn at the course.

Meet Your Teacher

Teacher Profile Image

Abdelrahman Abdou

Data Analyst & Excel Lover!

Teacher

Abdelrahman Abdou is a Microsoft Certified Trainer (MCT) and Analytics Consultant with over 8 years of Experience in Data Analytics, Reporting, and Business Intelligence in the largest Telecom companies in Egypt & Canada.

Abdelrahman is passionate about learning & teaching Excel & Power BI and loves simplifying information for his students and audience.

Abdelrahman loves watching football, playing video games, traveling, and going to the gym.

 

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.

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.

Transcripts

1. Course Introduction: Hello and welcome to this Excel pivot tables for beginners scores. My name is Abdomen Abdul, and I will be your instructor on this scores so a little bit about myself. I have a degree in chemical engineering and I have five years of experience is a data analyst and 10 years overall experience in Excel on. I've also been an online instructor since 2015 mainly teaching Microsoft Excel, and I'm a certified Microsoft Excel expert. So who should take this course? So this course is geared towards both beginners who want to learn pivot tables in excel from scratch and also intermediate users who want to increase their knowledge in pivot tables by learning the secrets that can make you get the best out of your pivot tables. So you can either start learning pivot tables from scratch using this course. Or you could watch certain parts of the course in order to learn the secrets of pivot tables to be able to get better at using pivot tables for data analysis. So let's speak about the contents off the course. First of all, we're gonna learn the basic self pivot tables. So how to insert a pivot table and also how to prepare your data for being analyzed by a pivot table in order to get the best out of your pivot tables. We're also going to learn about fields and calculated columns. We're going to learn about grouping, sorting, filtering, and we're also going to learn about slices and slices. Guys are a very important tool in pivot tables that can enable you to get the best out of your pivot tables. We're also gonna learn about calculated fields and calculated items were gonna also learn about pivot charts, and we're gonna have some data analysis, case studies and, ah, homework or an assignment in order for you to practice what you learn in the course. So that's it, guys, thank you so much for watching this introduction lecture, and I'm very excited to start this course with you. So without further ado, let's get started 2. Please Follow Me: thank you for enrolling into this class before we start the class. Please make sure to follow me on skill share by clicking on the follow button at the top of the screen or on the bottom right hand corner of the screen, so that you'd be able to receive updates about new classes that I release or any changes to existing classes. 3. 2-1 Tabular Format: Okay, So before creating our pivot table, we need to make sure of three things. Number one is the Our data is in a tabular format. Number two is that we do not have any gaps in our data. And number three is that we form it our data properly. In this video, we're going to speak about having our data in a tabular format. So you should actually have your column Names at the top showing a distinct category. So each column showing a distinct category. So we have a column for customers, another call for products region sales amount, order, mount order, date. And this is supposed to be the year. And this one is supposed to be the quarter. However, we do not have them labeled here because we want to demonstrate something. OK, so let's try to create a pivot table here. I'm gonna click inside my data press control and a on my keyboard to select only data, even including the columns that are not labeled. And then I'm gonna click on the insert tab on the ribbon click on pivot table, and then I'm gonna insert a pivot table and you worksheet click. OK, here. And as you could see her, a pivot table has been created. But our columns that are not labelled have not been included in the pivot table. Feels so the columns that were not labeled where, the quarter and the year. So this is why we need to make sure to label our columns. So if you go back to our data here and then create some labels for columns year and then quarter and then create another pivot table insert to select all our data, insert a pivot table and then click OK here. And you can see here that the columns have been created because they do have labels. So the bottom line from this lesson is that you need to make sure to create labels for all your columns, and please note as well that in older Excel virgins, instead of creating a pivot table without the unlabeled columns, you might actually get an error while creating a pivot table for columns that are not label . So always make sure to create labels for your columns before creating a pivot table 4. 2-2 No Gaps: Okay, So the second rule for creating or pivot table is do not have any blank rows or blank columns in our data. Says you can see her on our data. We've got column, if is blank. And we've got to blank rows here, row 18 and row 33. Okay, so let's try to create a pivot table here and see what happens. Now, I'm gonna select my headers here. I'm gonna hold my shift key on my keyboard and then select the rest of the headers with my arrows on the keyboard. And then I'm gonna press control shifting down on my keyboard and keep doing that until I said, like all the data here and then I'm gonna go to insert a pivot table. I'm going to try to create a new pivot table in your worksheet. And as you can see year, I'm getting an error. Pivot table field name is not valid. This is because we have a blank column, so I'm gonna click. OK, here. I'm gonna click. Cancel and I need to delete Molly Blank column. So right clicked on my column and deleted it. And now we need to also make sure not to have any blank crows in the middle off our data set. So imagine that you have lots of blank rows. Not only two, but lots. So you don't want to select them all one by one in order to delete them. So a better way to do it is to select all your data. Second year plus control, shifting down to select all your data set year on your keyboard and then go to the home tab on the ribbon click on finding select and click on Go to special. And then we're going to select blanks. So this actually select only the blank cells in our selection off cells. We're gonna click OK here, and you can see here only the blank cells have been selected. So I'm going to right click on the selected cells on any of the selected cells and then click on delete here to delete my cells. And I'm going to select entire Oh, because I want to delete the entire row of cells. The are blank. So I'm gonna click OK here. And as you can see here, I managed to delete my blank rows case I know we can go ahead and press control and ages. Selector data Make sure that we picked up all the rows here and then go to insert a pivot table and insert a new pivoted. As you can see here, we managed to insert or pivot table, and this is another rule that you need to follow. When creating a pivot table. Make sure that you do not have any blank crows in the middle of your data or blank columns . When you have blank rows, either they could be missing values. You need to fill them or if they are blank by error, and you just need to remove them than you remove them as we demonstrated. 5. 2-3 Number Formatting: Okay, So the third step here for preparing your data for creating a pivot table out of it is to form it each column properly. So you should check each column that contains the number or each column that contains the date and make sure that you formatted properly. So, for example, this is the sales amount here. This is not formatted properly because it's just numbers. Eso it's better to add a coma, actually. And maybe if there is a currency, you could add a currency here. So English, United States or English, Canada or so? So you had a currency to it, And there couple zeros you can remove here, using these arrows here for the decimal places. And then there is the order date column here, so Excel actually treats dates as numbers. So sometimes on the column containing your dates, you might see numbers. These numbers actually re present your dates, but they are not formatted properly, so if you need to format them properly, you need to select your column here and then go to this number format, drop down menu and then select short date. So this form, it's your dates properly, and please note that all my PC here I've got my dates formatted in the European way. So it's day, month, year, so on your PC. If you're in the US, you might have your dates format it as month, day year. Okay, so now that we've fermented our data properly, we can start creating our pivot table. So I'm gonna press control in a on my keyboard here, just like my data and an insert, a pivot table. And then I'm gonna click OK to create a pivot table in a new worksheet. And that's it. So we've completed the final step here for preparing our data for creating a pivot table out of it. This step will help you troubleshoot any issues with your data in the future. So having proper form, it is a general best practice when preparing your data 6. 2-4 Creating Excel Tables: so a great tool that you should utilize when creating pivot tables out of your data is Excel tables. So Excel tables have been added to excel since Excel 2007 and their greatest advantages that they have structured referencing. So this means that as you add more rows or columns to your table, your table gets expanded automatically. A new formulas get automatically applied to the new rose without needing to expend them for the new rose or without needing to drag them down for the new rose. So let's see how we can insert a stable so we can insert a table by selecting any cell insight our data set and then going to insert and then table. You can also, alternatively, press control and t on your keyboard, and this is how you can insert a stable and you should check. My table has headers here if it's got headers and we do actually have header sport tables or click OK here. And as you can see your we've got an Excel table created. So while selecting any cell in your excel table, you can select a design top here on the ribbon, and you could see her that we've got different styles that you can apply to your table so you can have it have different colors or so. So I'll just keep this style here. But you can choose out of these styles and you can also create it new custom style for yourself. You can also add a totals row. So while selecting any cell inside your table, you go to the design tab on the ribbon and then you check total row. You can see here that a total world has been added so you can have your on the total row. You can have different aggregation formulas. Seacon select average count Max men some. So all these air different aggregation for minister, for example. Some here would give us the total sales amount for all the rose here. Now, if you need to expand your data and add more data to your table, he can just hover with your mouse here to the edge and Zico's year. You will have your mouse turn into a two sided arrow, two sided dagon, an arrow. So if you click on hold and dragged down with your mouse here, you can see that you're able to add more rows to your table. Alternative. You can also select any row here and right click and then insert to insert Rozier, and you can repeat the action by pressing F four on your keyboard. Keep pressing a for and the action will be repeated. I'll just delete the X arose here because you don't need them. So select them right click and then delete the rose, a useful feature for tables as well as to give them a name. So while selecting any cell inside your table, you go. You go to the design tab on the ribbon and you can see her. There is a field called table name where you can change the name of your table. So, for example, I like to actually name my tables with a prefix, which is Steve bl and then whatever this stable would be. So this should be TBL sales, for example, as this table is a table of sales, so I'll name it this way and press enter. And now, while selecting any cell inside your table, you can actually create a pivot table out of it by inserting a pivot table here and ah and pressing. Okay, this will create a pivot table out of your table. You can also go to the design top, and you can also export it somewhere so you can export it to a SharePoint list. For example, you can also on your table. You can use the filters feature here to filter your data. OK, so tables are a very useful feature. When dealing with pivot tables, they will make handling your dates up much easier. 7. 2-5 Cleaning Data using TRIM(): in this video, we're going to show a tip for cleaning your data. And this is a common problem. Actually, that might happen to you when you use data for creating pivot tables. So here we've got some data here. We've got a column containing some countries and let's create a pivot table out of it. So I'm just going to select the whole column by pressing control and am a keyboard and then insert a pivot table. And actually, instead of putting it in the new worksheet, I want to put it actually just beside our data so that we can see what the problem is. So we get a pivot table here created I'm gonna put the country in the rose section of the pivot table, and normally when you create a pivot table, it should only show unique values. But as you can see here, we've got some duplicates of the countries who got is traded twice. Friends twice, Germany, twice. So what's the reason for that? So the reason for that is that we've got leading oh, training spaces in our values. So actually, if you double click on a value of press F two on your keyboard, you're able to inspect the values to hear the value, doesn't have any leading or training spaces. But if we check the other Germany because we've got Germany twice here on the pivot table, you can see here that it's got an extra space here at the end. So this is what we've got to Germany values because the pivot table were recognized, these values as two different values because one of them has a space, a trading space at the end and the other one does not, and the same cases for Francis Well, so as you can see here for friends, this one does not have any leading or training space. But if we check the other friends, he comes here. The other friends does have a leading space, actually, So this is a common problem that you might face when creating pivot tables that you'd have leading or trading spaces. And that means spaces at the beginning or the end of your values that would make your values seem different to the pivot table. Though you know you're intending for this to be Germany and the other one to be Germany. These should be the same value. This is what you want. So how do we get over this problem we can use actually a function in excel to clean our data, which is the trim function so equals trim and the term function removes any leading or training spaces in our values. So that means any spaces before a ward or any spaces after a ward extra spaces after award . And this is what we need So we can use the trim function and we can just apply it to all our values here. And then we can actually copy and pieced our values years to right click and we're gonna paste values because we're gonna pace the values, not the formula. And then we're gonna delete our function here, and then we can actually refresh our pivot table, right? Click on our pivot table and then refresh. As you can see here, we Onley now have unique values. We don't have any duplicates in our pivoting on. This is a common problem that you would face when using pivot tables 8. 2-6 Creating our First Pivot Table: Okay, so that's speak a bit about the layout and structure of a pivot table. So as we agreed before, the best thing to do when creating a pivot table is to first grade a table from our data. So with greater the table here from our data and to insert a pivot table, we've seen that multiple times on the course. But let's do it one more time here so you can actually go to the insert and then pivot table here, or you can go to design. And there is another option, actually, for inserting a pivot table, which is summaries with pivot table. So either of those would work says you can see here Excel managed to highlight all of our data automatically inside the table. So we're gonna insert a pivot table on you, worksheet. We're going to click. Okay, here and now we've created our pivot table. Now, as you can see here, we've got our pivot table tools at the top while we are inside our pivot table. But if we step outside our pivot table, you can see here that the two tops under the pivot table tools have disappeared. So in order to view these two tubs, which are the analyzing designed top you need to be inside your pivot table. You can actually also change the name of your pivot table. So here are Pivot Table has a name pivot table one. We can actually change its name. I'd like to give my pivot tables a prefects, which is PVT so PVT. We can call it PVT. Sales, for example. And now with changed the name of our pivot table on the right hand side with our pivot table field Spain. And this is where we get to manipulate and play with the fields of our pivot table. And you can actually show or hide the pivot table fields by stepping inside your pivot table. First, they need to select a cell inside your pivot table and then here under the analyze tab on the ribbon. There is a few list button here so you can toggle between the pivot table fields, appearing and disappearing using that button. Now let's explain the anatomy or the structure of a pivot table. So on a pivot table, we've got four areas that weaken drag fields into them. So we've got the filter's area we've got the columns area, We got the rails area and we've got the submission of values area or the values area. So basically, any feels that we drag into the filter's area will be on the top left corner of our pivot table, and any feels we dragged to the columns area will be on the horizontal area over pivot table at the top. They will be laid out horizontally, and any feels that we put in the Rose area will be on the left hand side of our pivot table , and any feels that we put in the values area will be in the middle off the pivot table. Now, let's create a quick pivot table here out of our data. So I'm gonna put the customer field here on the filters and I'm gonna put the year on the columns. I'm gonna put this sales person field in the row section and I'm gonna put the sales amounts in the submission of value section. So there you go. We've created our first pivot table, and in future videos, we're gonna learn more about manipulating our pivot tables 9. 2-7 Field List: Okay, so that's speak a bit more about the field list. So another way to show on hide your field list is to actually right. Click on your pivot table here and then click on Hyatt Field List, and you can also right click again and then show your field lists and us another way to show on high. Do you feel list? In addition to using the fuel list button here under the anal, I stab on the ribbon. If you need to move your field list, you can actually click on this arrow here and then click on move, and then you can start dragging and dropping your field list anywhere on the screen. If you need to dock it back to the right hand side of the screen, you can just click and drag the field list year to the very right Hindsight and then Excel will dock it automatically for you. Another way to docket after you move it. So start moving again is to actually double click on it, and this will also docket back to the right and site. If you need to resize your field list, you can click on this arrow here and then click on size and you can start re sizing it. You can also move your mouse cursor to the edge of the fuel list, and you will be converted to these two opposing arrows so you can just click and drag to resize your field lists. Also on our field list, we've got different views that we can choose from. So here you can click on this gear icon here that says Tools. And then you have different views for you feel list. So we're currently on a view called Fuels Section in Areas Section Stack. There's another view that says Feel section in areas section side by sides. Another one that says Fuel section on Lee and There's Areas section on Lee two by two. And there is Area section on Lee won by four. However, I personally prefer the first view. 10. 2-8 Field List Areas: and this video, we're going to speak about our field lists and our four different areas at the bottom. So if we go back to your data, you will see that the names off our column headers for a data match those off the pivot table fields that we have here on our field list. Now at the bottom, we have our different fuel list areas. We have four different field ist area, so you get the filters, columns rose and values, and in thes field list areas, we can actually drag and drop any of our field. So let's say I want to put my product name on the row section. I can actually click on it and drag it and drop it in the row section. Alternatively, there is a check box beside it, and if I click on that check box so I can un check and then check again and you could see her, it's been inserted on the rose area. However, it's not always guaranteed that it would go to the right area that you want when you click on the check box, so it's better to drag it and drop it to the area that you want and usually the fields that you would put in the rose section would be feels that you want to group by so you can group buy the product. It could be also the reason it could be a certain business unit, a certain manager sales person. So these air the fields that you'd usually put in the roast section. Now, on the columns area, you usually put deals where you would want to show the trend of the data. So usually you put the weeks, the months, the quarters or the years usually feels where you would want to show a trend. So here on the column section, I'm gonna put the years I'm going to drag and drop the year here on the column section. Now, on the values area here, you would usually put feels that you would want to aggregate, so you'd usually want to put the sum of sales the average price, the maximum price. So these air quantitative feels that you would want to aggregate So here, I'm gonna put actually this sales amount. I'm going to drag and drop the sales amount, and you can see here it defaults to showing the some. If you need to do a different type of aggregation, you can click on this arrow here and go to value field settings and then choose a different type of aggregation. You can choose the Count average Max Hman product, all these different types of aggregations. But here, I'm just going to stick with the some I'm gonna click. OK, Okay. So let's take a step back here and analyze what we've got on our pivot table. So in this cell here that I've got highlighted, we've got the sales for product one in 2016 which is 1,095,430. Now, if you go back to our data and we filter our data for product one and for the year 2016 you can see here these air older sales that happened for product one for 2016. Now, if we highlight the column here for the sales amount, all the values for a calm for the sales amount and we check the summation here it's 1,095,430. So basically, the pivot table is summarizing our data here nicely, and the same goes for product to in 2016. So this cell re presents the sales for product to in 2016 and so on and so forth. So, as you can see here, the pivot table is a nice way to summarize your data. Now let's speak about our final area, which is the filter. So the filter's area is an optional area where you can put a field and usually you would want to put fuels that you would want to filter with. So, for example, the sales person or the region. So here I'm gonna put the region, for example, I'm gonna put the sales person, and this way you're able to filter the data on your pivot table with certain cart iria. So, for example, I can filter only for the sales that happened in the Americas. And you can see here the values have changed to show only the sales that happened for the Americas and for the sales person, I can also filter by a certain sales person. As you can see here, it shows me on Lee the sales amount for that particular sales person and also for the Americans. Because we've got an America's filter applied already as well. Using a pivot table, you're also able to swap your feels. So, actually, I'm gonna swap here the product, name the location for the product name, put it in the columns and then take the year and put it in the roads. So it's actually a nice way to get your data in different forms here, and you can also hear Remove the filter as you can see here, by selecting all to this way you've removed you filter. So, as you can see are the pivot table is a nice way to put your data in any shape that you want. 11. 2-9 Drilling down on your Data: and this video, we're going to show a nice trick that you can use when you suspect that you have wrong numbers on your pivot tables are on your data. So, for example, here in our sales for product one in 2016 it's 1,095,430. Let's say that you suspect that this number is incorrect. Let's say that you should be two million, for example, not 1,095,000. So in this case, you can actually show or check the underlying data for this particular number by simply double clicking on the cell. As you can see here, basically, Excel has created in you worksheet that contains the data on Lee for that particular number , so it contains only the underlying data for product one and in 2000 and 16 and then you can check the numbers for the sales amount and see where the error is. However, if you need to make any changes, you cannot make them in this sheet because this sheet is not linked to the pivot table. This is just a new sheet that Excel has dumped the data underlying this cell here or the number in. But actually, if you need to make any changes, you need to make them on your data sheet. So actually, all press control NZ on my keyboard here, too. Remove that sheet, and then if you need to make any changes, you can go to your data and make the changes there. Okay, so this is a nice tip for you if you need to check the data underlying a certain number on your pivot table if you suspect that this number is incorrect or having an error, So thanks, guys, for watching, and I'll see you on the next one. 12. 2-10 Sort Field List A to Z: this video, I'm going to show you one of my favorite tips for dealing with Pivot table field. So actually it involves sorting. The pivot table feels alphabetically because it makes finding the pivot table feels so much easier. So sometimes when you have a lot of feels when you have a table with lots of columns here, you would have a long list even longer than this off pivot table fields. And if they are not sorted, it's not gonna be easy to find them, so you can easily sort them in an A to Z fashion by clicking on this gear icon here and then clicking on sort HZ. Alternatively, you can go to the analyzed tab on the ribbon click on options. Then you're gonna click on display here, and then you're gonna have an option here for feel this sort eight easy and click. OK does a very nice tip that you can use in order to sort your pivot table fuels in an A to Z fashion or in ascending order so that you confined your fields easily 13. 2-11 Double Clicking Labels to show more fields: Okay, Here's another tip that I want to show you, which is that we can double click on any row or column label items to show more fields. So, for example, here if we double click on product one, which is on the road section here, we can get we get a list of the fields and we can actually add more feels, which basically would breakdown product one. So, for example, we can break down products one by region if you click. OK, here, you guys hear that? Now we have a breakdown for product one by region, which is America's Australia and Europe. So we get the sales a month for each region under product one. As you can see here as well, the region field has been added to the rose section below product one. And when it's put below product one like this, this means that it breaks down product one by region, okay and use. You can see here as well. Product to and product three and product for all have been broken down by region as a result of the action that I've taken, which is adding the region under the product name basically and we can do the same for the year here for double click on the year Weaken. Break it down by the month. So by its sales month, if we click OK, here, he could hear that. Now we have 2016 and then older months and then 2017 and in all the months and our pivot table is getting pretty long. But you get the idea. Okay, so that's a nice tip. You can double click on the label for any field, and you will get a list of the fields and you can break down that field by other fields. 14. 2-12 Defer Layout Updates: if you're pivot Table is built over a huge amount of data. So hundreds of thousands of rose, for example, sometimes in depending on the power of your computer, it might take a long time for the life preview to be generated when you drag and drop the fields into the different areas of the pivot table. So a nice solution to do, which is to actually check this differ a layout update box here, which basically delays the layout update until you click the update button. So as you can see air if I put the sales amount, you can see here that the Life preview on the Left has not been updated with the sales amount field, so you can put whatever fields you would like here. So put the product name as well, I know. Put, for example, the sales year here and then at the end, after putting all my fields are click on update and everything gets updated. So this tip is useful when you have hundreds of thousands of data underlying your pivot table and generating the life preview is going to be slow, and you might not have the strongest computer in terms of specifications. So it's better to defer the layout update until the end and then just click on the update button to update your data so as to do the update process to do it just one time instead of doing it several times. Because basically, there is a Realtor Time update, as you add, feels to different areas of the pivot tables to do it all just one time at the end, instead of doing it several times. 15. 2-13 Pivot Cache: Okay, so that's speak about a term that you were here are lot when dealing with pivot tables, which is called the pivot cash. So when you create a pivot table, a copy of your data is stored in what is called a pivot cash. So the pivot cash is basically a duplicate of your data table, and it's created whenever you create a pivot table and the pivot cash is like a snapshot of your data table. So whenever your data is updated so more Roeser added, or values in your data are changed, you will need to refresh your pivot table in order for another snapshot to be taken from your data and put in your pivot cash and ultimately get your pivot table updated within you data. So the pivot cash is just a snapshot from your data. And this is why whenever your data is updated, this snapshot becomes old and we need to take another snapshot. And when we take another snapshot, which is by refreshing our pivot table are pivot table gets updated with the new values. So in this lecture, we were just explaining the concept of the pivot cash and how it works and the pivot cash the data in the pivot. Catch guests stored in the background system of your computer and off course because it's a duplicate of your data or a snapshot of your data if you have a big amount of data. If you have hundreds of thousands of rose and and you don't have enough resource is on your computer, you might see your computer slowing down when you're dealing with pivot tables having hundreds of thousands of rose. Okay, so that's it, guys, for the pivot. Cash. Thanks for watching I'll see on the next one. 16. 2-14 Refresh: Hey, guys, welcome back. So in this video, we're going to speak about refreshing your pivot table. So we've spoken before about the pivot table cash and that it's a snapshot of your data that is underlying the pivot table. So actually, when you change any of your data or if your data changes, basically you have to refresh your pivot table. So we're going to see how we can do that in this video. And also, I want to demonstrate something else. So I'm gonna copy my pivot table. I'm gonna hide like my whole pivot table here. I'm gonna copy but pressing control and see on my keyboard and then paste it just below here, creating just a copy of my pivot table by pressing control and V on my keyboard. Now I'm gonna go make a change on my data. So let's say on any off the rose for product one. I'm just gonna change the sales amount, make it one million, for example, on 23456 and then actually, for the new numbers to reflect on the pivot table, I need to refresh it. So to refresh my pivot table, I can either select my paper table and then go to the data tab on the ribbon. Click on this arrow here on the refresh old and then click on Refresh and you will notice. Actually, although I'm refreshing just one pivot table, both pivot tables will be refreshed, and the numbers on product one will update on both pivot tables. So it's just refresh it here, and you can see here in the number here for product one has updated for in 2016 because the change that we've done here waas in product 14 2016. This is why the numbers have updated for 2016 Mexicans here. The refresh has been done for both pivot tables. So why is that the case? It's because both pivot tables are linked or tied to the same pivot cash. So the data source is the same, which is the same pivot cash, which is our data. You can also refresh your pivot table by right clicking and then clicking on refresh so right clicking on the pivot table and then clicking on refresh. And this will also refresh your pivot table. OK, so basically, whenever your data gets updated you will need to refresh your pivot table because your pivot cash is just a snapshot from your pivot table. And so when the data gets updated, we need to update that snack shop by refreshing our pivot table. OK, so thanks, guys watching I'll see on the next one. 17. 2-15 Refresh All: Hey, guys, welcome back. So in this video, we're going to show how we can refresh multiple pivot tables in a workbook that have different data sources. So here we've got to pivot tables on this worksheet. We get the pivot table on the left, which is linked to data one, or has its data sources data one. And we get the pivot table here on the right, which is linked to data to or has its data source as date is too. So basically, we're going to make changes to both data sets and see how we can refresh both pivot tables at the same time. So here, we're going to make a change on data one for product 1 2016 So go to data one and have highlighted the seldom gonna change here. And I'm gonna put one million here on the sales amount and for data to as well of highlighted this selling gonna change, which is for product to in 2016 as well. So I'm gonna put one million as well on this cell and then to refresh both pivot tables were going to go to the data tab here on the ribbon. And then we've got a button here that says, Refresh all. And there's an arrow is well, you compress on the arrow and click on Refresh Old. Or you can click on the button and just refresh all the pivot table. So you can see here that both pivot tables have been refreshed with the changes, and the changes have reflected on both pivot tables when we clicked on refresh all. So when you click on re fresh old, you refresh Oh, the pivot tables in your workbook, even if they have different data sources. OK, so this is how you can refresh all the pivot tables on your workbook, even if they have different air sources, which is using the refresh old feature here in Excel. OK, so thanks guys watching, I'll see on the next one. 18. 2-16 Connect to and Refresh External Data: Hey, guys, welcome back. So in this video, we're going to see how we can connect to and re fresh external data. So let's say you've got a file that you share with your colleagues, and it's sitting on a server somewhere. So let's say we've got this file here, which is gold external data that sits in a server. That's emissions. This foul sits in the server, and you need to connect to the data in that file. So this files teams data and you need to connect to it. So actually you can insert a pivot table and then you're gonna click on use X, an external data source, and click on Choose Connection here. And then you're gonna click on, browse for more and then browse to your Felda fell a cheese on my desktop. So I'm just gonna browse for it here external data, and I'm gonna double click on it. As you can see here, we have a view here that shows us the tabs inside that file, and we've got only one top, which is called Data, and the first wrote of the data contains columns and headers. It's just the same day that we've been using. But it's in this external data file. I'm gonna click OK, here. So you're going to choose the top where your data lies and you're gonna click, OK, and then click OK again. And now we're able to connect to that file with the external data, and we're gonna be able to construct a pivot table using the data in that particular file. So I'm gonna put the product name here on the Rose and put the sales amount on the value section and you're able to connect that file. And if the data on the external data file changes, you can always refresh it as usual by right clicking and then clicking on refresh here. And there are some options as well that you want to show you. So I'm going to right click on my pivot table and then click on Pivot Table Options year and then on the data top. There is an option here that says refresh data when opening the file. So if you check the option and click OK, whenever you open your new file that has the pivot table connected to your external data file, Excel will refresh the pivot tables in the new file. So this is also a useful feature, since it always make sure that your pivot tables are refreshed whenever you open the file that is connected to the external data file. Okay, so that's it, guys, this is how you can connect to data in an external pile or an external data source. So thanks, guys watching I'll see on the next one. 19. 2-17 Importing from Access: Hey, guys, Welcome back on this video. We're going to see how we can connect to an access database and create a pivot table out of the data in the access database. So let's say we have an access database year. I'm just gonna double click on it and open it, and it's got some data. So here we've got a table that's called Data, and it's got our data as usual. And let's say you want to connect to this access database and create a pivot table out of it so we can go in a workbook, actually, and we can go to the data tab on the ribbon here and click on from Access and then go to the death stop here and then choose our access database and click open here and now we're gonna be given the option to create either a table, just a normal Excel table or a pivot table or pivot charts or only create a connection. We're going to choose pivot table here, and you can choose the cell that you want to insert the pivot table on and do not check this check box here, which is at this data to the day tomato, Because this way, you be working with power pivot. And that's a totally different story. Totally different course. So do not check this check box and then click. OK, here and now we have inserted a pivot table based on your data and weaken. Start creating our pivot table here. So I'm gonna put the product name on the rose section and put the sales amount, for example, in the submission value section. And now I've created a pivot table based on my data in the access database. And whenever the data and the access database cuts updated, I can update this pivot table or refresh it by right clicking and then clicking on refresh , and it will update the values in my pivot table. OK, so this is how you can link a pivot table to an access database. Thanks, guys watching. I'll see you on the next one 20. 2-18 Changing your Data Source: Okay, so to speak, about viewing and changing the data source for a pivot table. So here I've got a pivot table in this worksheet this called pivot, and in order to view and change the A source for it, I can just select to sell inside the pivot table and then go to the anal. I step on the ribbon and then click on change data source. As you can see here, the data source for the spirit table is just a range of cells here in the worksheet called Data Range and basically this range of cells if we add more rose to it. So when we add more rows store data, the pivot table is not going to detect these rows, so it's not gonna include them. So that's Strider, actually. So if you go to the worksheet called Data Range, and if you select themselves here and just duplicate them by pressing control in diesel, so like some of the blank rows churl indeed to duplicate. So I'm just adding more rose to my data, and then I go back to my pivot table and then go to the analysed have on the ribbon click on change data source. As you can see here, the data source has not included the new rose. So what we need to do is to include in euros we can select all the rosier Aiken, just press shift as I select more cells and then press control shift down on my keyboard to select all the rows, so that's one way of doing it. But the better way is to put my data in a data table. So here is another worksheet that contains a data table. So, actually, if I switch my data source to the data table So if I make this data source might data table here called TVL sales So this way, whenever I add new rose, they will actually be included. If I add more rows to the table, they will always be included. So let's just copy Cem rose here and just paste them. And let's check again the data source. As you can see here, the extra data has been included automatically. This is why it's always a good practice to base your pivot tables on Excel data tables and not just ranges because whenever you add extra data to your data tables, the table are gonna include the extra data automatically and the pivot table are gonna include them as well. Where is when you have just a range of cells? They will not be included automatically. So that's a tip for you. Because when you work with spreadsheets that other people have created, they might not know this tip and they would create their pivot tables based on just data ranges. Normal data range is not the tables. So you got to be aware of that, S o always create data tables out of your data ranges, and you can always, of course, convert you arrange to a data table by selecting any cell inside your range and then insert table and you're able to create adidas table out of that and then make your pivot table get its data from the data table. So thanks for watching. We'll see on the next one 21. 2-19 Clear and Clear Filters: if you've got multiple filters applied to your pivot table. So here I've got Filter for the Americas, for example in the region and for the salesman. I've got some filters applied here for some particular months, and I just need to clear all the filters all at once with just a click of a button so you can quickly clear the filters on your pivot table by selecting a cell inside your private table going to the analyzed tab on the ribbon, and then here, under this clear button, you press on the arrow, and then there is clear filters, and this will clear all the filters on your pivot table. So now we don't have any filters on our pivot table and if you've created the your pivot table in a certain layout, so you put some fields in the rose area and some peels in the columns, infusing the values, and you need to clear the pivot table and just redo it from the very beginning again. So you can also go back to the clear here and then click on clear old and this will clear pivot table and it will be ready again to be reconstructed 22. 2-19 Select and Format: Okay, so now we're going to speak about selecting and formatting certain sections of our pivot table. So first, to select informant a certain section, we need to make sure that an option is enabled on our pivot table. So, first of all, we need to select a cell inside our pivot table, and then we're going to go to the analyze, stab on the ribbon and then go to select, and then you need to make sure there's an option called enable selection. You need to make sure that this is enable it's currently enabled, and you will see that it has ah, gray filling here in sight. If I disable it, you'll see that the great feeling will disappear. So you just need to make sure it's enable and it has that grey feeling here inside. So now, with enabled selection of a certain section of our pivot table. Now let's say we want to select a certain product here and change its color in this pivot table. So I say I'm gonna hover over product one. And when I have this arrow here, this thick black arrow pointing towards the right, I'm just gonna collect here and you can see here, it's selected Product one. Now I'm gonna go to the home tab on the ribbon. I'm gonna make Let's say I'm gonna make the fund reds and as you can see here now we have the fund for product one red. So if you actually change the layout over pivot table So say we moved the product names to the column section he can see here that product one after being moved to the column section is still having a red fund. Now will press control and Z on my keyboard to get the pivot table to the previous layout again. Now let's say I want to change the color for quarter one in all the products to yellow the fill color. So I'm just gonna hover over key one until I get this arrow here and then I'm gonna press on it and you could see your key. One is highlighted on all the products. I'm just gonna make it yellow the fill color here. And now we have you one in yellow. Now, if you move the quarter to the column section of a pivot table, you can see here that key one is yellow on all the products. It remains yellow. After we changed the layout of our pivot table, press control and Z back here, too, put the pivot table back to its previously out. Now let's say I want to apply a change to all the role labels here for all the products. So I'm just gonna hover over my product, any product, just product one, for example. And I need to hover over and be on the right hand side a bit to the right, and I will have a narrow ethic, arrow pointing downwards here. So I'll just click here and deacons here. I've selected all the row headings for all the products. Now let's say I'm just going to increase their fund size like this. And also now, if I put them to the column section instead of the Rose section, you can see here that the product labels have a bigger fund size for all the products. I'll press control and Z on my keyboard to put the pivot table back in the previous form, You can also apply certain formatting to a Hill column. So, for example, let's say when I apply is certain formatting to the year 2017 here. So I'm just gonna have our over until I get this arrow pointing downwards. And now I can actually put a border around it. So I put a thick outside border here around it around 2017 for example. And then if I change how my pivot table look, So move the quarter, for example, toe the columns. You can see here that 2017 has a thick water around it, even after we changed. How are pivot table looks? So all press control and Z, um, a keyboard here to go back, and you could basically do the same thing for any section in the pivot table. Just make sure to hover over the section until you see the arrow and then click on it and highlighted, Then apply whatever formatting you would like. And this romantic will stick to it even when the layout or the orientation of the pivot table would change. Now we've spoken about selecting each individual section on our pivot table. So how do we select the whole pivot table? Well, there are two ways the first way is to go to the very most upper left corner here, which is this cell and under the sum of sales amount. Here you can see the arrow. If you click on it, you will select the whole pivot table. Another way, which is much easier, in my opinion, is to go to the analyzed tab on the ribbon, go to select and then entire pivot table, and this will select the whole pivot table for you. And then you can delete it to press delete on your keyboard and you pivot table will be delete it. Just press control NZ on my keyboard here to restore my pivot table again. OK, so that's it for selecting and formatting. I'll see you on the next one. 23. 2-21 Moving a Pivot Table: If you need to move a pivot table there two ways to do it so you can select to sell inside your pivot table and then go to the anal ice tab on the ribbon, and then you will have here a button that says, Move, pivot table. So if you click on it, it will give you the option to either move it in the existing worksheet somewhere. So you specify another location in the existing worshipped, or to move it to a new work ship. And if you click OK for a new worksheet Excel creating you worksheet with your pivot table moved into it all the way to the upper left corner. Another way to move your pivot table is to select your pivot table, and then here, under the internalized tab on the ribbon, you go to select and then entire pivot table, and then you compress control an X on your keyboard for cut, and then you can press control and the anywhere in your workbook to move it to that location. Control V for paste. So this is how you can move your pivot table 24. 2-22 Pivot Table Styles: and this video, we're going to speak about changing styles for your pivot tables, so they change the pivot table style. You can select any cell in your pivot table and then go to design tab on the ribbon, and then you can see here. We've got a pivot table styles option here, and you can select from different styles for your pivot, table says. You can see here we've got tons of styles that you can select from. So select this style, for example. And as you can see, your each style has an array off colors and styles that you can apply to your pivot table. There are other options here as well under the design top. So there is the pivot table style options area where you could earn choose to have the road headers here, having to fill color or not, and the column headers to and you could choose toe have banded Rose or Bandit columns, Michigan's years. You can play with the pivot table styles and select the one that best suits your taste. There is another option here that I want to demonstrate, but first of all, I'll just apply a different fund style to my row Headers year. So apply a night Alec, for example, for the products. As you can see here, they have all become italics. And then, if you go again to design tab on the ribbon, there's an option here. If you right click on any of the styles, there's an auction. This is applying clear formatting and apply and maintain formatting, and this will basically either clear or maintain the formatting that you have on you pivot table when applying the style. So applying clear formatting well, actually apply the style and clear the ICT Alec Fund that we had Cizikas here. Now the product labels are not italics anymore. The products air no italics. And if you press control and see on a keyboard and then try the other option, apply and maintained formatting, you can see here that while the style has been applied, the formatting as well has been maintained. So as you consider, the products are still italics here, there is another option as well, which is that you can set a certain style to be the default style in this workbook, so that whenever you create a pivot table, you'll be created with that particular style. So if you right click on any style and then set as default Now when you insert a pivot table. So if he insert a pivot table and let's say we're gonna base it on our data here you click . OK, you can see here that the style is now that style that we set as default, which is the yellow style here. Okay, so you can set a default style in your workbook so that you can use it all the time when creating a pivot table. OK, so that's it for pivot table styles. Thanks very much watching. We'll see on the next one. 25. 2-23 Custom Pivot Table Styles: and this video, we're going to speak about creating custom pivot table styles. So in Excel you can create a customized pivot table style. Or you can even duplicate a built in style from the styles that we've seen on the previous video and make some adjustments of your own and make it your customized style. So to do this, you can select any cell inside your pivot table and then go to the design tab here on the ribbon. And then you can either create a new pivot table style from scratch. But I think that that will be a long process for you so you can click here and create a new pivot, stable style. Another option is that you can duplicate an existing style, which is what most people would do so you'd right click and then duplicate here. And then this way you have another style, and then you can name it custom style green, for example. This is a green style overall, and then you might just do a few adjustments so you can do adjustments to any of the elements here. Feel free to explore them so you click on the element and then click on format and then you can do Adjust Mr the Color. So say I'm just gonna change the shade of green for the hetero to another shade click. OK, just a minor change and there is an option here, a check box to set this as the full pivot table style for this document. That is, whenever you insert a pivot table will be inserted with this style. But I'm not going to use that and you can press okay here and this way you will have your own custom style. Here, this is we've got our system style. You click on it while selecting your pivot table, you pivot table will be styled using that style. Now, a question that might pop up in the students had when seeing this video is how can I make a custom style available for any workbook while unfortunately, there is no built in way in excel to do that. However, there are work arounds for it. So axity there to workarounds because there is a work around for 2013 in 2010 and there is another work around a different one for 2016. So actually, the worker runs for 2010 and 2013 will not work in 2016. So first of all, the work around for 2000 and 10 and 13 would be to actually select your pivot table. Go to the analyze tab on the ribbon, select the whole pivot table here, entire pivot table and then control, See to copy. And then go to your, for example, new sheet or new workbook that you need to copy the pivot table style too, and paste your pivot table. Obviously, this is not gonna work here because this is Excel 2016. But this is how you do it in Excel 2010 or 2000 and 13. So actually select my favorite table and deleted here, and this is not gonna work. So I'll go back to my original workbook here and in order to move your style from one workbook to another in Excel 2016 what you need to do is to copy your sheet from your current workbook to the other workbook in Excel 2016 and you need to copy using a certain technique. So actually you need to have your old workbook hanging over the new workbook this way. So I've got my old workbook here, or the original work for custom pivot table styles over the new workbook, which is called New Sheet. And then you can press the control button press and hold a control button on your keyboard and then make a copy of your worksheet to the new one like this. And now the new works. It has been copied, and now your style has been imported. So if you go to the design tab on the ribbon here, you'll find your new style here. Even if you delete the pivot table, even if you select it and then delete it and that insert another pivot table, insert a pivot table to a new worksheet. Here you go to the design top on the ribbon. You will have your stylus well still available. Okay, so that's how you can move your style from one workbook to another in Excel 2016. Unfortunately, there's no built in way to do it so far, so thanks for watching. I'll see on the next one 26. 2-24 Subtotals: in this video, we're going to speak about changing the options for the sub totals in pivot tables. So sub totals are the totals for certain feels in your pivot tables, and let's see how we can change them. So if we select any cell in our pivot table and then go to design tab on the ribbon and then under the Laila Group here, there is an option called sub totals. So Weaken choose to not show the sub totals. So the subtitles here, for example, are settles here bold ID for product one and product to. And so for each product here, these air sub totals So subtitles a usually actually bold ID. So if we choose to not show the subtitles, you'll see that they will disappear. There you go. And then you could choose to show also toes at the bottom of a group. This way we will have the subtitle for each product at the bottom. Here she can see air suitable for product wins a total for product to at the bottom off the group and then show all subtitles at top of the group. You will see that the subtitles will move back to the top, and that is the original layout that we had previously. Also, when you add more fields under the rose section here, you will see that there will be a subtitle for all the fields except the lost one. So basically, if we had a fueled for the region, for example here, so we'll have product name and then sales person and then region on the roads sections you can see here. You'll see here we will have a sub total now for the product and then acceptable for the sales person. But we will not have a sub total for the region because this is the lost item, basically the bottom most item in the row section. This item does not have a subtitle because each item is basically it's a total. It doesn't need to have a subtitle there. No items to break it down with. Basically. So this is how you can change septal options in pivot tables. Thanks for watching us see on the next one 27. 2-25 Grand Totals: and this video, we're going to speak about grand totals in pivot tables. So when we select a pivot table, we have the option to view. The grand totals wore either the Rose on Lee or the columns on Lee or for both. Or we can actually hide the grand totals for both the rows and columns. So to do that, you need to select a cell in your pivot table and then go to the design tab on the ribbon and then under grand totals. Here you'll see four options off for rows and columns, and that removes the grand totals from both rows and columns. So if we click on that, you can see here that the grant holders have been removed. The grant of those feroz and the grand totals for columns they both have been removed. And if you go back to the grand totals option here, if you press on for rows and columns, you can see here we have grand totals for both the roads and the columns. And then there is on for Rose on Lee, so that shows the grand totals Onley for Rose. And then there is on for columns only. and that shows two grand total Onley for columns. However, usually I prefer the on for rows and columns option. So thanks, guys watching I'll see on the next. 28. 2-26 Report Layout: and this video, we're going to speak about the different report layouts for a pivot table. So excel pivot tables have different layouts that you can choose from, and in this video we're going to speak about the different layouts. So in order to change the layouts for you, pivot table, you need to select a cell inside your pivot table and then go to the design tab on the ribbon and then under the layout group here, you've got the report layout option, and currently we are in the compact form, the compact form lee out. So this is the default lee out that you get when you create a pivot table. So this is the compact form. And as you can see here, we've got two fields in the rose section, the product name and the sales person. And, as you can see here, both feels are in the same column as you can see here. So got each product, and then we've got each sales person. They are old on the same column and at the top here with just got a generic label. It doesn't say product name or sales person. It just says grow labels Now let's try the other layup that we have here, which is the outline form and, as you can see here when we select the outlying form, because here that each field on the Rose section now has been moved to its own column here . So you got the product name in the first column and then the sales person in the second column. You can see here as well that the labels or the headings for each product here. So for product one product to and product three, you can see here that it's in. It's own separate TRO above any sales person. So, as you can see here, this is the first sales person. But you can see here that the product being above it in the Rose section each product is in its own row. Because here that we have our sub totals at the top, however, we can always have them at the bottom, just like we showed on a previous video and injections year. Now we have an extra row inserted for subtitles, and they are at the bottom off the group. When using this outline form were able to also repeat the item labels so we can actually make product one appear in each row here. And to do that we can go to the airport layout and then we have an option that says, repeat all item labels and you will see here that now the row heading