I have to do analysis on a large (in the Excel world) dataset fairly regularly. Ideally, I would like to have some type of repository that I could dump the data into on a monthly basis when I receive the reports and then be able to query and sort the data as needed. Beyond Excel, I'm not sure what the next step up in the world is, but I knew the kind folks here would not be afraid to share an opinion or two.<p>The data consists of multiple performance measurements for multiple unique entities on a per month basis. So currently, my Excel workbook looks something like this:<p>Sheet 1: Summary/Analysis Page that performances basic calculations off subsequent sheets<p>Sheet 2: Performance Metric 1
Column A: Unique Identifier
Row A: Months
B,B and beyond: Data<p>Sheet 3: Performance Metric 2
Column A: Unique Identifier
Row A: Months
B,B and beyond: Data<p>Sheet 4: Performance Metric 3
Column A: Unique Identifier
Row A: Months
B,B and beyond: Data
It depends what you are trying to do exactly, but the excel add-in called powerpivot could be useful if you are trying to aggregate data and do adhoc analysis.<p>It is free :<p><a href="http://www.microsoft.com/en-us/bi/powerpivot.aspx" rel="nofollow">http://www.microsoft.com/en-us/bi/powerpivot.aspx</a><p>I have analysed lots of BI tools, which could be useful in your scenario, but most require a bit of technical / database knowledge to iron out wrinkles.
I worked R & D at a big consumer packaged goods company and ran into a lot of these 'excel messes' especially dealing with raw materials specifications. Here are my suggestions:<p>The 'R' programming language is pretty useful for statistics and could be used to aggregate a lot of files (and its free). Also if you want to generate dynamic reports using templates then you should look into sweave which is a package in 'R'. (<a href="http://www.stat.uni-muenchen.de/~leisch/Sweave/" rel="nofollow">http://www.stat.uni-muenchen.de/~leisch/Sweave/</a>) You could build beautiful PDF reports that you simply run a script to generate if they are easily created into a template. But that would also require learning Latex for creating documents, something that is used heavily in the academic world but I have yet to see it in the business world.<p>Another approach which I would try is to use a programming language to dump it into a relational database and then use SQL to access the info (which is heavily used in business setting). This option also also allows for others to use it with familiar business tools. My unconventional tools of choice would be Ruby and using Rails to build an interface.
I would use Talend for Data Integration to clean and load the data into MySQL. Then write some SQL for the reports that are query-able. Then build Ruby and Rails front-end for the more complicates data views.
GIS software?<p>Most data has a physical location associated with it (street address, lat-long, something mappable). "A picture is worth a thousand words". Mapping data can help you visualize and mentally process heavy data loads. And Excel is one of the database formats typically compatible with standard software so with a smidgeon of luck you can probably kind of plug and play.