Pulling database data into Excel and Pivot Tables.
Where/when to use the technique
When part or all of the data you need to build your reports is stored in a database.
Sourcing data via “Get External Data”
- Go to the Data tab > From Access
- Browse to your database, select it and click Open
- Choose the table or query you wish to import and click OK
- Choose to land it in a table or a PivotTable
- If you landed it to a table first, go to the Insert menu and add a new PivotTable
- Layout your PivotTable as desired
- Refresh your PivotTable from the database
Key points to remember
- Landing data in a table first:
- You can “see” the data in the worksheet so you can review it.
- You can easily write formulas to manipulate, change or add to the data.
- Means that your data set must be less than 1,048,575 rows.
- Landing data in a PivotTable first:
- You can’t “see” the data in the worksheet so you can review it.
- You can’t easily write formulas to manipulate, change or add to the data.
- Means that your data set must be more than 1,048,575 rows as the PivotTable summarizes it before it lands in the worksheet.
Lesson notes are only available for subscribers.