Your Data Is In Access But You Need To Create An Excel Report – Create An External Data Connection

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.

Follow How To Excel At Excel

Recommended Excel Resources

SHARE

Additional Info

Follow How To Excel At Excel

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel?
Click the link below to receive more Excel tips' and my Free Ebook

Sign Up For My FREE Excel Tips Newsletter and receive your own E-book of my Top 50 Excel Tips.