The idea behind building your pivot table using an external data source is that you can create and use a pivot table from a database that is not stored in your Excel workbook. This means you can create a pivot table without having data in your Excel workbook. User can create a pivot table of large Excel file without opening it physically which also saves a lot of working memory since files are not opened physically. This is ultimately one of the best techniques to work with large database pivot table. Although, Power Pivot can be the best alternative but it is not supported by all Excel version directly and common users are not well-versed with this Add-Ins. Another drawback of Power Pivot is that it uses a large volume of memory.
Below are the steps to create a Pivot Table by using an existing external data connection:
- Click any cell on the worksheet.
- Click Insert> PivotTable.
- In the Create PivotTabledialog box, under Choose the data that you want to analyse, click Use an external data source.
- Click Choose Connection.
- Browse for More
- New Source
- ODBC DSN Next
- Select ODBC from list and then Next
- Select DSN from list (refer to last para to create user DSN)
- Select particular sheet from list(if DSN is based on Excel workbook)
- Click Ok/Finish
- Now you can proceed for Pivot Table
Excel adds an empty PivotTable and shows the Field List so that you create own layout for desire Pivot Table report.
How to create an User DSN(Windows 8/10)
Note– User DSN is mandatory Create a PivotTable with an external Data Source.
- Go to Control Panel.
- Select Administrative Tools.
- Double click Data Source ODBC.
- In the ODBC Data Source Administrator dialog box, select the System DSN or User DSN
- Click Add. …
- Locate the necessary driver in the list and click Finish.
- Then complete final step of DSN creation.