We’ve appeared at the pitfalls of utilizing a spreadsheet application these types of as Excel to retail store lists of data. This technique could look like the very best alternative at 1st, but you can operate into problems sharing that data with numerous customers, validating the content material or even navigating your data. Why? Because you are utilizing a software that wasn’t built to do the position.

Now we’ll think about an imaginary (but regular) situation of a enterprise utilizing a spreadsheet-primarily based listing, and look at how this could be converted to a database application to prevail over these types of problems.

How workbooks get out of hand

Our listing commenced as a easy report of projects carried out for shoppers. As the business grew, so too did the selection of shoppers, with names and speak to information additional to the workbook. Also, some way was wanted of recording what different members of team were executing on these projects, so even extra data was additional into this workbook.

At this point the spreadsheet technique grew to become unworkable: there were considerably too lots of people today making an attempt to maintain it up to day, typically at the exact time. The business tried using instituting a rota, so that people today took it in turns to update the workbook, but this intended that some tasks were forgotten about ahead of they were recorded.

In the stop, people today set up their very own workbooks to maintain keep track of of their tasks, at times remembering to duplicate the data into the major workbook at the stop of the 7 days. Staff made their very own shorthand for these publications, and some modified the formatting and the get of the columns to suit their way of doing the job. Copying this data into the major workbook resulted in a awful mess.

This could be a made-up example, but I have actually witnessed all of these practices in true lifestyle. Let’s take a nearer look at some of the concerns thrown up by this technique of doing the job.


A good deal of problems

You can see the 1st sheet of our imaginary spreadsheet. The 1st column information the name of the venture to which every single entry refers. Some of these names are very long, however, so team could have been tempted to use abbreviations as a result, typos have crept in. This tends to make it challenging to tie up which tasks belong to which venture. The alternative does not have to be challenging: you could choose a limited name for every single venture that everybody agrees on, or give every single venture an ID selection and translate this to the venture name routinely.

There’s a related problem with the Started out column. Some cells comprise a day, but others report only a thirty day period – and just one or two information just say “Yes”. Excel does assist data validation, so it is attainable to make certain that specific cells always comprise data of a specific kind – but when a spreadsheet is made in an advert hoc style, it is rarely used.

“At this point the spreadsheet technique gets unworkable: there were considerably too lots of people today making an attempt to maintain it up to day”

You will not have this problem in a database application, given that the data kind of the subject will be preset from the outset. If you never know the correct day when operate commenced, you can use the 1st of the thirty day period, or 1 January if you only know the calendar year. If the venture hasn’t nonetheless been started off, you could possibly leave the subject blank – a NULL in database conditions. If you understood the venture had been started off but didn’t know when, you can use a day that would ordinarily be difficult for your data, these types of as 1/1/1900. Straight away it gets simple to form projects and attain a chronological overview of exercise.

A extra subtle challenge is presented by the column labelled Customer. The entries in this column are not linked to anything at all else in the workbook, but there’s a listing of Prospects on Sheet 1, which is probably what it refers to. Storing numerous lists of the exact things, referred to by various names, is complicated. You require to clarify the naming and settle on an unambiguous name for this entity: are they shoppers or consumers?

The Standing column is an additional just one where by there’s been no validation, so people today have yet again opted to produce regardless of what they want. It would be better to build a limited listing of all the permissible values.

The 2nd sheet – Sheet 1 – is just as problematic. For a start, the sheet name isn’t descriptive. What it actually contains is a listing headed Prospects, but this isn’t formatted as a desk in Excel: the handle is in just one subject, which restrictions your capacity to use Excel’s developed-in resources to search or form it. You could, for example, filter for addresses that comprise “Cardiff”, but the benefits would also include all those on Cardiff Street in Newport.

When it will come to addresses, the very best technique is to use individual fields for the postcode, county, metropolis, and road (while county data is optional for United kingdom addresses – see No counties, please, we’re British). Avenue need to comprise every little thing that isn’t in the other components of the handle.

There’s a Make contact with subject, which presents problems too. Exactly where we have a number of contacts within a single-shopper enterprise, their names have all been lumped into this subject, with their telephone quantities and e mail addresses equally positioned into the other fields. Separating these out will be challenging – particularly if there are three names in the Make contact with subject but only two telephone quantities.

The last column in this sheet is headed Past Contacted: workforce are meant to update this every single time they make speak to with a buyer. Considering that this data is an excess factor for the worker to remember, and there’s no promise they will – particularly given that it is hidden out of the way on a 2nd sheet– it is unreliable. This is seriously one thing the computer need to be monitoring routinely.

Ultimately we come to the Responsibilities sheets, which element the tasks and reviews for every single worker. These are not named continuously, and never comprise the exact columns in the exact get. Although it tends to make feeling for unique customers to enter their data on their very own sheets, the lack of coherence tends to make it challenging to collate and analyse the data. When a manager wants to see what operate has been accomplished on every single venture, for example, all the tasks have to be copied by hand from the unique sheets into just one listing ahead of they can be sorted and described on.


Setting up your database

Sorting out these concerns will take some operate, probably a number of days. Considering that customers will probably have to continue to use the outdated technique whilst we’re developing a new just one, it is very best to make a duplicate of the current workbooks from which to operate. This implies we’ll want to doc each individual action in changing the data, so we can speedily do it yet again when the time will come to change more than to the new technique.

The 1st factor you require to do is clean the data in your Excel workbook. Employing Come across & Switch can enable, and you need to delete any column or row that does not comprise data (except for the column heading row, which must be kept). Increase an ID column to every single sheet, in column A, and populate it with incremental quantities by typing 1 in the 1st cell, deciding upon to the bottom of the data (Change+Conclude, Down) then utilizing the Fill Down command (Ctrl+D). Create a learn listing of venture names, and anywhere a venture name is recorded, use the VLookup() operate to confirm its learn ID selection if there’s no selection, there’s an inconsistency in your data.

The moment your data is clean, it is time to design a new database to keep it. We’ll use Accessibility 2013, for the reason that in our theoretical example it is available to all our customers through our Business office 365 membership. When you create a new Accessibility database, you get a choice of building it as an Accessibility Internet App or an Accessibility Desktop Databases. Internet Apps have a simplified interface and can be used only if you have Business office 365 with SharePoint On-line or SharePoint Server 2013 with Accessibility Solutions and SQL Server 2012. We’ll use the traditional Desktop Databases, given that it presents extra selections and larger regulate more than the person encounter. 


Choose to create a new Desktop Databases and name it: Accessibility results in a new desk referred to as “Table 1”, and places you into the Style and design Perspective with just one column, referred to as “ID”. In this article you can design the tables you are going to require in your database. Just about every desk need to have an ID subject (an routinely incremental integer), but to stay clear of confusion it is very best to give it a extra descriptive name. In the Assignments desk it would be “ProjectID”, “CustomerID” in the Prospects desk, and so on.

You can set the data kind for each individual column created, and you require to give every single column a name and set any other homes and formatting as acceptable for the subject. As with the ID subject, make positive the column names make it obvious what data need to go in the subject – so, for example, use ProjectName somewhat than just Title, DueDate somewhat than Owing. You can use the Title & Caption button on the ribbon to create an abbreviated caption as nicely as the specific name. You can use areas in column names, but you are going to have to surround them with square brackets when crafting queries and stories.

“Although it tends to make feeling for customers to enter their data on their very own sheets, the lack of coherence tends to make it challenging to analyse”

Set the formatting on columns these types of as PercentageComplete to be Per cent and dates to be ShortDate, and also the highest size of textual content fields to a reasonable worth, or they’ll all be 255 people very long. Try to remember that some terms (these types of as Date) are reserved, so you can’t use them as column names: use TaskDate or one thing else extra descriptive as a substitute.

When it will come to columns where by you want to look up a worth in an additional desk (these types of as the Purchaser column in the Assignments desk), outline all those other tables in Accessibility ahead of you insert the lookup column. When it will come to Standing, the simplest solution is to just kind the values to be proven in the dropdown listing – but this tends to make it challenging to insert or edit the listing of attainable values later on. Except if you are dealing with a limited listing where by attainable values are unlikely to adjust – these types of as a subject recording someone’s intercourse – it is a better strategy to create an additional desk for entries these types of as ProjectStatus. This lets you to quickly insert excess selections to the listing in future with no a programming adjust.


Although we’re designing our database, we can put into practice enhancements more than the outdated spreadsheet-primarily based way of executing things. Just one criticism our customers had with their Excel workbooks was that every single task contained only just one cell for reviews, and at times they wanted to make extra than just one comment on a task – or, the supervisor wanted to make a comment about a task and then the person reply to this. Cramming every little thing into a single cell made it challenging to see when, and by whom, reviews were made. We can do better by building a individual desk for reviews, linked to the Responsibilities desk. In this way, every single task can have as lots of reviews as essential, with individual fields for the day, username and textual content of every single just one.

A further improvement we can make is to set entries these types of as ProjectStatus to display screen in a specific get, somewhat than alphabetically – for example, you could possibly want “Completed” to go at the bottom of the listing. To do this, insert a DisplayOrder column and use it to form the lookup listing. Really don’t be tempted to use the ID subject with this, any new information could only go on the stop of the listing.

To make certain our data stays clean, we can mark fields that the person must fill in as “Required”, and insert validation to make certain that the data entered is in the accurate variety. You can make lifestyle less difficult by setting reasonable default values: the CommentDate subject on the Remarks desk could have its default worth set to “=Date()”, which will routinely set it to today’s day anytime a new Remark is created. You can use validation together with a “Withdrawn” column in a desk (a Boolean) to halt customers including new information with certain values. This lets you to maintain historic values that used to be legitimate, but that are not used any extra. These capabilities can all be found on the Desk Applications | Fields tab on the ribbon or in the Subject Qualities in Desk Style and design Perspective.

Importing your data

The moment your tables are set up, you can use the External Details | Import & Website link | Excel button on the ribbon to append the data from your Excel workbook to the tables in your Accessibility database. Make a backup of your blank Accessibility database ahead of you start, in situation anything at all goes mistaken, and start by populating the tiny tables by hand if essential. Consider an additional backup at the time this is accomplished, so you can get back to this point if anything at all goes mistaken in the subsequent actions.

Now import the major tables that never rely on any other tables, these types of as Prospects, ahead of ending with the tables that do have interactions, these types of as Assignments and Responsibilities. If you rearrange and rename the columns in your Excel workbook to match the fields in your Accessibility database as carefully as attainable, you should not have any problem importing the data. Try to remember to make a note of every little thing you do so you can repeat it later on if you require to transform the data yet again.

The moment the data is imported, the tables in Datasheet Perspective need to operate much as the Excel worksheets did – but with much better data validation, hunting and sorting. If you desire, you can now start to design new varieties and stories primarily based on this data: for example, a Learn/Detail variety for Assignments could possibly exhibit the data of just one Venture at the leading of the variety and a grid of the Responsibilities for that venture at the bottom.

You could also set up a “My Tasks” variety that lists all the outstanding tasks for the recent person and an Overdue Responsibilities report that lists all the outstanding tasks for all customers that are previous their owing day.


No counties, please, we’re British

If you are storing addresses in your database, it is significant to have an understanding of what data you actually require. While county data can be useful for internet marketing – and could be wanted for some abroad addresses – it is no for a longer time officially used in United kingdom addresses.

The reason is that United kingdom postal addresses rely on the concept of a “post town”, where by write-up for you is despatched and sorted ahead of it is delivered to your doorway. Not all towns or villages are served by write-up towns in the exact county – for example, Melbourn (in Cambridgeshire) will get its mail through Royston (in Hertfordshire) – so specifying a county in the handle does not essentially enable anybody.

To stay clear of confusion, the Write-up Business office stopped utilizing counties in addresses back in 1996, relying on postcode data as a substitute – and by 2016, it options to remove county names from the “alias data file” of supplementary handle data. So, if you include a county in a United kingdom handle it will only be disregarded.