Using a central excel database

I’m wondering what the best way is to implement a central excel database into my workflow. Currently when I start a spreadsheet, I will copy common data (rebar sizes and areas, locations and climate loads, or steel shapes and properties) to a separate sheet in the workbook. Then I will define the table and use VLOOKUP functions to pull the data. It’s not a difficult task, but I feel like it is a bit clumsy and makes my files a lot bigger.

What I would like to accomplish is to have the “common data” resting in a single workbook in a central location. Then use add-ins or VBA to pull the data into current lightweight sheets? I’m just wondering if an add-in is the best way to accomplish this.


Excel does quite nicely accessing databases of all kinds using standard SQL, including,

  • Other SHEETS in the same workbook
  • Other WORKBOOKS’ sheets
  • MS Access
  • Oracle
  • SQL Server
  • DB2
  • A variety of text files

I have used this basic technique to acquire data for the past 25 years. It can be done using MS Query via Data > Get External Data…

If you’re grabbing data from Excel, use
Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* (and check the Use the Query Wizard)
… and drill down to whatever workbook you need.

Each Sheet containing accessible data in a Tabular Format, must adhere to the following requirements:

  1. Headings in Row 1
  2. Contiguous data in Table (no empty columns or rows)
  3. No other data on the Sheet

Set up your query in the grid (very much like Access) and when you are satisfied with the results return the results to the Sheet:
File > Return data to Microsoft Excel

The result will reside on the Active Sheet. A QueryTable is inserted as an Object in on your Sheet. The QueryTable can be Refreshed on demand to display current data.

Above is a snippet.