Did you know you can analyse data from an Access database in an Excel Pivot Table without importing the data first?
If you use Access then this is a great productivity booster because once you connect to your Access query you simply refresh the data connection and your Pivot Table updates, as do any charts or reports you have connected to the Pivot Table data.
Almost all Access databases consist of more than one table, so if yours is like this then you need to build a query in Access that brings together all the data you want to analyse in your Pivot Table.
Then you simply insert a Pivot Table and select the 'Use an external data source' option and click the ‘Choose Connection’ button.
You can then browse to your existing connection:
Once you have selected your file you can select the query you want to connect to:
Note: Remember, if your Access database has more than one table in it you will need to build a query first and then connect your Pivot Table to the query.
Tip: Since Excel imports the data in the query into the Pivot Cache it is recommended that you only include data in the query that you need, as unnecessary data will generate a large, slow file.
Once you click OK it may take a while for Excel to import all of the data into the Pivot Cache. If your Access query is large then this may take a few minutes, depending on the processing power of your PC and the location of the database.
Now you can Pivot away to your heart’s content…
and while you’re there you can add a PivotChart or other reports from the data.
Then each week/month/quarter simply refresh the Pivot Table (CTRL+ALT+F5) to update your reports.
Refreshing your Pivot Table using CTRL+ALT+F5 automatically connects to Access and brings in the new data. Job done 🙂
It's probably not even time for morning tea yet, but I'm sure your boss won't mind if you take a break since you've got your work done in record time.
Tip: Importing data into Excel isn't limited to Access. You can import data from the web, SQL Servers and other sources you may have access to.
The ability to connect directly to external data sources and analyse them with Pivot Tables eliminates the tedious tasks associated with importing data like copy, paste, formatting and complex formulas, not to mention the ongoing maintenance of this data as it grows monthlty and requires updating.
Get over 10 hours of comprehensive Excel online training, including tutorials on Excel Pivot Tables and Importing External Data into Excel.