5 Best practices for spreadsheet data structure

How good spreadsheet data structure helps you work smarter

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. Microsoft created Excel back in the late 1980s and it still dominates the market although Google Sheets is catching up. As an online spreadsheet tool, Sheets has much (although not all) of the functionality of Excel, combined with the power of real-time updates and collaborative functions. No matter which program you use, or which data you’re working with, good spreadsheet data structure can transform your work, boosting productivity and providing you with better data.

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.

A host of new online applications aimed at helping people to organize and manage their data, such as Airtable, even mimic the spreadsheet — but with 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 other software is leading to more spreadsheets. As companies generate more and more data, from more and more sources, spreadsheets are still the most simple, user-friendly and accessible place to centralize and process that data. No matter which software you use (Google Analytics, Asana, Shopify, CRMs like Hubspot or ERPs like SAP and Hyperion…) it’s highly likely that you store, share and process a lot of that data in spreadsheets.

In this sense, spreadsheets function as an important connecting tool, like glue, between software.

And they play a crucial role in decision-making. In fact, 70% of companies still make key decisions based on data from spreadsheets. Read on to find out how to follow best practices for spreadsheet data structure and make sure that the decisions you make are the right ones for your business.

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. Writing 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.

The main reason that spreadsheets are seen as so dangerous? The risk of manual error combined with poor spreadsheet design, overly complex spreadsheets and a 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, looked at a batch of 15,000 Excel spreadsheets from one company. She discovered that 24% of the spreadsheets with formulas contained errors, and that 6% of all email conversations were actually discussing spreadsheet errors.

This does not have to be the case. The incredible flexibility of spreadsheets and their universality means that they are here to stay, but we need to use them better. Luckily this is becoming easier with online spreadsheets such as Google Sheets, as they can be used in more streamlined, automated systems.

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

Here we’ve got 5 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 final 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? (a single number 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’ve planned what you want to visualize (the goal) 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 look like a report.

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.

It needs to have a logical, tabular format that can be processed correctly by the software!

If your data is unstructured, you will encounter problems applying formulas and processing your data. Plus you’ll get errors.

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

Any manual input in a spreadsheet increases the chance of errors, so remember to minimize this risk by using data validation. This is a really simple and effective way to make sure your data is correct and that it can be processed correctly.

Remember that technology is smart, but not as smart as us! A word can be typed slightly differently, and spaces and/or variation in keyboards and punctuation can cause errors. If you glance at a word or number in your spreadsheet, it might look the same (e.g., “Marketing”, ‘Marketing’,  ‘ Marketing’, 1000, 1,000, 1.000, the number “0”, and the letter “O”) but spreadsheet software will read this as different data.

Applying data validation to your spreadsheet ensures that people entering data in the spreadsheet don’t have to type anything manually. You do this by adding a dropdown menu to your input cells. Imagine you are using a column in your spreadsheet to track the status of orders. You can use data validation to create a dropdown menu, giving the options “pending”, “ordered” or “received”.

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 Google Forms

You might think of Google Forms as a survey app, but we recommend using it whenever you want to gather any kind of information from colleagues or customers. For internal employees, you can use it for things such as supply requests, job applications, IT, or vacation requests. For customers, it can be used for everything from orders to event registration. Google Forms not only save you time; they ensure accuracy. 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.

It’s also already in the correct format for transfer to your Google Sheet; read how to connect Forms to Sheets here.

The other advantage of forms is their user-friendliness. Let’s imagine that you’re a school administrator and you want to create a system for your teachers to report grades. You may be familiar with spreadsheets and regularly use them to analyze and collate data — but that might not be the most accessible data collection method for your teachers. Google Forms offer a much more user-friendly interface than a spreadsheet and you can import the data directly into a Sheet, collating the reports from your teachers automatically.

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 doesn’t change quickly over time. You need to have this data well organized so you can cross it with activity data. Imagine that you run a restaurant or a cosmetics company. You will need a database of recipes containing all your products, ingredients and quantities. This information is essential every time you generate a production order. It needs to be connected to your inventory system to calculate which ingredients are consumed, and in which quantities, for every single order.

It’s important that database data is kept isolated in a private or view-only spreadsheet — people must not be able to alter this data (either intentionally or accidentally). This guarantees data reliability and, if needed, data privacy. You may need to use multiple sets of database data for one specific process. You can bring them together in one single spreadsheet with multiple tabs, or if there’s a large volume of data (or different people need to maintain it) it’s better to keep it in separate spreadsheets.

Maintain one single source of truth

It’s really common to find the same data entered into multiple spreadsheets from a variety of silos, across the same organization. Single source of truth (SSOT) means structuring your data so that it is only created or changed in one place. 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’ share function) and make your spreadsheets talk to each other. Use a tool like Sheetgo to automate data transfer between spreadsheets, maintain one single source of truth and streamline data flows. This means you no longer need to manually copy-paste data or share spreadsheets as email attachments.

Activity data changes frequently. It’s the data generated by your processes.

This is the “current” data that reflects what’s happening in your business on a day-to-day basis. In an inside sales team, for instance, activity data might include the contact details and status of new leads. The team will be constantly logging and updating this information. In a finance process, 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 enter input activity data in a user-friendly way — but 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.

You can do this by:

• Using Google Forms.
• Integrating your software with a spreadsheet.
• Importing information to a specific folder.
• Using separate spreadsheets for individual users to prevent too many hands touching the same data.

Key takeaway: prepare your data, plan your system.

Spreadsheets play a key role in every business on the planet, and 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 organize and implement a clean and efficient spreadsheet data structure means we can 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.