Excel 2 Excel

Pull and Analyse Data from Closed Excel Workbooks

Although Excel is primarily a data analysis tool, it is still used as DATABASE by many. Primarily because EXCEL is much much easier to use than ACCESS. Moreover using some tricks and techniques you can incorporate strong Data Integrity features in Excel Workbooks which is the typical hallmark of ACCESS.

I thought an Excel Addin would be very handy which allows you to pull data from WORKSHEETS, EXCEL TABLES and NAMED RANGES in an Excel Workbook without opening it. Especially if you receive lot of data in a systematic tabular format which is periodically updated, you would love an Addin that  quickly retrieves this data, process and transform it without requiring you to open that workbook.

This Addin allows you to

  1. Pull Data from Worksheets, Excel Tables and Named ranges
  2. Aggregate and Group Data
  3. Design and run Queries directly in excel
  4. Maintain history of your recent Workbooks for quick access later


You can download the copy of this Addin from the link below

[download id=”527″]


How to Install this Addin

Save the PullAddin.xlam file in your PC.

Note: Please don’t change the name of file, otherwise some features would not work

Open any excel workbook. Press ALT + T + I to open the Addin Manager. Browse for the file and enable the Addin

Once installed you will see a new tab “PULL” in your excel workbook. Activate the “PULL” Tab and press the “Smiley Face Button”

A dialog form would appear as shown below



 Retrieving-Data-from-Excel Workbook   

Go to Page Top       

Press the “Select Excel Workbook” to browse and select your excel workbook to pull data from.PD5

Select one of the four options

  1. Custom Range
  2. Named ranges
  3. Excel Tables
  4. Current Region from cell A1

as shown in figure.

CUSTOM RANGE: This option is different from the rest as it allows you to pull entire data of a worksheet or you can specify a range of data e.g. Cells A2:F65 in Sheet2. The demerit of this option is that you can not apply QUERIES and AGGREGATION and GROUPING with this option i.e. you can only pull raw data


NAMED RANGES, EXCEL TABLES AND CURRENT REGION: In contrast to “Custom Range” option, these options allow you to select desired fields, apply queries, summarize and process data.

Current Region from Cell A1 means Data which is arranged in tabular format starting from cell A1 of the sheet with first row containing the table headings.


Once you click any of these options you would see  list of all the named ranges, excel tables or worksheets  in the “Select Table” frame. The file details would be stored in the quick links tab for quick access later


Select any table and you will see the next frame “Select Fields”  gets populated with all the column in that named range/table/worksheet which you selected.

Select and Add your desired fields to the “Fields I want” frame. You can remove rearrange, remove or add fields using the command buttons beside the frame


Click anywhere in the excel workbook where you want data to be placed and PRESS “DONE” button in the DIALOG FORM.


The Dialog Form works in MODELESS fashion i.e. you can work in EXCEL while the Dialog Box is open

The workbook details are added to the QUICK LINKS TAB so that you can quickly access it later

Aggregate and Group items

Go to Page Top 

This powerful feature lets you summarize your data in tables and worksheets which might span thousands or even millions of rows.

Since databases are often collection of individual transactions, users/ analysts would be interested in summarizing data. For example in a daily sales table having ten thousand rows of individual sale transactions, you might like to see the total sales for a particular period for each customer and for each sales person.

Check the “Aggregate and Group” check box; you would be taken to new tab “Aggregate & Group”.

Select Table; you would see the list of fields in both “Choose fields to Aggregate” and “Choose Field to Group by” frames.


Only choose those fields to aggregate which contain numeric values, select the aggregation function (sum, count, average etc.) you wish to apply.

Only choose those fields to Group by which contain non-numeric values

Select any cell in your workbook and Press Done ; you will see the summarized data reflecting your selections above


You can make queries while using the Aggregation and Grouping tool. For example in the case above you might want to see the results only for a particular salesperson or customer. Queries are covered in detail below.

Make Queries

Go to Page Top 

Queries work like Excel data filters. They allow you to get the particular records you need

To make queries check the “Make Queries” checkbox in choose fields box




Go to Page Top 

The Addin maintains history of 10 most recent Workbooks which you have accessed using this Addin.

This helps you quickly access your most used Workbooks without having to browse and locate them again.

Go to the Quick Links tab. Just Double click on the Workbook you want to retrieve data from


Go to Page Top