Design Article

Questions to ask before designing a spreadsheet solution

Before starting any spreadsheet solution, there are some questions that should be answered, even if only in your head. How much time and effort you put into answering the questions will depend on your answers, so for very trivial cases, it might be a few seconds of mental evaluation, for a large corporate solution where you are going to outsource the development, it might take days and need a written proposal.

Using the 5Ws and 1H as starting point*

  • Why do we need a solution?
  • Who is it for and who is going to use it?
  • What does it need to do?
  • When is it needed by and when will it be no longer needed?
  • Where is the solution needed?
  • How is it going to work?
  • And perhaps most importantly - Is Excel the right solution?

As a really simple example let us suppose we want a list of items to buy for a new hamster from the pet store.

Let us look into the questions more deeply for non trivial cases

Why do we need a solution?

The key thing here is to understand why a solution is needed. For example managing your current account, the need is to make sure you know how much money you have, which in turn allows you to answer questions like; Are you likely to go overdrawn before the next pay cheque? Can you afford to make a luxury purchase or that dream holiday?

But be careful, particularly in business. Developing a solution will cost time and or money, so you want to make sure that it is going to bring some value to the business. A good challenge is to ask - So what if we did nothing?

What does it need to do?

The why question gives us a purpose for creating a solution and now we need to look at what needs to be done. If we think about managing your current account, then we might decide we need.

Be careful – and this can be hard – not to get stuck into the how just yet.

Who is it for and who is going to use it?

If it is for you and you alone then the final design can be as minimal as you are prepared to live with. But if there is any possibility it will be used by other people then you will need to strengthen the design. As a simple example, if the spreadsheet is just for you, you can get away with terse column names, but if more people are using it, you will need more meaningful names.

As the number of potential users goes up, the more thought you need to put into this question and you will need to consider the different types of users. Also the intelligence and Excel experience of the different users must be consider.

It may help to break the solution down to 4 potential work areas carried out by different users - or the same user. Although the different areas place different requirements on the solution, not all of these work areas will be required for every solution and certainly the boundaries may greatly overlap.

1 Data entry

You need to make it clear what data needs to be entered, and make data entry easy and as accurate as possible, particularly if there is a large amount. So the key here is good layout and good data checking either by using Excel’s data validation or a more sophisticated solution such as using Visual Basic powered forms.

2 Data collation

Essentially it is pulling together data from different sources into one area so that Excel can work with it. It is usually easier if the data is already in spreadsheets form, but often it exists in different files such as text, html, xml files, or in databases. Easy, efficient and reliable data import is the challenge. Also you may need to make your solution robust enough to cope with missing data – or at least find out who hasn’t sent in their contribution yet!

3 Data analysis

Analysis is getting meaningful results out of the data. Typically involving, sorting, filtering, condensing, visualising.

4 Data reporting

In many cases the person analysing the data is the person who is going to use it. But, particularly in a corporate environment, "the Management" may want to see the information. You may need to summarise the data even more, and possibly prettify how the results are presented – the amount of which tends to increase the higher up the corporate food chain the report is destined.

Consideration also needs to be made on how users are going to use the solution. For example, is each user going to have their own copy of the solution for their own use, or are they going to have spreadsheets to fill out which are then collated centrally, or will there be a shared workbook on a network resource than several people can access?

Lastly if the solution is being used by more than 1 person, you may need to consider security and data protection, in other words restricting what certain users can see and do with the spreadsheet.

When is it needed by and when will it be no longer needed?

If the solution is required really urgently, just get it done, and only afterwards, if it is going to be used again, tidy it up and make it more robust.

But the longevity of a solution, particular a good solution is something that can take us all by surprise. When you suspect a solution might exist for some time, put extra effort into making it a robust solution, making sure it works well, is easy to maintain (as you might be the person who has to keep fixing it). Remember any little niggle, is still going to be there unless you fix it.

Do not underestimate how long a good solution may end up being used.

Where is the solution needed?

Developing a solution that can be shared between 2 or 3 co-workers in the same office will be less demanding than one that is going to be deployed across several departments, sites or even countries. It is easy in an office to walk round and help the co-worker use the solution or fix an issue that has arisen. But if the co-workers are on a different continent, the only help you can give will be a combination of emails, and teleconferencing – unless you are really lucky and get a trip to the Miami office. So spend more time making sure the solution’s usage is really clear and make it as robust as possible. Also bear in mind you may have to cope with different currencies, different date styles and other internationalisation issues.

How is it going to work?

In an ideal world this question should only be answered when you have answered all the “W” questions above. But this is not an ideal world.

It is worth thinking through how you might develop the solution, and in particular try to identify difficult issues that you will need to tackle. You may need to try a few approaches out to see which method works best.

The last question - Is Excel the right solution

For the bigger designs particularly if multiuser or geographical diverse, or if the results are critical to the business - ask yourself is Excel the right solution? In some cases a database, web tool or off the shelf software might be more appropriate – just make sure you can get the data out into a spreadsheet if you need to.

In conclusion

Hopefully you now have some top level ideas on what to think about before diving in and implementing an Excel solution. For large scale solutions, these questions are just the tip of the iceberg in a process of requirement capture and analysis which is beyond our scope for these articles. However in the future we will look at various design issues and also the question of Excel's suitability as the right solution.

* The 5Ws and 1H - Who, Why, What, Where, When and How - are considered basic in information gathering requiring factual answers rather than just yes or no.

Back to the top