How to Configure Excel 2010 Pivot Tables

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.

01
of 15

Final Result

Photo © Microsoft

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 straightforward scenario with an Excel 2010 PivotTable connected to a SQL Server 2008 R2 database using a simple SQL query.

02
of 15

Insert Pivot Table

Photo © Microsoft

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.
03
of 15

Connect Pivot Table to SQL Server

Photo © Microsoft

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. Check their website if you need to download ODBC drivers.

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

  1. Open the Create PivotTable form. Select "Use an external data source" and click on the Choose Connection button. Leave the location of where the Pivot Table will be placed.
  2. Open the Existing Connections form. Click on the Browse for More button.
  3. Click on the New Source button will launch the Data Connection Wizard.
  4. Choose Microsoft SQL Server and click Next.
  5. Enter the Server name and login credentials. Choose the appropriate authentication method:
    1. Use Windows Authentication: This method uses your network login to access SQL Server databases.
    2. Use the following User Name and Password: This method is used when the SQL Server has been configured with standalone users to access databases.
  6. Replace the table with custom SQL that will provide exactly the data we want in our Excel workbook​:
    1. 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.
    2. 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.

04
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 where the PivotTable will be, and on the right, there is a list of available fields.

05
of 15

Open Connection Properties

Photo © Microsoft

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.

06
of 15

Update Connection Properties With Query

Photo © Microsoft

Change the Command Type from Table to SQL and overwrite the existing Command Text with your SQL Query. Here is the query we 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=
Production.ProductSubcategory.ProductSubcategoryID

Click OK.

07
of 15

Receive Connection Warning

Photo © Microsoft

You will receive a 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.

08
of 15

Pivot Table Connected to SQL Server With Query

Photo © Microsoft

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.

09
of 15

Add Fields to Pivot Table

Photo © Microsoft

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

10
of 15

Add Grouping for Date Fields

Photo © Microsoft

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.

11
of 15

Choose Grouping By Values

Photo © Microsoft

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.

12
of 15

Pivot Table Grouped by Years and Months

Photo © Microsoft

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. It takes several clicks to change the view.

13
of 15

Insert Slicer (New in Excel 2010)

Photo © Microsoft

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.

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

15
of 15

Choose Values From Slicers Which Updates Pivot Table

Photo © Microsoft

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