Excel uses a grid structure for its interface for a good reason: Data is usually best collected and analyzed in a compact table to allow for easier analysis. Because of this, Excel is the platform of choice for collecting data in many applications like cost tracking, weekly timecards, or project management. In these types of scenarios, daily, weekly, or even monthly data can be entered and compared easily, in one location. Obviously one of the main advantages of tabular data entry is that you don’t have to transition to other pages or sections of the workbook to find the rest of the data.
Working with tables on web applications can be far more challenging than it is in Excel, the primary reason being the lack of real estate. A computer typically has a larger screen to work on, whereas mobile devices won’t let you see much at a glance. Let’s take the example of a project management spreadsheet like below.
 Project Page |
 Team Page |
 Tasks Page |
The workbook is pretty straightforward – there are 4 plain looking sheets in total. You can enter the project details on page 1, team members for that project on page 2, and on page 3, create the tasks and assign them to the team. The fourth sheet contains the list options and calculations for the ‘helper’ fields. This file is essentially the template that would be used for each project, and for each project, a new workbook needs to be saved.
The table on the third page is tied to the first table, and it pulls the hourly rates of each team member. The total cost of each project item entered into the third table is calculated. This means that every line item also comes with calculated fields attached to it.
The application below was created in SpreadsheetWEB and was designed to collect details like project deliverables, costs, and tasks. This application also allows saving data into the database using the Save buttons. Team member and task details are entered into separate tables along with additional information for each item.
On the second page, a project manager can set up the project team. Once the team members are entered, the team member names and salary details are passed on to the third page with the use of Excel formulas. This way, the project manager can then select the people responsible for each task and enter the estimated hours to find out about the total expected costs for a project.
If you were to do this in Excel, you would need a separate workbook for each project, and scalability would become a concern very quickly. This essentially means that each record saved in the database corresponds to one project in the web application. With a web-based solution, team members can also access this tool simultaneously and enter their information. Collected data is centralized, meaning that you won’t have to ask other users for the latest version of the workbook.
The input grid controls in designer applications can combine tabular data entry columns with formula columns, like in this workbook. This approach offers a lean way to organize application UI elements just like in Excel, and helps users see more at a glance. Have your time entry on an Excel spreadsheet? No problem, just copy that information and paste into the web application as you normally would. The input grids also allow copying data from external sources.
The data collected through Excel files is substantially harder to analyze, because it’s unstructured data. This means that you’re going to have to use tools like PowerQuery or even more formulas, just to make this data easy to read, and ready for reports. Data stored in a database, on the other hand, is structured, meaning that it can be used as is in most analysis tools. SpreadsheetWeb applications have built-in support for database integration, freeing you of the hassles of pulling your data from across Excel spreadsheets.
Placing each captured cell in a table format in a database could mean hundreds of columns containing your information, and this will result in poor performance, while also making data analysis more challenging. Using tabular data entry to collect data in a SpreadsheetWEB application results in an organized way to collect data through the use of multiple database tables. You can easily build a multi-table database for a designer application and automatically tie them to the primary table with IDs. This application has a primary table which contains the project details, while the team member and task grids data are kept on two separate secondary tables. The relationship is like below in the designer interface:
This structure will give an export like below when the data is downloaded:
The secondary tables are connected to the primary table through the ID numbers. This approach results in fewer database columns, a better database structure, and also allows for easily querying the application data across different projects.
Check out our video below on form-based input grids for more details on tabular data entry in SpreadsheetWEB!
