Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel   
 BI for Excel
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  

Home >  BI for Excel >

Consolidating Excel Data
From Many Workbooks

"What we regularly need is (1) the ability to send out an Excel template to multiple users ... potentially with multiple templates, tabs or forms per user, (2) the ability to receive these and reliably roll up data from these into a master spreadsheet ... with multiple passes of updates, (3) a way to move from a master index with one-line-per-item to a structured "form" display of the data (tab, form, etc.)." -- Bruce M.


When I read your note this morning I wasn't sure how to respond. Your problem is a classic one for Excel. It's the cause of a lot of Spreadsheet Hell.

One potential approach would be to write a complicated macro that consolidates those workbooks. But that would be expensive to create, and maintain. Worse, the macro probably would break continually because my impression is that you're consolidating data from many different kinds of workbooks, with a lot of user interaction. 

But then I got a call from a friend. He said that his firm had just closed the sale for an Excel-friendly OLAP solution to a company with more than 300 locations. The company will regularly consolidate Excel-sourced data using that program. Interestingly, the product that the company chose happens to be the least expensive of the three Excel-friendly OLAPs. It's what I recommend to small and medium businesses.

I think that general approach is the way for you to go. At the front end, use Excel to gather your data. At the back end, use Excel to report and analyze your data. But in the middle, use an Excel-friendly OLAP product to consolidate and manage your data. Unlike any other database technology I know of, it allows spreadsheet formulas to return values to cells.

Several days ago I posted What Do Users Need From Excel-Friendly Data Sources?, which describes the general approach. Particularly, read the last section titled "Write-Back from Excel".

Hope this helps,

Charley Kyd

(Email Comments)


ExcelUser, Inc.

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.


Excel Dashboards

Learn how to create top-quality dashboard reports with Excel.