On an uneventful morning three years ago you decided to create an innocent little Excel spreadsheet. The idea was to collect data on the applicants for the only position you had open at that time in your 10-people company. It is now 2019 and your team has grown to 150 employees. The spreadsheet has since turned into a monster of information, tracking hiring practices, candidates’ profiles, hires’ qualifications, teams’ quarterly performance evaluations, and at least a dozen other data points.
That’s an all-too-common situation for companies that go through an even the slowest of growths over time. You start collecting data and, before you know it, things escalate. More and more data points are perceived as “relevant” and it becomes all too obvious that a spreadsheet that takes a few minutes just to load can’t be scaled any further.
While certainly a useful tool with powerful capabilities, spreadsheets are not an optimal way to store and access data. Trying to add multiple levels of access to data, manage data entry by various parties, run assessments, track data accuracy, visualize information – spreadsheets have a tendency to outgrow their usefulness pretty fast. Their lack of transparency and failure to comply with data storage and management requirements turn them into a liability. At one point you need a smarter tool to support decision making processes. The question then becomes: how do you take your existing spreadsheet and turn it into something scalable?
Break down your data needs into action points
This is potentially the most difficult, yet crucial step. When starting on data projects, what we see many companies struggle with is identifying relevant business questions they want answered with their data. Questions that are too general may not yield business value. Questions that are too narrow may result in biased answers. That’s why it’s important at this stage to take inventory of your data and map out potential future uses of various data points. For example, which departments will benefit from sales numbers? Who would be most in need of hiring information? Which team will derive the most value out of the accounts receivables column?
The good news is that questions you ask of your data at this point do not have to be anything more than preliminary. They are used simply as a way to slice and dice your gigantic spreadsheet into more manageable bits.
Identify independent and dependent data points
One of the most challenging issues about migrating data out of a spreadsheet, especially one that’s been used for years and contains layers upon layers of information, is untangling the web of formulas. That’s why you should put quality time and effort into breaking down your spreadsheet into independent and dependent data points. Independent data, as the name implies, is the one you input as it becomes available. For example, the value of each sale, the amount you spend on advertising for each channel, or the amount of money you spend on IT every month. Dependent variables are the ones that are automatically calculated based on data input. Here we have such numbers as the average value of a sale, the ROI of advertising per channel, and the percentage of monthly budget spent on IT.
By separating independent variables from the dependent ones you can significantly clean up your data. As you go through the exercise, make sure to document which dependent data points you are eliminating and how they were calculated in the first place. This will most likely come in handy when you transition your spreadsheet into multiple, more scalable systems.
Clean up your data
If you’ve been using your spreadsheet for years, there’s a good chance that it will need a thorough cleaning before you can use it as a base for your evolving data needs. Take a close look at each row and column. Do you have any missing values? If so, why? Is it possible to find and add that data? Do not underestimate the impact bad data can have on your future business processes.
Back up your spreadsheet
In this day and age, backing up your data may sound like common sense, but you’d be surprised how many people forget or intentionally skip this step. Whether you plan on breaking down your massive spreadsheet into 5-10 more manageable spreadsheets or transitioning part of the data from it onto a software, having a back-up of it is crucial if at one point you need to backtrack.
Look for a set of solutions
Now that your data is prepped, it’s time to find it a new home. The mistake we often see companies make is trying to find a solution that would address all their needs simultaneously. What that often leads to is purchasing a bulky, expensive piece of software, which is not user-friendly. You end up with a poor adoption rate and go back to using your good old spreadsheet.
Instead, start small. Remember how in step 1 we advised you to go through the exercise of identifying specific business questions you want your data to address? Now is the time to look for solutions that cater to those specific needs. Don’t force your finance department to use the same tool your sales team uses, or your IT to input data in the same software that your marketing team works with on a daily basis.
Connect the data dots
Last but not least, you need to create bridges between your data points. Don’t let it get siloed within separate pieces of software. Data needs to be mobile. You also don’t want to have to input the same data into multiple solutions. That’s a waste of time and something will always get lost in the process. That’s why you should create automatic bridges between your solutions.
Some pieces of software already come with various integrations. For simple data integration processes you can also use such workflow automation solutions as Zapier. That way you can decide which data should be communicated across multiple teams or departments and set up integrations, which will automatically push certain pieces of information across teams and solutions. More complex data systems and integrations, however, should be set up by specialists in order to ensure your system’s scalability and ease of use.
While spreadsheets have numerous disadvantages and, at one point or another, have to be abandoned completely, they are the cornerstone of data for most companies. A spreadsheet is usually the first tool used for basic data analysis. It’s a reliable way of visualizing data and tracking trends over time. That’s why most software solutions on the market allow for data import and export via spreadsheets. So thank your Excel program for the job well done and prepare your data for its next home.