A Pivot Table is a powerful tool that calculates (or aggregates) data very easily in Excel
- Select your data
This step is very important because if your data aren't organized properly, you can't work with a Pivot Table
- Drag and Drop the fields
To create your pivot table, you simply have to drag and drop the fields to the Rows / Columns and Values area
Before creating a pivot table
To create a Pivot table, you 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
- Each header name is unique
The best option is to have raw data without any modifications, like in this example. Maybe 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 in Excel
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 table's name if 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 your data.
The Pivot table pane
- Press OK,
- A new worksheet is added to your workbook
- A new pane on the right shows all the options to use the pivot table in Excel
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 fields of your data source
How to create a 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
The future of the pivot table in Excel
Now with the AI technology, you can create a pivot table with a simple click with Excel 365. This article explains you how to proceed