An Excel Database
Excel is great for speed of development, but limited in how it handles data. Most databases are much more powerful, but more complex to use. With our approach, you can have the best of both...
What are the benefits?
You are probably familiar with using data tables (lists) to store data in Excel. By removing the data from the workbook and storing it in a database, you have a far more flexible system. You can:
- Display and use the data in many more ways than with normal Excel tables.
- Easily manipulate the data - select, join, sort, summarise, cross-tabulate, calculate etc.
- Organise the data logically into as many separate tables as are needed.
- Handle large volumes while avoiding large, slow workbooks.
- Greatly improve data checking on input.
- Protect against accidental or unauthorised changes.
- Share the data between different workbooks.
- Share the data between users on a network.
But you retain the ability to use it easily in your workbooks.
How does it work?
You can use any worksheet area to view a data table, which will have its own scrollbar and controls for moving through the data. Click on this demo to see how this works.
Alternatively, you can view the data one record at a time, as a form on your worksheet. Again controls are provided to move through the data, as shown here. Since this is a normal worksheet, the data can also be mixed with any other information, including cells calculated from the database data displayed.
A control can be used for selection - similar to using autofilter in Excel - as shown here. Alternatively, it is possible to select directly from a cell on a worksheet - as shown here - or even based on the current record in another displayed data table.
Data can be updated using exactly the same displays - and the same table can be safely updated by more than one user - as shown here. Data can also be updated easily from worksheet tables, external sources, or calculated from other data.
What can you do with the data?
Everything you could do with a table or list in Excel - and more...
- Use data directly in your worksheet formulas (there are functions equivalent to DSUM, VLOOKUP etc.).
- Use the worksheet data displays in combination with other worksheet information.
- Extract data to a worksheet table or other file (you can join, sort, summarise, crosstab etc. first).
- Create in-cell dropdown lists.
- Specify calculations in Excel to be carried out on any selection of records.
- Create reports (see the Report Writer page).
You can create complete systems which would normally be considered too hard for Excel.
Whatever your application, we are happy to take an initial look, with no obligation, and demonstrate how the database will simplify your development. Why not contact us... ?
For the technically minded, our tools are fully described on our technical web pages (opens in a new window). You can also download a free trial.
