Pivot tables are the best way to quickly summarize and analyze large sets of data in Excel. They are easy to use and you can have a summarized report for your dataset in a few quick clicks. There are a lot of entry points for pivot tables, and they are all worth knowing. Get your copy of the example workbook to follow along.

Create an Excel Table for Your Dataset

The first thing you need to know about pivot tables, is they work best when the source data is inside an Excel Table. Tables are structured objects that contain data. When you have a tabular dataset, you should put it in a Table. Here is an example dataset of product orders. Before you put your data inside a table, you will need to make sure it is in the correct tabular format. This will open up the Create Table menu and you should see a green dashed line around your data to indicate what data will be included in the table. Your data is now in an Excel Table. It will be very obvious as tables come with nice formatting that is automatically applied. You should now give your table a meaningful name instead of the generic Table1 name. You will now be able to reference this table name as the source data for your pivot tables.

Insert a Pivot Table with the Insert Tab

The Insert PivotTable command found in the Insert tab is the first method you should know about if you need to use pivot tables. Follow these steps to insert a pivot table. You now have a new blank pivot table in Excel!

Insert a Pivot Table with a Keyboard Shortcut

Unfortunately, there is no dedicated keyboard shortcut to insert a pivot table, but you can use the Alt hotkeys. When you press the Alt key, you will notice various letters appear in the ribbon commands. These allow you to navigate the ribbon commands from your keyboard. Press Alt, N, V, T to access the From Table/Range command found in the insert PivotTable options. The insert pivot table options will be the same from this point on.

Insert a Pivot Table with Quick Access Toolbar

If you are using pivot tables a lot, then you are going to want a way to quickly access the options to insert one. This is exactly what the Quick Access Toolbar is for! It’s a customizable selection of frequently used commands that are always available in your Excel app. You can easily add any command found in the ribbon to your Quick Access Toolbar. Follow these steps to add the From Table/Range command to your Quick Access Toolbar. This will add the From Table/Range command to your Quick Access Toolbar as shown above. Anytime you need to create a pivot table you can use this command since it’s always visible! These commands even come with Alt hotkey shortcuts based on the order they appear in the Quick Access Toolbar. This example shows the command is placed in the 5th position of the toolbar, so you can press Alt + 5 to use the command.

Insert a Pivot Table with Table Design Tab

Since pivot tables are used so frequently with Excel Tables, they have included a pivot table command inside the Table Design tab. Select a cell inside your table and the Table Design tab will appear in the ribbon. Go to the Table Design tab and select Summarize with PivotTable.

Insert a Pivot Table with Quick Analysis

There is a really cool option called Quick Analysis. As the name suggests, it gives you quick access to various tools for data analysis. Follow these steps to insert a pivot table with the Quick Analysis tools. When you select your entire data set, you will see the Quick Analysis icon appear in the lower right corner. When you click on this, it will open a menu with access to Formatting, Charts, Totals, Tables, and Sparkline tools. You can also use the Quick Analysis keyboard shortcut to select the data and open the Quick Analysis tools. Press Ctrl + Q to open the Quick Analysis tools. This can also be achieved if you right-click on the table and choose Quick Analysis from the menu. You can hover over the various PivotTable options to see a preview of the pivot table that will result. When you select any of the PivotTable options, it will insert the pivot table into a new sheet.

Recommended PivotTables are a great way to create a prepopulated pivot table. This will give you a list of suggested pivot tables you can preview and then insert into your workbook. Follow these steps to insert a recommended pivot table. This will open the Recommended PivotTables menu which will show you a list of suggested pivot tables you might be interested in. This will insert the selected pivot table into a new sheet in your workbook.

Insert a Pivot Table with Analyze Data

Analyze Data is an artificial intelligence feature that will help you spot interesting insights about your data. It allows you to query your data with natural language questions and also provides a selection of pivot table summaries and visuals to highlight trends and patterns. Follow these steps to use the Analyze Data feature. This will open the Analyze Data window pane on the right side. You’ll see various pivot tables available in the Discover Insights section which you can insert into the workbook. You can easily spot the pivot tables as they will have a prominent Insert PivotTable button in the lower-left corner. Click on the Insert PivotTable button and it will be added to a new sheet in your workbook.

Insert a Pivot Table with Table/Range Query

Power Query is a data extraction and transformation tool available in Excel, Power BI, and a few other Microsoft products. Your data might not come in the exact format you need before you can analyze it inside a pivot table. But you can reshape your data using Power Query and then load it directly into a pivot table. Follow these steps to load your data into Power Query. This will open up the Power Query editor in a new window. You’ll be able to apply any sort of transformation inside the Power Query editor. When your data is in the required format, you can then load it directly to a pivot table. This will close the Power Query editor and an Import Data menu will pop up. Now your transformed data will be available directly inside a pivot table. This is a great option for adding new calculated columns to your reports!

Insert a Pivot Table with VBA

You might be looking for a way to automate building your pivot table reports. This is where VBA programming may assist you in automating the procedure. VBA is the scripting language built into Excel and will allow you to automate your time-consuming reporting activities. Press Alt + F11 on your keyboard to open the VBA code editor. Go to the Insert menu in the VBA code editor and choose Module from the drop-down list. This will create a new module where you can add your VBA code. You can paste the above code into the editor. It will create a pivot table named myPivotTableReport with the Item column in the Rows area and the Amount column as a sum in the Values area. The Amount column will also get a currency format applied. Press Alt + F8 to open the Macro dialog box where you can run your code from.

Insert a Pivot Table with Office Scripts

There is also another option for automating your report solutions. The latest version of Excel for Microsoft 365 includes a new TypeScript based programming language called Office Scripts. The new Office Script language is now available in Microsoft 365 business plans and may be used from the online web version of Excel. Open Excel Online and go to the Automate tab, then click on the New Script button to open the Office Script editor. This will open the script editor on the right side of the Excel window with a blank script. You can click on the generic name given to the script and rename it with a descriptive name so you can easily find and run it later. The above code will create a new pivot table from the Orders table and populate it with the Item and Amount field. Click the Run button to run the script and create a pivot table.

Conclusions

Pivot tables are a fundamental tool for data analysis in Microsoft Excel, and there are many ways to insert pivot tables. There are options to insert blank pivot tables as well as suggested prebuilt pivot tables. Options like Recommended PivotTables and Quick Analysis tools can even suggest pivot tables that might interest you based on your dataset. Analyze Data takes things a step further and can create pivot tables based on natural language questions you ask. Power Query can import data from external sources, or transform your data to a proper state ready for use in a pivot table. It also allows you to conveniently load the results directly to a pivot table. You can also automate your pivot table report creation using either VBA or Office Scripts. The possibilities for automating your reporting are endless with these tools. Which method for inserting pivot tables do you prefer? Do you know any other ways to create pivot tables? Let me know in the comments below!