If you want to turn a sales or expense table with hundreds of rows into a short report in a few minutes, Excel's PivotTable tool is made for exactly that. Microsoft presents it as a powerful tool to calculate, summarize and analyze data. In this article we explain step by step how to create a PivotTable in Excel for Windows, based on Microsoft's official guide.
How to prepare data for a PivotTable?
For a PivotTable to work properly, the source table needs to be tidy first. Microsoft gives these recommendations:
- data should be organized in columns with a single header row;
- data should be organized in columns, not rows;
- each column header should be unique and not blank;
- avoid double header rows and merged cells;
- format the data as an Excel table: select a cell in the data, then Insert > Table.
For complicated or nested data, Microsoft recommends reshaping it with Power Query (for example, unpivoting columns).
How to create a PivotTable?
- Select the cells you want to use.
- Select Insert > PivotTable.
- In the dialog, confirm the table or range.
- Choose where to place the PivotTable: New Worksheet or Existing Worksheet.
- Select OK.
By clicking the arrow next to the PivotTable button you can also choose other sources: an external data source (Get from External Data Source), the Data Model for multiple tables or very large data (Get from Data Model), and Power BI (Get from Power BI).
Which area does each field go to?
Once the PivotTable is created, the PivotTable Fields pane opens. To add a field, select its checkbox. Excel places fields automatically as follows:
| Field type | Area |
|---|---|
| Text (non-numeric) fields | Rows |
| Date and time hierarchies | Columns |
| Numeric fields | Values |
To move a field to another area, simply drag it there. For example, when a "Product" field goes to Rows and an "Amount" field goes to Values, you see the total for each product.
Sum, count and percentages: how are values calculated?
Numeric fields in the Values area are shown as a SUM by default. If Excel interprets the data as text, it is shown as a COUNT.
To change the calculation, click the arrow next to the field name, select Value Field Settings and choose the operation you need in the Summarize Values By section. Number Format in the same dialog changes the number format for the whole field.
Values can also be shown as percentages: on the Show Values As tab of the Value Field Settings dialog you can choose, for example, % of Grand Total. To see both the amount and the percentage side by side, drag the same field into the Values area twice and set the options for each one.
How to refresh a PivotTable after adding new data?
When new data is added to the source table, the PivotTables built on it need to be refreshed.
- For one PivotTable: right-click inside it and select Refresh.
- For several PivotTables: select a cell in any PivotTable, go to the PivotTable Analyze tab, click the arrow under Refresh and select Refresh All.
Summary
- Source data should be in columns with a single header row and no merged cells.
- A PivotTable is created via Insert > PivotTable.
- Text fields go to Rows, numeric fields to Values; fields can be dragged to other areas.
- Numbers are summed (SUM) by default; the calculation and percentage display are changed in Value Field Settings.
- After adding data to the source, use Refresh or Refresh All.
If you want to learn Excel online, including building reports with PivotTables, message us on WhatsApp: +994 77 754 10 12
📚 Sources
🌐 The original article is in Azerbaijani; the official sources are available in Azerbaijani.
More articles
How do you sign up for an online course?
How do you sign up for an online course? Estavor Akademiya's courses, who they are for, and 4 steps to start live online lessons.
What is IFRS, who applies it and what is 1C for? A guide for learners
What IFRS is, who must apply it in Azerbaijan, which rules small businesses can choose and what 1C is used for — explained for accounting learners.
Styles, Automatic Table of Contents and Track Changes in Word
How do you create a table of contents in Word and track edits? Styles, Heading levels and Track Changes — based on Microsoft's official support guides.