The Complete Guide to Use Pivot Table

1. The Complete Guide to Use Pivot Table

2. Introduction to Pivot Table • One of the best things about Excel is the fact that a single worksheet can hold so much information. In fact, you could fill billions of cells if you needed that much room. A file with that much data could get pretty overwhelming, but there’s a feature called Pivot Tables that can help. PivotTables let you summarize and manipulate your data, without actually changing anything in the worksheet. Let’s take a look at how they work. In this example, I have some company sales data, with columns for the salesperson, region, account, order amount, and month. And I need to find the answer to a very specific question: What is the amount sold by each salesperson?

3. Example of Pivot Table • Because each salesperson has data on more than one row, I’d normally have to add these up in order to find the answer. But a PivotTable can do this for us automatically, which will make the task much, much easier. First you need to select your data. You can just click any cell if it’s formatted as a table; otherwise you need to select all the cells yourself (and don’t forget your column headers). When you’re ready, go to the Insert tab… then click the PivotTable command. You’ll usually want to place your PivotTable on a new worksheet, so make sure this option is selected. Then click OK… and a new sheet will appear.

4. Complete Solution • Over on the left is our blank PivotTable (it’s ok that there’s nothing there yet). To the right is a list of fields that have actually been pulled from our column headers. This is where we control what is or isn’t displayed in the PivotTable. But before we continue, let’s take another look at that question: What is the amount sold by each salesperson? This question tells us exactly which fields to use to find out the answer: Salesperson and Order Amount. All you have to do is check the box next to the field… and it’ll be added to one of the areas below — in this case, the Rows category. Another method is to drag and drop the field into place; we’ll put this one under Values.

5. Complete Solution • If we go back to the PivotTable… now we can see the information we were looking for; in other words, the amount sold by each salesperson. So our question has been answered just by choosing what fields to include. One of the best things about PivotTables is how easy they are to modify, so you can view and analyze different things. Let’s say, for example, that you wanted to know each person’s monthly sales, not just their total sales. Just drag the Month field… down here to Columns… and this will add a column for each month in the worksheet. If we wanted to know what each region sold, we could take out the Month and Salesperson fields… and then drag Region… down to Rows.

6. Pivot Table Best Practices • So each time we change the fields, the PivotTable is just combining the source data in a different way, so we can view the information we need and we’re doing all this without adding or changing anything in the original worksheet. • You can even experiment by dragging the fields into different areas, but some combinations work better than others. For instance, if I move Order Amount over to Columns or Rows…you can see that it doesn’t really give us any useful data. Since Order Amount contains numerical values, it really does work best in the Values area.

7. Advanced Excel Course by IPA • Finally, if we click the arrow next to Order Amount… and go to Value Field Settings… we can change the function to something other than SUM. Instead we could count the number of cells; calculate the average; or use any of the other options here. Some of these functions will be more useful than others depending on your data, so that’s just something to keep in mind. • If you to learn advance excel then join institute of professional accountants’ advanced excel course and learn complete advanced excel structured reference formulae, PowerQuery, PowerBI and more. In case of any query call us at 9213855555.