Link pivot to access (or other database source)

How to link a pivot table to MS Access or another source.

• 02:17
• Skill Level: 6
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Link pivot to access (or other database source)

With Pivot Tables, you don’t have to have the data base that you want to work with in an Excel spreadsheet. You can actually source the information from a variety of sources. So this is how you get information from an external source or data base into a pivot table.

To get started we have a blank spreadsheet and I am going to Insert Pivot Table. Normally you would choose a table or range but in this case I am going to say- please use an External data source. When I click on that cell this button becomes active and it says “choose the connection”. I am going to click on it and you’ll see if you previously tried to get connections, they will all be listed here, but for our purposes I am going to say “Please browse for more”. You need to go and find the data base. You may need some assistance from your IT people, if the data base is stored somewhere on the network. Mine is just stored on my desktop. You’ll see I’ve got an access data base called “Pivot Tables example Access”.

When you click on the Access Data Base, Excel immediately looks in the Data Base and says I found two different tables- which one do you want me to use? Typically speaking, there may be hundreds of tables and queries so you may again need some assistance from your IT guys. I know I am going to bring through the phone bill – the new phone bill. I click OK and I choose where I want to put the pivot table so in this case, in existing sheet A1 is fine – then I click OK and what you’ll see I have got a pivot table ready and waiting to be used. Just looking at the data here you’ll see it is exactly the same as the data we have been working with in this course. All the field items are listed here and again you can pull through the information fairly easily so, for example, – there is my bill data, I can pull it through, pull the months through. You will recognize these numbers and values.

So that is how easy it is to get the information into a pivot table. It is just a case of now working with it.