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, such as 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 gonna achieve-
Download Practice Workbook
You can download the practice workbook from the download button below.
Creating a Pivot Table Automatically in Excel: 2 Methods
To create 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.
Note: Recommended PivotTables feature is not available in the earlier versions of Excel before 2013.
- 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 can import them 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.
Read More: How to Create Pivot Table Data Model in Excel 2013
2. Use VBA Macro to Create a Pivot Table Automatically
While we are using Pivot Table fields to make a customized Pivot Table then 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 too 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 Delete a Pivot Table in Excel (3 Easy Methods)
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 Smart Methods)
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.
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.
- How to Modify an Excel Pivot Table
- Reverse Pivot Tables – Unpivot Summary Data
- How to Create Pivot Tables for Meaningful Data Analysis!
- Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
- 8 Excel Pivot Table Examples – How to Make a PivotTable!
- Excel Pivot Table Formatting (The Ultimate Guide)