PivotTable can be created either from a range of data or from an Excel table. In both the cases, first row of the data must contain headers for the columns.
If you are sure about the fields to be included in PivotTable and the layout you wish to have, you can start with an empty PivotTable and construct the PivotTable.
If you are not sure which PivotTable layout is best suitable for your data, you can use Recommended PivotTables command of Excel to view and choose the PivotTables customized for your data.
Consider below data range which contains the sales data for each Salesperson, in each Region and in the months of January, February and March −
To create a PivotTable from this data range, perform the following
Click PivotTable in the Tables group. The Create PivotTable dialog box appears.
In Create PivotTable dialog box, under Choose the data that you want to analyze, you can either select a Table or Range from the current workbook or use an external data source.
As you are creating a PivotTable from a data range, select the following from the dialog box
You can choose to analyse multiple tables, by adding this data range to Data Model. You can learn how to analyse multiple tables, use of Data Model and how to use an external data source for creating a PivotTable in the tutorial Excel PowerPivot.
A new worksheet is inserted into your workbook and it contains an empty PivotTable. Name the worksheet – Range-PivotTable.
As you can see, PivotTable Fields list appears on the right side of the worksheet, containing header names of the columns in the data range. Further, on the Ribbon, PivotTable Tools – ANALYZE and DESIGN appear.
We will explain about PivotTable Fields and Areas in the later chapters in this tutorial. For now, observe the steps to add fields to the PivotTable.
Assume that you want to summarize the order amount salesperson-wise for the months January, February, and March. You can do it in few simple steps as follows
Your first PivotTable is ready as shown below
You can see that two columns appear in the PivotTable, one containing Row Labels that you selected, i.e. Salesperson and Month and a second one containing Sum of Order Amount. In addition to Sum of Order Amount month wise for each Salesperson, you will also get subtotals representing the total sales by that person. If you scroll down the worksheet, you will find the last row as Grand Total representing total sales.
In this tutorial you will learn more about producing PivotTables as per the requirement.
Consider below Excel table which contains the same sales data as in the previous section −
An Excel table will inherently have a name and the columns will have headers, which is a requirement to create a PivotTable. Assume the table name is SalesData_Table.
To create a PivotTable from this Excel table, perform the following
A new worksheet will be inserted into your workbook and it contains an empty PivotTable. Name the worksheet – Table-PivotTable. The worksheet – Table-PivotTable looks similar to the data range case in the earlier section.
You can add fields to the PivotTable as you have seen in the earlier section – Adding Fields to the PivotTable.
If you don’t know how to use Excel PivotTables or which fields would result in a meaningful report, you can use Recommended PivotTables command in Excel. Recommended PivotTables will give you all possible reports with the data along with associated layout. This means that the, options displayed will be the PivotTables that are customized to your data.
For creating a PivotTable from the Excel table SalesData-Table using Recommended PivotTables, proceed as follows
In the Recommended PivotTables dialog box, possible customized PivotTables which suit your data will be displayed.
You will be get the preview on the right side.
Selected PivotTable appears on a new worksheet in your workbook.
You can see that the PivotTable Fields - Salesperson, Region, Order Amount and Month got selected. Of these, Region and Salesperson are in ROWS area, Month is in COLUMNS area, and Sum of Order Amount is in ∑ VALUES area.
PivotTable has summarized the data Region-wise, Salesperson-wise and Month-wise. Subtotals are displayed for each Region, each Salesperson, and each Month.
Excel pivot tables Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel Data Analysis Tutorial||Excel Power View Tutorial|
Excel pivot tables Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
Excel Pivot Tables Tutorial
Excel Pivot Table
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.