Learn A to Z of PIVOT TABLES in Microsoft EXCEL | Narayanan Veeriah | Skillshare

Playback Speed


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

Learn A to Z of PIVOT TABLES in Microsoft EXCEL

teacher avatar Narayanan Veeriah

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

13 Lessons (37m)
    • 1. Introduction

      2:04
    • 2. Create Pivot Table - Option I

      3:55
    • 3. Create Pivot Chart - Option II

      1:29
    • 4. Arrange Fields in a Pivot Table using Field List

      5:08
    • 5. Filter Data in a Pivot Table

      3:39
    • 6. Group / Ungroup Data in a Pivot Table

      3:19
    • 7. Work with Pivot Table Layout

      6:39
    • 8. Leverage Pivot Table Styles

      1:50
    • 9. Insert Pivot Chart

      2:07
    • 10. Move Pivot Table

      1:12
    • 11. Delete Pivot Table

      3:02
    • 12. Class Project

      0:59
    • 13. Wrapping up & Next Steps

      1:11
  • --
  • 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.

23

Students

--

Projects

About This Class

The PivotTable, in Microsoft Excel, is a powerful tool that you can leverage to analyze very large amount of data with numerous rows and columns. With that, you can group data, calculate, summarize, and analyze for making comparisons, patterns, and trends in your data. The pivot table enables you to ‘pivot’ (or rotate) the data to view it from different perspectives.

You will learn all that is required to make use of Pivot Tables in Excel. You will learn how to create and use pivot table, in Microsoft Excel, in this course. You will learn about the two ways to create pivot tables. You will learn to arrange fields, filter data and the change layout. Also, you will learn to make use of pivot charts. There is a class project that you will leverage to reinforce what you will be learning in this course.

The course progressively elaborates the concept and the application, so that you find that it is easy to learn and comprehend. 

The course is made up of the following lessons:

  1. Introduction.
  2. Create Pivot Table - Option I
  3. Create Pivot Table - Option II
  4. Arrange Fields in a Pivot Table using Field List
  5. Filter Data in a Pivot Table
  6. Group / Ungroup Data
  7. Work with Pivot Table Layout
  8. Leverage Pivot Table Styles
  9. Insert Pivot Chart
  10. Move Pivot Table
  11. Delete Pivot Table
  12. Class Project
  13. Wrapping Up & Next Steps

Meet Your Teacher

Hello, I'm Narayanan Veeriah (Nana). I am CFA (Chartered Financial Analyst), PMI-certified PMP (Project Management Professional) and IBM-certified Executive Project Manager. Have several years of work experience in banking, project management and SAP consulting. I have authored several books on SAP Finance. I have also been self-publishing, SAP books, on Amazon.

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. Introduction: Welcome to the course, loan here to insert of pivot tables in Excel. This is the second lesson of my nanos masterclass series. I am not ion and varia, I'm a chartered financial analyst. I have been using Excel for several years, bringing my experience on next, I will help you to learn the various aspects of Excel in a very successful way. The pivot table in Microsoft Excel really is a powerful tool that you can leverage to analyze very large amount of data with the name of rows and columns. With that you can group data, calculate, summarize, and analyze for making comparisons, patterns, and trends in your data. The Pivot Table enables you to devote our rotated to the data or to waive it from different perspectives. So what is the difference between a normal table under pivot table? In a normal table, that data is static. You need additional effort on tolls to summarise and Enter. In a pivot table, the data is in a summarized the format. You do not need additional tools. You can interactively the organised the data to help meaningful grouping and comparisons. Okay. Is there any special requirement for pivot tables? Yes, there are a couple of requirements. There should not be any blank row or column in our data table. Data table should not contain any total or subtotal rows. Your data should contain column headings, and that should be in a single row. You will learn how to create and use pivot tables in Microsoft Excel. In this course, you will learn about the two ways to create pivot tables. You will learn to arrange fields, filter data under change layer. Also, you will learn to make use of pivot charts. There is a class project that you will complete to leverage to reinforce what you will be learning in this course. Let us move on to the first lesson. 2. Create Pivot Table - Option I: There are two options with which you can create pivot tables in Excel. Let us see the first option in this lesson. This table consists of sample data relating to country wise, region wise sale of different products in various years. Took other with information on units sold, selling price, discount, cost of manufacturing, cost of goods sold, et cetera. As you can see, the data is large and you cannot meaningfully interpret to the same in its current form. Hence, we are going to use the pivot table. But before we create the PivotTable, Let us first ensure that the data table meets the requirements. As you can see, there are a couple of rows under column that is blank. Since we know that the data table should not blank rows and column. Let's remove them now. So I delete. I also delete this row, then this row, and then delete both. Next, ensure that each column has a heading under there too in a single row. If you see here this second column, the column heading is missing. So let me insert the column heading here. This is country. We also need to ensure that this data table does not contain any total or subtotal rows. So I don't see anything here. So it looks like if we do not have any such rows, now that the table looks fine, let's create the pivot table. So you need to select a cell inside the data range and then go to insert and then click on pure table. So it brings up a pop-up on which you will see three options to select to the data. So this is the first option. This is the second option under the system. Third option, we will select the first option under that is the default selection. As we use a single Excel table. The other options are for using external database-like MS Access, SQL Database, et cetera, or to use multiple Excel table using data model. When you use the first option. By default, Excel select the data range that you can verify for its correctness. If you look closely that dotted line all around to the selector data range. So you can see on the right, you can see on the left, you can see at the end as well. This indicates the selector data range. Or if you want, you can also manually select the data range. When you have selected the table or the data range, you need to ensure Where do you want to pivot table to be placed in a new worksheet or in an existing auction. If you select the existing worksheet or the current worksheet, nor to give the data range or the location where the PivotTable will be placed. Let's select the new worksheet. Now click OK. Excel inserts a new update before the shade where you have your data. If you look at the sheet now, you will see two distinct areas on your left and ANOVA right. On the left, you see the PivotTable display area. And on the right, you see the pivot table fields area. Typically, you will choose the fields from this field scarier to build as a pure target that we will discuss in lesson three. With this, let us move on to the next lesson, wherein we will look at the second option to create the pivot table. 3. Create Pivot Chart - Option II: In this lesson, we shall discuss the second option to create the pivot table. Assume the case of option one. Select the cell, insert in the data table, go to the Insert tab. Click on Recommended PivotTables, except brings up a pop-up screen on which you will see that XLS recommended your number of pivot tables based on your data. You may select an appropriate one. If you want. You can click on the blank PivotTable to start from scratch, as we have done in option one that we have discussed in the previous lesson. You can also change the data source by clicking on change source data and then select a different range or a different table are here from also you can select the external data source. Once we have selected the appropriate PivotTable, click OK. Excel. Similar case sub creating a Blank pivot table inserts a new worksheet with the pivot table already created. As you can see in this option, Excel not only creates the PivotTable, but also filled that up with some of the fields from the pivot table fields, our change in the fields to the pivot table that we will discuss in our next lesson. 4. Arrange Fields in a Pivot Table using Field List: In the previous lessons, we saw how to create a pivot table. In this lesson, we shall discuss about arranging the fields in the field list to build a pivot table. After you have created the pivot table, you will normally see the field list box on the right of the screen. If you do not see the field list box, click anywhere inside the pivot table, it should appear now, if that is still not miscible, click anywhere inside the pivot table, then go to the unrelated tab in the pure table, tolls on the ribbon and go to the show amino group and then click on filter list. In the field list bugs, there are two sections. Or the top, you will see the field list, a section on the bottom, the area section, the fields in the field illustrious section, nothing but the column headers of the underlying data table. The area section is made up of four areas. Filters area, Columns, area, Rows, area under Values area. In the filters area, you can drop the fields that you want to use as the top-level filters for the PivotTable. Typically, you will add data on time hierarchy fields to the column Syria, but you cannot get any non-numeric field aswell here. The fields in this area will be the column labels of PivotTable. Non-numeric fails are ordered to the Rows area. This or you are row labels in the pivot table. You can have more than one row failed to represent to the appropriate hierarchy. The numeric fields are order to the values. These are all the fields that you want to summarize. When you select a field from the filter list, then that failed is added to the respect to default area of the area section except for the filters area under you can see that details on the PivotTable as well. For the filters area, you need to explicitly and drug the record fields from the field. When you have a long list of fields, you can also use the Search option here. Instead of selecting the fields in the field or list, you can also drag them to the respective areas like this. You can also rearrange the fails by dragging them, but even the areas. So as to regroup them. You can also click on a particular field and then move to another area. You want. To remove field from the area. You just need to deselect the same. Or you can drag it back to the field list. You can drag the field list you anywhere you want. You can also resize the same. Using the Tools option in the field list. You can change the layout. Fight default, the field section and the area section are state. You can change them as well. You can also right-click on the PivotTable and then hide the field list if you wish. If you do not want to, the field is to be displayed. You can close that as well. Now that we have seen how to arrange the fields in the pivot table, let us do that for our example. So in our case, I want to the region to be the row fields are none. I wanted the country under columns. Then I wanted the grass sales in the values area. This is how the table looks like. I also wanted the product in rows, so I will drag the product field to the rows area. Now, within the rows, I can interchange. I can drag the region below the product or vice versa. If you look at the pivot table on the left, you will see that the gross sales in each of the countries, in each of the regions for each of the products are do you also see a grand total for each of the row and for each of the column. This is all about arranging the fields in our PivotTable for your analysis. Let's move on to the next lesson, wherein we shall discuss how to filter data in PivotTable. 5. Filter Data in a Pivot Table: In the previous lesson, we saw how to arrange the fields from the field released to build a pivot table. Now, in this lesson, let us understand how to filter data in a PivotTable pivot table by itself east where certain land flexible, however, you can add filters, a pivot table to act more flexibility. There are three methods by which you cannot filters. One is you can use the filters area to add the required filters that will act as the top-level filters for the Pivot Report. Say for example, let me add the year here to the filter. That does come AS a top-level filter for the report. I can also add more than one filter. Say for example, let me out a discount band here. Now, let me filter the data by using the field. Here, I select Dante, Dante, you can also select multiple items if you click on this checkbox. Now the values are displayed only for year 2020. And you can also further filter by using the discount bond. So I can select high discount. Then the values are only for selected filters here. So let me select all. So let me remove the filter here. Let me keep the year filter on there. The second method of filtering is you can use the Auto Filter either in the column or in the row. These are all the auto filters here. So you face select here. In the column label, I can select either of the countries. Say for example, I click for Australia, then this is filtered were Australia. And I can also use the row labels here. So I can select single or multiple values. Then the PivotTable now displays values for region eastern, North, and far country, Australia for the year 2020. So let me remove the filter here and then read may restore the values here. There is a third way by which you can visually filter the data. So what you need to do is you need to go to the analyst tab in the pivot table tools, and then click on Insert Slicer. Your pop-up box opens. Select the filters you want to look and select any number of filters. Say for example, I again select discount bank or I select Product. And then I click OK. Now you got two slices here. Let me bring them side-by-side. So you can click on any of the fields here and then filter that data will surely say, for example, I click only this product mix match. So you get the details only for mix-and-match. I can also further select this by using your discount band, for example, L1. And if you want to select multiple fields here as filters, then click on this multi-select option on the top. Then you can select or not more labels are still filter. This is how we use filters in our PivotTable to filter and enhance your analysis. Let me close this filters. So let me select everything here. Let me remove the filters. To remove the slicer, you just need to click on the slicer and then press Delete. This is all about using filters in PivotTable. Let us see how to group around group data in the next video. 6. Group / Ungroup Data in a Pivot Table: In the previous lesson, we saw about how to filter data in a pivot table. In this lesson, let us discuss about grouping and ungrouping off data. Though, PivotTable is a great way to summarize and analyze your data. You can still enhance the layout on format. When you group the data under Row Labels, You have the regions here. If you want to summarize a couple of regions, you can form them into a group. Click the region, press Control, and then select the other region, right-click and then click on Group. Now you help your group, which is made up of regions east and north. By default, XML namespaces, group one. You can change the name as well, go to the Pivot Table Tools, select to the Analyze tab and then click on phase settings and select Custom here under subtotals, you'll give a new name for that. So this is, is if we look at the fields here, you will see this new group of the field is turned west. You can also group the data on the column sites. Say for example, I want to group Austria on the mainland. I select this, england by pressing control. Now this time I go to the group menu in the analysts tab in pivot table tools, and then click on group selection. Now, there is a group one which is made up of Austria and inland. And do you have a group one total aswell. So if you select to the group and then click on Ungroup, then the Grouping will be remote. Let me also remove this group one here. I right-click on Ungroup. Now this is restaurant to the initial status of third table. Another interesting feature is you feel the date and then you feel drag the field into the row, column or column label. Then by default, Excel oranges that date into months, quarters, or years. We have added only the day to Europe. But Excel has added that. Someone here. You can right-click on that and then select group. Now you get up copper on which there is a starting date, there is an ending date under you can group either by days or months or quarters or years are all tokens that now if a select quarters instead of months, then this will be grouped into quarters, quarter, one, quarter, two, quarter, three, et cetera. You can also group it multiple, both quarters or months. You need to select both by selecting the values, by pressing the Control key, and then click OK. Now you help your region below which you have a product, below which you have a quarter on, below which you have a month here. This way, you can grow per ongoing data to enhance the layout and then do more summarize the analysis. This is all about grouping and ungrouping up data in the pivot table. Let us discuss about working with Pivot Table layout in the next lesson. 7. Work with Pivot Table Layout: In the previous lesson, we discussed about grouping and ungrouping of data. In this lesson, we shall discuss about PivotTable layout. Before we discuss about working with the layout of our PivotTable, let us learn to work with the Value Field Settings. You can access the value field settings from three different ways. Let us see the first one. Select any of the value field, cell sphere. Then right-click your mouse. Here you have the value field settings. But even before that, you can show the values of the value field either as a number as say it is R by percentage of various things. Say for example, the default is there is no calculation. You have the numbers. And if you want to show that as a percentage of grand total, you need to just select on percentage have grand total. Grand total will be a 100%. All other details will be in percentage to the grand total. Right-click again and go to Show Values As, and then select whatever you want a that has a column total, r as a percentage of row total. So there is a flexibility to show the value says. And if you click More Options, you will get a pop up from there also, you can access through various Minow. I'm right-clicking again. I am going to the Show Values As and then select or no calculation. You can also summarize values by some count or average, whatever it is. It again depends on what is the field you are looking at. If it is gross sales, for example, you will want to sum. There could be situations where you want to count to the records are under, there are possibilities that you could also use our h here also, you can use more options and then go to the same value field settings and then do whatever settings you want. Let me right-click again. So here you have a number format. So we can select our number format or you can even format the cells here. Let me select the number format. And this is a currency, so I go to currency and then I select a dollar. I don't want any decimal places, so I put that as 0 and then click OK. Now the grass sales have been formatted into dollar. You can right-click here and then go to value field settings. You get the Value Field Settings pop-up here. Here, instead of sum up gross sales, we can just say gross sales are, you can name that as well. And then here, this is the same which we saw earlier. So if you click on Show Values As you're then by default, there is no calculation, but you can show that AS a grand total. Laura, Percentage of Column Total are a percentage of row, row, row total, whatever it is. And here also, you can bring up the number format and then format to the cell. The second option to access the value field settings is you can go to the PivotTable fields on them in the values area, click on the Value field and then select the value field settings. Then the same. Pop up box appears here. So you can select what is third. You'll want to summarize by either some are count or whatever it is. And then you can also show values by percentages are BY, without any calculation. You can also do the number format here. If you click on that, it opens up the format cells dialog box. The third way is you go to the Analyze tab on the ribbon under pivot table tools and then click on field settings. So the same value field settings soap answer. Now that we have discussed how to summarize values by how do Show Values As and also how to form or to the numbers of the value fails. Let us to discuss the Pivot Table layout. Keeping your cursor inside the pivot table. Then go to the design under poor Table Tools core to the layer. So there are multiple options available here. Let us take the subtotal option now, here, you can choose whether to show the subtotals or not to show the subtotals. If we click on here, no subtotal will be shown. We can also show the subtotal, say that at the bottom of the group or at the top of the group, if you select these, the subtotals will come at the top of the group. And if you select this option bottom of the group, the subtotals will be shown at the bottom of the group. Same way you can change the granddaughter. You can switch off Rosen columns, grand total, no grand total will be shown. Or you can switch on only four rows, r, you can switch on only four columns. Let we should shut down for both rows and columns. So third option is showing a blank row after each item. So if you select to this, that will be a blank line inserted after each item under, you can remove that as well. Go to the report layout. There are multiple options available. What you see here is in the compact form. You can show it in an outline form or you can show it in a tabular form. You can also show the report with all the item levels repeater. Let me show it in the compact form. This is all fine, but how do I know that the summaries to values in the pivot table are correct. So let's select a case here. Let us select Australia, and then let us select this product mix match for the yeast. So I go to Data Filter by Australia. Then I select only the region east. Then I select the product mix match. If some of the grass sales, you will see that this is 353203. That is what you have in newer PivotTable summaries to values as well. If you want to see the details here, what we have seen in the data w by filtering, you just need to click on that particular cell, double-click on that particular cell. Excel brings up to the relevant records in a different worksheet. This is all about working with your table layout. Let us move on to the next lesson to discuss about pivot table style. 8. Leverage Pivot Table Styles: In the previous lesson, we discussed about PivotTable layer. Now in this lesson, let us understand how to apply the various styles to your pivot table. You can apply different styles to your PivotTable. To make it visually appealing. You can select from a variety of styles provided by Excel. You can also modify the options. Let's see how to do that. Place your cursor inside of the pivot. Go to the Design tab. Under pure Table Tools. Click on any of the PivotTable styles. It may want to select a light theme or a medium theme, or you may also want to dark theme. You can experiment with the themes and select the most appropriate one which you feel it's appealing to you. Once you have selected the PivotTable styles, you can further customize that using the pure table options, you can switch on and off the row headers as well as the column headers. We can also make the Rosa sub-band D21. You'll see a line here. And you can also do that for a column. If you click on this bounded column, you gotta line for each of the columns. You can also click up the column labels and then change that. Say for example, I changed this into countries. And then here for the row label, I can change that region by working with the pivot table styles and the pivot table style options, you can make your PivotTable more appealing. This is all about working with Pivot Table Styles. Let us move on to the next lesson where we will discuss about how to work with pivot tables, charts. 9. Insert Pivot Chart: In the previous lesson, we discussed about working with the Pivot Table Styles. In this lesson, let us discuss about how to insert onto work with a pivot chart. To add up to one chart, go to the underlay stab in PivotTable Tools. Click on the Pivot Chart. Under tolls man a guru. Excel brings up the insert short pop-up menu where you select to the required saw under click ok. Let us re-size this chart. So this chart is all about to the grass sales across countries in different regions for different products. This chart is similar to any other chart, except that you can interactively change the options here within the chart. Say for example, you can select a particular country or select a couple of countries here, or you can select your particular region here. Say for example, let me select region is conserved. And you can also selective or to the product. You can work with all our, you can select a particular product. Let me select coupler products here. You can click on this plus or minus to expand our collab psi and tear fields here. Assume the case of any other chart you can go to the design option on and select the record chart style you want. You can change the color. You can add a chart element. You can change the chart type or soil from here as well. You can add a chart element. You cannot add Chart Title, Data label, data table, all that you want. You will find working with pivot chart, very flexible us. This is very interactive way of selecting the data you need. This is all about working with the pivot chart. Let us discuss about moving the PivotTable in the next lesson. 10. Move Pivot Table: In the previous lesson, we discussed about working with pivot chart. In this lesson, let us understand how to move up your table from one place to another. Place your cursor inside the pivot table. Go to the unreleased. Under pivot table tools under auctions. Click on More pivot table, you will get a pop up. You may want to the pure table to be more to a new location in the existing worksheet. Then you need to select to the new location for that, and then click OK, the PivotTable will be moved. Or if you want to move the pivot table to a new worksheet, click on the new worksheet option, and then click OK. Then Excel will create a new worksheet on learn more the pure table. Then you can also use the select option to select the entire Pivot Table are to select reliable or values or both together. This is all about moving up your table. Let us see how to delete your pivot table in the next lesson. 11. Delete Pivot Table: In the previous lesson, we discussed about moving a pivot table from one place to another. In this lesson, let us see how to delete a PivotTable. Before we see how to delete a PivotTable, let us understand few more options in a pivot table that may come handy. Consider a pivot table where you have some MFA value. Blank here. You may want to the blank cells to be replaced with 0 are something like not applicable. So how to do that? Go to the Analyze tab in PivotTable Tools. Click on pivot table, then select the options here. So underlayer can format. You will have a format here under which you can say what you want to replace, the blank sales wheat. You can save 0 or you can say not replicable. And if we click on this particular checkbox, then all black cells will appear AS 0 here. There are some more options here. When you click on pivot table, you have a PivotTable name, that's the default name provided by Excel. So you can give a name here. You can also click on the Options and then do the named here until you can configure the layout and printing data under how the Totals and Filters should look like and how the display should be. You can also have a classic PivotTable layout. If you click on this, then instead of dragging the fails from the field area to this columns and rows and values area you can directly dragon draft to the field or to the grid. So if I click OK, then this is how it will look like. So for example, I can click and like this and then drag it. So this is the classic display, your pivot table. You can enable it only if you're comfortable with. Let me go to the Pivot Table Options, let me de-select to that. Now this pivot table, there are multiple filters here. You have a filter in region, you have a filter in product, you have a filter in country. So in one click, you can remove all the filters. So go to the Actions menu under unreleased. Go to the clear, aren't clear filters. And it's very clear. All the filters are to one go, place the cursor inside the pivot table, go to the Select option under Options, click on enter PivotTable. Now you can press delete to remove the Pivot Table, or you can also go to the clear option here under actions and then clear all. This will also delete to the pivot table. This is all about deleting a pivot table. With this, we come to the end of the course on pivot table. Let us talk about the class project in the next video. 12. Class Project: With the completion of the lessons, it's time we discussed the class project, this class project contents on exercise that will help you to reinforce the learning that you WHO had so far from the various lessons. Use the file 0 to iPhone. Yay too is that of pivot tables in Excel. Iphone class project dot XLS. It has two tabs. Data on result. That data tab has the sample data under the necessary instructions, complete the steps as detailed in the instructions under comparable final result with our details from the result two tab. So when completer, the result to tab will look like this. You can also refer back to any of the lessons. Should there be a need for clarification? With this, we have come to the end of this course on pivot tables. 13. Wrapping up & Next Steps: With the completion of all these lessons, it's time to wrap up the course and discuss the next steps. By null. Yoda successfully completed the class project. Do post your results. I'm let me know if you need the items or the clarification will certainly come back on that. Also. I encourage you to create your own projects. Unsure The same here. That really helped me to provide you with the appropriate feedback. It may also help other learners and they may also like to share their views and suggestions. Do not stop with these lessons. Take time to explore and practice. To learn more about pivot tables. Provide your feedback on the course, its content, presentation, et cetera. Will happy to receive them to incorporate in my feature coasters. And finally, you have big thanks for selecting this course. If you feel that this course has been informative and useful, do recommend to a France and others who may want a similar course.