Wednesday, 31 May 2017

Advance Excel - PivotTable Recommendations ~ GNIITHELP

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
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

No comments:

Post a Comment