A Pivot Table is a powerful tool which calculates or aggregates data very easily.
For many people, a pivot table is a complex tool for advanced users. But not at all.
It's really simple and all the calculations are made by the tool itself. You just have to drag an drop your data. 😎😃😍
Before creating a pivot table
To create a Pivot table, you just need a table of data that complies with the following rules
- no blank rows or empty columns
- no total or subtotal rows
- all the columns must have headers
The best option is to have raw data without any modifications, like in this example. Maybe a such a document looks too simple for a lot of users but it's the only way to work with a pivot table.
Creation of a Pivot Table
To create a pivot table you just have to click on the menu Insert > Pivot Table
And in the dialog box, you select the source of your data.
You can indicate the cell range of your data source or the name of the table once you have inserted your data into a Table (Insert > Table).
Inserting your data into a Table is the best choice because once your data source is updated, your pivot table will automatically use all of your data.
Pivot table interface
Once you have pressed OK, a new worksheet is added to your workbook with a new pane on the right.
In this pane, you have
- all the headers of your data source (this is why your data source must have headers)
- 4 areas to drag and drop the headers
How to create your pivot table?
It's very easy, you just have to drag and drop the fields you want to display.
In the following example, we want to know the total sales for each country.
- Drag the field Country into Rows. All the values in the column Country are not unique.
- Drag the field Total into Values
Just by dropping the field Total, the pivot table has made the SUM of each country