Transcripts for the above video clip:

Pivot Tables 2007- External Data 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.