Excel - PivotTable Recommendations


Advertisements

Excel 2013 has a new feature Recommended PivotTables under the Insert tab. This command helps you to create PivotTables automatically.

Recommended PivotTables

Step 1 − Your data should have column headers. If you have data in the form of a table, the table should have Table Header. Make sure of the Headers.

Step 2 − There should not be blank rows in the Data. Make sure No Rows are blank.

Step 3 − Click on the Table.

Step 4 − Click on Insert tab.

Step 5 − Click on Recommended PivotTables. The Recommended PivotTables dialog box appears.

Step 6 − Click on a PivotTable Layout that is recommended. A preview of that pivot table appears on the right–side.

PivotTable Layout

Step 7 − Double-click on the PivotTable that shows the data the way you want and Click OK. The PivotTable is created automatically for you on a new worksheet.

PivotTable Created

Create a PivotTable to analyze external data

Create a PivotTable by using an existing external data connection.

Step 1 − Click any cell in the Table.

Step 2 − Click on the Insert tab.

Step 3 − Click on the PivotTable button. A Create PivotTable dialog box appears.

PivotTable Created Appears

Step 4 − Click on the option Use an external data source. The button below that, ‘Choose Connection’ gets enabled.

Step 5 − Select the Choose Connection option. A window appears showing all the Existing Connections.

Existing Connections

Step 6 − In the Show Box, select All Connections. All the available data connections can be used to obtain the data for analysis.

Select All Connections

The option Connections in this Workbook option in the Show Box is to reuse or share an existing connection.

Connect to a new external data source

You can create a new external data connection to the SQL Server and import the data into Excel as a table or PivotTable.

Step 1 − Click on the Data tab.

Step 2 − Click on the From Other Sources button, in the Get External Data Group.

Other Sources

The options of External Data Sources appear as shown in the image below.

External Data Sources

Step 3 − Click the option From SQL Server to create a connection to an SQL Server table.

From SQL Server

A Data Connection Wizard dialog box appears.

Data Connection Wizard

Step 4 − Establish the connection in three steps given below.

  • Enter the database server and specify how you want to log on to the server.

  • Enter the database, table, or query that contains the data you want.

  • Enter the connection file you want to create.

Using the Field List option

In Excel 2013, it is possible to arrange the fields in a PivotTable.

PivotTable Create Dialog

Step 1 − Select the data table.

Step 2 − Click the Insert Tab.

Step 3 − Click on the PivotTable button. The Create PivotTable dialog box opens.

Step 4 − Fill the data and then click OK. The PivotTable appears on a New Worksheet.

PivotTable New Worksheet

Step 5 − Choose the PivotTable Fields from the field list. The fields are added to the default areas.

The Default areas of the Field List are −

  • Nonnumeric fields are added to the Rows area

  • Numeric fields are added to the Values area, and

  • Time hierarchies are added to the Columns area

PivotTable Default Areas

You can rearrange the fields in the PivotTable by dragging the fields in the areas.

Step 6 − Drag Region Field from Rows area to Filters area. The Filters area fields are shown as top-level report filters above the PivotTable.

PivotTable Filters Areas

Step 7 − The Rows area fields are shown as Row Labels on the left side of the PivotTable.

PivotTable Row Labels

The order in which the Fields are placed in the Rows area, defines the hierarchy of the Row Fields. Depending on the hierarchy of the fields, rows will be nested inside rows that are higher in position.

In the PivotTable above, Month Field Rows are nested inside Salesperson Field Rows. This is because in the Rows area, the field Salesperson appears first and the field Month appears next, defining the hierarchy.

Step 8 − Drag the field - Month to the first position in the Rows area. You have changed the hierarchy, putting Month in the highest position. Now, in the PivotTable, the field - Salesperson will nest under Month fields.

Salesperson Field Nested

In a similar way, you can drag Fields in the Columns area also. The Columns area fields are shown as Column Labels at the top of the PivotTable.

PivotTable Column Labels

PivotTables based on Multiple Tables

In Excel 2013, it is possible to create a PivotTable from multiple tables. In this example, the table ‘Sales’ is on one worksheet and table - ‘Products’ is on another worksheet.

Sales Worksheet

Products Worksheet

Step 1 − Select the Sales sheet from the worksheet tabs.

Step 2 − Click the Insert tab.

Step 3 − Click on the PivotTable button on the ribbon. The Create PivotTable dialog box,

Step 4 − Select the sales table.

Step 5 − Under “choose whether you want to analyze multiple tables”, Click Add this Data to the Data Model.

Step 6 − Click OK.

Pivot Multiple Tables

Under the PivotTable Fields, you will see the options, ACTIVE and ALL.

Step 7 − Click on ALL. You will see both the tables and the fields in both the tables.

Step 8 − Select the fields to add to the PivotTable. You will see a message, “Relationships between tables may be needed”.

Relationships Tables

Step 9 − Click on the CREATE button. After a few steps for creation of Relationship, the selected fields from the two tables are added to the PivotTable.

Cration Of Relationships
Advertisements