1. How a Pivot Table Works
Let's take a step back now and take a closer look at what is happening within the pivot table. The pivot table is basically taking all of the information from your data set, and presenting it to you in different ways.
2. Mapping the dataset to the pivot table fields
Each of the selectable fields within the pivot table correspond to one of the column headers of your dataset. When you select that field in the pivot table, it brings all of the data from that column into your pivot table.
3. What happens when you add rows or columns?
When you add a field to the rows or columns section, the pivot table will show you all of the unique values within that field. For example, selecting City will list Atlanta, Austin, Baltimore, and so on. When you select multiple fields at once, the pivot table will show all of the unique combinations that are contained in the original dataset. For instance, selecting the City and the Month will show all of the months for Atlanta, then all of the months for Austin, and so on.
4. What happens when you add a value?
When you select a field in the Values section, the pivot table will look at your original dataset and bring in all of the values from that field. The pivot table will perform a calculation on those values, and you are able to choose different calculations based on your needs. By default, the pivot table will usually try to use the SUM or COUNT calculations. If you are using SUM, then the table will add up all of the values according to the pivot table rows and columns. We'll talk more about the COUNT calculation in Chapter 3.
5. Checking the value calculation
For instance, selecting the SUM of Millimeters will result in the Pivot Table adding up all of the millimeters for each city. The value here, 1055, is equivalent to finding every row in your dataset that is listed as Charlotte, North Carolina and adding up the values.
6. Let's practice!
One of the great things about this is that you don't have to worry about breaking anything. The original data is safe and secure, and it will not be changed no matter how you manipulate the pivot table. This means that you can play around with the pivot table, and feel free to use a lot of trial and error in order to get the results you want. If you don't like the result, you can always remove the pivot table and start fresh, and again your original dataset will be unchanged.
Now that you know how a pivot table works, try the following exercises.