Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Connecting to external data sources (such as databases), and pulling the data into Excel.
Multiple versions of this lesson are available, choose the appropriate version for you:
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.