If your Excel file contains multiple spreadsheets with a very similar structure, this article is for you.
Whenever you started a new month data entry or added a new branch, you naturally added yet another spreadsheet to your Excel file, even though a new sheet data structure was still very similar to previous ones, if not even identical.
This is a very typical situation for businesses utilizing an Excel to manage their data.
Web-based database would allow organizing your data more structurally, making database management, data entering process and reports generation far more convenient and straight-forward.
While moving to a relational Web-based database, it is important to take a right approach from the very beginning rather than importing a messy set of sheets to separate tables.
Let’s consider another example when you have multiple spreadsheets (or workbooks) that contain the same sort of data, related to tracking your sales leads. We’ll show how to consolidate these multiple spreadsheets into a single table.
At first, this way of data storage may not seem obvious. Especially when the database is going to be built by uploading an Excel file.
A right approach would be importing such a data kept in multiple spreadsheets into a single table and then create multiple custom views with different filtering criteria. Furthermore, a data entering form may contain dynamically appearing sections.
Multiple spreadsheets
Let’s consider the following example. Say, you manage a driving school and keep clients’ data in the Excel file, consisting of three spreadsheets: Prospects, Applicants, Drivers.
The first spreadsheet keeps Prospect-related data records:
The second spreadsheet keeps Applicants-related data records:
The third spreadsheet keeps Drivers-related data records:
As ‘Prospects’ may potentially become clients, managers try to contact them and invite to apply for the driving school.
‘Applicants’ are the students taking driving lessons. When an applicant receives a driving license, he/she becomes a ‘Driver’. Therefore, each client progresses throw three stages: Prospect-Applicant-Driver.
Similar structure in one table
When you import this Excel file to an online database, three separate tables will be created automatically.
A better way, however, would be to import all these three spreadsheets into a single Clients table and then add a Status column, keeping record statuses: Prospect, Applicant or Driver.
You may also add such a column to each of your Excel sheets and populate it with an appropriate value to identify the sheet this column belongs to, such as Prospect, Applicant or Driver.
After importing these sheets into a single table you’ll be able to create separate views that display relevant columns for each client status.
As a result, all the records that were stored in three separate excel spreadsheets, are now kept in a single table and can be filtered in ‘Views’ by ‘Status’ value. Moreover, each view contains columns that are relevant to a corresponding status only.
You may also create separate sections on a data entry form. Each section will contain fields/columns that are relevant to each particular status and will appear on the form depending on the status selection. This may be achieved by configuring ‘Form Behavior’.
Prospect Form:
Applicant form:
Driver form:
The approach described in this post simplifies database structure and relations with other tables, while making database management, data entering process and reports generation far more convenient and straight-forward.
You may order a free trial of the above-mentioned Driving School template here >>
The same approach should be utilized when importing any Excel file containing multiple spreadsheets with an identical structure but keeping data for different locations, different months, etc.
These spreadsheets should be imported into a single table with an extra column, populated with a ‘location’, a ‘month’ or any similar identifier.
