Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Creating a Pivot Table Automatically

Is it a tough job to create a pivot table? If you create your pivot table with Recommended PivotTables option, it is one of the easiest jobs in Excel.

New Feature: Recommended PivotTables is a new feature in Excel 2013.

If your data is in a worksheet, 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. This dialog box will present you with thumbnails of some pivot tables. You can choose your required one from these options. The following figure shows the Recommended PivotTables dialog box for our Bank-accounts.xlsx data.

Creating Pivot Tables automatically.

Recommended PivotTables dialog box. You can choose pivot tables from a list in this dialog box.

Download Sample File

The pivot table thumbnails use your actual data, and there’s a good chance that you will get one from the options that will serve your purpose or at least will be very close to your purpose. Select that thumbnail, click OK, and Excel will create the pivot table on a new worksheet.

When you select any cell in a pivot table, Excel displays the PivotTable Fields task pane. You can use this task pane to change the layout of your pivot table.

Note: Your data may have in an external database. To make a pivot table using this external database, select a blank cell of an existing worksheet. When you choose Insert ➪ Tables ➪ Recommended PivotTables, the Choose Data Source dialog box will appear. Select Use An External Data Source, and then click Choose Connection to specify the data source. You’ll now see the thumbnails of the list of recommended pivot tables. Choose one that serves your purpose.

It may happen that no Recommended PivotTables is suitable for you. You have then two choices:

  • Choose a pivot table that’s close to your requirements, and then use the PivotTable Fields task pane to modify it.
  • Click the Blank PivotTable button (at the bottom of the Recommended PivotTables dialog box) and create your pivot table manually.

Happy Excelling 🙂

Read More: How to Create Pivot Table Data Model in Excel 2013

Related Articles

Khan Muhammad Kawser

Khan Muhammad Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1 Comment

Leave a reply