Excel & VBA Solutions > Data Collection and Record Keeping in Excel
Data Collection and Record Keeping Made Simple, Quick and Safe
We build robust Excel tables that are optimised for collecting data and keeping records. Our tables have smart productivity features that help you work faster and unique safety checks that prevent input errors at the source. All features are quickly accessible in one place and the clear table layout makes it easy to work with. Anyone with basic Excel skills can comfortably and safely handle our tables.
The Benefits of Using our Tables:
- Assisted data input reduces typing and speeds up data entry.
- Automated safety checks only accept complete and valid records.
- Filters keep the focus on relevant data and find records instantly.
- Sort features organise records quickly with just a few mouse clicks.
- Your data is kept in well-structured and consistently formatted records.
If you are looking for an efficient data collection or record keeping solution in Excel, try our tables. With our generic table template we can build any kind of Excel-based data collection and record keeping system, starting from just £25 plus VAT.
The tables we build for our clients range from simple data collection spreadsheets for day-to-day administrative and organisational tasks, to sophisticated Excel applications that use our tables to manage and control their data.
We are happy to look into your situation to see if you would benefit from using our tables. Enquiries and initial consultations are free with no obligation. Call Roger on +44 (0)1865 751 432 or email us
For more information see below: Our Tables Explained
Demo Spreadsheet & User Guide
To see one of our tables in action and try one out for yourself, you can download our demonstration spreadsheet for tracking business expenses. To download and access the spreadsheet, follow these steps:
||*Right-click the link below and choose Save Link/Target As. This will download a zip file. We recommend you save the zip file into a new folder on your hard disk.
||Unzip the content of the downloaded file into the new folder. This will extract two files.
||Open the spreadsheet "Excel Works Demo - Business Expense Tracking" and enable macros.
(*Right-click: click the right button on your mouse)
To learn how to use our tables see the User Guide to our Tables
Our Tables Explained
This image shows our demonstration table for tracking business expenses
Our tables are based on a macro-driven, generic Excel table template. From this template we can derive any kind of Excel table to a customer's unique specification. The template's compact structure provides all features easily accessible in one place. The robust table framework prevents accidental damage and protects the table's data. Our tables can be used on their own or can be integrated into Excel applications (where they could provide efficient data-entry mechanisms).
Features and Facts:
Back To Top
The title reflects the table's content and serves as a link, bringing the cursor back to the top record.
Back To Index
New records are added through the Data-Input Row above the Column Headings, making it clear what data to enter. This design of adding records on top of the table saves you time from having to scroll down to the last record to add a new one. The Data-Input Row and Column Headings don't move and always stay in view.
Data in the Data-Input Row is automatically checked before it is accepted and added to the table. Individual columns can be designed to require a data-entry or to accept only a range of suggested values (using data validation lists in other tables). New data won't be accepted unless the user has entered all required values. Accepted records are added to the end of the table and brought in view to allow a visual double-check. New records are automatically formatted with the rest of the table to maintain a consistent appearance.
After adding a record, the Data-Input Row gets cleared and prepared for the next one. If required it can populate cells with default values/formulas or keep entries that don't change between records. This reduces typing, saving you time and minimising errors.
For more information see the User Guide - Adding Records
Back To Index
Changing and Deleting Records
Records can be changed and deleted directly in the table as you would in other Excel tables. If needed, we can customise the table's behaviour to prevent users from changing values in individual columns or deleting records. For more information see the User Guide - Changing Records or User Guide - Deleting Records
Back To Index
Filtering and Finding Records
This image shows a filtered table with criteria in three columns
The integrated filter helps you identify and find records of interest quickly. Simply type your criteria into the Filter Row and the table will instantly show the matching records. The more specific your criteria, the narrower your results will be. To see all records again, clear the Filter Row.
Filter criteria are entered and changed directly in the cells of the Filter Row. The criteria always stay visible, making it clear which columns and what records are being filtered. The Filter Row accepts wildcard characters and comparison operators, allowing the composition of flexible filter criteria. For example, the asterisk (*) wildcard character in the example above finds everything that starts, contains or ends with "software".
For more information see the User Guide - Filtering Records
Back To Index
Sorting and Organising Records
Our table’s sort features help you organise your data with just a few clicks.
A quick-sort feature sorts a single column when you double-click its heading. An additional sort form offers advanced five-column sort options (see image on the left). Your sort options are automatically stored so you can always bring back your data into a standard sort order.
Sorting ordinary Excel tables can be dangerous. When sorting a table people sometimes miss to include all columns. This will result in a table with some columns sorted and others in the old order. Tables like these are very difficult to restore. Our tables won't allow this to happen.
Another problem arises when you make the wrong choice and tell Excel's sort form that your table has no column headings when in fact it has (or the other way around). The result is again a wrecked table.
Our tables avoid both of these problems. They will always include all columns in the sort, and spare you from problematic choices. With our tables, sorting will be simple and safe.
For more information see the User Guide - Sorting Records
Back To Index
Records are stored and structured in a way that they can be readily analysed with Excel's built-in features. You can use Excel's quick-totals in the status bar, the powerful PivotTable analysis tool, or formulas and functions on another worksheet. For more information see the User Guide - Analysing Records
Back To Index
Features for handling the data are easily accessible through a right-click menu (see image below).
There are just as many features and options exposed as required for the task. People across all Excel skill levels can work easily with our tables. There's no need for specific Excel knowledge apart from using Excel's PivotTable tool for advanced data analysis.
If you need a custom-built table with additional features, get in touch.
We can tailor the structure and behaviour of our tables to meet your unique requirements.
Back To Index
Our tables work with Excel 2000 and later versions.
Each table is built with a fixed number of columns, depending on the data it holds, and is located on its own worksheet. It can store almost as many records as there are rows in Excel. However, we don't recommend using Excel to store that amount of data (*see note further down). The table's structure is protected to prevent it from accidental damage. A workbook can contain any number of tables functioning independently or being connected to others (for example for look-ups used in data validation). Ordinary worksheets can be used to access and analyse the data or present it in different ways.
Our tables are controlled by programming code contained in a separate file and nicely separated from your data. If we add new features or fix something, we simply update the code and put it up for you to download. All you need to do is download the updated file and put it in place of the old one. Your data will never be affected.
*Even though newer versions of Excel can hold more than a million records, spreadsheets are not meant to replace a database. Trying to solve a problem with Excel that actually requires a database will sooner or later backfire and cause you trouble handling your data. If your situation calls for a database, you should use one. If you are not sure, get in touch so we can point you in the right direction.
Back To Top | Back To Index