The Pivot Table is one of the most powerful features in Excel. It is an interactive way to quickly summarize large amounts of data. A PivotTable report is useful to summarize, analyze, explore, and present summary data.
So let’s get started with how to create Pivot table.
Our dataset consists of 15 rows and 6 columns.
Sample data for Pivot table
Following are the steps to create pivot table.
1. Go to Insert tab, click on PivotTable and then click PivotTable to insert pivot table on excel sheet.
Pivot table option on Insert tab
2. On Clicking PivotTable option, following dialog box appears. Excel automatically selects the data and the default location for a new pivot table is New Worksheet.
Selecting data range for pivot table
3. Just click OK. Excel will automatically take us to New Worksheet and this is how PivotTable report looks at the start. If we read the instructions carefully, Excel clearly says To build a report, choose fields from the PivotTable Field List.
Building a pivot table
4. Following are the four important report areas in building PivotTable, check the above screenshot.
- Report Filter: Use to filter the entire report based on the selected item in the report filter.
- Column Labels: Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it.
- Row Labels: Use to display fields as rows on the side of the report. A row lower in position is nested within another row immediately above it.
- Values: Use to display summary numeric data.
5. Let’s build a report. As we can see our columns of the data appears as field to be added to the report. Check the below screenshot and how our final PivotTable report looks
Drag fields to the different areas
- Country Field to the Report Filter area.
- Company Field to the Row Labels area.
- Amount & Units Field to the Values area.
Pivot table report
6. As we can see in the above screenshot, a table shows the summary of sum of amount and count of units each company had. Also, report has Filter option country wise. Check the below screenshot.
Pivot table report for the country “INDIA”
7. We can also check how the companies have done Product-wise. To add Product to the report, drag Product Field to Column Labels area. Check the below screenshot.
Product wise Pivot table report
Start practicing, Click here to download the excel pivot tables tutorial workbook and try out yourself.
In this article, I have shown only how to build a report, you can also design the report layout and format the way you want. Check this beautiful article which will help you to do the same.
That’s It!!! It looks complicated though but believe me once you start practicing and get use to PivotTable, you will be enjoying this feature. It is one of the most powerful feature of excel and one should know how to use it.
Any queries or clarifications related to this article please write me at pareshj@snpinfotech.in or visit www.snpinfotech.in
S&P Infotech Blog 