Pivot Table helps you in reorganizing the data in a spreadsheet and/or depending on your specific requirements, it can sum up values and/or compare information.
What you see in a typical Pivot Table?
1. Report Filter: This allows you to only look at certain rows in your dataset.
2. Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns
3. Column Labels: These could be your headers in the dataset.
4. Value: By default, when a field is dragged to Value, it always does a count.
However, this allows you to also do a few other manipulations with any numeric value you pull in, including – sum, count, average, max, min, count etc.
Creating a Pivot table
Step 1: Identify yourself with your data so that you know what needs to be done with it. Once that is done, go to Insert Tab and select Pivot Table.
Excel will automatically populate your Pivot Table, but you can always change around the order of the data. In the example that we have taken, we are trying to calculate the average spend of each country for the products sold.
On the right side, once a pivot table is inserted – you will see all your column titles from the data that is in in the Excel file. From there on, you can place them into different parts of the pivot table.
Step 2: Organize your data.
To organize this data just drag and drop Title to the Row Labels box.
Step 3: Start doing the calculations
You can summarize the metrics that you want to calculate in the Values section.
Let’s start with Amount.
As you drag Amount into the Values box, you might see, as the sourced data has blank rows at the bottom, that the Total column will show 1s and 2s. By default, this variable will summarize by frequency rather than add up. You can go back and delete the extra rows, or you can add up Amount.
Taking other approach, you have to click on the arrow next to Count of Amount in the Value box and select Value Field Settings.
From there, move the highlighted selection from Count to Average and click OK.
You can do that for each variable and that is how you create and use pivot tables.
Congrats! It’s a feat in itself.
Aequor 