Data Analysis with Excel - Master Pivot Tables - 1 | EdSolver Learning | Skillshare

Playback Speed


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

Data Analysis with Excel - Master Pivot Tables - 1

teacher avatar EdSolver Learning, Lead with Learning

Watch this class and thousands more

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

Watch this class and thousands more

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

Lessons in This Class

32 Lessons (3h 5m)
    • 1. Master Pivot Tables

      0:53
    • 2. Insert Pivot Tables

      5:48
    • 3. Pivot Table Fields

      3:04
    • 4. Table Rows

      2:48
    • 5. Table Columns

      2:13
    • 6. Table Values

      2:21
    • 7. Table Filters

      2:09
    • 8. Quick Layout Changes

      4:53
    • 9. Pivot Table Styles

      4:38
    • 10. Show or Hide Totals

      2:48
    • 11. Field Setting Options

      6:53
    • 12. Options to Summarize Data

      6:19
    • 13. Number Formats

      4:06
    • 14. Calculations Related to Totals

      8:33
    • 15. Calculations Related to Difference

      10:34
    • 16. Running Total

      8:15
    • 17. Ranking

      4:28
    • 18. Insert Calculated Field

      7:58
    • 19. Modify Calculated Fields

      5:57
    • 20. Insert Calculated Items

      9:09
    • 21. Filter and Sorting

      8:27
    • 22. Multiple Filters

      3:49
    • 23. Report Filter Pages

      5:46
    • 24. Date Filters

      12:04
    • 25. Value Filters

      9:00
    • 26. Label Filters

      8:26
    • 27. Slicers vs Filters

      6:47
    • 28. Slicers in Action

      6:15
    • 29. Customizing Slicers

      7:50
    • 30. Using Date Timelines

      3:33
    • 31. Multi level Timelines

      5:35
    • 32. Customize Your Date Timelines

      3:46
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

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.

78

Students

--

Projects

About This Class

Data Analysis is one of the most important aspect in every business in this 21st Century. Nowadays businesses are flooded with lots of data. The main role of Data Analyst is to find relevant information which can be useful in making some important decisions in the business.

Why one should learn Pivot Tables?

Excel Pivot Tables is one of the most essential tool for all the data analysts and business intelligence executives. With the help of pivot tables in excel, you can fetch very crucial insights from your large databases of business.

This class is a part of entire course. After completing this class successfully, you will be able to master all the pivot tools in excel including -

  • Basic Pivot Table Options

  • Pivot Table Design Tools

  • Basic Aggregation Options

  • Advanced Calculations using Pivot Tables

  • Calculated Fields and Calculated Items

  • Basic and Advanced Filters

  • Slicers

  • Timelines

  • Grouping and Ungrouping Functions

Meet Your Teacher

Teacher Profile Image

EdSolver Learning

Lead with Learning

Teacher

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

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

Data Analytics

Computer Software and Systems

Financial Management

Business Management

Enterprise Solutions

Digital Marketing

Creative Designs

And much more......

 

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

Class Ratings

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

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

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

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

Transcripts

1. Master Pivot Tables: Hi. I will come into this course wherein I am going to teach you that how you can use Excel pivot tables to increase your net analytical skills. In this century, data analytics and data science are considered to be the best curio for everyone. But there has to be some starting point from where you need to understand that what are the things that ended analyst has to do. Now in this entire course, we are just going to focus on one particular aspect that is PivotTables within Excel. We will see with the help of an example that how you can create, managed and find different data insights with the pivot tables. After this class, you will be able to analyze any kind of data given to you. And you will be able to find out the insights which can help you in making some decisions. I hope that you enjoy this class and you sign up for this class right now. 2. Insert Pivot Tables: So now let us start creating our first pivot table with our dataset. For this entire learning sessions, we are going to use the net asset of Temple supposed to, which is available with w. Now these simple data contains around 10 thousand rows and it contains the data of Sais in different regions and City of us. This is a kind of useful data because it has more than 20 columns and enough number of rows, which we can use in our pivot table analysis. Now, before creating a table, we need to understand that what kind of data we are using and what are the different columns for these datasets? For this dataset, we our different columns which are row ID, order ID, order date, shipping date, shipping more. Customer ID, customer name, segment, country, city and state, which are geographical areas. Postal code, also a kind of geographical area and region. Then we have product id, category of product subcategory of products and product name. Now these three classifications are for a specific product. And then we have numerical values, which are Sales, Quantity, Discounts and Profits. Now with this dataset, we are going to create several pivot tables and we will understand each and every functionality of a pivot table. Now, to insert a pivot table, we need to go to the insert tab in that we get three different options. The first is inserting a simple pivot table. Then we get an option where in Excel will recommenders that which are the sample we will tables which we can directly use the reports. And the last option is inserting a simple table. Now, when we just select this table option, it will select the data automatically and it will create a simple table. You cannot easily summarize and analyze with these options. To analyze the data, we need to insert a pivot table. For that, we will click on pivot tables. Here. The dataset is already selected in case if we wish to add something else or we wish to omit this data, we can make some changes from this range area. We can even connected with any external data source or other file to this x. Right now, we will just select this table data range. Then you also get an option to provide the cell range for output. Generally, we create a new pivot table in new worksheet. You can add this data to a data model so that it can become easier for you to analyze the data for various KPIs and various revolts. You can tick mark on this to create a separate data model for your entire project or you can keep it as blank. Now let us click on OK. Now this generally creates Blank pivot table unit to drag-and-drop different objects in your pivot table areas. Let us see. We will include the segments. Then we can include different regions. And finally, let us include sales inevitable. Now with these options, you can see that we are easily created when PivotTable, which showcases that in a particular region, which particular segment is performing better or was. Now this is the case wherein we create a customized pivot tables. Apart from this, Excel also provides us one option wherein we get Recommended PivotTables and we do not need to select the categories. Likewise, what we have seen earlier. For that, we need to go to Insert Table. We will just click on Recommended PivotTables. Now for this, let us it excel will automatically recommend different kind of pivot tables. Like for this data will summer profit by state. Then we get an option of sum of profit by region, then sum of sales, discount, profit, etc, as per different region. Then some of discount by state. So accordingly, Excel will analyze your dataset and it will recommend you that these are the ideal pivot tables which you would like to create. So now, let us see. We wish to get to know about the profit per region. So we will simply select this recommended pivot table and we just need to click on OK. Now, you can use this pivot table into a reports and your work is done. So in these three ways, you can insert different kinds of tables and you can walk out with pivot tables in Excel. We can modify it, and we can also make some changes in the layout and looks up this pivot table that we will see in upcoming sessions. And we will also explore these different PivotTable fees in our upcoming sessions. That's it for now. See you next time. 3. Pivot Table Fields: So when we talk about PivotTable feeds, generally we recall of rows and columns. But in this case, we need to understand what are the different options that you can utilize and you can work with your pivot table fields. Generally, all the fields are divided into two major categories, which are a categorical data. You can call it as dimensions. And the other one is numeric data. You can call it as values. Now, all these dimensions and values are sorted out in the form of each column in the pivot table fields. Now, you can simply hide or unhide the PivotTable piece from this option, all you need to do is select an appropriate field and drag it to the rows, columns, values, or Pintos. You need to make sure that in case of values, you can only drag then the tau, which has some numeric values. For rows and columns. You can use date or any other categorical data. While dealing with pivot tables, it is advisable that if you are having any kind of column which is related to date, then you should use it in rows instead of columns because it makes it easier to drill and drill down if you are using the Late column in the roles. Now, this is the standard pivot table fields layout. You can go to the settling option and you can select change the layout of PivotTable fees. As for your working requirements, there are different options. You can even remove. The cards are the fees from this display area. Let us see. We just want the field section will not want any other cards of rows, columns, and everything. So this will only showcases all the fields which we are using right now in our Pivot Tables. Likewise, right now, these pills are sorted as per the data source order. It means that the basic data which we have JO, which starts from row id, will continue till the end. And in the same way right now, we can see all the fields in our PivotTable Field, but we can simply sort it to a to Zed, which makes it easier for finding different fees. And if we are working with multiple tables and multiple datasets, this becomes very useful. Now, let us switch it back to the data source order. So these are the basics of pivot table fields. In coming sessions, we will see what this each card for those columns, rows, and values can be useful. Let's say for now, see you next time. 4. Table Rows: Now let us talk about the rows in our pivot tables. When we talk about rows, these are generally the details which we include in our PivotTables in the form of categorical data. In case of rows, we will not include any kind of field from our pivot table, which will represent some values. Like in this dataset, we will not include sales, profits and discounts in our rows. Generally, you get to see the bleak card for the rows on the bottom corner. All you need to do is select any of the field from your quiz area and drag it to the Rows column. Let us see. We wish to include the segment in-laws. So this will represent the summary of different kinds of segment in our rob. Now one good thing that we can do in PivotTable is we can add multiple categories within laws. Let us see under the head of segment, we also wish to find out the data as per different city or as per different states. We can simply add the states in the rows and everything will be drilled down to a state level. So first of all, the data will be classified on the basis of segment and then on the basis of states. And anyone do vice versa. We want the state-level little first. And within each state we wish to find out that which segment is performing better or not. Generally, we use the details like category subcategory and further formats to combine it easily. In this dataset, we have these kinds of fields. Also. Let us remove the state and segment and focus on a group of data. Now by group, we mean that each of the fields are related to each other. So in this case, the product category, subcategory and product name are related to each other. These are in hide out. So let us see. We include the categories in our row. Now within category, we can find out the data for each subcategory. These are unique and these are only interested in a particular category, only. Like bookcases, jazz tables, and politicians are the part of furniture segment. Only. Those subcategories are not in office supplies and technology. So this gives you a clear idea about the main category, as well as the details of a particular subcategory level. So in this way, PivotTables can be useful and you can drag multiple kinds of rows into a pivot tables. 5. Table Columns: Alright, so now we'll edit Ross and now let us add some columns, slower pivot tables. Now there are some things which you need to keep in mind while we're building a pivot table, while dragging the fees into columns, you should never use date themes and such wheels which have a hierarchy. The simple reason is that we generally tend to navigate or scroll up and down. We do not wish to scroll left and right to get certain information. So it is a general tendency that you use the date field and some hierarchical fees in your rose only. So in case of columns, we will use those kind of categorical datas which are limited in options. Let us say in this case, we'll segment. There are only three segments and we can easily incorporate in our columns. So let us go back to our PivotTable fees. From these options. We can simply say the segment and we can drag it to the columns. Now once we add certain columns, we also get the details of Grand Total, row totals and column totals. Unlike Rawls, we do not use multiple fees in columns. The only reason is it becomes difficult to navigate. Let us say along with segment, what if you wish to the region also, we can do that, but it is a very bad habit while presenting the data. And it is not easy to read the data from these kind of pivot tables. For example, let us use some values to identify the scene. In case we have added quantity, it is difficult to read out the data that how much a particular region within a particular segment has the quantity of sales. Instead of that, we can simply keep any one data or any field for the columns and it becomes easy to read the data. So in this way, any of your fees to the column, and you can create an appropriate Pivot Table for your reports. 6. Table Values: Okay, so now we have added rows and columns, and now let us add some numeric values to our Pivot tables to complete the same. Now we generally add some numerical data in the field values. This will include all the kind of numeric data it could be in terms of money or currency, in terms of number of items, in terms of amount, in terms of size or anything. In this dataset, we have different numeric fields. Let us find out that which other fields that we can use in values play card. Now here we have sales, quantity, discount, and profit. We can use any of these in the values field. Let us see, we want to find out the details about seals. We just need to drag this is into the values. In the same way, we can use multiple items in the same format, at the same time orders. We can prepare a separate table for each of the value category. Whenever we will add values. By default, Excel will analyze the type of data and it will convert in the number or sum, count, average, etcetera. Like in this case, we have added sales and proper. And generally for this kind of data, excel provides us the summary in terms of sum. So it is the sum of profit for different categories and subcategories for different segment of products. We can keep sales and profit both at the same time. Or we can prepare different PivotTables. Like in this case, we just want to get the data of sales. Simply remove extra items and we can arrange also all this fields. Within the values field. There are different operations which you can use for these values, like changing then a Mohammed's, changing the summarization. That is, whether we want to use it as some count, etc. And we can even apply some basic calculations to this values. All this things we will learn in our upcoming modules to analyze our data as per our requirement. 7. Table Filters: There are different ways to apply filters. We are pivot tables. One of the easiest way is to drag the field into the filter card shown over here. Now by doing this, it will create a filter option, a bot pivot table, and you can easily toggle around and filter out the data manually, just like a normal table. Let us see for this data, we wish to filter the data on the basis of region. We can simply drag the region sweet into filters. And you can find that a particular filter region is created over here. Just like Ross columns and values. We can even apply multiple filters at the same time. Let us say along with the region, we also wish to apply the states as a filter. However, we need to make sure that the filters will walk in a manner they are applied. It means that if we are kept the region first, first of all, the data will be filtered as per the region. And then we can select a particular state. Like here you can see a long list of all the states. But in case of region, if we select central for states, you can only select the data which are in the central region. Otherwise, the data won't show up. So in US for Washington state is not a part of central region. So you will not be able to find any data in Excel PivotTable. So you need to make sure that you are applying relearned filters and you know that what exactly you want to find. So to avoid these kind of conclusion, you will also make sure that you apply a single filter at a particular time. This makes it easy for you to present as well as for the readers and that decision-makers to find out the what actually you want to present it to them. This will not make any kind of difficulties in data rating. In our upcoming sessions, we will also see some basic and advanced filters that you can apply. Apart from this method. 8. Quick Layout Changes: Hi, welcome back. So now you know that how to create a pivot table. In this module, we are going to see that how we can change the design and style of our PivotTable. Now, to change the design and style of PivotTable. First of all, we need to select anywhere on our pivot table. Then we can go to the pivot table tools in that we are the designs action. Now in this session, we will discuss that how you can apply some basic templates or you can see some pre-built layouts for our PivotTable. Now by pre-built layout, we mean that grad limited options. However, you can customize it on your own. But these quick layouts can help you in improving the look of your pivot tables. Now to change the layout of your pivot table, you need to go to the Report Layout option and you get several options in this menu. Now in this, you can see that the host option is showing the pivot table in a compact form. Now, the basic layout of any of the PivotTable which we create is a compact form. Only. If we wish to change it, we get two different option. We can show the same in an outline form wherein the category of the data will be displayed in a separate column. And the sub category, which is the second level of data in our rows, will be displayed in another column. The third layout option we have is showing in a tabular form. Now when this subtotals are automatically brought down at the end and subcategories are displayed first. Like you can see over here, that foster Paul, we will get the values of all the subcategories, and at last, we get the total of that main category of data. In this case, bookcases, chairs, permissions, and tables are given priority in terms of providing the values for the users. And at the last, we get the total of all this under the furniture category. Now let us compare it with our compact form. In compact, we get the total value of the main category first, and then we get the distribution among the sub categories. So now, depending upon your reporting requirements, you can select an appropriate layout for your pivot tables. Now, apart from these options, we also get two different options. Lead whether we want to repeat all label items or we do not want to repeat all liberal items. So whenever we are displaying our data in outline form, we want to repeat the tom of category that is furniture in this case, alongside bookcases, chairs, furnishing, and tables or not. So what happens if you select repeating all items? So when we click on that, now the word furniture will appeal against each subcategory. Now, the thing is, when do we use this? Well, if we are preparing some report wherein we need both the values subsequently for analyzing the same data in another software. Or we want a printed report wherein we want both 30 days, side-by-side like category and sub category. We do not want in a tree format. Use this option. And then we can remove the subtotals to make it clean looking. Now let us convert it back to the compact form. Apart from these layout options, you also get an option to insert the blank rows to make your pivot table visually good treat. So for that, we can go to blank rows option and we can insert or remove blank rows after each item. So in this case, our main category is regarding the product category that is furniture, office supplies, and technology. So if we will insert a blank line, so after all these main categories, there will be a blank line and we can see a clear distinction or difference between these categories. So now visually you can easily compare that beach other subcategories belonging to the main category and what is the total sales value of each category, as well as sub-categories. So in this way, you can make some basic changes. Create pivot tables as far as whatever we are dried withing. That just inserting a blank line. After all the main categories will look good to our pivot table. So let us keep this and in next session, we will apply some changes to the style of PivotTable. 9. Pivot Table Styles: Hi, now modified our pivot table. Now let us apply some different design styles to make it more attractive or let us make it very decent looking for reporting purposes. Now to change the style of entire PivotTable, we will select anywhere on the Pivot Table, which will open up the dialog boxes for pivot table tools. From that, we will go to design section. Now when this we get some prebuilt style options as well as we get different style option in which we can chain the layout of our laws, columns had us and et cetera. So let us see which are the style option of pivot tables available within x, x2. Now in this, you can just hover over different options to select an appropriate pattern for your pivot table. Let us say you are using this pivot table for a print option. In print case, you will try to focus more on the black colored combinations. A simple color like this, because you are not going to display any kind of colorful presentations, but you are going to print it for your filing purpose. So in that case, you want your pivot table to be as simple as like this. So you are just going to print it out and use it for your piling purposes. But let us see, this pivot table is to be used for a presentation. In that case, you need to make sure that it is attractive enough to highlight the data and to also attract the attention of your viewers. So in this case, let us find out which could be some better option for styling of our pivot table. Insert applied modes. We can even look at the dark moods in which we can see that it adds a highlighter to the data. Let us see in this option here, we can see that the totals of category, I'll highlight it with different color shade and the totals of subcategory are kept symbol. Then you can change the different color layout options from these panel. For reporting purposes, generally, we use blue and orange color maximum times because these are visually appealing for your viewers. So let us keep this one. Now. You can simply copy this and paste it to a presentation. Or you can even present this Excel file directly to your views. And they can easily identify that what you wish to highlight with the help of your data. Now, apart from changing the colorant style, we can even make changes to the rows, columns, and headers. Let us see whether we wish to include the row headers or not. We can select it from this option. So in this case, we are going to highlight the rod as. We are also going to highlight the column headers. If we don't wish to do that, we can simply intake from these options. Now this will make it very simple table without any kind of different styles. Then we get an option to select banded rows or Banded Columns. Now this again depends upon the type of layout that you have selected from this. Right now we have selected this option. In this case, for banded rows and columns. It just creates an outline or borders. So here it goes. A perfect look like a table. But if you change the main layout totally like this one. So in this case, banded rows and bended columns will be totally highlighted with a different color. Now you can preview all these options directly from your pivot table styles, or is to make it simple and good-looking, we can simply go back to this version. Now in this table, you can clearly see that apart from your entire excel sheet, your data is perfectly highlighted with proper borders, color, shadings, and proper titers of your rows and columns. So in this way, you can perfectly style your pivot table to make it a good visual treat for your viewers. 10. Show or Hide Totals: Alright, so now the last part to make the changes to the layout of your pivot table is to decide that whether you want your raw daughters column daughters ends up daughters. A new pivot table visible R-naught. You can show it or hide it as per your requirements. Now to do so, we need to select anywhere on our pivot table. Then we can go to Design tab in that we get the option for subtotals as well as granddaughter's. Now grand total will include both for rows as well as columns. We get four different options. The first one is we do not want any kind of granddaughter's for rows and columns, if we will switch it off automatically, granddaughters at the raw level and column level will disappear. Now, we can select that whether we only want the grand totals for our rows or only for columns, let us see if we select four rows. So it will total up the details or sum of all the same for category and subcategory level. Now we will not get the entire grand total for consumer segment, corroborate segment, and homophilous segment. But we can get a grand total of, Let us see, tables within the furniture category. Now for that totals is of tables is shown at the last column. Now instead of raw level, we just wish to get the totals for columns only. So now in this case, we will just get the grand total of consumer segment, corporates like mine and home office segment at the bottom. We can even keep both simultaneously. Now, apart from Grand tutors, we can't even control the subtotals within our PivotTable. Now in this period table. So office supplies, technology, and monitor our subtotals. We can simply hide it. We can show it at the top of the glue, or we can even show it at the bottom of the group. Now, we can design it as part of our requirement. Let us keep it as at the top of the group, as we'll get earlier. So in this week, you can play around, or we can say that you can show or hide granddaughters and subfolders in your pivot table who make sure that you only keep those things in your pivot table, which you wish your readers or viewers to view the scene. 11. Field Setting Options: Hi, welcome back. Now in this module, we will discuss several ways in which we can summarize the data within a Pivot Table. Now, in this case, we can change the format of the summary of our categorical data, as well as over numeric data within our pivot tables. In this first session, we are going to discuss how we can change some settings for the categorical data. Now quad that, let us create one separate pivot table and we are going to practice all the things within the same Palmer. So for inserting a PivotTable, we will move to insert tab. From that, we will select on PivotTable option. And we will create a new pivot table in a new sheet. Now while this module, we will try to compare and analyze the data of sales and profit of various categories and subcategories within the business. Now go get that data. We need to drag the fees, which are the category and subcategory. So let us drag the category field into the laws. Let us drag subcategory again into the rows. Now are the values. We will select the sales and we will select the data of profit. Now this brings us at somebody data of sales improper for entire business. Now when you talk about changing the value field settings, there are several ways in which you can open up the dialogue box of changing the field settings. The first option is to simply double-click on the heading given in the table itself. So let us say if we go for a double-click over here, it opens up the value of waves adding dialogue box. But this option is only available if we are just selected the values. And we do not know anything though than values in our columns. So in case if we will open up any of the category in column, this option, want help us. Now, let us talk about the second option from the rows or columns. We can simply select on the down arrow key, given with the each field. In that, we get the last option of opening up the field setting dialog box. And the last method is under the PivotTable Tools in the analysis section, we get an option of opening up the field settings. Now let us talk about what are the settings that we can change in terms of summarizing their value and displaying the values. Now, again, this depends upon the type of field that we are finding out. Now called this dataset. Let us talk about the Gadigal. Now, categorical data, meaning the data which does not have any kind of numeric nature. They'll different fields settings like we can change that, how we want to display the subtotals for that particular field, and how we want the layout of our table regarding that feel. Now when we talk about subtotals and photos, in this case, whatever changes we make will just Flag to that particular field only. Now in this case, we have selected the categories, suppose for the sales and profit instead of sum, we want to find out the maximum value of a particular transaction. Now, when we will go to the customer and we will select the maximum, and we will click on OK. Now this will only change the data of categories, only four subcategories. It will still show the sum of series and some Flaubert and not the maximum values. Now to make the effect in the subcategory as well, we need to go to Field Settings. We need to select on custom, and we can select on the maximum value for the subcategory, because already some of these are selected. We get the maximum values of each subcategory at the bottom of our pivot table. So in this way, at the major level of your level of details and at the minute level that is subcategory in this case, you can find out some separate values, thus apart from your regular pivot table. Now again, let us get back to the normal mode. Cornfields settings will go and select on automatic settings. So again, this will showcase the sum of sales and profit for both categories and subcategories. Now when we talk about other fields settings, we can change the layout in terms of normal display and also for the brain. These options are similar to what you already seen. Now the first option of modification that we get in this list is repeating the item labels. Now this will be applicable when we select a pivot table in a tabular form or in an outline bomb. This is not applicable for the compact form as it will keep as it is. The second option is inserting a blank line after each item of the table that we can do from these options as well. And we can even eat the items without any data. So if there are any categories which does not have any sales or profit in that particular way. The aid, we can still showcase it in the heading section. So you do not require any particular value for that. You can just keep the heading of the data and it will summarize it up as zeros is or property. Now when you talk about the printing option, you get an option to insert a page break at the end of each item. After furniture, after office supplies and after technology, that will be a page break and each of these items will be printed in a separate page. So let us keep inserting a blank line and let us click on ok. So now, just like the other options that we have seen in our previous modules, this will keep a blank line at the end of the category and you can make some visual changes into your pivot tables. Now in our upcoming sessions, we will talk about the field settings of numeric data. 12. Options to Summarize Data: Hi. Now let us talk about some summarizing options available for the values in our Excel pivot tables. Now, whenever we create a pivot table in x, x2, generally it provides the summarization of data in the form of some, like in this case, we get the sum of sales and some appropriate when we create a simple PivotTable. But what happens if we wish to get the data or the summary of data in different formats. Well, we can do that within our Excel PivotTable only. Instead of sum, you can use several other measures or the way to summarize the data. Let us see, instead, getting to know about the value of protons is we are interested in knowing that what are the number of transactions that have occurred for each category and sub category. So far that we can use the count instead of sum of sales. How do I do that? Let us find out. Now to change the summarization of any of the field, we need to select on the value instead of the categorical fields. For that, let us see. We will select the sum of sales and we will go to the Value Field Settings. Now V0, we get different options for summarizing the use by whether we want to summarize the data by some of the data or count, that is the number of reputation entries. Or we can select the average of the entire dataset. Then we can find out the maximum value for that particular category or subcategory. We can even find out the minimum value. We can get the product count numbers that will be the distinct do count. Then we have standard deviation and standard deviation for population data, variance, variance for population data. Now, these options are also to figure out on the basis of the type of data that you are dealing with and the values that you have selected within your pivot tables. In this PivotTable, we are dealing with sales and profit. So generally, for these kind of data, we will say let evil some average count, maximum value or minimum value. Let us say instead of sum, we can select on the measure as coulomb. So whenever we will select, entire data will be represented on the basis of the number of entries that are available for that particular category and subcategory. Now, unlike what you've seen in our previous session, this will imply two entire dataset, not just to a particular category, like when the genes are summarization for category from the field settings of category, it does make changes in the nature of the supply and technologies data. It did not reflect on the subcategories. But when we make some changes to the value field, it will apply to entire column. So in this case, the count off sales will be presented for all the categories as well as subcategories. Now this count represents the number of sales transaction for this particular category and sub category. And from this, we can find out that which category or subcategory has the maximum number ofs's transaction out of all this. In the same way we can even apply to the profit. Let us see, we want to find out that what is the maximum amount of profit that they will gain for each category and subcategory from a particular transaction. Only. So far that we can select our data of profit. We can go to the Value Field Settings. Now this time, instead of sum, we will convert it to the maximum value. So this will bring up the details of that particular transaction wherein we have got the maximum profit by one transition. Okay? So now this will represent that in the furniture category, there is one transaction that is fetched the maximum revenue, or we can say the maximum profit for us of $113, then one transaction only. And in office supply, V over one transaction wherein V0 received a profit of $4,946. And in technology, it is $8,399. So we can compare that which particular category or subcategory has been more successful. And we can find out the GWAS, that particular transaction from which we got this much revenue or this much profit. Let us say in the same way we want to find out the minimum value of profit. Or we can see even if it is a loss, with the help of changing the summarization options, we want to find out that which transition has made the maximum loss and which was that particular category or subcategory prom over list. So for that, instead of the maximum value, we will go to the Value Field Settings and we will select on the minimum value. And we will click on, okay. So here we can see all the entries of losses are brought up and we can find out that width subcategory had that one transaction which made is the maximum loss. We have seen that bevel, several transaction in technology category which has the maximum profit. Even if we talk about losses. It is also the same case. The maximum amount of loss in a particular transaction was also coming from the technology getting, getting. So we can find out that whether there are equal amount of distribution of profit and losses in these categories or mode. In this way, you can use different summarization option and you can analyze your data from a pivot table very easily. Now, in upcoming session, we will talk about how we can use different number formats to represent the data in our pivot table. 13. Number Formats: All right, now let us see that how we can change the number formats within our pivot table. For this example, we have taken up the sum of sales and profit in our pivot table. Now, these are the values in terms of money or currency. So instead of keeping it in a general format, we can change it in a format of currency. Let us see how to do that. Now to change the number of Ahmed within a particular field, we can go to the field settings, let us say for this example, let us open up the field setting dialog box of sum of sales. Now, we can go to the Number Format option, and you will get different options like whether we want to convert it to a particular number, wherein we can adjust the number of decimals, whether we want to convert it to a currency into an accounting from into date, time, percentage, fraction, palm, enclose, scientific function, integral or text format, or any custom especial option for this. Let us convert it to the Guernsey. Then let us select any of these option available for that currency symbol. Now this a set of different cities and states of United States. So let us select the currency symbol of the United States. Then we can decide that whether we want any kind of decimals after the values or not. Now by looking at this, we can come to know that these are the large numbers. And sales and profit are in thousands and into a 100 thousands. So we do not need any kind of decimals at the end of these currency values. So we can reduce it to 0, and we can click on OK. Now, again, we need to click on OK to apply these fields setting. So here we can see that now this data has been converted into a Clio format of currency, and it has also removed all the values after decimals. So when we compare these data alleles, it was looking just like this, which will still kept for the sum of profit. But now the CFS values at clearly visible. And we can find out and we can make some conclusions easily with the help of this will also remove the decimals and we'll apply the appropriate currency value. So any user can identify that what these values are and how to take decision on the basis of this. In the same way, begin even apply a number format. Now the difference between number format and guns format is just the symbol of currency. Let us see pour some of profit. We can go to the Value Field settings in number format. Apart from general, we will go to the number option. We will reduce the decimals. We can select that whether we want a comma separator R-naught. So it will separate the values up to each 1000 values. And let us click on OK. Again, we will click on okay. And this represents the data in a proper format. So here we can see that the decimals are removed. The data is converted into a number format similar to currency, just with the difference of two currency symbol. So in this way, you can change the number formats of the relevant data which you are using and you are trying to analyze that with some simple changes. Now this also creates a good-looking pivot table, as well as you also get to know about the characteristics of that particular data. In our upcoming module, we will see that how we can use some basic calculations apart from summarizing the data, as well as apart from changing the number values. 14. Calculations Related to Totals: Hi, welcome back. Now on this module, we are going to talk about some basic calculations which are used in our Excel pivot tables. Now by basic calculations, we mean that we are not going to merge multiple wheels, but we are going to change the format of a particular field in a way that we can get some data insights. Now, in basic calculations, we get several calculations regarding the totals, differences, ranking, etc. Now in this session, we will talk about some different options which we can convert. And we can find out the data insights on the basis of a granddaughters as well as row and column totals. Now to understand in a better way, one thing we need to keep in mind that these calculations are used to find some data insights. It means that you are going after so much more to buy, which you will extract something out of your pivot table. Let us see, in this case we will create a pivot table. So we'll move to insert tab. From insert tab, let us select on pivot table. And let us create a blank table in our new sheet. Now in this case, we want to find out each region has what Paulson days, all share of profit for entire business on the basis of categories as well as subcategories. And now when we talk about these kinds of business problems, it seems like these are going to be very hard and we are going to walk a lot. But with the help of Excel pivot tables, we can simplify this in very easy manner. So now, first of all, get all the things in our PivotTable views. Then we can apply some calculations. We will simply drag the categories and subcategories in Dover laws. Then in our columns, we will drag the field region. Now this will create a crosstab in that we can fit a verb values, which will be the profit for entire business. Now, these are some long-term Legos and we can convert it with changing number formats. And let us convert it to a currency form or a simple number home. Without any kind of decimals. Let us use the gammas are brittle and click on ok. So now with this dataset, we are going to find out that what proportion of profit comes from a particular region, from a particular subcategory. Now to analyze this, let us use some basic calculations in our PivotTables. Now to insert a simple calculation for our table only, we need to go to the Value Field settings of profit. Here. We get the Show Values As and we get different options for calculations. By default, no calculation is selected as Excel will not run any kind of calculation on our data when we create a simple PivotTable, but we get different options regarding the daughters. Now by totals, we mean grand totals, column total, and rotten. Now these two options are very much useful in analyzing your data. Let us see what happens when we select the percentage of grand total and we click on OK. Now, whenever we will select the personal brand total, it will consider the profit of entire business as a 100%. And on the basis of that, it will find out that what is the proportion of, let us say, labels in central region in entire business. So if you're earning a $100, you are owning thirty-seven cents from labels from central region. How would this data you can compare that, which is the profitable region as well as profitable category or subcategory photo business could be continued on. Like here, we can see that in case of furniture, it provides us negative one person returns. So it means that whatever profits we are learning, we are losing some proper Because of that particular category. So we can remove furniture from central region to increase our profitability. In the same way we can even find out the most profitable category in a particular region. Like in this dataset, we can see that invest Region, Office Supplies are the most profitable. When we compare it with the entire dataset, we can come to know that 18.37% of the total profit of our entire business comes directly from the seas of office supplies within this region. So with this, we can focus on developing the marketing plan for West region regarding office supplies. Now, in this case, the total profit of entire business is considered as a 100 person and it is distributed in categories as well as subcategories. Now the second option of showing up the total is, and instead of grand total, we can select as column total. Now when we talk about column total, each column represented here is about a region. So now the distribution is only limited to a particular region. So for this particular table, we can compare and analyze all the data within a single region itself. So in this case, when we talk about the proportion of profit and loss is not only limited to the main Profit and Loss of central region. So when we see that the value of furniture indicates minus 7.23%, it means that furniture makes loss of 7.23% of that entire profit of particularly center vision and not of entire business. So this will help you in analyzing the data on the region basis or whichever field you know, selected in your column. Now contradictory to those, we get an option in showing the percentage of raw totals. So when we select that, now the a 100% attribution will be given to each row so you can compare everything row by row. So in this case, when we talk about bookcases, you that 51% of revenues comes from the central region. So basically, you can pick up any of these category and you can compare region by region. That region is profitable or loss-making for that particular subcategory of products. Let us talk about the art office supplies. In this case, we can come to know that from entire profit on from this particular subcategory, we get 18% of profit from central region, 29% from East Region, 16% from South, and approximately 36% from the west region. So in this way, you can compare and analyze and which region is better for that particular subcategory. And I would completely depends upon your requirement that what you are exactly analyzing, like if you are focusing on a particular subcategory and you wish to find out that which is a profitable region, you should select this kind of pivot table. If you are not focusing on a particular category, you are focusing on a region. And you want to find out that which get negatives proper table in which categories are loss-making, then you can use the percentage of column in our pivot table. And if you are looking at your overall business, you can select the percentage of grand total pivot table. So in this way, you can use these percentage of total calculations in your pivot table depending upon what you are analyzing and what kind of data you have in your pivot table. In our upcoming sessions, we will talk about several different categories of these kind of calculations. 15. Calculations Related to Difference: All right, now let us see some calculations regarding difference. Now when we talk about difference, we will calculate the difference of other rows and columns depending upon one particular selected item. So let us see if we want to find out that if the west region is most profitable, How much less profit does Central, East, and South Region make comparatively to the west region. So in that case, we can select the west region as a parameter and we can find out the difference of Central East and South Region competitively to the west region. How to do that? Let us find out. Now. In the similar way, we will go to the Value Field settings of profit. This time, we get two different options regarding finding the difference. The first one is difference from and the second one is percentage difference from. Basically it is one and the same thing. But one will represent the data in form of currency and the, another one will convert it in a percentage format. So let us start with the difference form function. There are several things that you must keep in mind while using these calculations. In this case, when we select on defense from we can select when this field, and we can select one item, like right now, the category is selected as the base wheeled and furniture is selected as these item. If we will calculate on that basis, we will just get the answers for office supplies and technology because those are the following categories to the Fahmy Chu XL will only calculate the difference between the categories available in a particular field. It will not include subcategories or other versions. So in this case, we get a not applicable value to all the subcategories. So you need to make sure that you apply the perfect combination of your calculations to a data relevant to the analysis you are doing. In this case, you cannot use the depends from function. But instead of category, we can use it for our regions in this particular pivot table. Let us move back to the Value Field settings. This time, instead of category, we will select region in our difference from this. So it will consider region is the base field and in the base item we can select west region. So now we will not get any kind of data of west region because that will be the base item and we get the difference in the columns of Central, East and South region. Let us click on OK to apply the same. So now this gives our result in all these three columns. It is a comparison with the west region. So when we see that the South region has a negative value of 4,734. It means that the profit from south region is less than by $4,734 comparatively to the west region. That will be. The similar case in case of extrusion SLS central region. It will basically calculate the difference between profit of these regions with the west region. Now in this case, you need to make sure that you will not get any grand total column for your entire data which you are selected. So in this case, we are selected the base field as region, so we will not get the column grand total. To remove the confusion. We can even hide all this granddaughters. And that makes it a complete pivot table ready for analysis. Now you can compare that why these regions are less profitable comparatively to the west region. You can find out very few sub-categories wherein there is positive difference, comparatively little west region. So it means that we are getting most of our profit from west region compared to all these three other regions. We can even do more kind of analysis apart from these categories as well. When we talk about difference calculation, the fostering that comes into our mind is the time period. We always try to calculate the difference of profit between different time periods. So in this case, let us remove categories and subcategories, and let us bring the new field, that is order date into the rows. Now, we select these kind of date with Excel automatically brings in three different classifications in the rose that is yours, quarters and order date. So when we will drill down under EGL, we get the data for each quarter. And under each quarter, we get the data for each of the months. For this case. Let us see. We want to compare that. How much sales has increased or decreased comparatively to the year 2016. So let us remove quarters and ordinate come over rows. So it will be simply the number of your debt is 2016171819. Now, let us compare all the data. So this time, instead of region, we will select the yard as the base p. Now, we will select the date, or we can say your 2016 as the base item. We can even select the previous or the next. Now when we will select the previous one, it will find out the difference from its immediate previous year. So in the year 2019, we can observe the difference between the profits of your 201819 in the rock 2018, we will get the difference of the 201718 and so on. So let us see, we select the previous year as for comparison, and let us click on OK. And so now it is very important that how you analyze these data. So when we see that the central region shows the data in 2017 as 11,177. So it means that in the year 2017, they have made extra profit of $11 thousand compared to the year 2016. And then when we compare the data of 2018, it indicates that it has on a $1000 more than the previous year, that is the year 2017. So it will compare the difference between the two years which are immediate to each other. And then it will be the difference of 201819 that will be shown in the year 2019. Now, let us cross verify the scene. So we will simply copy this entire pivot table. Then we will paste it in our sheet. Now for this table, let us remove all this calculations. So we will simply keep as no calculation. And now we can even simply cross verify the scene. Now that difference that is shown over here of $8,182 is basically the difference of profit of 2018 minus the profit of 2017. Let us use a small calculation to verify this aim. We will simply select the value from this pivot table of 2018 minus the value of 2017, and we get the same values, which is 8,182. Now, instead of taking a difference from the previous year, let us set one basis on the basis of which we are going to compare. So assume that V0 started her business in 2016, and then we wish to know that whether we are performing better every year or not. So we will move to a value print settings. Instead of previous, we will select the 2016 year as the base year, and we will click on, okay. Now here you can see that apart from South Region, in all the region, we see some positive difference. So it means that after the inception, that is the year 2016, fear always increased our profit in all other regions. But when we talk about south region, we see some downfall in profit. And it does not mean that these region is making a loss, but it means that comparatively to the year 2016, it has performed. Now similar to the defense norm, we can even use the formula of percentage difference from the basic difference will be, instead of this absolute values, we will get the positive difference from the base observation. So let us move to the Value Field settings in Show Value As we will select for Sandage, different Strom, instead of just different Scrum, we will select the earth as the base field and as Bayes item, let us select the year 2016 only and we will click on ok. So now here you can observe that there are multifold jumps. If we talk about percentages, particularly pour the central region, we can even cross verify the same with our basic data of this pivot table. In 2016, the proper Tom central region was just $540. But when he talks about 217, it was $11,717. So you can absorb or 2% thousand jump in profit. So there could also be a reason that it might have happened that in the year 2016 in central region, we have just started our business and we are not perform as expected. But when we talk about East region, we can see twenty-three percent jump from the base profit in the year 2017, 18% from the year 2018, and we'll seen a 94% jump in the year 2019. So in this way, you can compare various kinds of data with the help of difference from and percentage difference from calculations into a pivot tables. You can use different kinds of datasets and you can even use different fields with the same dataset to analyze it with different angles. In our upcoming sessions, we will talk about running total calculations and the ranking calculations. 16. Running Total: Alright, so now let us analyze our data with the help of running total. Now, running total is mostly used when we are trying to compare or we are trying to find out the progress of any of these measures. Let us see if we want to set up a benchmark and we want to find or track the progress of our profit for different years. Let us see how we can use it with the Excel Pivot Tables. Now in this case, we will remove categories, categories from our pivot table, keeping the same regions in column. We will select the order date and we will drag it to the laws. Now, this is a kind of detail table, but we want to find out the progress in a particular year. To do it in a better way, we can simply select the years and we can drag it to the filters. And we can remove the quarters from the Roz Picard. So now in this way, we can select different yours, like 117 and all the relevent data of 2017 will be displayed in our pivot table and you can easily see and compare the entire table. Now when we talk about running total, it will simply sum up right from the data of January till December. So we'll set up a certain benchmark offer, more profit. We can see that in which we have already achieved that yearly target of our profits. Now to change it to a running proton format, we need to go to the Value Field settings of the profit in that we will select on Show Value, As in the calculation form. We will select the running total in. Now for running total, we will keep the order date as the base field and we will click on ok. So now here you can see that right from the beginning, that is right from the January, we get the data to December. And whatever values we see over here is actually the total profit of this entire yo. We can simply filter out for different years and we can see that how the data changes. Now to compare the difference between the actual data and the format of table that we repaired. Let us simply select entire pivot table. We will copy this pivot table, and we will simply paste it alongside so that we can easily compare the data. Now in this pivot table, we will remove all kinds of calculations and we will showcase the absolute values of profit. Now side-by-side, we can compare entire data and you can see that how these calculations add up all the values, right, from the January till December. Let us see, for the west region. For the west region, we can see that the profit in the month of January was $644. Then in February they earned $388. So the total comes up to 13391, $1210 in March, which makes the total could 2243357 in April, which makes the rotor to 2600 and so on. We can see that by the end of December, we can compare it with the grand total of the prophet of west region in the year 2018. So assuming that if we set up our profit target of $20 thousand for the west region, we can see that by the end of the month, November itself will already achieved the total profit target of entire year. So whatever extra profit that you owned in December are the bonus for us. If we see that V0 setup the target of $10 thousand for each of the region. In that case, we can find out that enrich mn. These regions have the target. So let us compare all the values one by one. Now for central region, we can see that the 10 thousand mark is crossed in the month of October for East region, del crossed the 10 thousand mark in the month of May itself. The South Region grows the 10 thousand mark in the month of July. And west region caused the 10 thousand mark in the month of August. So by comparing all this, you can come to know that East region was the fastest in achieving the target of $10 thousand mark it did it in the month of May. In this way, you can compare and analyze the data and you can easily track the progress of different P's. Like in this case, we are tracking the progress of all the regions in achieving the profit targets. In the same way we can even use the positives from of running total. Now that will be helpful in identifying that by a particular month, how much portion of the total profit they have already achieved. So for that, let us select anywhere in the first pivot table. In that, in welfare inside things, we will select the percentage running total informally. We will keep the order ID as the base field also, and we will click on ok. So now here you can see that month by month, how much exit polls centers of their entire pro, who achieved at the end of each month. Like in this case, when we talk about the east region by the month of May, it says they achieved their 54% of entire profit. If we compare the same with other regions, south region is the second one nearest to it, which achieved almost 47%. West region was just around 16.98%. It means that they'll actually earn more profit after the month of May instead of before may itself. If we talk about the central region, it is at minus 1.22%. So it indicates that in the prior month to the main, they'll already made a lot of losses. And after me, they have on some extra profits instead of 2018, we can even compare it for the year 2019. Now, in this case, we can see that all of these region, how profitable values. Now some things you need to make sure that there could be some values beyond a 100%. Because when we talk about the percentage of running total, Always the last value will be a 100%. So in case if it is losses afterwards, then the previous values could be greater than a 100%. So in this case, when we talk about the central region, which we can see that almost a 106% profit as you would in July, almost 128% profit attitude in September. But how is it possible? Well, it indicates that on the profit by the month of July itself. But then in subsequent months, there would have been some months where in the occured losses and by the end of December, they come to the neutral level. Let us compare it with our absolute value. Now, here we can see that till optimal central lesion was almost making profit in each of the month except February. But when we talk about November and December, they made use losses of 1500.1200, which cost around 30% of the entire profit. So in this case, you can observe over here that why this 128% reduced to a 100% at the end. So make sure that you do not get carried away or get confused by looking at these kind of because if you are uncomfortable with Poisson telephoning total, you can use simple running total method to find or analyze your data. In our last session of this module, we will talk about the ranking functions. 17. Ranking: Okay, so now let us discuss about the ranking options. Now generally, we use these ranking calculations to showcase that which among the entire dataset has the highest or the lowest value. Now for this example, let us say, instead of knowing the person day of profit on, we want to know that within each Yo, which month has gained the maximum profit in each region. So in that case, we can use the ranking function and we can set up on the basis of either low to high or high to low. So now, to convert a simple data into the ranking one, let us select our table. We will go to the Value Field settings in Show Values As we will get two different options that whether we want to rank from the smallest to largest or largest to smallest. So let us see what happens in both these options. Frequency, the value for rank smallest to largest. We will pick up the ordered it as the basic field and we will click on ok. So now when we do the ranks from the lowest to the largest, it will assign the highest rank to the highest value. Now, in this case, this indicates that this has gained the maximum profit. And when we talk about the November month, which is given rank as one, this indicates that this has maximum loss. Now, this is a contract theory part operand. You need to make sure that what kind of data you are dealing with. In this case, we cannot assign ranks like that. For that, we are different option Olympian settings. And instead of smallest to largest, we must select the rank as largest to smallest. Now, in this case, Excel will assign the rank one, which has the maximum profit, and to which I second maximum profit, 34, and so on. So for this monthly dataset, we will get the 12 rank to the maximum loss making or least profit-making months. Now let us click on OK. So here we can compare that pour the central region now the January indicates the false crank. So now he all to one rank in January indicates that incentives region, january was the best month in terms of profitability. Here to cross verify the same, we can see in our regular pivot table that in the entire dataset, the maximum profit comes in the month of January, which is $2866. When we compare it to all of them. And we can find out that none of the other one has more profit than the month of January. Likewise, we can see that the second best month was the March, then May, and June, and so on. We can find out that which particular month brings us the more profit in a particular region. Now when we talk about the east region, the story is, in January was the worst month in terms of profitability. And when we talk about November, that was considered as the best month for profitability, as in East region beyond maximum profit in the month of November. Now, doing this kind of analysis will help you in figuring out and setting up some strategies to boost up your sales and profit. With this, you can target a particular month when you find that in this month, you are getting more profit. So you can push more advertisements in that particular month. Like in case of East region, we come to know that in the month of November we are getting more sales. We can push some more efforts to even get some more profit in that particular month. We can also focus on some non-performing seasons and we can try to increase the profit in those months as well. So in this way, you can use the ranking functions within our pivot table to analyze our data in a better way. So that's it about different types of basic calculations in Excel pivot tables. In our upcoming modules, we will learn some calculated fields and Calculated Items. 18. Insert Calculated Field: Hi. In this module we will talk about that, how we can use calculated fees and calculate items into our pivot tables. Now basically, these are used to create a separate set of data which are not actually present in your current database. So let us see here we have several columns, but we want some kind of data by adding up two columns or doing some calculations. And we want to create another column, and we want to use it in our PivotTable. We can do that as well with our pivot tables. How to do that? Let us find out. In this session, we will see that how we can create calculated fields, which will create a hypothetical column, and then you can use it into your dataset. To begin with this session. First of all, we will create one separate PivotTable, will move to Insert tab, and from that, we will click on pivot table. Now this time, we will create a citizen preferred summary. And on the basis of that, we will create some calculated phase and we will insert it into our pivot table. So simply let us read the categories and subcategories into rows. Then from the values, we will select the data of sales and the data of profit. Now, in this entire dataset, we can observe over here that we are just given the amount of sales, quantity of sales, discount and profit. What if we wish to know that? How much cost do we occur to make such products? Well, to do so, we can apply simple calculations of sales minus property. One way to do so is to modify your actual data present over here. You can see that from both the columns, sales and profit, you can create another column, which will be the difference of these. However, in large dataset, it is impossible and time-consuming. So to analyse these data assembly, we will create a custom field on the basis of data that we have in our pivot table. Now to insert a new calculated field, we need to select on field items and sets given under the analyze tab in PivotTable Tools. Here we get the option of both inserting a calculated field as well as inserting a calculator item. Now let us click on Calculated Field. Now, this will create a field and that will be visible under your PivotTable Field section. Let us see, we want to rename it as cost. Now for this, the simple formula will be the value of c is minus the value of profit. So in the formula section, we will keep it as equals two. Then we will insert a weird narratively given from here, which are currently available in our dataset. So let us insert sales minus, we will use profit. So now these calculations will be available for all the Pivot Table Options that we create or copied from this table. Let us click on ok. So now here you can see that when new column is added, that is sum of costs. Now, this indicates the cost of product that we are already sold in the market. And in the pivot table field. Well, you can observe that a new field cost is added. So in case if you remove categories and subcategories, then also the cost will remain same. If instead of that, you use some different measures, like let us say region. Now, the calculated field will work the same way as we decided in our actual pivot table. Even if we change the categories, the formula remains the same and we get the exact answer into our pivot table. So it becomes very much easy. Analyze these data. Now. Let us make our original PivotTable back. Let us use categories and subcategories only. Now, apart from these options, you can even use any kind of normal Excel formula into the formula bar of calculated field. Let us say if we wish to know that, how much profit person days do we on, on the basis of the sales? For that, we will create one more calculated field. Let us name it as profit percentage. Now, por profit person, we can use the formula as the profit divided by sales. So we will simply insert the field of profit and we will use the function as profit divided by the scenes. Now let us click on OK. Now this data is not in the percentage format. So far that in our values section, we get the sum of profit percentage. Let us go to the Value Field settings, a number format. We will change it to the percentage form and let us click on OK. Now this makes it very easy and you can analyze your data easily on the basis of profit percentage. In this current example, will only seen some basic formulas. But if you wish to use any kind of conditional function or any other formula related to date or any other statistical functions that also you can use in the Formulas tab. Like in this case, if you wish to find out that which category is profitable and which category is loss-making, you can do that as well. So for that, instead of a simple functions that we have performed a Now, we will use a logical function of a. Now that we can add up into our calculated field as well. Let us create one more calculated field. This time, we will rename it as profitability. Now, in this field, instead of a simple calculation, we will apply the IF function. So in this case, we want to find out that whether we are making a profit or not. For that, we will use the function as if into bracket. We will select the category. So in this case, we will select the profit field and there were condition will be that it has to be greater than 0. So for that, we will write down the command as profit is greater than 0. In that case, we will replace it as one in this table. So our first condition will be typed down one if this is true or is the second condition will be if we are making losses, type down 0 over there. So we can simply add 0 if we fail in this condition. Let us close this bracket and let us click on, okay. So here we can see that wherever we are making losses, it has replaced it with 0. So you can easily trace out that reach other field or which are the subcategories which are making losses for us. So in this case we can find out easily that supplies then tables and bookcases are the subcategories that I'm making losses for us. All of the businesses are profitable. So in this way you can use different kind of calculator to please, and you can analyze your data easily within pivot tables. Now, you can think around that. If we have made some mistakes in this calculated field, how can we sorted? Well, we also have some modification path for the scene. In our next session, we will talk about that, how we can easily modify the fees that we have created using different calculations. 19. Modify Calculated Fields: Hi. So now you know that how you can create a calculated field within your pivot table. But what if you wish to modify the scene? In this session, we will see that how you can select your previous sued calculated fields and how you can easily modify within your pivot tables. Not to modify any of the calculated field. You need to go to the analyze step in your pivot table tools. Then we will move to Calculated Field. And in that, instead of creating a new one, we can easily select any of the previous ones. Like here, we have already created three different calculated fields which will cost, profit percentage and profitability. Now let us select the profit percentage. Now when we talk about profit person is actually two methods by which you can analyze your data. The first thing, which is very common and everyone used is you find the percentage of profit on the sales amount. However, this can not be the only way to think of it as a revenue perspective. When we talk about profit person days, you can also calculate that how much person you actually on, on the basis of your investment. So instead of Cs, we can pick up the cost as the b's. Whenever you are calculating profit percentage on the basis of your cost, you are actually finding out that for whatever amount you invested in your business, how much exit percentage of revenue you on, on that. So this both at two different classifications of profit percentage. However, when we talk about some normal terms, we always calculate the profit percentage on sales because it is our natural tendency. And also, we tried to figure out the numbers on the basis of our sales volume. But there are some specific cases on which you are going to analyse that how much return you are getting from the investments in your business. In this case, if we assume that we are investing a $100 in our business in form of various kinds of costs. We are utilized that money. We also have other categories and we assume that we'll purchase the raw materials. We are paid to the laborers and all that sum up to $100 for a particular product. Now, we're selling it at $120. So in that case, if you calculate the profit percentage on the basis of say's, It will be lower. But if you calculate on the basis of cost, which is a $100, in that case, your profit percentage will be exactly 20%. The simple reason is that in denominator instead of C's, if you pick a cost which will be less than your actual value. So again, it completely depends on the perspective of your analysis requirements. And on the basis of that, you can calculate the profit percentage on the basis of sales are profit person days on the basis of cost. Now let us see, instead of say's, We were actually required to calculate the profit percentage on the basis of the cost. So instead of creating a new calculated field, we will simply modify the scene. Once we see like we easily editable, he'll like instead of sales, we will remove it and we will add cost in the denominator. Now, this will keep the name profit percentage of the calculated field is same, but the calculation within that, we'll be changed. Now to modify it, we simply need to click on the Modify button given over here, and we need to click on OK. So now you can observe there are several changes in the profit percentage. Overall, our profit percentage was around 12%, which now comes around 14%. This does not make it completely changed. The main reason is now we are taking up the cost as the base field for profit percentage calculations. And it means that we are earning revenue on our investment is 4124%. Now to come whether seem, let us create one more calculated field. This time we will rename it as profit percentage on seals. So in this case, when we will calculate it, we will simply pick up the profit field and we will divide it with the value of scenes. So let us drag the profit field. Then we will divide it with the C's and let us click on OK. Now, we can easily convert the number format of the same into a percentage form. And let us click on ok. So now you can easily compare these both column side-by-side to make it compatible, we can simply remove this sum of profitability. And now you can find out that there are some differences in percentage of profit on sales and cost. Now you can easily also compare that which measure you want to analyze and on what business you are going to present your report. So in this way you can modify with Joe formula, you have setup for a calculated field. You can keep your field on the same name and you can make the required changes if any mistake has happened or you want to calculate on an another angle for the same dataset. In our next session, we will talk about how you can use Calculated Items instead of calculated fields. 20. Insert Calculated Items: Hi. In this session, let us talk about the Calculated Items. When we talk about analyzing PivotTables fees, you can use calculated fields as well as calculated items to analyze your data with different angles. Now when we talk about calculated items, it is slightly different from Calculated Field. The formulas and functions will remain same whatever we have used till now. But there will be some differences in terms of its visibility. When we talk about calculated fields, it is automatically included as a new field into our Pivot Table field area. So in this example, so far, we have created several calculated fields like cost, profit percentage, the profitability and profit puzzled on scenes. Now, instead of creating a calculated field, we can even use a calculator item to include one more subcategories. So in this case, you won't be able to see any of the field added into a list. But within a field, you will get one new value. Like in case of furniture will bookcases, chairs, punishing, and tables, which are the subcategory is now you can add one more subcategory by combining two or more than two options given over here. Now to understand in a better way, instead of subtitle agree, we will use a region in this example. So within each category of product, we are going to keep the data distribution on the basis of region. So now here we get the details of Central, East, South, and West region for each of the category of product. Now, when we generally see that total is given off an entire region. But what happens if we just wish to find out the total of two regions, or let us say three regions. For that, you can create one more calculated item which will be added in all the category of product. Let us see, we want the combination of central and eastern region and south and west region. So we can simply create two different items which will be listed under furniture, office supplies, and technology. So for that, we will go to the Analyze tab in the pivot table tools in field and items, we can see two different options, calculated field and item. So if you have selected anywhere in the values, you won't be able to create a calculated item. So now let us select anywhere on the row labels. Now, if you will see, now you can create when calculated item. So you cannot create any calculator item in the value sections. So we will create two Calculated Items. One will be Central East region and the another one will be southwest region. So let us create our first calculate item, which will be central east. Now, just like what we have seen, we can easily create a new formula. Now, unlike calculated field, where we only got the fields to add up over here, we also get basic field item. So like in this case, within region, we can select any of the item given in the list. Instead, we can select on city and then we can add so on. But whatever item you wish to select must be present in your pivot table. In our pivot table, they are two different items. One is the region and the second one is category. So you can only select the items which are visible in a pivot table or which are included in your pivot table. So let us select region. Now, we want to sum up the data of central region and East region. So we will simply insert the value of central region and then we will insert is Tunisian. Now we just need to click on oaky. So here you can see that under each of the category, now, along with the full region, we also held Central East data. So now this is basically the combination of sales, profit, cost, profit percentage of Central and East region. Here you can observe that if you will total up both these items, you will get the total of Central East. Now in the same way, let us create one more Calculated Item. We will again select anywhere from the row labels given over here. Then we will go to calculate item. Now, we will go for the combination of south and west region. We will simply add the sum of south and west region. You can also see that now this item is included in this list, which is a sublist not in the main field. You cannot see these items anywhere in the actual field area. So you can not import it as a field, but within your legions, that will be added as another item. Now for this, let us add the sum of everything of south plus we will add up the data of west region. Now when we will click on OK, this will create another item within the list which is stated as southwest. Now this is only visible til you keep or use these Pivot Table. If you will create one new pivot table all the way around, then you cannot see these options in your data. This does not affect any of your data within your actual database. But in the pivot table you will be able to see two more categories within your region. So now let us simply copy this pivot table. Now let us paste the same pivot table in a new sheet. Now, we can simply drag out region from this data. So here you can see only we have the category. Under the category. Let us drag the subcategories. Now, if we use the region in the filters, you will get one notification automatically, which will indicate that this field cannot be placed in report because it contains calculated items. The reason is these Calculated Items are saved within our PivotTable. Whenever you will copy the same pivot table elsewhere, you can always use the same interior display area, but you cannot use it as a filter. Because your main data does not have those two new categories which are created, but your pivot table will definitely have those two. So instead of filters, we can simply drag Region and we can edit to laws without any kind of conclusions. And we can remove the subcategory come over data. This is the main function that you need to understand. Once if you've created any of the calculated items, you cannot use the same into your filter basis. This simply creates a hypothetical category within your particular data, like in region, these two items, which are Central, East, and Southwest are the hypothetical examples are the subcategories which are shown. So in this way, you can create and use calculated items to analyze your data in a better way. Like here, we can compare that when you talk about combinations, whether the sales and profit from Central and East areas combined are higher or the season profit of the combination of south and west regions are more. So in this way, you can analyze your data by creating new calculated items without creating any calculated field. You must remember that whenever you will create calculator item, it won't up your entire PivotTable Field area. It will not create a separate field and you cannot bring it to your pivot table separately. So whenever you wish to analyze the data unit to being the regions in your pivot table. Now in the same way what we have already seen for calculated Ps, we can even go to the Calculated Item options. We can see which were the Calculated Item which are already available for this data set. And we can easily modify the scene. So now you can try out on your own different types of Calculated Items for these fields available in our sample. So try and analyze different things in your data. See you in the next session. 21. Filter and Sorting: Hi. In this module, we will see that how you can use some basic filters to over pivot tables. When we talk about filters. These are very much necessary to filter out the data to a specific point. Like in your pivot table, you want to dig out some information about a particular thing, or you want a particular range of data that you can do with the pivot filters. In this session, we will see that how you can apply some basic filters from a pivot table itself without adding any other field. And we will also see that how you can use the sorting options given in the Pivot Tables to understand the basic filter functions and basic sorting functions, we will create when Pivot Table of categories and subcategories. And we will include the data of sales and profit into our pivot tables. So to create one PivotTable, we will go to the insert tab and insert tab, click on pivot table, and we will click on OK. Now in our rows, we will add categories and subcategories of product. Once we include those in our rows, then in our values section, we will add the quantity of sales, the amount of sales, and the profit that we're on throughout this period. Now, there is a separate filter section wherein you can drag your data and you can apply filters on that. But this is not the only one where you can apply filters. When we talk about the pivot table, you can directly apply the filter's on all the data that you're already dragged into a pivot tables. Let us see, in this case, we want to apply filters on our categories. So simply, you can right-click on the row labels given over here. And you can see that you get different options, like whether you want to include all or let us say you just wish to analyze the data of technology category products. You can omit others and you can click on, okay. So now in your pivot table, you can only see the data which is relevant to the technology products. Let us reselect all of them. Now, one thing you can observe that there are categories as well as subcategories in laws. So what is the option to put some filters into a subcategories? Well, that also can be done from here when we talk about royal liberals. And if you have multiple items in your rows, all will be visible into your field options like my default category is the host field that we imported, that will be sean falls. Then you can also go to the subcategories. Now from u, you can omit some of the details which you don't want into your pivot table. Let us say you don't want the data of bookcases, jazz and copiers. So you can simply antique those and you can click on OK. Now you can observe that all these subcategories are not included in your pivot table. Again, you can clear out all the filters by simply going through the subcategory. And you can select all the other option to clear out the filters is obviously the option given over here. You can directly clear filters and all the data will be brought back to its natural palm. This is a simple beauty will. It means that we have sales, profit and quantity on the columns. We do not have any particular field into our columns. For this example, let us see if we add the segment into our columns. Now, under column levels also, you get the option to filter out the data. Let us see, out of all these segments, you just wish to analyze the data of corporate segment. You can all of those and you can click on, okay. So whichever data you have in your pivot tables in form of rows or columns, you can simply put filters on those values. Now let us remove the segment from our table. These are the basic functions of filters that you can apply directly from your table. Now, along those filters, you can even sort your data in a way that you want to present for your reports. Now for sorting the various options which can be available. Let us click on Row Labels. And here we can see that whether we want to sort the data of categories or sub categories that we can select, let us the category is false. Now, we get two basic options of sorting the data is whether we want to sort from a to Zed or from sorting to eight. If we will sort from X_ t2 in accordance, the details will be applied like in that case, Technology will appear first and furniture will appear last. We can even make it from addToSet in that case. Accordingly, the details will appear in the Pivot Tables. If we wish to change the sorting order of subcategory, we can see like that. And when we make changes to the sorting options of subcategories, you can observe that categories are not changed. In that case, furniture only remains the false, but within furniture, all the subcategories will be sorted out. Let us remove these sorting options and let us keep sorting. It's-it only. Now these are very basic sorting options. Here we can observe that there are three different values, the quantity of sales, amount of sales, and profit. When we talk about pivot tables, we can even sort the data on the basis of that as well. Now, let us say we want to sort our categories on the basis of one of these speed. We can go to the more sorting option over here. We can even do it my newly or instead category. We can select any of these measures. In case of values, the a2 will be from smallest to largest and from Z to a will be largest to smallest. Let us see. We want the largest value first in terms of category. For that we can select on descending. Then we want the most profitable products on the top. For that instead of category, we can select some of proper. So when we will sorted like this, the most profitable category will be brought up. Then there will be the second, third 1. Let us click on OK. So now here we can see technology category has put on the false position because it earns the maximum profit. Then we're the second that is office supplies. And then we get the details about furniture. Let us see some more sorting options for this. Now, if you don't want to sort your data on the basis of any of the v and which are present in the pivot table, we get some more options as well. For that, we need to click on the More option over here. We need to undertake the auto sort option. Now here, in this option under forced key sort order, you can select any of these options which are on the basis of the days or on the basis of the month. Now this helps us in a lot of times when we are unable to sort the data in a proper format. However, for this example, we have one dataset which is already created on the basis of the particular date format and one format. So whenever we are dragging our date fields, we easily get in the same format. But if that is not sorted in a way, like if it is sorted on the basis of texts, we may not get january at the Haas plays. In that case, we can select any of these options to sort our data. So in this way, you can apply some basic filters to your pivot tables and you can sort out your day tack considering the fees which are already available into our pivot tables in our upcoming sessions, we will see how we can apply some multiple letters from the fees which are not available into our pivot table. 22. Multiple Filters: Okay, now let us see that how you can add some multiple filters from the fields which are not available into our pivot table display area. Well, for that, we simply need to drag and drop all those feeds into the filters tab over here. So far, we have added categories, subcategories, the quantity of sales, amount of Cs and profit into our 20 will. Let us see what if we wish to add some other options into your fees, like shipping mode or customer segment or region. We can simply add all of these options and we can drag it over filters area. Let us see what we wish to add the customer segment into the filters. We just need to select the option and we can drag it to the filters card over here. So now we can easily select all or any one option from this given data. Well, in case of a grid table, it was not easy to add multiple filters. But from this filters card, you can add as many filters as you wish. But you need to make sure that you arrange them in a proper format. Like along which segment you also want region into the filters. Then we just need to drag the regions field and we need to drag it to a public art. You can also apply the filter to a particular region. For all segments are you can select any one or two segments and that will display the data of that particular segment in that particular region. Like in this case, we have filtered out the consumer segment data for central region. So on the basis of these analysis, you can select the which products should be continued in that particular region and which should be removed for that particular consumer segment. And instead of consumer, you can even select on corporate segment. So you can apply both these filters at the same time. You can even add filters further from this level, like along with this, we also wish to add one more filter. That is the shipping mode. We want to find out that how many packages are delivered on the same day. And you can filter out the data on that business as well. So now in this PivotTable, we are only the data of corporate customers from central region to whom we are delivered the products on the same day. To remove and return, you just need to go this filter public art and you need to drag it out. Now, we have two filters, the segment and region. Right now we are selected the multiple items options on. You can even switch it off. In that case, you can only select one option or you cannot select a combination. Like now, as we have switched off, we can either select the consumer, corporate or home office segment or the data of all the segments. We cannot select a combination of consumer and corporate. Let us say we select homophily segment and we click on OK. So in this way, you can switch or switch off the selection of multiple items for each of your filter. And you can filter out the data as per your requirements. If you will select more than one item in your Filter option, you will be able to see that it will highlight it as multiple items. So it makes your analysis easy. And even you can transfer these files to your superiors for analyzing the data. 23. Report Filter Pages: All right, now let us see that how we can use the Report Filter pages option to make reports very quickly with the help of these Excel Pivot Tables. Now when we talk about reporting, that is very much necessary when you are using these kind of pivot tables. You may create a certain layout, but you want your data to be printed for your documentation verbose, and you may need several different types of reports. Four different accounts are for different segments. Let us see, for this data which we have created using our PivotTable, we have the summary of sales and profit for different categories and subcategories. Suppose if you are supposed to generate reports of a particular customer, of a particular segment or of a particular region that we can do using this option. Let us see how to do that. Now, in over this pivot table is first of all, let us remove all filters. So we will keep all values and we will create reports for different segments as well as region. Now, if you do not know how to do that, one unusual thing that everybody does is to create a separate pivot table for each of the values. Like, instead of creating reports directly, I will select consumer details. Then I will copy this, and then I will paste it into another sheep. But with the help of PivotTable filters option, you can directly create reports with just a few plates. So let us select all. Now to create reports using your filters, you need to go to Analyze tab. And that in the first option of PivotTable options, you need to click the small arrow available over there. And you will see one option of show Report Filter pages. You need to click on that and you will get all the options of filters which enabled into your pivot table. Like in this PivotTable V0 added region and segment into our PivotTable. Suppose if we want the similar kind of report for each and every region, we just did to select on region. And we need to click on OK. Once we click OK on the basis of this pivot table, we can see that all the sheets are automatically created for each region like this data is so central region, for East region, for South Region, and similarly for west region. Now, if you want to prepare your report on the basis of segment instead of report, again, you can go to the Report Filter wages. You will select on segment and you will click on OK. Here you can see that you can directly find out the report for consumer segment, corporate segment, and home office segment. Now, these filters are not that large in quantum. It means that there are only three to four options in each category. We held three segments and we only have four regions. We can even copy and paste from our old method and we can easily create reports. But this function is very much useful when you are using a large dataset. For example, let us move back to our main Pivot Table. Now, in this pivot table, along with region and segment, let us add the consumer name. Now suppose instead of a region, you want to create a report for each of your customers to measure the profitability with them. Here, you can simply go to the Customer Name option and you can see a long list. Now selecting every customer's name one-by-one, then copying the data and creating a new sheet can become a daunting task. In such situations. These option is very, very useful. So you just need to go to shore report filter bases. Now, this time we will add customer name as show Report Filter pages. We will click on OK. Now, because this is a last dataset, Excel may take a few seconds to analyze all the data and it will create reports of each of the individual customers separately. And you can see over here that one-by-one, each customer's reports are created and you can even find out the name of each customer on the name of sheet. Again, it depends on the size of the data that how long will it take? Generally, it takes a few seconds to one or two minutes to complete an entire process. Alright? So almost within a minute, V0 created all the reports of our customers. Now, in this excel sheet, you all need to do is just take printouts of your customers and you can use it for your documentation. Or you can even find the summary of each customer. And you can find that whether that particular customer is profitable for you or not. So here we can see with the name of each of the customers, the excel sheet have been created. And you can easily take the printouts of the same, or you can even analyze the data as per one individual customers also. So now these are the basic filtering functions which you can use to filter your data as well as to create such reports. In our next module, we will see that how you can use some advanced filter options into pivot tables. 24. Date Filters: Alright, so now we have learned that how you can use filters into our PivotTables. Now, there are some specific categories of filters which you can call a kind of advanced filters that you can apply to a PivotTable as well. Well, in this module we are going to talk about such advanced filters which can make your wall very, very easy. Now, when we talk about data analysis, you are always trying on getting some more and more insights from your data. In that case, these advanced photos help you a lot. If we see there are basically three different types of altos which you can apply. The first one is date filter, the second is value filter, and the third one is labeled filter. In this session, we will see that what are different Date filters and how we can apply it to our data. So first of all, let us go to the insert tab index. We will create a new pivot table. We will simply select on new pivot table and we will click on OK. Now to understand it in a better way and to see its effect directly into your data. We're going to drag the order date field into our rows. Then we will select various values of sales in our values section, and we can add any of the other measure into columns. This time. Let us see, we drag region into the columns. Now here we have a different region. Why summary of sales for different years. But we want to apply some Date filters to that. Now to apply the date filter, all you need to do is go to the ordinate column, click on the small button. Here. You can see that you can either directly select any of these options or you can select on date filter. Now whenever you will select on Date filters, you will get a bunch of options. And let us see one by one that some similar options that you can analyze pastas, whether it is equals to before, after, or between. Now, for that, you can select a particular date, before a particular date, after a particular date, or between two dates. Let us try all of them one by one quickly. If we will select on equals two, you can simply select any of the date in your dataset. Now this dataset contains the date values from 2016 to 2090. So let us go back to the 2019 and let us select, and of course September 2019, and we will click on ok. So now here you can see within 2019, you only have one value, and that value is specifically for September. So in your entire pivot table, you can quickly find out the data of a particular date using this date filter. Now, let us quickly clear out this date filter and let us apply one mode. All you need to do is simply clear out built up from oddity. Here. Again, the data of all the years becomes visible into a Pivot Table. Now the second option in the same classification is you can select any particular date and all the data before that date. Will be visible into a pivot table. So let us say we go to before option. Here. In the dead selector. We can simply select any of the date or we can even type it down like if we want the data of 15th, June, 2017 and all the data before that particular day. We just need to click on OK. Now, all the data before the 15th June only will be visible into a pivot table. Similarly, you can simply apply operator for after a particular date. Let us say you only wish to find out the data after. Pause January 2019 already to do a select on filters, Go to the after option and you can simply type it down as far as January 2018 and so on. So all the data right, from cause January 2018 till the last day of this, let us add will be visible into a Pivot Table. Now, apart from before and after, you can specify a date range. Let us say you want to find out the data between two particular deeds. We can find that with the option of between two particular deeds. So here you can specify two different dates. Like if we want to find out the data from January 2019 till four February 2019, we can do so as well. All you need to do is simply write down the relevant dates into this between option and you need to click on OK. Now you will only get the data for the specified range in between two particular dates. Now this can be termed as a one classification within your Date filters. That is before a particular date, after a particular date, between two specified dates and a particular date only. Now let us see what are different other classifications for your Date filters. Now, when you talk about the second group of Date filters, you can term it as a current or clouded scenarios. So if you are maintaining datasets, you can simply find out the data of tomorrow, today, or even yesterday. Now one question might arise that how you can find out the data for tomorrow. So if you're walking on Excel sheets, which are budgets or which indicates the expected values of sales for future dates. For that, you can use the option of tomorrow as well. Now, these similar options are available on a daily basis, that is tomorrow, today, and yesterday for the weekly business that is next week, this week, and last week. In the similar way, you get it on monthly basis. Quarterly basis as well as yearly basis. Let us see. You just want to find out the data of last year. So you can see like on last year and you get the data off 2019. Remember, this options walks. Well, when you are using a loud dataset, it means that either you are using the most recent dataset or you are continuously updating your data for this pivot table. If you are connecting any external data source which has allowed a tiny boats, then these options can be very much useful as you can see some real-time updates into your pivot table with these kind of filters. Similarly. You can find out the data for the last quarter as well. So if you will select that option, unfortunately, we will not get this option in this particular dataset because in our sample dataset, we only have the values up to 2019 only. So let us simply clear out these Date filters. Now, we'll talk about a particular date range functions, as well as the sequence functions, which are for daily, weekly, yearly basis for next, current, and previous. Now, there are some other specific options for date filters as well. You can go and select the option of year-to-date. Right now, we are not getting any kind of data as we've already described that we have the dataset only up to the year 2009. So if we are having the current year's data that will be displayed up to the current date itself. So whenever you are analyzing, you can get the year to date. The JAR which ends on that particular date in your pivot table. Let us clear our desorption and see some more options which are available for date filters. Now, when we talk about Date filters, one amazing thing that this Excel PivotTable Tools provide is that you can even create a custom date filter for your fields. And you can apply some standard Date filters to a pivot. So if you will go to a Date filters at the last, you get two different options. One is all the dates in that particular period in that it will be sorted on the basis of photos as well as months. And you get the option of custom filter. If you will select the custom filter, you can tickle around different options, like equals does not equals is before or is before or after, or is after or equal to. Now this is a combination of what we have seen in the beginning of this session. So let us say you want to find out a particular date is before. Then you can select a particular date and all the data before that date will be visible into our pivot table. Now when we talk about some standard date filter options, you can simply notice over here that if you select on Date filters, you can simply go down to the second last option that is all dates in a particular period. So if you will select quarter one, now, this will present you the quarter one data of each of the years, not a particular quarter one as a whole. Generally what happens that you wish to analyze the data on year on year basis. It means that you wish to compare the quarter 119 to the quarter one figures of 2018. In such cases, you can simply select this option. And now, within each year, you will only get the data of that particular quarter. You cannot find the data of other quarters in that particular year. In the similar way, you can even select a particular month as well. So in this case, if we simply remove the filter and we can add a new date filter, we will select all dates in a period of January. So now the data of January of each row is visible entire pivot table. And you can easily analyze and compare that in which you're in January month, you got more sales. Now, if we do not use these filters, this cannot be made possible. The only reason is if you will remove the year's end quarters from your rows, you can simply see that now the data of month wise is displayed in a pivot table, but that is sum of all the January's for all the years. But if you wish to specifically find out that the tau January 2016 and you wish to compare it with January 2017, January 20182019. In that case, it is very much necessary to apply the Date filters as we have seen previously. So let us simply bring back all this and we will simply go to alternate field. We will go to Date filters. We will go to all dates in the particular period, and you can select any of the month or any of the quarter. Now this also depends that whether you are having a group of data in your fluids or not. If we only have the date wise data, then it may be possible that these options are not visible. But right now we have the data for different years within which the data is classified in quarters. And that's why we are able to particularly Find out the data for that particular month or quarter or let us say if we select on me, now all the data which is off Maman in each of them will be visible in your pivot table. And you can compare that in May 2017, whether CS was increased compared to May 2016, R-naught. So you can find out you are on your analysis using these functions. So if you don't learn these functions properly, you might get confused on using these photos. So that's why it is very much necessary to understand and learn all this advanced maltose into pivot tables properly. Because with this, you can analyze any kind of data in a way you want to find out your insights. So that's it about Date filters. In our upcoming sessions, we will see that how you can apply some filters as well as labeled fetus. 25. Value Filters: Alright, so now let us see that how we can use some other valuable tos into our pivot table to analyze your data very quickly. As we have seen, that how we can apply the Date filters in the same way, we can even apply filters to a specific values. We can simply find out the data which lies between two specific grains in numeric values. But remember one thing, value filters are not directly applied to the value fields. Instead of that, whichever field you have dragged into a rows and columns, you can simply select them and you can apply your valuable toes on that feeling. For this example, instead, region in columns. And instead of the order date in rows, we will simply select the data of the product name and we will drag it to the raw speed. So it will simply select the data or product name instead of category and subcategory this time. And along with sales, let us simply use proffered indoor values. So now we have two different values into our pivot table that it says in profit. And we have a useless of different product names. Now, if you wish to analyze the data of these products on the basis of certain value is you can simply go to the product name field. You can apply a valuable tool in that you get different options, like a value specifically equals to something, which does not equal to something greater than and greater than or equal to, less than and less than or equal to between, not between and top and bottom values. Let us begin with equals two. However, if we talk about this data set, this is not an appropriate option to use the scene because the value of sales will be definitely in some range, not a particular value. But if you are dealing with some examples in which you have a vector that is a's. You can simplify and out the data for all the people who belongs to a particular age. But as you can see, that there is one data which indicates the value exactly of 49.98. So if we will use that, the sum of C's is equals to 49.98. And if we will click on, okay, only the relevant data will be visible. Now in this case, you can even apply any of the field because we have to value is that it says in proper, you can select any of them. Then you get an option of equals two does not equal to greater than and all of them, which we have already seen. So you can see like on equals two and you can click on ok. So now here you can see that only the relevant data is visible. Our PivotTable theory. Now let us try out some other options like value range. If you select the data between arrange, you can simply select that what exact range you want to find out. Let us say you want to find out the sales of the products which are between 100.10 thousand. So you can simply start with a beginning range as 10000 and the ending value will be 10 thousand. Then you need to click on ok. So now all these products held the sum of series. We're doing 110 thousand. If you think that you are not finding exactly the same thing, you can simply. Go back to your photos and you can edit the same as well. So instead of 110 thousand, Now let us simply use nine thousand and ten thousand. So all the data, which has the totals is between $910 thousand will be displayed into a pivot table. So now you can get a small list of the data which is relevant to your analysis. Let us simply clear out this filters and let us see what are the other options, then you can even apply the options like greater than or less than. So if you apply the formula of glitter than all the values above that will be displayed into PivotTable fees. So if you see your sales has to be greater than 25 thousand to be a part of this list. You can simply click on, okay. And you can find that there are only two products which are completely outperforming and giving you the C's about $25 thousand. In the same way, you can even find the values for less than. You can simply apply a value filter of less than. And you can select the value, let us say 500. So you only want the data with as the CS value less than 500. And you can click on, okay. So here it is an entire list of products which has the value of C is even less than 500. Now just like this, you can find out the Nita of top certain values and bottoms or ten values. It means that you can select any number of selected products and you can find out from the top of the water. So let us say you want to find out top five performing products from this dataset. You can simply select from top and bottom motion given over here. Then you can put the number of items that you want to find out. Let us see if we wish to find out five particular products. You can simply type down as file. Then you can select the criteria whether you want the top five products on the basis of sum of sales or some of, let us say we wish to find out top five profitable products from this entire dataset already to do is click on OK right now. And you can see that these are the five products which gives you the maximum profit out of all others. In the same way, you can even find out the top five loss-making R. We can see file bottom products which are not giving you profits. For that. Instead of command of top, we can select and bottom. Let us say instead of file, you wish to find seven products. And then let us click on ok. So now these are those seven products which are not making you good properties are, in this case, even this, I'm making some use losses. So in this way you can find out top-performing products as well as non-performing products from your entire catalog. This is about a particular product. In the same way you can even find out the data for a particular customer. Let us see if we remove the product name from this pivot table. Instead of that, let us write the customer name. Now you can simplify and out top pie customers or bottom by customers as well. So for that, we need to apply that value filter on Customer Name field. If you have applied any of the filters into a product name, that won't help you out because simply your product name is not anywhere in your pivot table. So in the similar way, you need to select the Customer Name. In that you need to go to the filters. In that you can create any kind of new valuable tour which will be applied to your pivot table. Let us say you are finding top 20 customers on the basis of sales. You can simply click on OK. And now here you can see that these are all the 20 customers to whom you are selling the maximum items. In the same way you can even find out the bottom Gandhi customers. We will again go well your photos and we will select on what I'm going to take us to most and we'll click on ok. So now here you can observe that from your bottom 20 customers, you are just making a sale of around $1500. But when we talk about the top customers, here, you can observe that from your top 20 customers, you are almost making a sale of $264,909. So there is a huge difference to the CEO, to a particular customers. So now you can target these 20 customers to get more and more revenue into a business. Along with that, you can simply see the profit of them as well. You can even find out that the few customers which are giving us good sales amount, but in that also we are making some losses. So we need to figure out the discounting strategies for them. We need to provide them less discounts and we need to sell them at a particular price wherein began on some profits. So in this way, you can apply various value filters to a dataset to analyze your data very quickly. In the same way, there are also some liberal filters, which we'll see in our upcoming session. 26. Label Filters: Now when we talk about some liberal filters, these are almost similar to rvalue builders. The basic difference between label and valuable tours at that level, filters are actually applied on the categories, while value filters are applied on numeric values. If we talk about Label Filters, these are least used by most of the people. The only reason is that most of the people don't know how to exactly use these Label Filters and how easily you can find out the data with the help of labeled windows. In this session, let us see how you can apply some labeled filters to filter out the data and find specific things from your dataset. First of all, let us simply remove the customer name from our laws. We will keep only sum of sales into our values. Then let us bring the data of region in columns. And we will bring the data of categories, ends up categories into our rows. Now, these are going to be useful to see how labeled filters are actually applied to these fields. To apply the label filter, all you need to do is select a particular category of data, then you can go to the label filter options. Again, these are almost similar options to what we have already seen into overvalue holders. But in value filter, we use to type down the numbers or numeric values. In this case, we are going to use some alphabetical orders to filter out the data. So let us see if we use the equals two sine, we can simply find out the data of a particular variable from that field. So let us say we are using subcategory. Now we know that art is a part of a subcategory. So we can simply type down ART and we can click on OK. Now you need to be very specific when you are using equals to Peter. Because if you make any kind of mistake in spellings that won't recognize. The second option is does not equals two, which is almost similar to the filtering options of equals two. So if you will select does not equal two, and now you will type down everything apart from art, everything will be visible into a pivot table. Let us simply clear the smelter from our subcategories. Let us try it out on category now. Now next we have that begins with ends with contains greater than or less than and between filters. So when we talk about begin with, you can simply put any particular letter and all that it is, which begins with that particular letter, will be displayed. So you will simply write down f. Only furniture will be displayed and all other fields will be hidden. In the same way you can even find out the data for ending with. So if you simply type down the word, why, only technology will be displayed and all other will be hidden. About begin with and bandwidth liberal filters. Then we can use contains. So if you will type down contains no. So in the entire board wherever know is included into your PivotTable Field that will be displayed. So in this case again, technology is available. So if instead of that, if we will go back to contains mode and if we simply type down, you are, there is a part of furniture, right? So now only furniture will be displayed in all other category will be removed. You need to be very specific on which field you are applying these labeled filters. Right now we have applied all this level filters into categories. You wish to apply any of the columns. You can even do so with the same technique. So let us say instead of category and subcategory, we wish to filter out the data from the region. All we need to do is simplifying the region field and we need to apply a label filter. Let us see if we select less than or greater than. Now. One common question which comes in our mind that how we can use a less than and greater than combination pour on non-numeric things. Like in values, we can understand that we can find out the data less than $1000, greater than $10 thousand, etcetera. But how can we do it when we talk about four bytes? Well, in the same way, we just need to type down the alphabet or combination of alphabets to find out the details. So here we can see that South begins with the data of s. So if we simply write down as greater than S p. So it will start and consider the first letter is as, second is p. And whichever are greater than these letters will only be displayed. So as o will be considered as less than SP and that won't be displayed. So in that case, we only get the data of west region into overfeeding. Instead of that, let us see, instead of we'll type down S. In that case, the details of south region will be displayed because S o will be considered greater than S. In the same way we can even apply the less-than filter. So let us see. Instead of greater than, we will go to the less than option. Similarly, we will use as P and we will click on OK. So now in this case, Central, East, and South, all will be considered less than that particular phase. And that will be displayed into a PivotTable area. So similar concepts which are applied to numeric values will also apply to the alphabets. It is not necessary to provide two alphabets only. You can provide n number of alphabets. So let us say you just missed to provide one alphabet and you want Excel to determine the details on the basis of that. So simply, you can go to less than, you can type down as W, again, less than w, r Central, East and South. So all will be displayed. Now apart from greater than and less than, We also have the option of between and not between. So if we simply type down the data between C and T. So all the details between C and T will be visible if and if we will make it from D2D. D2d options will be displayed. In that case, East and South Region data will be visibility of wheel and all other will be filtered out. So these are the basic options which you can apply in terms of labeled filters. That is, equals to R nought equals two begins with an ends with, in the similar way you can use the contractility parts of that. That is, does not begin with and does not end with. Contains in that particular word has to be available in that data, then greater than and less than and between a specific range. Remember, this liberal filters can only be applied for the fees that are available into the rows and columns of your pivot table. If you wish to clear out the filter, all you need to do is go to the field and you just need to select unclear out filter. 11. Important thing is that if you will create any kind of labeled filters or relevant or audit filter, which are a type of advanced filter that will be carried throughout all your sheets. So if you've created another pivot table, this filter will also carry forward to that pivot table as well. So this creates a particular filter on that entire field and that is carried forward across all your datasets. It is not something like a filters God, which only applies to that particular field. You need to make sure that whichever filters we apply our ports on specific reasons and you are gaining some data insights with the help of these filters. In our upcoming sessions, we will learn that how apart from Pintos, we can use some other pivot table tools to analyze your data and to find out various insights from your data. 27. Slicers vs Filters: Hi, bye. Now you have mastered how to use some filters into a pivot tables to analyze your data. But filters are not the only option which will help you into an Excel pivot tables. Well, in this module, we're going to talk about one amazing feature of Excel PivotTable that is slicers. Now people often get confused between filters and Slicers. So in this session, we will see how these both are different and in which situations you should use either filters or slicers. To start with, let us create a new pivot table. We will go to the insert tab. From that, we will select on pivot table and we will click on OK. Now to understand the difference between polluters and Slicers instead of categories, we will select the order date into our roles. We will wreck the Order Date. And in terms of values, we will select the amount of sales. Now in column, we can select any of the other option. Let us select segment into our columns. Now here you can see that the data is given for different yo, within each year we have data for different quarters and months. If you just want to analyze the data for yours only, you can simply select the quarters and order date and you can remove it from your Ross field. Now we only have the data for each of the years. Now this makes it very simple and clear looking PivotTable. Now, assume that you want to add the categories and subcategories into her pentose. So you can simply go and select on the option of category. You can drag it to filters and you can even drag the subcategory into the filters. Now on the basis of this, you can select a particular category and subcategory for this entire. You want to analyze the data of a particular subcategory within a category for each of the years for different segments. Now first of all, let us see that how you can do it with filters. Well, from your, we can simply go to the furniture guy degree and we can click on OK. Now we can select a subcategory. Let us see if we select art and we click. Okay. So did you notice it means that it is not a part of furniture. Now, if you add this kind of hierarchical data into your filters, this makes her task very daunting. Instead of this, you can use slicers, which makes you evolve very easy. Let us remove all this filter and we will add both these items into our slicers. Now to insert a slicer, you just need to select anywhere in your pivot table. Under a PivotTable Tools in analyzed section, you get an option of inserting a slicer. Now let us select, Insert Slicer option. Now you can add multiple slicer at a time. Let us say you want to use categories and subcategories into your slicers, you just need to select on both of them. And unit two. Ok. Now this two different slicer options will be visible to you. Now when we compare it with the filters, let us see what are the main advantages of using the slicers. Let us say you just want to showcase the data of furniture. So from the category, you can select on furniture option. But now in case of sub categories, you can see that only four items are highlighted. So it indicates that because you have selected furniture, only these what are the subcategories which are included in the main category, which was actually not available into your filters. So if you do it only round of intos, you need to make sure that you know each and every item of your. But in case of Slicers, This makes it very easy. The second biggest advantage of this slicer is that you can use it for your desk bored as well. Now, scrolling throughout the filters may become difficult tasks. It may be possible that you know that which are filters that are stored in your beard day we'll add, you are used to use it, but if you present it in a visual form like a slicer, it becomes easy for your users to navigate and dig out the data into an Excel PivotTable whenever they want. So in this way, you can select any of these and you can even use the multiple items. Again, if we have some advantages, we also have some limitations to the slicers, like we are already seen in our basic and advanced filter options. We had n number of options which we can use to find out and filter out the data on the basis of our requirements. But with the slicers, you can just cut out the data from the given categories. Here we have selected category and sub category. So we can just took ALL around between the values given in these categories. In this case, you cannot filter out the data with the specific values, like for a specific value filter. Or you cannot set up any kind of specific date filter. You cannot even find out the top ten values or bottom ten values or something like that. So this is also a major limitations. In that case, you can use filters instead of Slicers, but if you are presenting it to your viewers and they are supposed to navigate the data, it is a better option compared to the filters. We also have one more thing in favor of filters compared to slicers. Now slicers show everything in a visual format, like you can see over here, that because you have added one Slicer, it is visible alongside your pivot table. But if there are so many observations in a particular item, like here, in this case of category, we only have three items or classification within it. When we talk about subcategories, VL around 18 to 20. But if we have the customer name into slicers, that can become very much difficult task because you cannot visually make it fit into her entire slicer options, and it becomes difficult to select any of them. In those cases, it is advisable to use a filter instead of a slicer. So these are the situations where you should use filters. Otherwise, slicers becomes very much useful, especially for its visual appearance alongside your pivot table. Now, in our upcoming session, we will see how you can create different kinds of Slicers for different data we use, and how you can use it to slice out the data into our PivotTable. 28. Slicers in Action: All right, so now let us see how this laser works in different way and we will analyze this data with different components. Let us start from where we left. Wheel created two simplest answers, 14 categories and subcategories. Now, when we talk about slicers, either you can select one particular item or you can select multiple ones. Like by default, you are able to select only one category from these. Let us say if you select office supplies, both others will automatically be unselected. Now, if you wish to select multiple items, all you need to do is tick mark on these options. You can even use the shortcut as altar. So you can directly select on these options. And from now, instead of single item, you will be able to select multiple items. If you want to remove from any of these, you just need to select it again. So that will be simply removed from your selection. If you wish to select only one item at a time, you can simply switch off this multi-select options, and from then you can only select one option. However, this depends on each of those lasers. Well, it means that if you have selected the multisyllabic option for category, that simply doesn't apply to sub-category. You also have to select a multi-select option on four sub category as well. So all the slicers are completely independent in terms of how they're actually used. However, they are interconnected when we talk about slicing the data. So it means that if you select furniture and office supplies, it will only highlight the data which are actually included in furniture or office supplies. Now slicers basically does the same functions. What are filter do? So if you wish to switch it off, you just need to select on the Clear Filter option given over here so that it will automatically clear all the filters. And remember, this works for all kinds of data. It means that it is not necessary that if you will, silicone furniture, then only the relevant categories will be available. Subcategory if you do vice versa as well. Like if you select on add items, only office supplies will be highlighted and furniture and technology won't be highlighted. It indicates that whichever subcategory you've selected from these options belongs to office supplies. However, if you will click on furniture, then it will simply remove all the data. The only reason is yours elected furniture from category and sub category, which is not a perfect combination. So for furniture, you need to select either from bookcases, chez punishing or table to make your data visible into a pivot table. So you need to be very careful while applying slicers. Let us create one more PivotTable. And for this, we will apply a slicer on the basis of state. Then we will find out that how these slices can be very useful when you are analyzing any kind of data. For that, we will go to Insert tab. In that we will go to Pivot Table Options and we will create a new blank pivot table. Now this time, instead of Filter1 slicer, we will add category and sub categories to our rows. Then FOR values, we will simply add the amount of sales and amount of profit. Now, this is a simple PivotTable. Now, instead of filters, we will simply select anywhere on our PivotTable and we'll go to Insert Slicer option. This time, we will select the item state as a slicer. Now this will enable all those RED options available to be sliced up. Now, this table will very much help you in analyzing the data on the basis of each of the state within the United States. If we talk about this data, let us say you just wish to get the details of California. So you just need to select on California and the data relevant to that state will be visible and you can easily explain their data to your managers. Suppose if you are using this pivot table as a presentation, you just need to create a slicer on states and let your manager asked any questions about any of the states. If they asked about the profitability of Florida, all you need to do is select and Florida and the data applauded, I will be visible to you. Now you can clearly explain them that Florida is a loss-making state because in furniture category and in office supplies, we are making huge losses in Florida. In technology also, we are not making much profit and overall, we are losing around $3,400 from Florida. Let us see. He wish to ask you the data of a combination of Florida and Georgia, all you need to do is just select on this multi-select option. And along with Florida, you can select on Georgia. Now, for multi selecting option, enabling this every time is not necessary, you can simply turn it off. All you need to do is select multiple items by pressing the Control key. Let us say that along with Florida, Georgia, you want to add some more states. So let us go to Indiana. We just need to press on control key and we need to select on Indiana to remove any of these for the selection, you just need to again press on control key and unit to silicon that options. So now this is the combination of data of Florida and Indiana. In that way, you can select multiple item as well. And not necessarily there all the time. You need to switch on this multi-select option and switch it off. You can just press control v plus a left-click on any of the option. And that will be activated and will be selected into your PivotTable visible areas. So in this way, you can simply use slicers to navigate through a data and to analyze your data. It is also very much useful in business presentations because in real time, you can answer to any of the questions related to your database. In our upcoming sessions, we will see how you can adjust to the settings of this slicers and how you can change the look and layout of the slicer. 29. Customizing Slicers: Okay, so now let us see that how you can change the look and layout of your slicer and how you can provide some customized settings into a slicer. Well, to do so, you just need to select on those slicer that you recently created. Like in this case, we are created the slicer offstage for our sales and profit summary data. Then whenever you will click on the slicer, you will see that as slicer tool option will be opened up, you need to go to the Options. And from this, you can change the settings obviously. So you can adjust the buttons and size of your slicers as well, and you can change the style and layout of your slicer. First of all, let us see which are the style options that you get to change into his license. You just have to click on the down arrow key. And here you get different dark and light background options for your slicer. All you need to do is select on any of them, and that will be automatically applied to a slicer. Now you can even match the same with the color layout of your pivot table as well. We have already seen that how you can change the layout of pivot table, right? So accordingly, you can change the layout of your slicer as well. Let us see if we use the DAG mode accordingly, the color options will be changed from changing the color of. One of the most important thing in this slicer options is how you set up the number of buttons and how you set up the height and width of this lesson. Like in this case, we can see over here that because there are so many states and we can not easily keep in a grid format. So here it is a long list and you need to scroll it down again and again. So what if you wish to convert into a grid format? All you need to do is you can stretch your slicer to maximum size. Then over here, you can simply increase the number of columns. So right now we only have one column data. That's why we need to scroll from up to down. So instead of that, we can increase, let us see for columns. And right now you can see that along with the pivot table, you can easily manage the size of your slicer as well. And within that, you can easily find out the data and name of each of the states. Now when we talk about the size options, you get this iss options for both. So you can simply control the size in terms of height and width of buttons as well, which will be just changing the format of these buttons. And you can entirely increase or decrease the size of this slice or table. So let us say if we increase the width of entire slicer, it will automatically increase the size of the buttons as well. But in terms of height, that won't affect much. So if you will increase the height of entire slicer, the size, or you can say the height of the buttons will not increase. Similar way, if you will, reduce the height of the burdens, it will not reduce the entire size of your slicer. So you need to make sure while selecting an appropriate height and width of your buttons as well as entire slicer. So now we have talked about the look and layout of the slicer. Now let us see what are different setting options, which you can imply to a slicer. Generally, we do not need to adjust the settings of slicer, but in case if you require, you can change 700 sightings. Now to change the settings of our slicer, you can simply right-click on your slicer. And at the last, you can select on slicer settings or as another slicer tools in options, you get one option of slicer settings. Now, you can simply select any particular name which is to be given to the slicer. You can select the display order and you can even add the caption. So let us say if you want to change the name of the slicer, you can simply change it. He'll, And in the display name also you need to change it to states. Now, we just need to click on ok. So here you can observe that instead of state, now it is indicated as states. Now, apart from changing the name, you can sort this entire table as per your requirements. So basically it is a table of all the states. Right now, all the state names are sorted from a dataset. You can simply sorted from Z to a and also from a to C. Now this is applicable to all kind of Slicers. Now the next thing is whether you want to hide the items with no data or you can show it with data. So to understand it in a better way, let us go back to our previous slicers of category and subcategory. To make it clearly visible, we will simply adjust these slicers, what we're learning right now, let us simply add the number of columns over. He'll let us apply the similar kind of formatting to our sub categories as well. So now you can clearly, visibly see all the categories and subcategories. Now, if you select on sub-categories, we can go to Slicer Settings. In that we can simply hide the items, no data, and we can click on OK. Now, previously we have seen that if we select on furniture, all the remaining items were not highlighted. But now if we will select on furniture, only four items will be displayed in subcategory and all other items will be automatically hidden. In the same way if we will select on technology only the items which are included in technology, which are accessories, copiers, machines, end phones are displayed and all other are hidden. Same applies for office supplies if you select multiple items. So all the subcategories which are included in this two will be displayed and all other will be. So that option is very much useful when we talk about hiding the items. But if you just want the Highlighting option that also can walk out. So if you do not hide, you get different options, like whether you want to visually indicate the items with no data or not. If you will switch off that. Now that will simply walk like filters. So now if you will select on furniture, it won't make any kind of changes into a subcategories data because it will not highlight the relevant fields to furniture. So this again makes confusion. So make sure that you do not do this. You always need to visually indicate the items with no data. Then the last option is show items deleted from the data source. So this is useful when if you are making some changes to our original database. Let us see if from original database you remove all the technology related items, then also the word technology will be shown in this license. So in this way, you can use slicers in different ways and you can provide appropriate settings as per your requirements of analyzing your pivot table. So far in this module, we have seen that how you can insert a slicer, how you can use different types of slicers with multiple select option as well as individual options. How are you the look and layout of slicer and how you can update to some important settings of your slicer. In our next module, we will talk about the timeline, which is slightly different than slicers. See you in the next session. 30. Using Date Timelines: Hi, welcome back. In this module we will see that how you can use Dick timelines, which will pivot tables to analyze your data very smoothly. Well, we have till now talked about various filters and sizes. Now date timeline is also similar to that, but this can only be applied to a specific date format data from your dataset. Like in this case, we have two different date columns, which are order date and ship date. So only on these two fields, we can apply a date timeline. Now let us see how we can use these columns into slicers as well as indoor date timelines. And what are the basic differences of using both of them. So most of all, we will move to insert tab. From insert tab, we will simply select on PivotTable option. We will create a new pivot table. Now again, we will drag Category ends up pedigree into our rows. Then in our values section, we will add the data of sales as well as profit. Now let us say this is the data of an entire period, right, from 2016 to 2019. We want to filter out the data on the basis of date. We can either add it into our filters action or we can create a slicer. Or the better option among all this is to create a date timeline. Now, to insert a timeline, you need to go to the pivot table tools in that under analyzed section, you get the option of inserting a timeline. Now as here, you can see that you can only select out of these two options. So you need to make sure that whichever dataset you are using, it has a particular date format field. In that. Otherwise, you cannot create a timeline from your field if you have any. But if it is in text format, it want recognize. So it is better to use a particular date format into your raw data as well. Let us say we want to create a timeline on order date. All we need to do a select on this checkbox and we need to click on OK. Once we will click OK, it will automatically create an entire timeline for this data. Now, in this case, it is already buy forget it as months. So you can even particular unselect a month within your entire yo. Now, in this session, let us compare it with slicers. When we talk about the order date, you can even create a slicer policy Sagan, let us see, go to Insert Slicer option. You can even select on order date and you can click on opening. Over here. You can see that order date is completely formatted on the basis of a particular day. You need to group them or you need to convert it to yours. So in this way, it will directly create a slicer for Your Order Date column. It might not normally walk out as you always. Comparatively, instead of a slicer, you can use a date timeline, specifically import the date field. Now in our upcoming sessions, we will analyze all WHO's up a date timeline and how you can analyze the data on the basis of. We will also see that how you can analyze the data on the basis of different layers of date timelines. That is, in terms of months, yours on a particular date. We will also see that how you can use the multi-level date timelines, meaning that how you can build around the data on the basis of months, days, your squatter, et cetera, with the popular date timeline. 31. Multi level Timelines: Okay, so now let us explore all of you chose operate timeline. First of all, we will simply select on this lesser and we will remove this slicer goes in this session, we will walk out on our timeline. Now as you can see, that all the data is distributed in the terms of mn. So you can start from any month and you can end up to anyone. And you can analyze the data of the same into a pivot table. Like if you only have to analyze the data of January 2016, we just need to select on January option. If you wish to analyze the data arrived from January phil June 2016, you can simply drag it. June 2016 and all the data from January to June 2016 will be visible. In that case, it is not necessary that you can select the month from a particular year only. Let us say, instead of that, you want to analyze the data from October 2016 till March 2017. You can even do so by simply dragging this timeline option. So right now the data which is visible into Pivot Table Is the data right from October 2016, build mass 217. Now, when we talk about the multiple level in terms of datetime line, it means that you can switch between months, quarters, dates, as well as yours. All you need to do is simply LEA this filter, foster Hall, and then you can select any of these options, right? Given from the top right corner. Right now, the month is selected and that is why the data is continuous or distributed in tubs of month throughout the day timeline. But if you wish to analyze your by your all you need to do is select the option of yours. Now, you can only select a particular Yo and the data of entire Your will be shown into our PivotTable. Now within yours, you cannot filter out the data up to month level. So in that case, select the data of minimum one year. The next option is selecting quarters. Now when you select partners, it is similar to months. The data is divided on the basis of the major term which will be yours. And within that, you will get the option to drill down your data up to a quarter level. So within 2016, you can select from quarter 1234. Within 2017 also, you will get the option of selecting quarters and whichever NGO selected that will also be visible over Hill. Now, instead of Cortes, let us say you select months. Again, this will remain the same as we have seen earlier and beyond months, you get the option of selecting a particular date. Let us see if your manager asks you to find out the data of a particular date. You can either do that with the help of date timeline. Let us say you are supposed to find out the details of sales and profit of 15 togas, 2 thousand ating. Well, you can simply go to your date timeline, and you can simply select on August 15th, 2018, and all the data relevant to that particular day will be displayed into a pivot table. This also had to in reporting and analyzing the data. If you want to remove this filters, all you need to do is simply clear out this date timeline. Now, one of the major question is that can you put multiple date timelines for the same data of all Zim Pivot Table? The answer is yes. Let us say you do not want to switch again and again from days to Joe's crew quarters or two month. In that case, you can simply create multiple debt timelines for the same pivot table. Let us create one more timeline for the same table, we will go to insert timeline option. We will again select the order date and we will click on ok. So an idea, you can see both the timelines are selected on our foss timeline. Let us switch it to yours. So probably over a particular job. And in second timeline, let us select quarters. So what if we select 2019? Well, then the data in terms of quarters will be highlighted and selected for the force to the last quarter of 2019 only. Now, within that, if you wish to find out the data of foss quartile, you can see the immediate effect in the terms of your as well and in terms of quarter as well. But you need to make sure that you need to clear your filters before applying another one. It means that now instead of this quarter timeline, it will directly go to your your day timeline and you select 2018. It will automatically select the entire your false. So the major level of detail will be considered false. And then you can select any of the CTO from your quarter timeline. Similar to that, you can even convert it to months and you can select a particular month and the datetime that which you created on the basis of yours will be adjusted accordingly. So in this way, you can use these timelines and you can use some multiple layers of date forms like yours, quarters, months, and dates to analyze your data very easily with the help of pivot tables. In our next session, we will see that how you can change the look and layout of the date timeline. 32. Customize Your Date Timelines: All right, now let us see what are the customization options that are available for the date timelines when there are only few options available, but the important ones which you need to know that how you can change the look and layout of your date timelines. To do so, you need to select on any of your timeline. Then simply you need to go to the options. Under timeline tools, you get different options, like what exit name you want to give it to a timeline. How you want to style your timeline with different color options, where you need to arrange it, how you need to resize it, and what are the things that you need to show or hide into your timeline? Let us see how to change the name. But only thing you need to do is change the name of a hill, and that will be simply updated into your timeline header. Then you can select any other color options from different light modes, as well as dashboards. You just need to select any of the options and you can see how this changes the behavior of the timeline. It is advisable to select mostly the light moves because that won't distract the audience from the main data, then you can simply arrange, bring it forward or sand it back. If you are having multiple items in the same datasheet, then you can simply resize it on the basis of the height as well as width of entire timeline. Generally, we can give it in a compact form so that you don't need to scroll it down. If you are having some long or last datasets like here, we have months. So to occupy all the months of a particular year and the same direction, you can increase the width of your timeline. The last thing which you can optimize is what other things that you want to show or hide into a Timeline. The first is header. If you will simply remove it, the heading of a timeline won't be shown. Then we held a selection label. So whatever area you're selected is displayed over a hill, like if those elected entire timeline, it will show up as it as all periods. If you will select a particular month or multiple ones, it will show that you have selected the data from February to March 2016. If you will hide it there, it won't show up on the left corner. The next option is scroll bar. See a scroll bar at the downside. But if you will remove it, you need to manually do it from these arrow buttons at the end. If you will, add a scroll bar, you can easily draw if you are having such long day taglines. In case of a short one like this, you can simply select and you can remove the scroll bar because you only have four values and you can easily toggle around the same, right? So in that case, you can remove the scroll bar if you wish to. And the last thing is time level. If you want to fix your date timeline to a particular thing like in case of this, if we want to fix it for, uh, your only no, we don't want that annual the user of this excel sheet change the level of date. That is a sort of Your, we don't want anyone to selected on the basis of month or quarter or date. So if you will simply untick, now you cannot change the time level. This timeline is fixed on the basis of your only. To enable it again, you can simply select this into options you can take on time level. So in this way, you can make some modifications to a date timeline, and you can easily walk with it to analyze your data.