When Excel isn't enough - taking better control of your data
Microsoft Excel is equal parts powerful and daunting. Used correctly, it allows skilled users to store, sort, manipulate, and report on impressively complex datasets. In the wrong hands, however, it can be prone to user-error, slow-moving, and unreliable if not kept in check. Just ask Public Health England this week.
Many startups use Excel or one of its cloud-based cousins to get things off the ground. From inventory management to content planning to game design, it’s a flexible and entirely adequate solution that’s both low-cost and readily available to everyone. Still, there comes a point where “good enough” isn’t actually good enough.
There are some clear warning signs that can tell you that spreadsheets are holding your business back. You may start to notice more and more time is spent digging through rows of data to find the correct record. The spreadsheet itself may start to chug, stumbling under its own weight as it struggles to load everything in. Errors can start to creep in when a formula goes awry thanks to an innocent typo here, a bit of data moved there. It’s important to recognise these signs before it’s too late as they have a tendency to snowball.
This is typically where an agency like Gravitywell can help. Many of our projects begin life within the columns and rows of Excel and it’s our job to take that data and find a better, more robust home for it in a more powerful and accessible database system. How we do that varies between clients and use cases but there are a few key learnings that we’d like to share to make the transition as easy as possible.
This is often the first place that spreadsheet databases start to come apart. As businesses grow, so does their data and it can be difficult to alter a system when your only options are to add more columns or sheets. Data can end up spread out too far to make sense of or lumped together in a single place, making it a chore to navigate and parse.
A better solution is to start work on normalising your data by separating it into its constituent parts. Find and weed out any redundancy - columns that mean the same thing, rows repeated between sections etc. so that you’re left with the most basic building blocks of your system. With that, you can get to know the relationships between everything and start connecting them together in more meaningful ways.
Through this process, your data takes on its own unique shape that’s unconstrained by a basic set of columns and rows. It also makes it easier to make changes later on, adding to, removing, or editing items as your needs and resources evolve.
A well-designed and maintained database is only as large as it needs to be. The move from a spreadsheet-based system is a perfect opportunity to perform an audit and trim some of the fat. Often when we receive a first draft spreadsheet that needs to be converted into a database we find we have to be fairly ruthless. Do you need this extra reference when another already exists? Can we group these fields under a single, reusable value? Is it necessary to split out every line of an address?
It’s also useful at this stage to think about the ways in which you’ll be using the data. Any fields that you don’t need to see or use can often be dropped without issue. The less you have stored, the less time you need to spend maintaining it.
While part of this comes down to tech and what kind of budget you have for your project, it actually starts with your data. Identifying the areas that you know are likely to grow in the future can help ensure that your system takes on a shape that allows for it. For research, take a look at the queries and formulae you use most frequently in your current setup. Information like this is invaluable when it comes time to start building a database as the priority is finding the fastest possible tools and structure to retrieve and manage exactly the data you need.
A database, regardless of where or how it’s stored, is a high-value asset for your business that needs to be protected. Whether you’re managing sensitive user data, customer orders, or business-specific content, it’s the heart of your digital ecosystem. The platforms we use come with their own security features and protections but your data can do a lot to keep itself safe as well.
First, there are the obvious factors - don’t store passwords as text, let banking information be handled by separate, regulated systems, and only store what is absolutely necessary to make GDPR compliance easier. Second, think about what information you’re storing and how it will be queried. It might be helpful to organise your data into levels of privilege and sensitivity. From this, a database designer can start to think about how to separate it out - keeping different concerns apart so that, for example, it would be impossible for customers or low-level employees to access more information than what is absolutely necessary.
Embrace the chance to take responsibility of your data
Moving away from Excel can be a big step. It signifies that your business or project has grown from an idea into a living, growing system that needs additional care and attention (as well as investment) to keep it working well. At the same time, it’s an opportunity for you to take full control of the knowledge and resources you’ve built up and make it work for you with absolute reliability. We believe that working with your data should be easy and enjoyable, not something that triggers a sense of dread every time you see the Excel icon.