By Riaan Bekker, Force Solutions Manager at thryve
Spreadsheets are very useful. Yes, they can be very narrowly defined and even encourage the silo cultures that exist between departments or teams. But they are also a very well-understood and widely-used business tool.
Whether you want to use data more holistically or if you wish to establish a real-time data-driven business, spreadsheets are an important part of the process. You may choose to integrate them more closely with your data operations or aim to phase them out. Whatever the case, spreadsheets shouldn’t be ignored.
You can easily import spreadsheet data into leading business services such as Salesforce, Riskonnect or Tableau. I have compiled a few points to consider that will make this process a lot easier.
What do you need done?
There are various reasons why you’d want to import spreadsheet data into other places. You may want to aggregate data, meaning you are collecting from different spreadsheets and putting that data into a central source, then have your employees use those tools. You might need bi-directional data movement, where the data changes made on the one side is sent back to the spreadsheets. Or you might want to keep the spreadsheet and its data, but analyse it on a visualisation platform that will treat the spreadsheet as the source. Knowing what you want to do will define how to manage your spreadsheets and which features you need in the software on the receiving end.
To Excel or not Excel?
You will most likely be using spreadsheets in Microsoft Excel, or perhaps an alternative such as LibreOffice. That’s all fine, but you should put some thought towards how you save these files. The XLS format used by Excel is proprietary, and older copies might not be usable in the future. This issue applies to just about any spreadsheet software’s preferred file format. If you want to ensure your data is readable later down the line, make a habit of exporting copies into CSV format. You can still keep the Excel files, but CSV files are useful to have for future uses or to import more easily into different applications.
Does your service provider offer import tools?
Best of breed services that use your data should offer tools to make spreadsheet imports easy. Platforms such as Salesforce and Tableau provide a wide variety of import tools, some which can also reformat your data to fit best with the platform. Certain import tools also support bi-directional data flow, enabling the platform service to update the spreadsheet. In some cases, such as with Riskonnect, the imports can be customised, then standardised through a weighting process. But if you find yourself having to add spreadsheet data into a system manually, then you’re doing it wrong, and that system is not doing its job.
Are you using the right templates?
Data is all about formatting. If a service can read the data format it’s stored in, that will make your life so much easier. This is why it’s important to use the right templates. A spreadsheet can be very compatible with whatever data analytics or management service as long as you apply the correct templates. Most reputable vendors will supply such templates, and a solutions provider such as thryve can help implement those and create buy-in among your staff.
What about formulas and pivot tables?
There is one unfortunate reality about spreadsheets: their proprietary format makes it difficult to translate elements such as formulas and pivot tables into a language other systems can understand. This is one reason why CSV files are very useful for importing data, as they only reflect the final output of data and not the formulas underneath. So, when importing data, you cannot rely on those formulas and pivot tables to be transferred as well. In cases where the spreadsheet remains the data source, such as with data visualisation services such as Tableau, the changes made on the spreadsheet will reflect on the data output of the service. If the output formulas and pivot tables are key to understanding your data, look for services that let you use spreadsheets in parallel with them.