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.
whoah this weblog is fantastic i like studying your posts.
Keep up the good work! You know, lots of individuals are looking round for this info,
you could help them greatly.
I am extremely impressed along with your writing abilities and also
with the layout on your weblog. Is this a paid topic or did you modify
it your self? Either way stay up the nice high quality writing, it is uncommon to look a nice blog like this one these days..
Pretty! This has been a really wonderful post.
Thank you for supplying these details.
Hello every one, here every one is sharing these knowledge, thus it’s pleasant to read this blog, and I used to pay a quick visit this weblog daily.
Keep on working, great job!
Quality articles or reviews is the important to be a focus for the people to
visit the web site, that’s what this website is providing.
Wow, that’s what I was searching for, what a stuff!
present here at this website, thanks admin of this
web page.