The Excel Hell: Using Spreadsheets For Exporting & Data Integration

Spreadsheets have made our lives a lot easier, but…​ Much like the “union” situation where you have layers of old systems, this dependence on them is not ideal. 

Stewy really does not like having to deal with all this fragmented data in spreadsheets
Two raccoons helping some employees sort through there data and compile what is important for them, so it can be put in a dashboard
A raccoon looking a little dismayed and confused about the general chaos in data, with a bunch of it's buddies trying to sort it out
Mr ringtail, the head sys admin racoon runs a tight ship, looking intently at the screen to make sure everything is going smoothly
Big Steve is dismayed by the amount of paper that is accumulated and needs to be compiled before a decision can be made.

Data integration from spreadsheets overview

Often there are just a few stand-alone systems that are in their original form, and perhaps still supported to varying degrees. But to get data, it needs to be compiled by a person who has to access the various sources and put it into one file. Often this data is presented in the form of a spreadsheet. 

.XLS files, the tired old work horse of the modern office

Often we see this dubious honor being fulfilled by the .xls file or a spreadsheet. While nice for calculating, making projects and budgets. It often gets used to do a whole lot more.

However there are obvious limitations to this approach, as Excel is not the easiest to handle at times, and it can’t do certain things well. Also, if you have a few systems it usually means there are a few points of contact for maintenance, and some vendors may not support older versions.

The risks of cloud "freemium" spreadsheets applications, Data Sponges or Sieves

Also, sometimes “free” services for cloud spreadsheets and or word processors are used as data repositories. Because they are indeed handy, and multiple people can use them at once. Are not ideal for storing sensitive data such as client data should be well protected. But “freemium” software is often free because it gets to use your data to make predictions, entice advertisers, and do other things with it. So from a data privacy point of view, these things are a big no-no.

Saying Goodbye to the Excel Hell: A KPI dashboard eliminates these problems

In this scenario, you don’t have a jumble of systems but perhaps a lack of automation coupled with some questionable privacy practices. So a unified dashboard is a great way of automating data collection safely and accurately. We just need to define what we want to measure and how. Then Dashcoon can help you consolidate systems that need to be secured and unify the data in a dashboard suitable for your organization.

A Scenario involving compiling from spreadsheets

Lets join Steve on his Spreadsheet Sojourn Throughout the Company

We once again join Steve on his journey throughout the company in search of files and data. Let’s imagine in this scenario he does not have to meet with people or request access and or files. But he has direct access.

Which saves him a lot of time, because he does not have to wait on people. But even if this is not the case, there are still plenty of other factors which could slow him down or cause errors in his report. Here he has to gather data from 3 sources to be able to give an over view picture with his report. But will these need to be compiled and curated by Steve. 

Various spreadsheets, with various standards and different logic

This is not always the easiest task, because every file export has a different structure. Sometimes Steve needs to pull the data out of a PDF file, or an Excel sheet. Because these come from different software databases, they don’t always have the same logic or structure. And Steve has to spend some time equating them to each other before he can back up his claims in the final report. Which is an arduous process and a great way to have errors sneak in. A lot of copying and pasting happens, different files, different structures and Steve might be pressed for time. Human error can creep in anytime.

No automation is a real boondoggle in all this spreadsheet Chaos

In this case, the SaaS Software they use for some process in the company has a nice API option. This often means the information is pumped out in a streamlined fashion with a few options rather than just numbers on a sheet. Unfortunately for Steve, the companies systems are not ready to use and receive this. A well-defined unified dashboard could take this into account and automatically do all the work for Steve. saving him a lot of time, and removing the risk of human error. 

Conclusions We Can Gleam From This Manual Spreadsheets Compilation Conundrum

  • This setup might not require asking a lot of people for data or access. However, it can still be time-consuming to make a report.
  • Human error can also creep in quite easily here. With the various spreadsheets copying and pasting and equating of data.
  • No “standard” is easily maintained for these files or the report. So, the ambiguity of the reported data persists.
  • Sensitive data can easily leak out with all these manual exports, or get left somewhere it should not be.
  • Some Bespoke Software and A good Data Dashboard can help save time and reduce errors across the organization.

Common spreadsheet data merging questions

Questions about importing data, merging data and different file types.

Solutions for importing PDF data into a spreadsheet

Using OCR software

One solution is OCR (optical character recognition) software. This type of software will try to get the contour of letters by “reading it”, rather than the usual 1s and 0s digitally that would be normal for computers to use to speak to one another. So this approach will often still lead to the need for human intervention most of the time and can’t be fully automated.

Dedicating an employee’s time to data integration tasks

The manual approach doesn’t take software, but it does take time and is good for small files. But if you have a system that is sending out dedicated PDF’s it would be an issue.

Coupling the systems into a Dashboard

If you find yourself needing to type information from a PDF into an Excel sheet then it’s time to consider automating. In these cases, skipping the standard export function is often a good idea. As its output is vastly different and can’t be easily equated to one another, in these instances it’s prudent to figure out what you want as output write a costume solution for this, and have it automatically transported to a dashboard.

It's more common then you might think.

Do you recognize this situation?

Dashcoon can help you save some trees and streamline your data flow into a neat dashboard!

contact us
Get Started