5 Best practices for spreadsheet data structure

Get a selection of expert articles

Spreadsheets are one of the most widely-used pieces of software in the world. Over 1 billion people use them to collect, process and report data every day. A large portion of those users still have a lot to learn to make the most of worksheets. They will usually get drawn to flashier features like formulas, but spreadsheet data structure is just as important.

Microsoft created Excel back in the late 1980s and it still dominates the market. Google Sheets is a strong contender but it’s still catching up. Sheets has many (although not all) of Excel’s features, but it also offers the power of real-time updates and collaborative functions.

Regardless of the program you use, or the data you’re working with, good spreadsheet structure can transform your work. Organizing your data will boost productivity and provide you with better insights.

Spreadsheets: the glue between applications

With all the new software out there, surely spreadsheets are becoming a thing of the past? You might think so, but research from Gartner shows that the number of spreadsheets is actually doubling every year. Among other things, this is because spreadsheets can be great bridges between different apps.

A lot of new online applications aimed to organize and manage data, such as Airtable, mimic spreadsheets. However, they package it in a more modern and user-friendly interface.

Why? Because columns and rows are still the best format for collecting, transforming, analyzing and reporting almost any form of data.

The proliferation of new software is leading to more spreadsheets. As companies generate ever more data, from ever more sources, spreadsheets still provide a solution that is simple, user-friendly and accessible. No matter what software you use, surely you store, share and process a lot of that data in spreadsheets.

Spreadsheets are often the glue between different software. They are multipurpose tools that are relatively accessible but still very powerful. 

The most dangerous tool in the world?

The sheer volume of data stored in spreadsheets — and the incredibly important decisions based on that data — has led to warnings about the dangers of spreadsheets, especially from the world of finance.

In Forbes, Economist Tim Worstall said that Excel might be the “most dangerous piece of software on the planet.” He said it was responsible for some painfully expensive decisions: the entire financialization of the economy over the past 30 years and therefore, indirectly, the financial crash of 2007.

Why are spreadsheets so dangerous? There isn’t a single culprit, but rather a combination of drawbacks:

  • Risk of manual error.
  • Poor spreadsheet design.
  • Lack of training.

A survey in the UK found that 33% of large businesses report poor decision-making due to spreadsheet problems, and 17% have suffered financial losses due to poor spreadsheets.

Another study, carried out in the US by spreadsheet expert Dr Felienne Hermans, analyzed 15,000 Excel spreadsheets from one company. She discovered that 24% of the spreadsheets with formulas had errors, and that 6% of emails were about spreadsheet issues.

Spreadsheets are here to stay, and making better use of them is critical. Luckily this is becoming easier with online options like Google Sheets that offer a centralized and team-oriented approach.

Good spreadsheet design will save you time and frustration, and of course, provide you with accurate data.

Best practices for good spreadsheet data structure

1. Always start at the end

As with any strategic activity, you can only plan a course of action when you have a clear goal in mind. It’s the same with spreadsheets and data systems.

Start by asking yourself:

  • What data do you want to see and in what format? (e.g. a chart, a graph, a single metric)
  • Who is the audience and how often do they need to view it? Is it a single metric to be checked weekly in a chart or a quarterly report for investors?

Focus on what data you want to see, not what data you already have. Design it with the audience in mind and remember that data must be presented in a format that’s easy to understand.

2. Collect data in a tabular format

Once you have a clear goal in mind you need to go back to the beginning and look at how you gather the data. A common mistake here is that people design input spreadsheets to emulate reports.

An input spreadsheet is where you collect raw data. That could be anything from a list of supplies received to a list of contact details. If your spreadsheet is being used for data collection and collation, don’t be tempted to make it look good.

If your spreadsheet is not structured correctly, you will encounter problems applying formulas and processing your data. Plus you’ll get errors.

When planning the structure of a spreadsheet, columns are for groups of related data. Your spreadsheet needs to have a logical, tabular format that can be processed correctly by the software!

For good spreadsheet data structure, remember:

  • Format your data like a database.
  • Make sure all header rows are the same.
  • Check that every row and column contains data.
  • Do not merge cells.

3. Data validation is your best friend

Manual data entry inevitably increases the chance of errors, you can minimize this risk by using data validation. This is a really simple and effective way to make sure that new data is introduced correctly.

Remember that technology is smart, but not as smart as you! A word can be typed differently, that means errors can be caused by spaces, keyboard variations and punctuation  (e.g., “Marketing”, “Marketing “,  “Markting”; 1000, 1,000, 1.000; the number “0” and the letter “O”). At a quick glance, cells might look the same but a slight difference will make spreadsheets interpret them as different data.

Applying data validation to your spreadsheet ensures that people entering data in the spreadsheet don’t have to type anything manually. For example, if a column in your spreadsheet tracks order status, you can reduce options to “pending”, “ordered” or “received”.

All you need to do is add a dropdown menu to your input cells. 

In Google Sheets go to Menu > Data > Data Validation.

In Excel, go to Data > Data Validation and select an option under Allow. You can read step-by-step instructions on data validation in Excel here.

4. Get safer input data with Sheetgo Forms

You might think of forms only as a way to conduct surveys. But we recommend using them whenever you want to gather any kind of information from colleagues or customers.

You can use them for a variety of purposes:

  • Supply requests
  • Keeping records of job applications
  • IT tickets
  • Requesting vacations
  • Submitting purchase orders

When someone enters data themselves into a form, it’s more likely that the details will be correct. No more spelling mistakes or wrongly entered phone numbers.

And this is where Sheetgo Forms come in, they are a great way to keep track of important data. They are also easy to create, you just have to select a spreadsheet and turn it into a form. The process automatically identifies the information type for each field. 

The other advantage of forms is their user-friendliness. Let’s say you’re a school administrator and you want to create a system for your teachers to report grades. You may be familiar with using spreadsheets to analyze data, but that might not be true for your teachers.

Sheetgo Forms offer a much more user-friendly interface than a spreadsheet and you can import the data directly into a Sheet, processing reports from your teachers automatically.

Check out everything Sheetgo Forms can do for your projects.

5. Keep your reference data and activity data separate

Database data is data that you use as reference.

This data is stable or fixed; it might change but at a much slower rate. Your database needs to be well organized so you can cross it with activity data.

Imagine you run a restaurant, you need a database of all your recipes including products, ingredients and quantities. This information is essential every time you generate a production order. Also, it needs to be connected to your inventory system to calculate the necessary ingredients to fulfill a single order.

It’s important that database data is kept isolated in a private or view-only spreadsheet. Only people who fully understand the system should be able to alter this data (either intentionally or accidentally). This guarantees data reliability and privacy.

You may need to use multiple sets of database data for one specific process. For smaller spreadsheets, it’s ok to group them together in a single spreadsheet with multiple tabs. But for larger datasets it’s better to keep them as separate spreadsheets.

Maintain one single source of truth

Single source of truth (SSOT) means structuring your data so that it is only created or changed in one place. It’s really common to find the same data entered into multiple spreadsheets from a variety of silos, across the same organization

When a data entry or update is made in one location, it should be updated everywhere. To do this with spreadsheets, you need to share files (for example, using Google Sheets) and make your spreadsheets talk to each other.

A tool like Sheetgo can help automate data transfer between spreadsheets to maintain one single source of truth. This means you no longer need to manually copy-paste data or share spreadsheets as email attachments.

Activity data changes frequently

Activity data is generated by your processes. This is the “current” data that reflects what’s happening in your business on a day-to-day basis.

In a sales team, for instance, activity data might include the contact details and status of new leads. The team will constantly log and update this information. In finance, activity data includes things like expenses, funding and loans.

Activity data must have a date (time) attached to it. This is essential for creating a dashboard or report.

It’s important that people can input activity data in a user-friendly way. But that’s not all, it’s also important that they do not interfere with another person’s data. At the same time, the data needs to be well structured so you can apply formulas for analysis.

There’s different ways to gather data efficiently:

  • Using Sheetgo Forms.
  • Integrating your software with a spreadsheet.
  • Importing information to a specific folder.
  • Using separate spreadsheets for each user.

Prepare your data, plan your system

Spreadsheets play a key role in every business. Thanks to the power of the cloud, it’s now easier to work with them more safely and productively than ever before.

Taking the time to implement a well organized spreadsheet data structure is really important. Doing this enables us to take advantage of all the benefits that sheets have to offer, while minimizing the risks.

Looking for more help and advice for working with spreadsheets?

Check out our posts on How to connect Google Sheets automatically , how to upload Excel to Google Sheets, how to improve the performance of Google sheets or how to create a dashboard.

Ready to streamline your spreadsheet data?

You may also like…