BWL Construction Consultants outline why the success of your project may rely on a simple spreadsheet

Our team of Quantity Surveyors use excel spreadsheets daily. In fact, across a range of industries, spreadsheets are one of the most widely used tools today. As assembling data into rows and columns is still one of the best formats for collecting, transforming, analysing, and reporting information, it is an essential tool for any business.

Spreadsheets also play a crucial role in decision-making. As human beings, we tend to think in pictures and communicate in words but make decisions using numbers. This means spreadsheets are not only a powerful method for influencing decisions but can be inherently risky if they guide you to the wrong decision. This can occur if spreadsheet data becomes confused, over-complicated, or is prepared by people untrained in setting up and using spreadsheets effectively.

For our Quantity Surveyors, development proposals and contract administration decisions are made more accessible if attention is paid to the data being managed. Establishing good practices will save time, eliminate frustration, and provide more accurate information.

Here are some tips for creating usable and practical spreadsheets:

Start with the end in mind

Start by asking yourself:

1. What is it that you are producing?
2. Who is the intended audience?
3. What information do they need?

This will help you focus on the desired outcome of the exercise, which will guide how you present your data. Placing data into a spreadsheet without considering these elements may lead you to overlook a necessary aspect and miscommunicate your intended purpose.

Collect your data in a tabular format

A common mistake that people make is formatting their spreadsheet like a report, focusing on the presentation of the spreadsheet rather than its readability. Avoid formatting based on visual appearance and adopt a simple, logical, tabular structure that can be processed readily by the software and followed easily by the human eye.

Remember, if your information is poorly structured, you are likely to encounter errors when applying formulas and processing the data. Ensure that you:

1. Make sure all header rows are the same;
2. Check every row and column contains data;
3. Do not merge cells.

Use data validation

Any manual input for data to a spreadsheet creates the possibility of error. To minimise this risk and ensure consistency of entry, use data validation.

For example, an errant space at the end of a piece of text is visually difficult to detect for humans; however, it will be interpreted differently by a computer. Applying data validation to your spreadsheet will ensure that people entering data don’t have to type everything manually and, as a result, will make fewer mistakes.

Keep your reference data and your activity data separate

Reference data is content that is fixed or doesn’t change very often. For example, a Contract Sum Analysis may comprise the description and quantities of the work. Activity data is the data you apply to the reference data, such as the percentages of work completed every month. Keep your reference data separate from the activity data. Reference data doesn’t change, whereas activity data does change. If these aren’t kept separate, there is a risk of confusing the logic of the spreadsheet.