Creating a Pivot Table Automatically in Excel (2 Methods)

Excel is a powerful tool for organizing and analyzing data, and pivot tables are an essential feature for summarizing and manipulating large amounts of information. Pivot tables allow you to easily extract insights from complex datasets but creating them manually can be time-consuming and tedious.

Fortunately, Excel provides several tools to help automate the process of creating pivot tables. In this article, we will explore creating a pivot table automatically in Excel using two techniques: the Recommended PivotTables feature and VBA macros.

By the end of this article, you will have a better understanding of how to streamline your data analysis workflow and save time with automatic pivot tables in Excel. Here’s an overview of what we are going to achieve-

How to Automatically Create a Pivot Table in Excel: 2 Methods

To create a Pivot Table automatically, we’ll use the following dataset which contains a company’s regional sales data.

1. Use Recommended PivotTables Feature to Create Pivot Table Automatically

First, we’ll learn a fantastic way of creating a Pivot Table automatically in Excel. Microsoft introduced an exclusive feature in Excel 2013, that is- Recommended PivotTables. Using it, we can create many types of Pivot Tables in a concise time. When you need some simple Pivot Tables suddenly, then this feature is effective and saves time.

• Click on Recommended PivotTables option from the Insert ribbon.

• Then select the data range from your worksheet and press OK.
• Or, if you first select any cell within the data range and choose Insert ➪ Tables ➪ Recommended PivotTables, Excel will quickly scan your data, and the Recommended PivotTables dialog box will appear.
• If you have data from an external data source then you can import it by using the Use an external data source option.

• Soon after, a dialog box will appear, and it will present you with thumbnails of some pivot tables. You can choose your required one from these options.
• This feature offers some simple automated Pivot Tables, so if you get that no automated Pivot Tables suit you then you can click on the Blank PivotTable option to open the Pivot Table fields and select the customized fields according to your requirements manually.

• After clicking on the Blank PivotTable option, you will get the main Pivot Table fields like the image below.

• Here you see, we made a customized Pivot Table.

2. Use VBA Macro to Create a Pivot Table Automatically

While we are using Pivot Table fields to make a customized Pivot Table, it takes time and is not feasible when you need to insert Pivot Tables frequently. To overcome this situation, using VBA is the best option. We can set the fields in a Macro, and whenever we run the Macro, it will create a new Pivot Table in a single click and delete the previous one.

• First, place the dataset from cell A1. We changed the sheet name to ‘Sales Data’, and we’ll use this sheet name in our codes. So, if you have another name then don’t forget to update it.
• Next, click on the Visual Basic command from the Developer ribbon.

• Later, insert a new module by clicking Insert > Module.

• Write or insert the following codes in the module-

Sub Create_PivotTable_Automatically()
Dim PV_Sheet As Worksheet, DS_Sheet As Worksheet
Dim PV_Cache As PivotCache
Dim PV_Table As PivotTable
Dim PV_Range As Range
Dim LtRow As Long, LtColumn As Long
Dim xSheet1 As Variant
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
For Each xSheet1 In ActiveWorkbook.Worksheets
    If xSheet1.Name = "Output Pivot Table" Then
        xSheet1.Delete
    End If
Next xSheet1
Worksheets.Add.Name = "Output Pivot Table"
Set PV_Sheet = Worksheets("Output Pivot Table")
Set DS_Sheet = Worksheets("Sales Data")
LtRow = DS_Sheet.Cells(Rows.Count, 1).End(xlUp).Row
LtColumn = DS_Sheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PV_Range = DS_Sheet.Cells(1, 1).Resize(LtRow, LtColumn)
Set PV_Cache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PV_Range).CreatePivotTable(TableDestination:=PV_Sheet.Cells(2, 2), TableName:="Automatically Created PivotTable")
Set PV_Table = PV_Cache.CreatePivotTable(TableDestination:=PV_Sheet.Cells(1, 1), TableName:="Automatically Created PivotTable")
With ActiveSheet.PivotTables("Automatically Created PivotTable").PivotFields("Region")
.Orientation = xlPageField
End With
With ActiveSheet.PivotTables("Automatically Created PivotTable").PivotFields("State")
.Orientation = xlRowField
End With
With ActiveSheet.PivotTables("Automatically Created PivotTable").PivotFields("Product")
.Orientation = xlColumnField
End With
With ActiveSheet.PivotTables("Automatically Created PivotTable").PivotFields("Total")
.Orientation = xlDataField
.Function = xlSum
End With
End Sub

• In the last section of our codes, we mentioned the ‘Region’ heading to filter sales data by the regions, mentioned ‘State’ for the row field, ‘Product’ for the column field, and ‘Total’ for the value field.
• If you have different headings, then change them according to your required fields.
• Finally, just run the codes by clicking on the Play icon or pressing F5.

• Select the macro name and click on the Run tab.

• Instantly, it will create a Pivot Table in a new sheet named ‘Output Pivot Table’.
• You can change this sheet name from the codes.

• From the upper drop-down option, you can change the filter by region.

• Here’s the filtered Pivot Table for the ‘South’ region.

Read More: How to Use Excel VBA to Create Pivot Table

Frequently Asked Questions

1. Can you use a macro to create a Pivot Table?

Yes, we can easily use a VBA macro to create a Pivot Table. We have shown the way in the second method.

2. How to auto refresh Pivot Table every 5 minutes?

We can auto-refresh Pivot Table for every certain time interval. Read the following article to learn it easily:

How to Auto Refresh Pivot Table without VBA in Excel

3. Can you make a pivot of a Pivot Table?

Yes, it is possible to create a pivot of a pivot table in Excel. This technique is called “nested pivoting” or “double pivoting.” To create a pivot of a pivot table, you can follow these steps:

• First, create a pivot table with the desired data fields and column/row labels.
• Then, add additional fields to the pivot table by dragging them to the “Values” section of the PivotTable Fields pane.
• Next, right-click on any cell in the pivot table and select “Show Details” from the context menu.
• Excel will create a new worksheet with a table of detailed data for the selected cell(s).
• You can now create a new pivot table based on this detailed data table by selecting the data and choosing “Insert PivotTable” from the “Tables” group in the “Insert” tab.
• Finally, customize the new pivot table by selecting the desired fields and column/row labels and apply any necessary calculations or formatting.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

In conclusion, creating a pivot table automatically in Excel can save you a lot of time and effort, especially when dealing with large and complex datasets. By utilizing the built-in features of Excel, such as the Recommended PivotTables feature and VBA macros, you can streamline your data analysis workflow and gain valuable insights with ease.

Whether you are a beginner or an advanced user of Excel, learning how to create pivot tables automatically can significantly improve your productivity and efficiency. So, start exploring these techniques and take your data analysis skills to the next level.

Related Articles

• How to Insert A Pivot Table in Excel
• How to Create Pivot Table Report in Excel
• How to Create Pivot Table in Excel for Different Worksheets
• How Do I Create a Pivot Table from Multiple Worksheets
• VBA Code to Create Pivot Table with Dynamic Range in Excel
• How to Use VBA to Create Pivot Table from Named Range in Excel

<< Go Back to How to Create Pivot Table in Excel | Pivot Table in Excel | Learn Excel