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

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

Abdelrahman Abdou, Data Analyst & Excel Lover!

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

Abdelrahman Abdou, Data Analyst & Excel Lover!

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
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.

65

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

Hi, I'm Abdelrahman and I'm and Excel Lover :)

I'm also the founder of ExcelBonanza.com, a website dedicated to making you better at Microsoft Excel!

I'm a Certified Microsoft Excel Expert with 10 years of experience in Excel.

I strive to make my courses engaging and informative to help you get better at Excel!

See full profile

Class Ratings

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

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

Your creative journey starts here.

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

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

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

phone

Transcripts

1. 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 headings are gonna be inserted in each row, so this is actually useful. If you're trying to do a look up And if you're trying to get your data in a tabular form or if you're trying to print your data in tabular form, this would be useful. I'll press control and zeal my keyboard to undo that and remove the repeating off item labels. And now the third report layout that we have here is the tabular form. So if we select that, you can see here that now the labels of the first row field are not on their separate TRO anymore. Aziz, you can see here now it's product one, and then we have the sales person, the first sales person starting on the same row. Where is on the outline form? You can see here that this first sales person starts on the following Rome not the same bro . So this is the tabular form for you. You're also able to repeat old item labels on that form. And, as you can see here that Sepp totals on the tabular form appear at the bottom. But they cannot appear at the top anymore because we don't have space for them. Because now the item labels for the salesperson start on the same row as the item labels for products. So as you can see her if I go to sub totals and I tried to show the subtitles at the top, I can't do that on the tabular form. I can either just remove the sub totals or have them appear at the bottom. I want to point out as well that if we go to the compact form were not able to repeat item labels because basically, there is no space for the item. Lee was to be repeated because all the item labels here are owned the same column. So the product name and the sales person they're both on the same calm. There's no space to repeat the item labels. They're already there. Okay, so these were the three different layouts for pivot tables that you can choose from by default. You do get the compact form at the beginning, and then you can choose which ever form that you want. But when you create a pivot table, it's always gonna be created in the compact form. So thanks guys watching this video and I'll see on the next one. 29. 2-27 Blank Rows: When using pivot tables, you have the option to insert an extra line after each item. And to do that you need to select a cell inside your pivot table, go to design tab on the ribbon, and then here we got under the layout group would get blank rows here. And if you click on insert blank line after each item, sincere that now a blank line gets inserted after each item. So that gives you a little bit more space on your pivot table report. If you need not to have that space, you can click on Remove blank line after each item, and as you can see here, the blank line has been removed. 30. 2-28 Expand and Collapse Buttons: and this video, we're going to speak about the expand and collapsed feature in pivot tables, so this feature allows you to drill down on specific rows and columns, or you can also summarize at a high level. So next to each item label, you will see here a plus and minus button, and this allows you to expand or collapse that particular label here, says Eakins. Here I'm expanding and collapsing product one. And that means that I'm either showing or hiding the breakdown off product one by sales person. As you can see here on a row section, we've got the product name, and then we get the sales person under. And that means that the product name will be broken down by sales person. If you need to expand or collapse, not only one item, so I'm currently expanding and collapsing. Product one. Just product. One. I can also expand and collapsed the entire field. That means the entire product name fields, all the products product on product tube and product. Three. We can do that by right clicking and then expanding collapse. We have here also an expanding collapse option, and this will expand and collapse just this particular item in the field. But then we have expand entire field and collapse entire fields, so collapse entire field will collapse all the products, the whole product name, field and then expand. Entire fueled will expand all the products as well. Another way to do it is to select. It's the item on your fields year. Go to the analyzed tab on the ribbon and then under the active field group here you will see claps, field and then expand field, and that expands and collapses the entire field. Now what happens if you try to expand the bottom? Most field in the hierarchy here says, You can see here on the roads section. We've got the product name and then the product name gets broken down by sales person. But there is nothing that the sales person gets broken down by or Goetz's drilled into bicycles. Here it's the bottle, most field of our hierarchy. So what happens if we try to expand that? Let's see, Let's right click on any item on our sales person. So any sales person here and then expanding collapse press expand and basically what you get here is a dialog box that asks you which field you would like to expand the sales person with. As it is here, we can expend it by any of these fields. So let's choose the quarter, for example, On press. Okay, as you can see, are now each salesperson gets expanded by quarter, and that means that each sales person gets broken down by quarter. We can also expand the quarter further, for example, by month. So if we right click here on any of the quarters and then expanding collapse, expand that and we can expend that by sales month, for example, impress. Okay. As you can see her, each quarter is being expanded by month. And because you're not all the months air appearing because not all the months, half sales. So basically only the months that belonged to this quarter that do have sales are appearing basically. And and this is also for that particular sex person says you can see here we get each quarter broken down by month. You can also do the same here on the column section. So even if we move the quarter here to the column section, you can see here that we've got a drill down here year. You can drill down into the year by quarter, as you can see here, using this expand and collapse button. And then you can also drill down the quarter by month as well as you can see here, you can drill down the quarter by month. You can also right click and expanding collapsed the entire field as we showed previously. We can also collapse the whole pivot table here so we can actually click on the year and then collapsed field, so that basically collapses. Older feels under it. You can also collapse on Lee the quarter so that you would still have the year broken down by the quarter so you can actually shape your pivot table whichever way you like. So you can basically collapse. The pivot table here collapse entire field so that you get a high level view that you can send to the upper management or the executives, for example, who don't care much about the details they just wanted. See the big picture, and then you can also drill down into individual items so that you can do more analysis on your data or send that to the particular departments who would basically be interested in the details. One more thing I'd like to show you is how you can hide or show the expanding collapse buttons to these plus on minus buttons. You can do that through going to the anal I stabbed here on the ribbon and then here you've got a plus and minus buttons button here. So if you press on that, you can tuggle these plus and minus buttons on or off so that you can show on hide them. And by the way, if they are hidden, you can still expand and collapse. Using the buttons year on the active field area, expand field or collapse field here is you can see or by right clicking and doing that through the expand collapse options. So that's it, guys, for the expanding collapsed feature on pivot tables, Thank you very much for watching and 31. 2-29 Move and Remove Fields and Items: and this video, we're going to speak about moving and shifting around fields on items in our pivot tables. So when it comes to items, we can actually move on item from one position to another in a pivot table. So here, for example, for product to which is an item under the product name field. It's not the first item here on the field. You can see here that first item here is product one, but we can actually move product to to replace product one here and become the first item on the product name field. And to do that, you can just click on product to here and then hover over the corner for product. Two year until you get this foresighted arrow. Click and drag upwards here to replace product one. And as you can see here, we managed to replace product one with product to and product to is now the first item on the product name field. I'll press Control and Z on the keyboard toe. Undo that, and alternatively, you can also click here on product one, and then you can type product to instead, and this will replace product one here with product to and product who becomes the first item here. Ah, press control NZ to undo that and then you can also do the same thing with the year. So here we've got 2017 is the first year we can make 2016 the first year here. Or we can put back 2017 to be the first year normally, but default. You should have 2000 and 16 as the first year here. You have other options as well, so you can move around on item label by right, clicking on it here. And then there is a move option here, and then you can move it one step down. So move product one down will move it. One step down here to replace product to, or you can also move it to the end. And that means that you will go down to the very bottom here, and when it's at the very bottom, you will have an option here, move it to the beginning or move it up. So up one step or to the very beginning, to put it back here to the very top position, you get the idea, and if we explore the move option more right click move. Here you can see air more product name to the right or to end, and this will basically move the field. Either one step down or to the very end here on the roads section. Or you can also move it to the column section. So if you click on this move product name to the right, this will move it one step down on the roast sections. As you can see here and now, it's sales person than product name. And if there had been three fields, we would have been able to notice here the end option. But you know the end option. We've got only two fields. But if you add 1/3 field the quarter, for example, we can make the product name field. For example, if you used the end option here, we can make it at the very bottom field here. You can also use this Rosen column section as well to move around fields, and this is much easier even. And of course, you can move items fields from the rose section to the column section. This way. So basically, this has been a demonstration on how to move and shift around fields on items in our pivot table. So thanks so much watching, I'll see on the next. 32. 2-30 Show and hide Field List: As you can see here, I'm selecting my pivot table and I can't see the field list. All the reason for that is that the field list can actually be shown or hidden. And to do that, you can go to the inn Ally, stop here on the ribbon after selecting a cell inside your private table and then click on field list here. As you can see her, this will show your field list, and if you click again, this will hide your field list. Alternatively, you can right click on your pivot table, and then there is height field list. Here. You can click on that to hide the field list in the or right click and then show the field list. So as you can see where you can show on hide your fueled list on your pivot table. 33. Show and hide Field Headers: if you need to show on higher do field headers such as thes row labels or column labels. So these headers here you can go to the anal. I step on the ribbon here after selecting a cell in your pivot table, and then you can see are a button that says Field Heather's. You can toggle the field headers on our off using that button. 34. Number Formatting: If you need to change the number formatting on your values field, you're able to do that using multiple ways. So one way is to go to your values field here, which is the submission of sales in this case under Submission of Values section here. So you click on this arrow and then there is value field settings and then you click on number format and you're able to change the formatting of your number here. So this is the sales amount. It should be in dollars. So I'll press on currency here and I'll choose United States dollars for examples of English. United States are most of the distant places are click OK, click OK again. As you can see here, the values field formatting has changed. Alternatively, you can right click here on any cell on the values field, and then there is value field settings here, and this leads you to the same option. So this is how you can change the number format for you values field. Another way to do it is to press control and one on your keyboard, and you will get also to the same dialog box here pushes for number formatting. And also, if you need to learn more about number formatting and creating custom number four months, you can check the video on my YouTube channel. I'll leave the link in The Resource is section If you'd like to check that 45 minute in depth video about custom number formatting, So thanks for watching guys I'll see on the next one. 35. Changing Field Names: and this video are going to speak about changing our fueled names. So here we've got our pivot table and let's say I want to change the name of a field here, which is the sum of sales amount. Let's say I want to change it to just sales amount. So if I click on this arrow here and click on value fueled settings and try to change it to sales amount, I'm press okay. Actually, I'm going to get an error. And the reason for that is that the original name of the field, which is the same thing as a column. So the original name of the field or the column here on your data table is sales amount. So you're trying to assigning the same name of an already existing field. So this is why you're getting an error. So to get around that you Congar back to value fueled settings and you can make it sales amount. But just add an extra space here, impress okay, and Excel will accept that it will recognize this name as a different name than the one that you have on your original data table. Of course, you can change it to any other name if you'd like, so you can go back and make it total sales, for example, and that's totally acceptable. Another way to change field names is through the anal ice tab on the ribbon. So while clicking inside your pivot table, go to the analyzed type on the ribbon. You can see here that the latest name we've assigned to the sales column has been reflected here on the active field. It says, Total sales. Let's say I want to change my year to sales year, for example, and I can do that and press enter and the name has been reflected here is well on my pivot table field, Spain. You can see here it's now sails year instead of year. So this is how you can change your field names on your pivot table. 36. Presetting Number Formatting: Okay, so here we've got a pivot table. We've got our months on the road section and we've got the submission of sales amount here on the submission of Value section. And let's say we want a place of formatting to the submission of sales amount. So Atacama and a currency so we can click on any cell here on the sales amount and then right click and go to number format. Alternatively, compressed control on one on your keyboard and you'll get to the same dollar box here we can go to accounting. We can remove the decimal places and give it an accounting form. And we can also select our currency here. English, United States press okay. And Deacon sear that we've applied formatting to the submission of sales amount. However, if we put the sales amount again on the submission of Value Section to do, let's say a different kind of announces, he can see here that the formatting has not been applied and we'd have to reapply it again . So to get around this and make our formatting stick, we need to do it in a different way. So press control and Z twice on my keyboard here so that I would go back to the original form and then while selecting a cell inside my pivot table, I can go to the analyzed tab on the ribbon and then go to select and entire pivot table, and then I'll go back to select and go to values year, and then we need to go to the number formatting. But instead of right clicking, you cannot right click. In this case, you have to press control on one on your keyboard to press control, and one because if you right click and select format cells, it will not work. So we pressed control, and one, we are now on the format cells dialog box. We're gonna go to accounting, remove the decimal places and select English United States. And then we're gonna press. Okay, Jake is here. The formatting has been applied. Now, if we drag and drop the sales amount again on the submission of Value Section, you can see here that the formatting has stuck to this sales amount and this is how you can apply formatting to your values and make it stick 37. Moving around multiple aggregation fields: Hey, guys, welcome back. So in this video, we're going to show a nice tip that you can use when you have more than one field in the values area to Currently we have only one field, which is the sum of sales. So that's at another field to the value section. And let's make the aggregation be the count for the sake of argument. So I'm just going to drag the sales amount here to the submission of Value Section, and I'm gonna change the aggregation on it. So I'm gonna left click on it and then value field settings. And then I'm going to select count instead of some and then press OK year because here that we now have two aggregation fields. But you can see here as well that we have an extra field that's appeared here on the world section, which is the Sigma Values field. And this field allows you to move multiple aggregation feels at once. So actually, this field here, the signal values feel with all hours to move the sum of sales and count of sales both at the same time, so I can actually move it to the column section and because here the view has changed. And we've got actually our aggregation fields here stacked across the columns. And if we move it back, you can see her. The view that we've got here, which is that each sales person has a breakdown by the some of sales amount and account of sales amount. And we can also move it above the sales person. And in this case, we're gonna have a breakdown for each aggregation. So there's some and account. We're gonna have it broken down by the else person. So that is a nice small tip for you here, which is that you can move multiple aggregation feels at once using this submission of values field that appears when you have multiple aggregation. Feels so. Thanks, guys watching. I'll see on the next one. 38. Indenting rows in compact form: Hey, guys, welcome back. So as you can see here, we've got a pivot table, and this pivot table has the park team and the sales person on the road section and the year on the columns section and the summation of sales year on the Submission of Value Section. And as you can see here, this pivot table, if we select a cell inside it, go to design Tab and then on the report layout. We're currently actually in the compact. For now. If we switch to the outline form, you will see here that the sales person has been indented to the right, and it is in its own column at the moment. Now we can actually achieve something similar to that. But actually, in the compact form, we've explained the different pivot table layouts reportedly outs previously on a previous video. So please refer back to that video. Now, if we switch back to the compact form, there's an option that we can use, which we can get to by right clicking and then clicking on pivot table options. And then there is an option here, it says, when in compact form in dentro labels by one character. It's, Carney said at one. But weaken. Change that to 10 for example, 10 characters. And then we press okay. And as you can see here, it's been indented easy, cause here the sales person has been indented by 10 characters. It looks now more like an outline form, but actually we're currently in the compact for so it's a way to achieve a look that is similar to the outline form, but actually in the compact form. 39. Changing the layout of a report filter: in this video, we're going to see how we can change the filter layout in pivot tables says You can see here we've got a pivot table here we get a product name on the Rose and the Year on the Columns section and the submission of sales on the Submission Value section, and we get a bunch of filters year, region sales, person quarter and sales month. And as you can see here, the filters are in a single column, but you can actually make them be in a single row or multiple worlds. And you can set a limit on how many filters appear per column or Perot so that if you said a limited to, for example, than you'd have to filters in this column and then Excel would create another column for the other two filters. So we'll see how we can do that. Basically, you can right click on any cell in your pivot table and then go to pivot table options here , and you can see here there is an option here, display fuels and report filter area down than over, so it's currently down. Then over this means that they'll be stacked across columns like this. And then there is report filter fields per column, so it's currently zero. So that means it will put all the filters in one column. But let's say I'm gonna put a limit of two per column so we'll actually put each two filters in a single column. So we get here full filters, so we'll have to columns here. So each two filters are in a column. You can also right click here pivot table options and then the second option here instead of down then over. It's over. Then down this will make them get Start the crossroads. I'll switch to the zero option here in you can see here it's now, report filter fields, Perot instead of pair column. So press Okay, so zero is usually the default option to see what happens here. You can see here that now the filters are spread here in a single row is against year. They are all in a single rope. Now, if we go again to the pivot table options and then we can make it to Perot. So we've got four filters, so we're gonna have to rose with two filters, each press OK you can see here we get to Rose and we've got to filters per each row. So this is how you can change the pivot table filter, Leo. 40. 2: Sometimes your pivot tables might have some errors like the division by zero error, for example, and you could have this air because one of the values here had a division by zero air on your data set here. So one of the values on the sales amount had a division by zero error, for example, so to deal with that error or to change how it would look on your pivot table instead of this ugly Dave error, you can right click on your pivot table and then go to pivot table options and then under layout and format, here we get for air values show, and then you can just check that check box and then write the value that you need to display instead of the error. So you could write a zero, for example, or you could write the word error and press OK. And as you can see here now, the word there appears instead of division by zero. Or you can also change it to a certain number such a zero, for example, press OK and you can see here it's been changed to a zero, so this is how you can change how air values look on your pivot table 41. Formatting empty values: Sometimes you might have empty data on your pivot table. So, for example, here in September 2016 we've got empty data here, and the reason for that is that if we go back to our data, we can see where the transactions that happened in September 2016 did not have a sales amount. The sales amount is blank. This is why, on our pivot table, September 2016 is blank so you could change how the blank cells show on your pivot table. You could do that by right clicking and then going to pivot table options year. And then there's an option here for empty cells show so you can show no transactions, for example, or no data or anything else. So here we've shown no transactions. So this is how you can change how empty cells show on your pivot table. 42. Keeping column widths the same after refreshing: As you can see here, we've got a pivot table. And if I changed the width of its columns and try to refresh the pivot table, so right click and then re fresh because here that the width of the columns returns back to what it was. So to prevent that from happening, we can right click on the pivot table and then go to pivot table options. And then there is an option here. There is so to fit column wits on update. So if we disable the option and press OK here and then we change the width of the columns again and then right, click and refresh because here that the columns have not returned to their with before we change the with so you can see that the columns retained their with that we changed them to . 43. Autorefreshing pivot tables upon opening workbooks: Let's say that you have a workbook that has its data source in a different server, for example, or that this workbook is a shared workbook between you and your colleagues, and you need to make sure to refresh the pivot table on it whenever you open the workbook. So whenever you open the workbook, you refresh it manually by right clicking and then clicking on refresh. Well, this could make you prone to forgetting to refresh the workbook, right? So what you can do is that you can make the pivot table get refreshed automatically whenever you open the workbook. And to do that, you can right click on the pivot table and then go to pivot table options and then go to the data top here. And then there is an option. He refresh data when opening the file. If you check the option and press OK, your data will be refreshed whenever your workbook is opened. So this is how you can refresh your data automatically whenever you open your workbook. 44. Printing Pivot tables: if you need to print your pivot table, Where you need to do is to select your whole pivot table by selecting a cell inside your pivot table and then going to the analyze tab on the ribbon and then under the select option. Here you click entire pivot table. You got to select the entire pivot table, and then you go to the page layout tab under the ribbon here, and then you select your print area. So you click on print area and then set print area, and now you're print area is set to be the pivot table. And now, if you go to file and then print and consider that our pivot table is set to be printed here. So if you press print, your printer should print your pivot table. If you need to separate your pivot table into multiple pages, what you need to do is to select the road that you need to have a separation on. So in our case here, it's gonna be girl having the year 2017 for example. So we want 2016 to be on a page and then 2017 to be on the following page. So I'm going to select the road here for 2017 and then under the page layout top here, we're going to select breaks and an insert page break sickens. Here there is ah ah, line that's been inserted. Here it's This is a page break, and this will break your printing area. And now you've got to select your pivot table again and go to the anal. I stub on the ribbon and then select entire pivot table, and then you gotta go again to the print command. You can either do it through the file menu as well done before you compress control and P on your keyboard. As you can see here now, 2016 is going to be a printed on the first page on. Then, if we scroll to the next page, you can see here we get 2017. It's gonna be printed on the following page. So this is how you can print your pivot table and how you can also separate you pivot table or split it into multiple pages. If you have more years, for example, you can insert multiple page breaks 45. Report Filter on Multiple Pages: What we have here is a pivot table having our sales per month for 2016 and 2017. And as you can see here, we've got two filters on our pivot table. We've got the customer filter and the region filter. What we need to do is to create a worksheet for each customer here, our customers air three companies Millennium, true life and visionary. And we need to create a worksheet for each customer having a pivot table with the numbers for that particular customer filtered. So where we can do is select a cell Insight are pivot table, go to the analyzed tab on the ribbon and then under options. Here we go to show, report, filter pages, and then we're going to select our customer show all report, filter pages off. We're gonna select customer, and this will create a worksheet or a tab in a workbook for each particular customer. So we're gonna have three tops for each other three customers if you click. OK, here. You can see here we've got a top for millennium, Another one for true life, Another one for visionary. And as you can see in each worksheet or in each top you can see here the pivot table is filtered for that particular customer. As you can see here, true life is filtered for to life, and visionary is filtered provisionally. Now what we can do is that we can group these sheets and print them, for example, so that we can have a page for each customer. So to do that, we're going to select one customer, and then we're gonna present hold the shift bun, and then we're going to select here. The sheet for the loss concerns you consider it selects all the sheets in between on now we can press control and pee on our keyboard for printing. And now we can print the worksheet you can see here. We've got three pages, one for millennium, the other one here for true life and 1/3 1 for visionary. Now we can print our report here. We're gonna print it to a pdf, so I'm gonna click on print here and now I'm gonna save it to my desktop. And this is gonna be sales per customer. I'm going to save it. And when we open our pdf, you can see here We've got a pdf report here with the sales for each of our customers on a different page. Now we can UN group are sheets by right clicking on any of them and then on group sheets. And this is how you can own group your sheets again after grouping them. And now this is how you can spread a filter on multiple worksheets so that you can print a report with each item off the filter on a different page. 46. Exploring different aggregation options: and this video, we're going to go through the different aggregation options that are available in pivot tables says You can see here we get a simple pivot table where we got the year and then it's broken down by months, and we've got the sales amount for each year and for each month as well. So you can see here that the aggregation that we've done is the summation. So we summed up the sales amount, but we can do other types of aggregation as well. So to do another type of aggregation on the sales amount, for example, where you need to do is to drag your field here. Which anarchists is gonna be the sales amount. We're going to drag it again to the submission of Value Section, and then we can click on the field itself and then go to value field settings and you can see here we've got other options. So summaries values value field by other options other than the sum weaken do count, for example. And if I click OK, this gives us the count of rows for that particular item. So here in January 2016 we've got eight rows Now, if you check our data set and that filter for 2016 end for January, he can see here that we've got eight rows. I'm going to remove my filters, you but pressing control and see twice on my keyboard. And then I'm gonna go back to my pivot table and then let's explore mawr aggregation options. You can also go to the aggregation options. Another way to do it is by right clicking here and then summarize values by Nicosia. You've got some options and you can also more options for the full list here so we can do average as well. On this will give you the average sales instead of the count of number of sales. And then we can also do the max here, and the max will give you the amount on the transaction that contained the maximum sale. So here in 2016 January, we filter again here. By 2016 and January, he can see air that the maximum sales him out on a particular transaction was 94,605 which is the same amount that we've got here. You could also choose minimum instead of maximum, which will give you the amount on the transaction that contained the minimum sale. And if we go to more options as well, we've got here product which will basically multiply the amounts of all the sales transactions that happened in January. So we select product. It will give us a very big number, and this number is basically the result of multiplying all these sales amounts by each other. So if you go back to our pivot table on, if we explore more options that we got here to go to more options, we've got also standard deviation for the simple and then for the population and variants for the simple and for the population. The other option that you need to highlight here is the count numbers and count numbers will count on Lee the cells that contain numbers. So, for example, here in January, we've got the count numbers. It's eight and it's equal to the count because account will count both Dexter numbers. But count numbers will only count numbers. But all the values here are numbers actually on the sales amount. But if we replace one value, for example by text here just any kind of text. And if we remove the filters here and then refresh our pivot table. So now we've got a value. On January 2000 and 16 there is a text, so the count numbers should be seven. As you can see here, it's become just a seven. So this is a useful option. If you need toe on Lee, count the cells that contain numbers. Okay, guys. So that's it. That was a quick tour through the different aggregation options that we got here on the value field settings. Thank you very much watching I'll see on the next one. 47. Adding multiple subtotals: when it comes to settles in pivot tables, the default aggregation applied for settles is a summation. So, for example, here the subtitle here on product one is basically the summation here for all the sales done for sales persons that sold product one so you can see her. 1,095,430 is the summation poor? The sales for all the salespersons that sold product one, however, were able to apply more aggregations on the sub totals. So we're able to have account or an average, and were able to add multiple subtitles as well at the same time. So let me show you how we can do that. So we're going to go to the field that has the subtitle that we need to change here on the field list. So we're gonna go to the product name because this is the subtitle that we need to change, click on it and then go to the field settings. And then here we've got some options. Under sub totals of filters, you got automatic. Automatic is basically the summation. Some is the automatic subtitle is applied and we've got none which would remove the sub total and with custom and custom opens more options for us. Here on a zika sear, we can add some count average max men product all these guns of subtitles and were able to add them at the same time as well. So we can click on one and then press and hold the control button on our keyboard and then click more. Subtitles that we need to add I'm gonna add some count, average max and men all of the same time. And if we press okay, here, you can see area that we've got different sub totals. Add it to our subtitles. So this is a great way if you need to include multiple settles at the same time multiple different sub totals at the same time quickly. 48. Percentage of grand totals: and this video, we're going to see how we can create a percentage of grand, total calculated field in our pivot table. So we're able to insert a calculated field on the submission of Value section that would calculate the percentage of each of the values in our pivot table from the grand total value here that has red borders on the bottom, right corner of our pivot table. So to do that, what we need to do is to insert another field here on the submission of Value section. But we're going to click on it and then go to value field settings and then go to show values as and then instead of no calculation or gonna select percentage of grand total. And we can change its name as well. 2% of grand total and we can click OK here. And as you can see here, we were able to insert a calculated field that calculates the percentage of grand total for each value on our pivot table. So basically this value is 9.9% from the grand total value. And actually, if we some the individual values here across the columns, for example, so if you some this value and this value you will see here that there summation is 15.61% of the grand total, which is basically the percentage of this sub total here from the grand total. So this subtitle 1,266,446 is 15.61% off this grand total. So basically this percentage of grant a little would insert a calculated field that would indicate the percentage of each item over pivot table from the grand total. 49. Percentage of Column Total: Another calculation that we can perform on pivot tables is the percentage of column tool. So we're able to calculate the percentage of each value from its column total. And to do that, we're gonna insert our fuel that we need to apply the calculation. Stewart, In our case here, it's the sales amount, and then we're going to click on it, go to value fuel settings and then go to show values as and then here, we're going to select percentage of column total, and then I'm gonna rename it to percentage of column tool as well. And then I'm gonna press okay, here and now we have a calculation that calculates the percentage of each value from its column total. So, for example, here 17.47 percent is the percentage of 737,378 from this total here, which is four million, 221,579. And if we sum up the percentages of our sub totals, so we sum up 17.47 percent plus 21.9 plus 61.45 here if we sum them up. We get a submission of 100% and basically their 100% is this value here, which is the total for the whole column. And if we sum up all these individual values here and exclude the sub totals, we're going to get 100% as well. And this is because each of these values, the percentage beside it, is the percentage from the column total basically. Okay, so this is how you can perform a percentage of column total calculation in pivot tables. 50. Percentage of Row Total: in this video, we're going to see how we can perform a percentage of Roe total calculation in pivot tables . So a percentage of wrote a little calculation would give us the percentage of each value from its row total. So let's see that with an example here. So I'm gonna insert again the sales amount. I'm gonna put the sales amount fueled on the submission of value section. So I'm gonna create another instance of that fueled here and then I'm going to click on it , go to value field settings and then go to show values as. And then I'm gonna select here percentage of Roe total and I'm gonna change its title here 2% of Roe total and I'm gonna press OK, and now we have a percentage of wrote a little calculation here. So as you can see here, we have the percentage of the row total beside each item here or beside the value of each item. So, for example, here on this sub total is 737,378. It's 58.22% off the road total, which the row total here is one million, 266,446. And basically this value is 77% of its roto wrote and its Roe total here is ah 110,699. So 85,235 77% of the 110 1006 199. So this calculation here will get us the percentage of each value from its row total. And this is the road total here, which is under the submission off the sales amount that so the value on each row here represents the total for each row. And if you check this value, for example, this susceptible here is the total for this Plus this. So these two values here some up to 1,474,967. So this is how you can perform a percentage of wrote a little calculation in pivot tables 51. Percentage of Calculation: in this video, we're going to explain the percentage off calculation in pivot tables, so the percentage off calculation displays the value of one item as a percentage of another item. Okay, so let's take an example for that. So I've got a pivot table here that shows the sales on each year here for each region. And let's say I want to display the sales of each year as a percentage of the previous year . So I want to see the percentage of 2017 sales from the sales off 2016 for example. And if there had been a 2018 year here on my data, then I'll be able to display the percentage of the sales in 2018 from the sales in 2017. You get the idea. So to do that, I'm gonna insert another field here for the sales amount so that I can edit that field to display the sales as a percentage of the previous year. So I've got this sum of sales amount to I'm gonna click on it, go to value field settings here, and then go to show values as on then for the show values as instead of no calculation, I'm gonna select percentage off and they want to display the percentage of the previous years. I'm going to go to the year field here and then I'm going to select previous and then I'm going to rename it so I can rename it percentage of previous year and then I'm gonna click . Okay, here and now. As you can see here, I can see the percentage of the sales from the PVC ear off course because 2016 is the earliest year on my data. There isn't a year previous to that, so it's showing 100% because we're getting the percentage of the sales of 2016 from the sales of 2016 which would deal the 100%. But here in 2017 I get the percentage of sales for 2017 from 2016. So basically saying here that the sales for 2017 war 65 points 69% off the sales in 2016 you could also get the sales off a certain item as well. So I'm going to go here to the stop percentage of product one. So let's say we want to get the percentage of the sales for each product as measured from product one. So I want to display the percentage of the sales for product to as a percentage of the sales for product one and percentage of the sales were product three as a percentage of product one and so on and so forth. Let's say that product one is our main product and we wanna get the percentage of sales for each product with respect to product one. So we can do that as well. We're gonna insert another field here from the sales amount, and then we're going to click on it, go to value field settings, go to show values as and then percentage of, and we're going to select here. The base field is going to be our product name. So this is the field we're gonna base our sales on. And then we're going to select here Product one. So this field here is gonna be named percentage of product one sales, and we're gonna click OK here. And as you can see here, this shows us the percentage of sales with respect to product one. So here, because product who has sold more than product one is showing more than 100% here on 2016. So that means that product to was 124 percent 0.65 of product one. This means it sold about 25% more than product one. And this is the case for product three and for product for as well. It shows the sales as a percentage off product one. Okay, so this is how you can do the percentage of calculation in pivot tables. 52. Percentage of parent row total: and this video, we're going to speak about the percentage apparent row total calculation aint pivot tables . So the percentage apparent row total calculation displays the percentage of an item from its parent row total. So we can display, for example, the percentage of Q three in America sales from the total sales for America's. So let's have an example here. So I'm gonna drag the sales amount field again here to create another field of the sales amount. And then I'm going to click on it good to value field settings and then go to show values as. And then I'm going to select percentage your parent row total. And then I'm gonna change its name here. Percentage of parent trow and then I'm going to click OK, here. And as you can see here, we get the percentage of the parent TRO for each item. So here, for example, 7.6% for key one is basically it's saying that key one is 7.6% of the total sales in the Americas and Q two is 32 point 63% of the total sales in the Americas as well. And here when we say that on the Americas is 21.9%. That means that the Americas are 21.9% off their parent row. So the parent TRO, because the Americas heir a sub total their parent rose actually the grand total year at the bottom. So this means that the Americas are 21.9% of the grand total off the sales, and Australia is 17.47% of the grand total of the sales in 2016 and so on and so forth. So this calculation displaced the percentage of each item from its parent row. Another example that we can have here is displaying the percentage of each month from its parent row. In this case, in the case of this pivot table, which is its year so we can do the same here, drag another one for the sales amount and then go to value field settings here show values as percentage of parent row total, and then we could name this percentage of year, for example, and if you click OK, here, you can see here the it displays the percentage of each month from its parent here. So here February under 2016 is 5.42% of the year 2016. And actually, the year 2016 is 52.2% of the grand total for 2016 and 2070. I need to also add one more thing here, which is that when we say that a field is the parent of another field, the reason that it's apparent of another field is that it's above it here in the hierarchy on the Rose section here. So here the year is the parent troll of the sales month because it's above it on the hierarchy here on the Rose section. The same goes here on the first example where the region is the parent of the quarter, because it's above it here on the roast section. So actually, if we put the quarter above the reason like this, the quarter will be the parent of the region. So this is what it means when you do a percentage of the parent total here, which is that it displays the field as a percentage of its parent. So thanks, guys were watching and I'll see you on the next one 53. Percentage of parent column total: in this video, we're going to explain the percentage apparent column calculation aint pivot tables. So you could probably guess what that does since we've explained the percentage of Parent Row total calculation. So instead of showing the percentage based on the parent row, it's going to show the percentage based on the parent column. So that's insert another field here for the sales amount and then click on it. Go to value field settings go to show values as on them percentage apparent column total here, and we can name that percentage of parent column. And if you press OK here, you can see here what we get. The percentages that we get here are the percentages of each quarter from their parent column, which is the year here. So the year here is the parent column, since it's the one above the quarter on the columns section here in hierarchy. So what it's saying here is that, for example, for key one, it's 7.6% in 2016 off the total sales for 2016. And for Q two it was 32.63% off the total sales in 2016 and so on and so forth. If you add the percentages for each quarter in 2016 here for key one key to Q three and Q four, you'll see here that we get a submission of, ah, 100% under 100%. It's basically this amount of sales here for 2000 and 16 and also here for 2016. It's 60.36% off its parent column. So the parent column is the grand total for all the years here, which is this amount. So here you can see here that 2017 the percentage of it is 39.64% of the parent calm. And the parent column is this total some off sales amount, which is this grand total. That represents the grand total for all the columns here. Okay, so that's it, guys, for the percentage of parent column total. Thanks for watching. I'll see on the Net 54. Percentage of Parent Total: in this video, we're going to explain the percentage apparent total calculation. So the percentage oh parent total calculation shows the cells percentage based on a chosen parent or base field, and the percentage is based on that particular field instead of using each fields immediate parent. So the main difference between this calculation and the percentage of parent throw total calculation, for example, is that the calculation is based on a field that you can choose any other field that you can choose instead of the calculation based on the field that is thean immediate parent off your field. So let's actually taken example here and have created a pivot table here with three different calculations so that we can see the difference, and this will make it easier for us to understand the concept. So I've got a pivot table here where I've got the region and the product name and the sales person on the roads section here, and I've created three different calculations. So, first of all, the percentage of grand total and we've seen this calculation before. We've explained it on a previous video. The percentage of grand total calculation is basically the percentage of the amount here, which is the sales, for example, for Ivan Davis, for product one in the Americas. Okay, so this is the amount here. 38,803. So open 48% is the percentage of this amount 30,803 from the grand total, which is eight million, 114,925. So this is the percentage off the grand total. It's opening 48% and then we have the percentage of the parent row total. So what is the parent row here? The parent row is basically the field that is just above the sales person here. Right? So this amount, the amount of the sales 38,803 is the amount of the sales for that particular sales person . Which is Ivan Davis. Right? So what is the field just above it? Here on the hierarchy, on the roads section here, the parent TRO is actually the product name, right? So this is the sales for Ivan Davis inside product one or the sales for Ivan Davis that he sold how much he stole product 14 so it's 38,800 freeze. So this is 17.63% off his sales for product one. And that is in the Americas as well. So 38,008. So basically 38,803 is 17.63% of 220 1001 127 which is the sales for product one in the Americas. So it's basically dividing the amount by its parent row, and the parent troll here is the product one. And how can we determine the parent row? It's the one just above it. Here on the roads section. This is the parent rope, and we've explained the percentage of the parent row total on a previous video. But I'm having here on the comparison so that you can easily understand the difference between it and the percentage of parent total. So all we've covered so far our calculations that we've covered on previous videos, but where including them here so that you can easily understand the new calculation here. So what is the new calculation? It's the percentage of parent total, so because you can choose the parent by yourself. So I've chosen the parent here to be the region. So how can I do that? I'll actually remove this third calculation and reinserted again so that I can show you how have done it. So I'm gonna insert again another instance of the sales amount here. I'm going to click on it, go to value field settings here, and then go to show values as. And then instead of no calculation, I'm going to select percentage apparent total calculation. I'm going to select my parent as the region so that we would have the percentage of Ivan Davis from the sales of the Americas, for example. So I'm going to change the name here. 2% of region total. I'm gonna click. OK, Ok, so what does this number represent? 2.63%. This is the percentage of 38,803 from the region which is the Americas in this case 1,474,967. So this number here represents stooping 63% off the percentage of sales in the Americas. Where is actually this number which is the percentage of parent row totals the percentage of the parent row. So the row immediately above the sales person, which is not the reason it's actually the products. So this number represents 17.63% of the percentage off sales or product one. And that is in the Americas. Roles was not product one in the absolute, its product one in the Americas. Okay, so I hope that I've been able to illustrate the difference between the two calculations, which is the percentage of parent wrote total on the percentage of parent total. Through this comparison, I know it could be sometimes confusing, but I think the comparison here, in my opinion, would be the best approach to show the difference between the two calculations. So thanks guys watching this video, and I'll see you on the next one. 55. Difference From Calculation: and this video, we're going to discuss the difference from calculation. So the difference from calculation considers the difference of one value from another. And the other value could be the value for the sales, for example, on the previous month or on the previous year. So that's illustrate that with an example, let's say I want to create a calculation here that would get me the difference between this sales on a particular month and the sales on the previous month. So I'm gonna insert another instance here of the sales amount, and then I'm going to click on it, go to value field settings here and then go to show values as on. Instead of no calculation here, I'm going to select difference from. And as I said, I want to create a calculation that would compare the sales on a particular month to the sales on the month just before it. So we're going to select the base field here as the sales month, and then we're going to select previous so that we can get the sales difference between the current month and the previous month. And now I'm gonna rename this to difference between this month and previous month. I'm gonna click OK here, and as you can see here we get. The results we get are the differences between the sales on the current month and the previous month. So, for example, here for February 2016 the sales was 29,549 and the bump previous to it, which was January 2016. It's 330,000 181 because here there's a $300,000 difference between February 16 end and January 16 and you can see what the difference is in negative because January was greater than February. So it's This is current month minus previous month and is because here for Marsh sales was 122,000 189 and February US 29,000. So differences 92 thousands a positive difference because Marsh, the sales on Marsh was greater than this sales on February 2016 and we can do a bit of custom member formatting here. It's a good practice. We can select the whole call him here, press control and one on our keyboard to go to number formatting and the formatting is a currency, but we can actually color the negative numbers and red. It's always a good practice so that we can easily distinguish the months where we had a decline in sales so that they would stand out this way. So now we've calculated the difference between the current bond and the previous month. We can also calculate difference between the cells on the current year and the previous year is well. So if we insert another instance here of the sales amount and then click on it, go to value field settings and then go to show values as instead of no calculation, we're going to select difference from. And then the base field here is gonna be the year and we're going to select previous and then difference. Between this year and previous year, I'm gonna click, OK, and as you can see here, we get the difference between this year and the previous year, off course in 2016 because it's the earliest year on our data. We don't have any data because we don't have a year on our data. That is before 2016. But here in 2017. On January, for example, it's negative. 130,000, 880. So what does that mean? That means that this is the difference between January 17 here and January 16. So in January 17 the sales was 199,301 and in January 16 330,000 181. So there was a decline of negative 130,000 880. And this goes for February as well. This is the difference. 150,803. So February 17 the sales wills 150,000 more than the sales in February 16. We can also do the same thing here for this column. Do some custom number formatting. So Arkan selected press control, run our keyboard, and then we can select the red color for negative numbers so that they would stand out as well. This is a good practice. So as you can see here, we could do the difference between this month in the previous month or different stream this year and previous year, and we can do the differences well between this month and the following month. Okay, so you can do the same if you click on any of them. Value field settings. Years show values as instead of previous you select next. You can do that as well, and you can do the difference between a particular year and this year. So you can select to do the difference between this year and 2016. For example, not the previous year, just 2016 in particular. If we had had more years than two years. So if you had add three or four years of data would have been able to compare, for example, 2016 to 2000 and 14 in particular, for example, and so on and so forth. So press cancel here, and then we're gonna have another example here for comparing to a particular item. So as you can see here, we've got a pivot table where we have the region, and then it's broken down into the different products and weaken. Compare all the regions to a particular reason so we can compare all the regions to the Americas, for example, so we can insert another instance year of the submission of the sales amount, click on it, value fuel settings, show values as and then we're gonna select difference from. And then the base field here is gonna be the region, and we're going to compare everything to the Americas. So we're going to compare Australia to the Americas, Europe to the Americas. And of course, Americans to the Americans will be a zero because they're the same thing. So we're gonna create a field here named difference between this region and America's. For example. On we're going to click OK, here says you can see here we get the difference between Australia, for example, and the Americas. So in Australia the sales were 1,266,446. And in America's they were one million, 474,967. So we get a difference year of negative 208,521 and it goes the same as well on a product level. But here the sales for product one in Australia was greater than the sales for product one in America's. So the difference here is a positive difference. 279,554. So we're now comparing to a particular region so you can compare to a particular item or a particular value. So you can compare to a particular value is well, not on Lee two, previous or next. Great. So that's it, guys, for the difference from calculation. Thank you very much watching this video I'll see on the next one. 56. Percentage of Difference From: in this video, we're going to explain the percentage difference from calculation. So this is similar to the difference from calculation. Except the result is a percentage. It calculates the percentage off difference instead of giving us just the difference. So see that with an example. Let's do the same thing here that we've done on the previous video on the difference from video. So I'm gonna calculate the difference between a particular month and the previous month. So I'm gonna drag another instance here of the submission of the sales amount and then click on it, go to value field settings, click on show values as. And then instead of no calculation, I'm gonna select percentage difference from, and it's gonna be the difference from the base field being the sales month and then previous. So now we're going to get the percentage of difference between a particular month and the previous month, and we're going to give it a custom name percentage difference from previous month, and we're going to click OK, here. So, as you can see here, this is giving us the percentage of difference between a particular month and a previous month. So the calculation here being done is, for example, for February 2016 were actually subtracting the value of January, which is 330,181 from February. So it's 29,549 minus 330,000 181. So we're going to get the difference this way. And then we're gonna divide that by 330,000 181 which is the value for previous month. Okay, so it's basically current month minus previous month, divided by previous month. So this is what gives us the result here. And the result here is negative. 91.5%. Same goes for march, for example here 313% 3130.51. So that's basically current 1 122,189 minus previous month, which is 29,549 divided by previous month, which is 29549th and this gives us 313 0.51%. We could also do the percentage difference from previous year. We're gonna drag another instance here of the sales amount, go to some, click on it, go to value field settings and then show values as and then here, the percentage difference from and the base field here. We're going to select the year and we're going to select for the previous year. Click OK here. And as you can see here we get the percentage of difference from previous year. So here, for example, negative 39.64% is basically the value for January 17 minus the value for January 16 divided by the value for January 16th. This gives us negative 39.64% on actually forgot to rename it here. So I'm going to rename it, which is percentage difference from previous year and click OK, here. As you can see here, this is the percentage difference from previous year. And we can give a custom number formatting here for two columns, So I'm going to select them and then press control and one on my keyboard and then go to custom here and then for the custom number, formatting is gonna be 0.0% for positive numbers, and then I'm gonna put a semi colon and then for the negative numbers year is gonna be negative 0.0%. And then with red in between square brackets. And this means that there get of numbers, will have a red phone. 12 positive numbers will have a black fund, and the negative numbers will have the negative sign as well. And if you need to learn more about custom number formatting and understand exactly what these symbols mean, I have a video on my YouTube channel, the Excel custom number formatting masterclass, where I show you everything about because the number formatting and what these symbols mean . So I'd recommend you watch this video. It's just that this course is not a custom number formatting Course, I'm not going to go into detail about the topic, and I'll leave you The link for the YouTube video in the Resource is section. So I'm gonna click OK, here. And as you can see here, we now managed to color the negative numbers in red. We could also do the same thing here about comparing to the Americas as we did on the previous video. So I'm going to drag another instance here of the sales amount and then click on it, value fuel settings and then show values as. And then instead of no calculation, we're gonna be doing percentage difference from And then the base field is gonna be the region and that it's gonna be compared to the Americas, So percentage difference from America's. And then we're gonna click. OK, here. As you can see here we get the percentage of difference from the Americas. So this number here is basically the sales for Australia, minus the sails for America's divided by the sales for America's. And now we can give our column here some custom number formatting. So I'm going to select it, press control and one on my keyboard and then go to custom here, and then it's gonna be 0.0% and then a semi colon and then negative 0.0% with red in between square brackets and I'm gonna click OK, here. As you can see here, our negative differences here have been highlighted in red and again. If you need to learn more about custom number formatting, I'll leave you the link for my YouTube video. And the resource is section. Okay, so that's it, guys. For the percentage of difference from calculation. Thank you very much for watching this video. I'll see on the next. 57. Running Total In: Hey, guys, welcome back. So in this video, we're going to see how we can create a running total in pivot tables says you can see here we get a pivot table here. We've got the sales month spread across the columns, and we got the year in the region here on the road section, and we get the submission of sales amount on this emission of value section. And what we want to do is to create a running total here across the months so that the value on each month would represent the value of the sales year to date. So the value of the sales so far in that particular year. So, for example, January would have the value for the sales in January, but February would have the value for the sales in January plus February on then. Marsh would have the value for the sales in January, plus Ribery plus marsh. So it's a year to date value. So to do that, we're actually gonna insert another instance here off the sales amount on the submission of Value section. We're going to click on it, go to value field settings here and then go to show values as and then on this menu, Instead of no calculation, we're going to select a running total in, and then we have to select our base field. So the base field is the field that we're gonna base our accumulation of values on. So the field, actually that we want the values to accumulate on is this sales month, right? Because we want January to have the value for January and then February to have the value for January plus bribery and the marsh to have the value for January plus robbery plus marsh So we're gonna base are accumulation of values here on this sales month, and then we're going to give it a custom name. So we're gonna write running total and then dash months and then we're gonna click, OK, and now let's examine what happens. We're gonna take this row here for Europe in 2016 as an example. So the submission of sales amount here in January is 244,946. The running total is the same because so far in 2016 in Europe, we've only been through January. But then, if you look at February. The value for the sales in February is 29,549 and then the running total is actually the value for the sales in January, plus for every 274,495. So it's basically this value plus this value value for January plus February and then Marsh the sales in it World's 54,556. But the running total is 329,000 and 51 which is the value for sales for Marsh plus February plus January and so on and so forth. So as you can see here, we got a running total across the months. We can also have a running total here across the reasons, for example, so that we would have the sales here for America's and then here in Australia would have sales for Australia plus America's and then in Europe would have the sails for America's plus Australia. Plus, you're so to do that, we're gonna insert another instance here of the sales amount. Click on it, go to value field settings here, go to show values as instead of no calculation. We're going to select a running total in and then we have to select our base field here as the region because we want the values to accumulate across the regions here. And then we're gonna name this running total region and we're going to click. OK, and as you can see her on the running tool region, we have the sales accumulating across the reasons. So because here in the Americans in 2016 cells with zero on, then in Australia it was 85,000. So this the accumulation so far is 85,000. But then here in Europe, we have the accumulation of Europe plus Australia plus America's sales in Europe where 244,946. But the value here in the running total you can see here it's 330,000 181. So basically you can accumulate values across any field if you selected to be your base field here. So that's it, guys, for creating a running total in pivot tables. Thank you so much for watching this video and I'll see on the next one 58. Percentage of Running Total In: Hey, guys, welcome back. So in this video, we're going to see how we can create a percentage running total using pivot table. So as we've seen on the PDS video, we managed to create a running total. But that was a value. Now we can create a percentage running total where actually our sales were accumulate as percentages, and the percentage will be the sales so far in the year divided by the total sales for that particular year. So let's have an example here. We're gonna actually insert another instance of the sales amount here into the submission of value section and there were going to click on it, go to value field settings and then go to show values as. And then instead of no calculation, we're going to select percentage running total in and the base field is gonna be the sales month because we want our sales percentage to accumulate across the months until it reaches 100% at the end of the year, which is gonna be in December. So you will see that the percentage running total in December is going to be 100% and then we will give it a custom Names is going to be percentage running total dash months and then we're gonna click. OK, here. So let's examine here. What happened? Let's see here the totals or the sub totals for 2016. So, as you can see here, the sales in January 2016 was 330,181 and this represents a 20160.63% of the sales in the hole 2016. This is the percentage of running tools so far now. In February, sales were 29,549 and then percentage running total is 9.4%. So this 9.4% represents basically sales for January plus sales for February, divided by total sales in 2000 and 16 in the whole 2060 on then. As you can see here, the percentage of running total is going to keep on accumulating until it reaches 100% in December 2016. Because basically the values are being accumulated month over month until December 2060 so December 2016 represents ah 100% and you can also accumulate values across any other field that you want by changing your base field. And we've seen that on the previous video and this video. So that's it, guys, for the percentage running totals. Thank you very much watching this video and I'll see you on the next one. 59. Ranking Values in Pivot Tables: Hey, guys, welcome back. So in this video, we're going to see how we can insert a rank in pivot tables so we'll see how we can rank our values in ascending order. So that smallest to largest or in descending order. So its largest to smallest. So here we've got a pivot table where we have our sales persons and we have their sales amount. And let's say we want to rank these sales persons according to their sales amounts, whether in ascending order or descending order. So we can insert actually another instance here of the sales amount into our summation of value section. We're going to click on it, go to value field settings and then go to show values as and then here we're gonna choose on the drop down menu. We're going to choose rank smallest to largest, for example. So this were rank from the smallest value to the largest value, so the smallest value would take a value of one, and then largest value will take the largest value basically, and then the base field here, the field that we want a base, our ranking on is gonna be the sales person because we want to rank the values for outside its persons and then we're going to give it a custom name here. So rank smallest two largest and then we're going to click. OK, so is it cause here it's given us some numbers year, which are rankings. However, we just need to change the number formatting instead of currency. We're just gonna make it a normal number here. And as you can see here, the smallest value takes the value of one on. Then the second smallest takes a value of two for the rank here and then third smallest takes a value three. And that's actually sort our values here in ascending order, just to be able to see them easier, says he consider our values here are given ranks according to the order, with the smallest being given number of one and then the largest being given the largest number, which is basically the count of the values here, which is in our case, 10. We can also do a rank largest to smallest, so the largest value is going to take the rank number one and then the smallest value is gonna take the rank number 10 in this particular case, which would be useful here because we might need to point out the sales person who has the highest sales. So let's do that. We're gonna insert another instance here for the sales amount. We're going to click on it, go to value feel settings and then go to show values as. And then we're gonna select rank largest to smallest. And we're gonna selector based fields here to be the sales person We're going to do a custom name here, rang largest to smallest, and we're going to click OK, here. And it's being given the incorrect number formatting. So we're going to correct the number formatting here to be just a number. And as you can see here we have our rank here, largest to smallest to largest value here takes the value of one. Here, Madeline has the highest sales. She takes a value of one and then smallest value. Bob. He takes the value off 10. So it's a simple ranking of your values. So that's it, guys, for inserting a rank on your values and pivot tables. Thank you very much watching this video I'll see on the next one 60. index Calculation: Hey, guys, welcome back. So in this video, we're going to explain the index calculation in pivot tables. So the index calculation shows the relative weight of each cell when compared to its row Total column total and grand total. So it's taken example here. Let's say that we have some money that we can invest in extra marketing for one of our products in a particular region, and you want to decide which product in which region should be invest in its marketing? Should we invest in its marketing so that we get the most bang for buck for what we invest in marketing? The index calculation can help make the right decision. So, as you can see here, we have a pivot table that shows us the sales for each of our products in the different regions. Now, an initial intuition would suggest that we check which product has the high sales in which region. So someone could just highlight all the sales, were over the products and create a heat map here, going to conditional formatting color scales and create a heat map and the highest values, the greenest and the highest in sales. This product to in Europe. So that's what we should invest in. We should invest in extra marketing for product to in your Well, let's see what the index calculation has to say. I'm going to copy my pivot table here and paste it below, and I'm gonna change the calculation on my pivot table. I'm going to click on the submission of sales here, go to value fees, settings, go to show values as and for the calculation. I'm going to change it to index. And I'm gonna change the name here to index and I'm gonna click, OK, And let's check our values year. Our values should be around one, and they shouldn't have a currency format. They should have a number for months. I'm just going to change the format here to a number with two decimal places. So, as you can see here, the greenest value, the highest value, is actually for product three in the Americas. So that's what the index calculation is suggesting. It is suggesting that we invest in product three in the Americas that we do extra marketing for product three in the Americas so sickens here. The index for product three is 2.12 and the index calculation would deal. The number that is around the one it could be less than a one could be more than one or could be a one, depending on the value and the higher the value. Basically, the more weight this value has in the array of values that we have in the pivot tables on the higher the value, the more weight this value has in our pivot table. Now let's actually see what the formula for the index is. So the formula for the index is actually the value in the cell multiplied by the grand total, divided by the row total multiplied by the column. Tal. So, for example, here for product three in the Americas, it's 2.12. That's because the calculation here is value in the cell. So product in America's This is the value in the cell 1,165,659 and then multiplied up by the grand total. What's the Grendel? It's this value here, 6,589,000 and 66. So this is the grand tool, and what's the row total for product three in the Americas. The total for the whole row is 1,474,967. That's the road oval, and what's the column totaled? Column total here is two million, 452,000 and 43. So that's the column total here for the comb containing product three in the Americas. As you can see here, the index is 2.12. So this is the calculation for the index. The calculation for the index usually yields a number that is either greater than one or less than one. And the higher the number, the more weight this value has in the pivot table with respect to the other values or compared to the other values. And as we saw in the formula, setting the value in the cell aside, the index would depend on the grand total row total and column total for that particular value. Also, if you think about it, the sales for product three in the Americas So this value is actually 48% out off the column total, So it's 48% out of the sails for product. Three in all, the other regions. So about half of the sales that we do for product three worldwide is done in the Americas or happens in the Americas. So it's a considerable amount. Also, this sales for product three in the Americas represents about 79% off this sales for all the products in the Americas, and the sales for product three in the Americas represents about 18% off all of our sales worldwide, says also in considerable amount. So as we demonstrated here, the index calculation can help us in decision making. So that's it, guys, for the index calculation, I hope you found this video useful. Thank you for watching, and I'll see on the next one. 61. Grouping Dates: Hey, guys, welcome back. So in this video, we're going to speak about grouping dates in pivot tables. So I've got a pivot table here, and what I'm going to do is that I'm gonna drag the order date here in the road section and look, actually, what happened? Excel automatically grouped the dates by quarters and years. So is because here it's group the dates here by year. And when you drill down, it's also been grouped by quarter quarter, 1/4 to while this is actually an order date field. So you should just contain a date like the first of January 2000 and 16 for example, just a date, but excel automatically grouped it. Depending on your Excel Virgin, you may or may not experience this, so I have office 3 65 year. So this is what happened on my version. We can actually UN group the dates says to be able to see our dates as normal. So if you right click here and then go to ungroomed and this will UN group are dates and we're gonna be able to see our dates as usual. Now if we need to group our dates we have different options for grouping Weaken, right click here, go to group. And as you can see here we have the start date and the ending date which excel automatically chooses here based on your data. So this is the earliest date on my data and this is the latest date on my data. So Excel has chosen those as to start an end date. You can edit that and you can write whatever start and end dates you would like And then we have here the option to group by months or days or quarters or years. So say when a group by days instead of months here and Digitas here when I clicked on days , I need to click again on months toe insulate months because he could actually select multiple grouping categories or multiple group by criterias. You could group by days on months and quarters and years at the same time so that you'd have this drill down effect that we saw at the beginning of the video. So I'm gonna group by days and, let's say, on a group by seven days us to have a week of data group by a week of data. And if I press okay, here, you can see her. Now My data is grouped by a week. And if I drag here, for example, the sales amount, I'm able to get the sales amount for each week here. So this is the sales amount for the week from the 10th of January 2016 Phyllis 16 to January 2016 and so on and so forth. I could change my group by criteria so I can right click, go to group here on instead of days. I could just select months and un selected days. And if I click OK here, I'm able to group by months and far right click again go to group. And instead of months, I select quarters. I could also select quarters and years at the same time, for example. And if I click OK, I get my data group by years and quarters as well. You can drill down from the year into the quarter, says Dickens. Here these air the group buy options here for dates you're able to group by days or months or quarters or years. OK, so that's it, guys for grouping dates. Thank you very much Watching this video I'll see on the next one 62. Grouping by ranges: Hey, guys, welcome back. So in this video, we're going to see how weaken Group by ranges in pivot tables. So I've got my pivot table here, and this time I'm actually gonna put the sales amount here in the rose section. And as you can see here, we get the amounts for our sales transactions year. So these amounts are actually the amounts for our sales transactions here or the sales amount that was in each row off our data. We can actually group these amounts into bins or buckets. And to do that, we can right click, go to group. And as you can see here, the amount here would be starting at the amount for the minimum sales amount or the minimum amount for our transactions, which is 20,259 and ending at the maximum amount for our transactions. I can group these into bins or buckets off 10,000 if I click. OK, here. You can see here they have been grouped into bins or buckets off 10,000. Now, if I drag here, the sales amount into submission of Value section, it would tell us how many transactions occurred in each bucket. So it's basically telling us that there were 19 transactions that happened between 20,259 to $30,258. Okay, on then, 22 transactions between 30,259 and $40,258. And if you don't like these bins, you can actually put bins of your own. So if you right click, go to group here, I can make my bins, for example. Start from $10,000 up until ah, $100,000 for example. And with $10,000 increments If I click OK, here, you can see here I get my bins year between 20,000 to 100,000. And I didn't get any bins on the $10,000 amounts because I did not have any transactions between 10,000 and 20 thousands. So this is why I don't have any results for the 10,000 to 20,000 bins. But if I add like another row of data here, if I just copy any raw data that I have, just copy any row here and put the sales amount as 10,000 and just refresh my pivot table. Because here I have this one transaction between 10,000 and 19,999. So that's it, guys. For grouping by ranges in pivot tables. Thank you very much watching this video I'll see on the next one. 63. Grouping by Text Fields: Hey, guys, welcome back. So in this video, we're going to see how weaken Group by texts fields and let me show you an example here. So we've got a private table here, and I'm gonna put our regions here on the road section and you can see here we've got our regions Africa, Americas, Australia and Europe. And let's say I want to divide these regions into Central and East and West, for example. So for me, because I live in Canada, so my East is gonna be Africa and Europe. So I'm gonna click on Africa and then hold the control button and click on Europe. And then I'm going to right click here, go to group. And as you can see here now, Africa and Europe have been put into Group one. I can actually rename the group into east, and then you can see here America's is put into a group called America's. I can rename that into Central, and then I'm gonna rename Australia into West since Australia is on my west as a live in Canada. So as you can see here, I've been able to divide my reasons year into three categories. East Central and West, and a Zika is here. There's another field that has been created on the row section here called Region to, and we can use that field actually to use our new Divisions Year East Center on West so I can first start by renaming that other field called Region to. So I'm going to click on it, go to field settings here, and I can just rename it to my reasons, for example, and I'm going to click OK, here. So now I have the My Regions field and I can move it actually to the column section if I want to, and I can drag now. My sales amount here into the value section and, as you can see her, have been able to slice and dice my data with my new reasons. As you can see, her Africa and Europe half Onley values in east because they are in the East region and America's has only values in Central because it's on the central region on Australia has only values in the West because it's on the West reason. So that's it, guys for grouping by text fields, so you're able to create your additional custom groups here in pivot tables. One more thing, actually, is that the new field that we've created, which is Mother My reasons field is not part off. My original data is just in the pivot cash. So we don't have a column here called my region or a column for our new regions or new divisions. It's only stored in memory. So it's like a virtual column, basically. So thanks, guys, for watching I'll see on the next one. 64. Grouping by Time: Hey, guys, welcome back. So in this video, we're going to see how we can group by time. Using pivot tables Says you can see here. I've got a sales data set and a spurt of the daily said we get the transaction times which time of the day the sails transaction happened. And let's say that we want to create a pivot table in order to some our sales by our so that we know which hour of the day we have the most sales on. So to do that, I'm gonna insert a new pivot table. Here's insert pivot table, and I'm gonna insert it onto the pivot cheat here, and I'm gonna click OK here to insert my pivot table. And now I'm gonna put the transaction time here on the road section, and I'm gonna put my sales amount on the submission of Value Section. As you can see air because I'm using Excel 3 65 XL actually has automatically grouped my transaction time by hours and minutes. Usually, this doesn't happen in earlier versions of Excel and to do the grouping. You right click here on the transaction Time column here and then click on group here. As you can see here, Excel is currently grouping by hours and minutes and seconds. But I just need to group by hours because I wouldn't be grouping by minutes or seconds. That's too much granularity for me. I just want a group by our and you could also tell Excel, which starting our you need the grouping to start from and which ending our You need the grouping to end at as well. So if you click ok here. As you can see here, I managed to group by our So basically, this is the submission of sales that happened on the first hour of the day, which is 302,937. Let's actually give some number formatting here to our data. So if I go to analyze and then select entire pivot table and then select the values on a right click and click on form, it sells here, I can select the currency. So say I'm going to select the US dollars and I'm gonna click. OK, here. As you can see here we have a currency. So basically, on the first hour of the day, we have 302,937 2nd hour of the day, 256,853. So, as it is here, we can know which our the day we had the most sales on. So if he actually go to the data top here, and then we could assert our data by the highest our. So basically, around 1 p.m. we had the most sales. Now, sometimes you would see the hours in a MPM format instead of a 24 hour format, and this actually depends on the date and time settings on your computer. So if you right click here on the clock at the bottom right corner of your screen, I'm using Windows 10 and clean click on a just date and time to go to your date and time settings. You click on change date and time format and then, actually it depends on the short time format here, and it's a big glitchy, actually doesn't work well all the time, so usually this should yield an E M P M format, but actually, the format I have here is a 24 hour format here on the pivot table. It's a bit glitchy, and it doesn't always follow it correctly, but the format of the hours on the pivot table should actually usually follow your date and time format settings. But it's a bit glitchy is it doesn't always work well, so for the 24 hour format, you should actually have this selection here, although I'm having this election, which should yield in a MPM format. But anyways, if you need to change the format between 24 hour and AM PM is actually through your date on time settings on your operating system here on Windows. Okay, so that's it, guys, for grouping by time. Thank you so much for watching this video I'll see on the next one. 65. Grouping by Half years: this video. We're going to see how we congrats by half years. So as you can see here, I've got my pivot table and I've got the sales month on the road section and I've got the summation of value section here having this sales amount. And let's say I want to grew by half year. That is, I want to put the 1st 6 months of the year in a group and then the 2nd 6 months of the year in another group. So to do that, I'm just going to select my 1st 6 months right click and then click on group here. And if you consider they've been put in a group called Group One, I can change its name here. So I'm gonna name it first half, for example. And then I can select the second group of months here the last six months of the year, Right Click group and said that group, too, And then name it second half. And now I have two groups and I can see the totals here for Group one. So for the 1st 6 months and then the total for the 2nd 6 months, or the for the lost six months, and here you can see here it's a field that is only in the pivot table cash. It's called sales month to I can remove the sales month and it could see the first half and second half. If I need to change the name, I can click on it, go to field settings and then I can rename it Year Half, for example and click. OK, so now I've been able to group my data by first half and second half. And if I need to own group my data, I can select my group's year right click and then UN group to UN group my data. I'm gonna press control and Z here to go back to my groupings. So that's it, guys on how to group by half years. 66. Grouping by a Date Starting on a Monday: and this video, we're going to see how to group our dates by weeks. Starting on a certain day Says you can see here. I've got the order date here on the road section and the submission of sales amount on the submission of Value Section. Unless, say, when a group my dates by weeks, that start on a Monday. So I'd want to group here my dates into seven days that start on a Monday. So to do that, I'm going to right click here on the dates and then click on group. And as it is here, the first date here on my data is actually the 10th of January if I check the 10th of January 2016. So if I go to my calendar here and check 10th of January 2016 as you can see here, 10th a junior 2016 is a Sunday. But this, according to my grouping, or according to the grouping that I want to apply, would belong to the week starting on Monday, the Fourth of January. So it actually want to change my grouping here to start on the fourth at January 2016. Because That is the week starting on a Monday that the first date on my data belongs to, and I want to select here to group by days, and the number of days is going to be seven. Of course, if you want your weeks to start on a different day other than a Monday, then you could choose the Tuesday or Wednesday. So if you want our weeks to start on Tuesday, then would choose the fifth of January in this case. And if you want them to start on Wednesdays, then would choose the sixth of January and so on and so forth. So after selecting days here and number of days seven days, I'll click. OK, and as you can see here, I managed to group my dates into weeks starting on Monday's. So That's it, guys for grouping by dates starting on a certain day. Thank you so much Watching this video also on the next one 67. Grouping by a Custom Date: in this video, we're going to see how to group our data around a custom date so we can group our data based on a custom date. So here, I've got my pivot table and I've got the order date here on the world section and the sum of sales amount on the submission of value section. And here is because here we've got data for 2016 and for 2070. And let's say I want a group my data based on the 1st 6 months of 2017 so that I would make that a group and then anything before that is another group and anything after that is another group as well. So to do that, I can actually right click and then click on group here and I'll be starting on the first of the 1st 2017 and ending on the 30th of June here, 2017 and then I'll select to group by months and then I'll click OK, and let's see here what happens? So I'm grouping by months. Between two dates the first of January 2017 and the 30th of January 2017 on because I'm grouping by months, you can see her that Excel has group my data by months between the starting date and the ending date. So, as you can see here and then I've got actually another cluster of data or a group of data, which is anything before my starting date. So anything before the 1st January 2070. This is a group here, and this is the submission of sales about for that period of time and then anything after the 33 June 2017. It says here greater than 33 June 2017. Because here we've got the submission of the sales amount for that as well. So, as you can see, I managed to group my data by months, and I put a custom starting and ending date. So that's resulted in having my grouping done by months because they selected months during these two dates or between these two dates. And then I've got another group where before thes two dates and then 1/3 group after these two dates, probably you wouldn't be using that a lot, but it's just good to know that you could do it. Okay, So that's it, guys. For grouping by custom date. Thank you very much. You watching this video And I'll see on the next one. 68. Grouping by Fiscal Years and Fiscal Quarters: and this video, we're going to see how to group by fiscal years and fiscal quarters in pivot tables, so there is no built in way in pivot tables in order to group by fiscal years or fiscal quarters. So you have to write formulas on your data in order to create a fiscal your column and a fiscal quarter call. So I created the columns here on my data, and I just need to write the formulas to calculate the fiscal years on fiscal quarters for the order date. So, first of all, for the fiscal year, I'm going to assume that the fiscal year starts in April, since it starts in April, where live here in Canada but you could always tweak The format is to match your businesses fiscal year start date, so to calculate the fiscal year, basically because it starts in eight pro the 1st 3 months on the calendar year belonged to the previous fiscal year. So January, February and March, on the calendar year on a certain counter year, belonged to the fiscal year for the previous year or belonged to the previous year's fiscal year. So here, for example, for the order date ninth of October 2016. This still belongs to the fiscal year of 2016 because the fiscal year of 2016 starts in April and toe any dates between the first of April 2016 and the 31st of December 2016 belonged to the fiscal year of 2016. And the dates between the 1st 2 January 2016 up until the end of March, 2016 belonged to the fiscal year for 2015. So we can actually write a simple formula here that the fiscal year is equal to the year of this state minus. And we're gonna actually right, a true or false condition that's gonna transform into a one or zero, which is that we're gonna test if the month of the state is less than four. So if the month of the date is less than four than this partier off, the formula is gonna become true. And if it is greater than four than it's gonna become a false, which is the case here, and this door falls will be transformed into a one or a zero because we have a minus sign here. So that means that in case the condition is true and in case this month is less than four, we will be subtracting one from our year, so the date will be belonging to the previous fiscal year. But in this case, in this particular order dates case, which is ninth of October 2016 here the condition will be a fall. So also will be subtracting zero and so it will be in the same fiscal year, So this order date will be in the fiscal year off. 2063. Press enter Now let's drag the formula down and let's check a date that is on the 1st 3 months, for example, this 1/9 of March 2016. This belongs to the fiscal year off 2050. Because here the condition that we put to the test if the month is less than four and actually the month is less than four, right, because it's a three, then this condition will become a true and then because of the negative sign, you will be subtracting the one. So if we highlight the whole thing here, impress F nine here because here it subtracts the one from 2016 so it becomes a 2015. So this is how you can calculate the fiscal year. Now let's see how to calculate the fiscal quarter. So to calculate the fiscal quarter, there is a very easy way to do it, which is using the choose function so equals Jews and the Jews function. You simply give it an index number, the index number would be an integer, and you give it a number of choices. And based on the inter jury that you're going to give it, it's gonna make a choice out of these choices. So the integer we're going to give it is actually the month of our date. So that's going to be an integer between one and 12 because they're only 12 months in a year, and then the values were going to give it to choose from. We agreed before that the 1st 3 months belong to the fourth quarter off the previous year, right? The 1st 3 months on a calendar year belong to the fourth quarter off the previous fiscal year, so the 1st 3 months on 2016 will be on the lost quarter or the fourth quarter off 2000 and 15. So this is why you were going to give it the truces of 444 and then 111 Right. This is when the 1st 3 months of the new fiscal year, which is 2016 start right and then to to to these air the following three months, which correspond to the second quarter off the fiscal year 2000 and 16 on then 333 which are the third quarter off the fiscal year off 2060. So this part of the choose function here would yield a number between one and 12. So for highlight this month, part here, and you press f nine because here it gives us the number 10. So that means it will choose the 10th choice out of these choices. So get six year 789 10 so october would belong to the third quarter off the fiscal year off 2060. So if a press enter here and we dragged the formula down, were able to get the fiscal court there also other ways to get the fiscal quarter, and I actually have a video on my YouTube channel that explains different ways to get the fiscal year and fiscal quarter and quarter and all these date calculations, and I'm gonna leave you the link for that video in the resource is section and I would highly recommend that you would watch it. Now let's refresh our pivot table here, Sophie, Right Click on our pivot table on refresh it and then you can see here we're able to group with fiscal quarters, for example, and with fiscal years, if you would like so we could remove the fiscal quarters. And now we're able to group by fiscal year. So there is no built in way in pivot tables to group by fiscal quarters or fiscal years. But we're able to do that through a for minutes. Okay, so let's see it guys on how to group by fiscal quarters and fiscal years. Thank you so much watching this video I'll see on the next 69. Errors when trying to group by Dates: in this video, we're going to show you some issues that you might face when trying to group dates on your pivot table. Says because here, I've got this pivot table and I've got the order date here on the world section and a sum of sales amount on the Submission of Value Section. And I'm gonna try to group my dates here it or so right click and then group. And as you can see here, I get an error. Cannot group that seduction. And the reason for that is that I've got some inappropriate data arm Lee date column. So if I check my order date column here, for example, this value here, this is actually a text value of the date. So this looks like a date, but it's actually not a date. If I check here the number formatting Eakins here, it's actually giving me the same formatting for all because it's actually a text. And there is no number because, you know, if it is actually a date, it will give me the value of that date under this number here. So this is not a date. This is just a text. I could even test it with the is text function here. That gives me a true if this is a text and sure enough, I get a troop. So this is a text naughty date, so I can actually fix it by just typing in the date like this. And this one is fixed Now, now, some other issues here, for example, this is an error value and you could Sierra's Well, I've got here instead of a date of got pull. So as you can see here, these air some of the issues that you might face because you've got inappropriate data on the order Date column This column supposed to have only dates. So in order to detect these values, an easy way to do it is to highlight the whole column and press control and g on your keyboard for go to and then click on Special here, go to special and then you could check for constants Are numbers or texts or logical or errors. Actually, we don't want to check for numbers because our dates are actually numbers, so we don't want to check for that. We want to check for text, logic, ALS and errors. And if we click OK here, because here we managed to select these values. The are not axity numbers. We could highlight thumb by filling the color here and his guys here. We managed to highlight them by yellow and we could start changing them. So this one, for example, is the date in July 2016. So I'm just gonna write any day tear. So, for example, six of July, 2000 and 60. And this one is well here. So this is a date in January 2017. So I could just write the seventh of January 2017 for example, and so on and so forth. And then I could just remove the filling here and no Phil and no Phil here as well. And we don't have any other values, and it could do move. The Phil here is well on the header off the column. So as you can see here, I'm trying to show you the issues that you might face when trying to group dates. It's mainly because of inappropriate data types on that particular date column. So that's it, guys, for this video. Thank you so much watching. I'll see on the next one 70. Grouping Pivot tables from the same data source: let's say that we need to create to pivot tables from the same data source with two different groupings. So, for example, here this pivot table is grouped by quarters here on the order date field. Now let's say we want to create another pivot table from the same data source, which is this data here that is grouped by months, for example. So let's try the traditional approach here. I'm just gonna insert a pivot table and I'm gonna insert it. Let's say in the same worksheet here, I want to create it on the same worksheet as this profitable, and I want to pivot tables from the same data source with two different groupings. Now, I'm gonna create this pivot table here, and then I'm gonna put the order date here on the world section, and I'm gonna put the sales amount on the submission of value six and already you're seeing here it's actually gonna had and grouped by quitters as well. So it seems like both of these air linked. Let's sorry to change the grouping on that 12 months, for example, when will the quarters here click? OK, could see here that they're both link. So we're not able to create to pivot tables from the same data source with different groupings, at least using this way. Actually, there are work arounds to do it. So the verse work around is to create a new workbook ears. I'm gonna press control and end on the keyboard to create any workbook, and then I'm gonna insert pivot table. I'm gonna go get it from that data source here. So I'm gonna go get it from this table and I'm gonna put it in an existing worksheet. Let's put it here on this worksheet first and let's go to a new workbook here. So, as you can see here, we've got a new workbook with a pivot table here that we just created has put the order date on the world section and this Just remove these groupings here and let's put the sales amount on the submission of value section. You can see here This pivot table is now grouped by months. Although it's from the same source, it's actually not linked grouping wise to the other pivot tables. I can select this pivot table here, press control and X to cut it and then go to my other workbook here and press control envy . Now I can actually right click group And I can group by quarters, for example, on this one and easier. When I changed 2/4 on this one, it didn't change the other two. So they actually are not linked at the moment, although they come from the same data source. So although this pivot table here comes from the same data source, it has a different pivot cash. Now, another way to do it is to press olt d p on your keyboard and this will open the pivot table and pivot chart wizard and is gonna ask you here, Where is the data you want to analyze? And it's actually in a Microsoft Excel list their database. So I'm gonna click next year, and then I'm gonna selecting migraines, So I'm gonna select my data here. This is my data, and I'm gonna press next. And here is giving me some sort of ah, warning message of new report will use this memory if you base it on your existing report. Pivot table seven, which was created from the same data source. Do you want your new report? to be based on the same data as your existing report. This means it's giving me the choice whether I would like the new pivot table to use the same pivot cash or a different pivot gush. In this case, I actually wanted to use the different pivot gash. So I'm gonna press no here so that they would have to separate pivot gushes. So I'm gonna for snow. And is it cause here it's giving me the option to put it in your work shit or an existing worship? I'm just gonna put in existing worshipped here, put it in this cell and then press finish. And now I am going to put the order date here on the road section. I'm gonna move any grouping and do the grouping again by myself. Gonna put the sales amount here and let's do a different grouping here. So group by, let's say, by days, year and by months, and click OK here, Cizikas here. I managed to group this pivot table with a different grouping and it didn't affect the other pivot tables because it's using a separate pivot cash. It says you can see here two ways to get around grouping pivot tables from the same data source, since if you do it just the straightforward way it will use the same pivot cash and the grouping will be linked. But as you can see here, we showed two ways to get around that. So that's that guy's for grouping credit tables from the same data source. Thanks so much for watching this video. I'll see you on the next one. 71. Showing grouped dates with no data: We've got this pivot table here where we have the order date on the world section and we get the summer sales amount on the submission of Value Section. And let's say that we group these dates here by 70. So we right click, go to group and then we select a zier and we select the number of these to be seven, and we click. OK, so you will notice here that there are some weeks the are missing. So, for example, here the week from 27th of March 2016 up until the second of April, 2016 it's missing. And the reason that it's missing is that we don't have any transactions happening on the dates of this particular week. So in order to show missing data here, we can actually go to our field here. So click on it, go to feel settings, and then we can go to layout and print, and then we take here, show items with no data, and we click. OK, here. So now we're gonna be able to show the items with no data. Another way to get to that. You can right click here and then go to feel settings Goto Leo in print and we get to the same option here. So we managed now to show the items with no data. We could also show zeros on these items since they don't have any sales. So we might as well display them a zero. We could right click here, go to pivot table options and that for empty cells show a zero and click. OK, so there you go. This is how you can show items on a field with no data. So there you go. This is how you can show items on a field with the data and also replace the missing values with zeros, for example, or any other value that you would like. 72. Sorting by Smallest or Largest: So let's speak about sorting in pivot tables. So to do your basic sorting and pivot tables in ascending or descending order, there are multiple ways to achieve that. So the first way is that if you need to start a certain field, you could go here to the pivot table fields and click on this arrow here and then sort, for example, a to Z or Z two A's so by ascending or descending orders. So if you click A to Z here, you're going to sort in ascending order, and you could look Z to a You're gonna sort in descending order or searching the product names so you can see your product for Comes first. Now when sorting in descending order and then product three, etcetera, etcetera. Another way to do it is to make sure you select a cell in the field that you need to sort, and then you can right click and click on sort here and sort eight z or Z to a. So put it back to an A to Z sort here and 1/3 way to do it, as well as to go to the data time on the ribbon well, selecting a cell inside the field that you need to sort and then you consort using thes two buttons here, so a to Z or Z to a You can also certainly aggregated field here the submission of sales amount. But you can't sort it. Using the pivot table feels here using this arrow he