How to link a pivot table to MS Access or another source.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.