Being able to analyze all the data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data that is stored outside of Excel, like in a Microsoft Access or Microsoft SQL Server database, or in an Online Analytical Processing (OLAP) cube file. In that case, you’ll connect to the external data source, and then create a PivotTable to summarize, analyze, explore, and present that data.
Here’s how to create a PivotTable by using an existing external data connection:
-
Click any cell on the worksheet.
-
Click Insert > PivotTable.
-
In the Create PivotTable dialog box, click From External Data Source.
-
Click Choose Connection.
-
On the Connections tab, in the Show box, keep All Connections selected, or pick the connection category that has the data source you want to connect to.
To reuse or share an existing connection, use a connection from Connections in this Workbook.
-
In the list of connections, select the connection you want, and then click Open.
-
Under Choose where you want the PivotTable report to be placed, pick a location.
-
To place the PivotTable in a new worksheet starting at cell A1, choose New Worksheet.
-
To place the PivotTable in the active worksheet, choose Existing Worksheet, and then in the Location box, enter the cell where you want the PivotTable to start.
-
-
Click OK.
Excel adds an empty PivotTable and shows the Field List so that you can show the fields you want and rearrange them to create your own layout.
-
In the field list section, check the box next to a field name to place the field in a default area of the areas section of the Field List.
Typically, nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and date and time fields are added to the Columns area. You can move fields to a different area as needed.
: You can also right-click a field name, and then select Add to Report Filter, Add to Column Labels, Add to Row Labels, or Add to Values to place the field in that area of the areas section, or drag a field from the field section to an area in the areas section.
Use the Field List to further design the layout and format of a PivotTable by right-clicking the fields in the areas section, and then selecting the area you want, or by dragging the fields between the areas in the areas section.
Connect to a new external data source
To create a new external data connection to SQL Server and import data into Excel as a table or PivotTable, do the following:
-
Click Data > From Other Sources.
-
Click the connection you want.
-
Click From SQL Server to create a connection to a SQL Server table.
-
Click From Analysis Services to create a connection to a SQL Server Analysis cube.
-
-
In the Data Connection Wizard, complete the steps to establish the connection.
-
On page 1, enter the database server and specify how you want to log on to the server.
-
On page 2, enter the database, table, or query that contains the data you want.
-
On page 3, enter the connection file you want to create.
-
To create a new connection to an Access database and import data into Excel as a table or PivotTable, do the following:
-
Click Data > From Access.
-
In the Select Data Source dialog box, locate the database you want to connect to, and click Open.
-
In the Select Table dialog box, select the table you want and then click OK.
If there are multiple tables, check the Enable selection of multiple tables box so you can check the boxes of the tables you want, and then click OK.
In the Import Data dialog box, select how you want to view the data in your workbook and where you want to put it, and then click OK.
The tables are automatically added to the Data Model, and the Access database is added to your workbook connections.
