Excel and data
We specialise in a data-driven approach to building models in Excel which combines maximum flexibility with ease of maintenance.
Microsoft Excel is an extremely powerful system for analysing information. But when the user first creates a workbook, he or she is presented with a completely blank grid. There's nothing to lead the user to the type of good design which is as important in getting the best results out of Excel as it is to a full database product such as Access. But what does this mean?
Most business workbooks exist to record and analyse data. How this data is stored and then managed is, we believe, one of the most important factors in creating effective workbooks. We have also developed a range of tools in VBA which simplify the analysis and reporting of the data. The benefit to the user is in ease of maintenance of the data and the flexibility possible in how the data can be analysed and reported.
In summary our approach is:
- Analyse and model the data which an Excel model is to contain.
- Plan the best way to structure the data within a workbook (very often this will be in the form of a number of Tables, in the same way as the data would be structured if Access or another database product were being used).
- Using largely standardised code tools, feed this data into whatever analysis the user may require.
For more information on our approach, please see the Database page.
Another situation we often see is where a workbook is being used as a "template", where it is reused for a number of different data sets. A common example of this is a quote system where quotes are prepared for different clients. Often such workbooks will contain multiple worksheets of the same design, but with different data.
Normally the resulting workbooks are each stored separately. The problem with this approach comes when the user wants to change the design of the workbook. The new design can easily be applied to any new data sets, but it is almost impossible to get existing data into the new design without laborious retyping or copying and pasting. We have developed a solution to this problem, which allows the data to be separated from the workbook and stored in xml files. This makes it easy either to load different data sets into a single worksheet or to load data into amended versions of a master workbook. For more information on this, please see the Template page.
To follow up...
If you are interested in this and would like us to look at any Excel problem you may have, please use our Contact page.
Cookies are used for navigation purposes within this website.