How to Configure Excel 2010 Pivot Tables

of 15

Final Result

Photo © Microsoft
This is the final result of this Step by Step tutorial - Click on the image to see a full sized version.

There has been a gap between Microsoft Excel and the top tier business intelligence (BI) platforms for many years. The Microsoft Excel 2010 Pivot Table enhancements along with a couple of other BI features have made it a real competitor for enterprise BI. Excel has traditionally been used for standalone analysis and the standard tool that everyone exports their final reports into. Professional business intelligence has traditionally been reserved for the likes of SAS, Business Objects and SAP.

Microsoft Excel 2010 (with the Excel 2010 Pivot Table) along with SQL Server 2008 R2, SharePoint 2010 and the free Microsoft Excel 2010 add-on "PowerPivot" has resulted in a high end business intelligence and reporting solution.

This tutorial covers a straight forward scenario with an Excel 2010 PivotTable connected to a SQL Server 2008 R2 database using a simple SQL query. I am also using Slicers for visual filtering which is new in Excel 2010. I will cover more complex BI techniques using Data Analysis Expressions (DAX) in PowerPivot for Excel 2010 in the near future. This latest release of Microsoft Excel 2010 can provide a real value for your user community.

of 15

Insert Pivot Table

Photo © Microsoft
Position your cursor exactly where you want your pivot table and click on Insert | Pivot Table.

You can insert a Pivot Table in a new or existing Excel workbook. You might want to consider positioning your cursor down a few rows from the top. This will give you space for a header or company information in case you share the worksheet or print it out.

  • Open a new or existing Excel 2010 workbook and click on the cell where you want the top left corner of the Pivot Table to be.
  • Click on the Insert tab and click on the PivotTable drop down in the Tables section. Choose PivotTable. This will launch the Create PivotTable dialog form.
of 15

Connect Pivot Table to SQL Server (or Other Database)

Photo © Microsoft
Create your SQL query and then connect to SQL Server to embed the connection data string into the Excel spreadsheet.

Excel 2010 can retrieve data from all major RDBMS (Relational Database Management System) providers. SQL Server drivers should be available for the connection by default. But all major database software make ODBC (Open Database Connectivity) drivers to allow you to make the connection. Check their website if you need to download ODBC drivers.

In the case of this tutorial, I am connecting to SQL Server 2008 R2 (SQL Express free version).

  • A - The Create PivotTable form is the first form in creating the connection to SQL Server. Select "Use an external data source" and click on the Choose Connection button. Leave the location of where the Pivot Table will be placed unless you want to create a new worksheet and place it there.
  • B - The Existing Connections form lists any connections in the current workbook, on your computer and the network you are currently connected to. Existing connections are really just text files with the connection information needed to access a particular data source. In our case, we are going to create a new data source. Click on the Browse for More button.
  • C - Click on the New Source button will launch the Data Connection Wizard.
  • D - Choose Microsoft SQL Server and click Next.
  • E - Enter the Server name and log on credentials. Choose the appropriate authentication method. If you are unsure which method to use, contact your database administrator.
    • Use Windows Authentication: This method uses your network login to access SQL Server databases.
    • Use the following User Name and Password: This method is used when the SQL Server has been configured with standalone users to access databases.
  • F - In this step, we are going to choose a table as a placeholder. We are going to be replacing the table with custom SQL that will provide exactly the data we want in our Excel workbook.
    • Select the database that you will be connecting to. In this example, we are connecting to the AdventureWorks sample database provided by Microsoft. Check the Connect to a specific table and choose the first table. Remember, we are not going to be retrieving data from this table.
    • Click Finish which will close the wizard and return you to the workbook. We will be swapping out the placeholder table for our custom SQL query.

You will be returned to the Create PivotTable form (A). Click OK.

of 15

Pivot Table Temporarily Connected to SQL Table

PivotTable is connected to SQL Server with the placeholder table.

At this point, you have connected to the placeholder table and you have an empty PivotTable. You can see on the left were the PivotTable will be and on the right there is a list of available fields.

of 15

Open Connection Properties

Photo © Microsoft
Open Connection Properties form.

Before we start choosing data for the PivotTable, we need to change the connection to the SQL query. Make sure you are on the Options tab and click on Change Data Source drop down from the Data section. Choose Connection Properties.

This brings up the Connection Properties form. Click on the Definition tab. This shows you the connection information for the current connection to SQL Server. While it references a connection file, the data is actually embedded in the spreadsheet.

of 15

Update Connection Properties With Query

Photo © Microsoft
Change table to SQL query.

Change the Command Type from Table to SQL and overwrite the existing Command Text with your SQL Query. Here is the query I created from AdventureWorks sample database:

SELECT Sales.SalesOrderHeader.SalesOrderID ,
Sales.SalesOrderHeader.OrderDate ,
Sales.SalesOrderHeader.ShipDate ,
Sales.SalesOrderHeader.Status ,
Sales.SalesOrderHeader.SubTotal ,
Sales.SalesOrderHeader.TaxAmt ,
Sales.SalesOrderHeader.Freight ,
Sales.SalesOrderHeader.TotalDue ,
Sales.SalesOrderDetail.SalesOrderDetailID ,
Sales.SalesOrderDetail.OrderQty ,
Sales.SalesOrderDetail.UnitPrice ,
Sales.SalesOrderDetail.LineTotal ,
Production.Product.Name ,
Sales.vIndividualCustomer.StateProvinceName , Sales.vIndividualCustomer.CountryRegionName ,
Sales.Customer.CustomerType ,
Production.Product.ListPrice ,
Production.Product.ProductLine ,
Production.ProductSubcategory.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID=

Click OK.

of 15

Receive Connection Warning

Photo © Microsoft
Click on Yes to Connection Warning.

You will receive an Microsoft Excel Warning dialog box. This is because we changed the connection information. When we originally created the connection, it saved the information in an external .ODC file (ODBC Data Connection). The data in the workbook was the same as the .ODC file until we changed from a table command type to SQL command type in Step #6. The warning is telling you that the data is no longer in sync and the reference to the external file in the workbook will be removed. This is OK. Click Yes.

of 15

Pivot Table Connected to SQL Server With Query

Photo © Microsoft
PivotTable is ready for you to add data.

This takes back to the Excel 2010 workbook with an empty PivotTable. You can see that the available fields are now different and correspond to the fields in the SQL query. We can now begin adding fields to the PivotTable.

of 15

Add Fields to Pivot Table

Photo © Microsoft
Add fields to PivotTable.

In the PivotTable Field List, drag ProductCategory to Row Labels area, OrderDate to Column Labels area and TotalDue to Values area. The image shows the results. As you can see, the date field has individual dates so the PivotTable has created a column for each unique date. Fortunately, Excel 2010 has some built in functions to help us organize date fields.

of 15

Add Grouping for Date Fields

Photo © Microsoft
Add Groupings for date field.

The Grouping function allows us to organize dates into years, months, quarters, etc. This will help summarize the data and make it easier for the user to interact with it. Right click on one of the date column headers and choose Group which brings up the Grouping form.

of 15

Choose Grouping By Values

Photo © Microsoft
Chose grouping items for date field.

Depending on the kind of data you are grouping, the form will look a little different. Excel 2010 allows you to group dates, numbers and selected text data. We are grouping OrderDate in this tutorial so the form will show options relating to date groupings.

Click on Months and Years and click OK.

of 15

Pivot Table Grouped by Years and Months

Photo © Microsoft
Date fields are grouped by years and months.

As you can see in the image above, the data is grouped by year first and then by month. Each has a plus and minus sign which allows you to expand and collapse depending on how you wish to see the data.

At this point, the PivotTable is pretty useful. Each of the fields can be filtered but the problem is there isn't a visual clue as to the current state of the filters. Also, it takes several click to change the view.

of 15

Insert Slicer (New in Excel 2010)

Photo © Microsoft
Add Slicers to PivotTable.

Slicers are new in Excel 2010. Slicers are basically the equivalent of visually setting filters of the existing fields and creating Report Filters in the case that the item you want to filter on is not in the current PivotTable view. This nice thing about Slicers is it becomes very easy for the user to change the view of the data in the PivotTable as well as providing visual indicators as to the current state of the filters.

To insert Slicers, click on the Options tab and click on Insert Slicer from the Sort & Filter section. Choose Insert Slicer which opens the Insert Slicers form. Check as many of the fields as you want to have available. In our example, I added Years, CountryRegionName and ProductCategory. you might have to position the Slicers where you want them. By default, all of the values are selected which means no filters have been applied.

of 15

Pivot Table With User Friendly Slicers

Slicers make it easier for users to filter PivotTables.
As you can see, the Slicers show all data as selected. It is very clear to the user exactly what data is in the current view of the PivotTable.
of 15

Choose Values From Slicers Which Updates Pivot Table

Photo © Microsoft
Pick combinations of Slicers to change view of data.

Click on various combinations of values and see how the view of the PivotTable changes. You can use typical Microsoft clicking in the Slicers meaning that if you can use Control + Click to select multiple values or Shift + Click to select a range of values. Each Slicer displays the the selected values which makes it really obvious what the state of the PivotTable is in terms of filters. You can change the styles of the Slicers if you want by click on the Quick Styles drop down in the Slicer section of the Options tab.

The introduction of Slicers has really improved the usability of PivotTables and has moved Excel 2010 much closer to being a professional business intelligence tool. PivotTables have improved quite a bit in Excel 2010 and when combined with the new PowerPivot creates a very high performance analytic environment.

Was this page helpful?