Blue Flower

Insert or Create PivotTable in Excel

PivotTable (Pivot Table) used for the organization or grouping of data according to certain criteria (conditions). If you want to create a PivotTable in Excel related to a specific range of data then you notice it in the picture below.

pivot table in excel

The first step is to create a PivotTable selecting the entire range of data, or a range of cells with the column headers.

pivot table in excel 2013

Insert a Pivot Table in Excel

How to manually create a Pivot Table in Excel. After selecting the data range or ranges of cells on the Insert toolbar, click PivotTable command. If you want to Excel for you to create a Pivot Table then on the Insert toolbar, click on the Recommended PivotTables command. Excel will automatically create a new worksheet and make it to the PivotTable.

insert pivot table in excel 2013

Now you can open a new dialog window where you notice the settings that need to be determined. As we have previously selected a range of data in the dialog box have a valid address for the Table/Range. The next step is specify which Excel Worksheet will create a PivotTable. I'm in this example created a PivotTable on the same worksheet starting at cell A10.

create pivot table in excel 2013

After creating a PivotTable situation might look like the image below. Note newly opened pane on the right side of the Excel Worksheet after you click one of the cells in the pivot table. Pivot Table in this case has a range of A10:C16. The range of cells is variable and depends on what you all fields retreated from the upper pane in the lower part of the four-stage shaft. Fields are retreating by option drag-and-drop.

Rows - the names and ranks of the mainly accommodation data contained in the selected rows of the table but not necessarily.

Values ​​- we group the data and create a report in a PivotTable.

You notice in the picture below that after creating a PivotTable emerged two additional toolbars on ribbon, Analysis toolbar and Design toolbar. On these toolbars are commands for editing and extra work with PivotTable.

created pivot table in excel

After creating a PivotTable, we can change some of the data according to our needs. These modifications can be done using the Value Field Settings. See the image below. Other possibilities PivotTable explore more personal. (You can see also How to Create a Pivot Table from Multiple Worksheets)

change value field settings for pivot table in excel

How to Delete a Pivot Table in Excel

  1. Click on the one cell in PivotTable report.
  2. On the Analyze toolbar in the Actions group, click Select command and then click Entire PivotTable.
  3. Press DELETE key on keyboard.

Automatically Refresh a PivotTable

If you want to automatically refresh the pivot table then use this VBA code below that set in Sheet Module.

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Pivot" to your sheet's name
     'Change "PivotTable1" to your pivot table's name

Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
End Sub
 


or this VBA code for multiple PivotTables


Private Sub Worksheet_Activate()

'auto update multiple PivotTable
   For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
   Next pt
End Sub