If you have data stored outside of Excel, such as an Access Database, SQL Server or and OLAP Cube then you can still use a Pivot Table to analyse this data, yes all you need to do is connect to this data as an ‘external data source’ and Voila!, you can use the familiar and powerful features of an Excel Pivot Table.
I am going to connect my Pivot Table to the Northwind Access database which I have downloaded to my machine. (Northwind is the example database that Microsoft Provides, and is a good source of data to practice on).
- Click on any cell in your Excel worksheet
- On the Insert Tab | Tables Group Select Insert Pivot Table
- Select Use an external data source in the ‘Choose the data you want to analyse section’
- If it is a new connection to a new data source, use the Browse button to navigate to your data source
- Once connected you will see a list of available tables and queries in your Access Database
- Select the data source you want to use (in my example it is orders) and hit Ok
- Select to place the Pivot Table on the existing worksheet on onto a New Worksheet.
Once this is done you will be able to use the Excel Pivot table as normal, you are just connected to your external data – in the case the orders table in the NorthWind Database. Now time for some analysis and Excel Pivot Table magic!.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.