Spreadsheets are not for reporting purposes
Timely access to key information is the foundation of business decision-making. Regardless of the rapid development in data analysis and display, we often see that many companies still use good old Excel (or other comparable tools) as the basic tool for internal business reporting.
It is not hard to see why spreadsheets are so popular. Every user wishes that the organisation had a uniform and comprehensive IT support introduced for all business processes. One where the data were captured once, at the source, and then displayed every time when needed, in the desired form.
Unfortunately, the reality is different. Such comprehensive systems do exist, but one can quickly see that the various parts are connected rather by the same name and licence policy than not by actual functionality. The next question is how many companies can afford to introduce such a tool – the significant financial aspects not being the main obstacle here.
For this reason, we are facing a set of solutions, which usually also reflects the organic growth of the company. Integrations between systems transfer data with more or less success, and ensure the unification of information. But – each tool reports about its own data. By providing an automatic data transfer among systems, many problems were solved, but not all. Unified reporting across all data sets still needs to be arranged.
If the company has a dedicated IT department, it can be contacted. This of course is a blessing and a curse at the same time. Such departments are the home of technicians – who traditionally and craft-related do not understand business requirements too well, and whose deadlines would have inspired epic poems in the past.
The solution known to business users is of course obvious. They draw the data from various sources into a spreadsheet and prepare the report in this way. In the process they cleanse the data, enrich them with external sources and adapt as required. All this they do on their own, as best as they can, when they need it.
The hidden traps of spreadsheets
Decision-making based on spreadsheets also presents a risk. Usually it is not really clear how old the information in the spreadsheet is. It may happen that key business decisions will be made based on obsolete data. Of course, the information when the data were updated can be added to the spreadsheet – but this depends on internal rules and their conscientious implementation. So, there is more than enough space for errors.
In addition, there is no information where the data were obtained. True, we can record in a separate sheet which sources were included and which queries were used. But whenever there is a hurry – and when isn’t there – these notes are the first to fall victim. The end result may be far away from what is written in this manner.
Even if we are very sure that the record is correct, where the data came from and when, the information in the spreadsheet can be amended at any time. Without trace. Sometimes completely legitimate – this data is missing, that one jumps out too much – and sometimes not so very. The fact is that after some time we do not know any more what is true and what is a sole reflection of our wishes.
The preparation of a spreadsheet may take quite some time. All steps required to obtain the data must be repeated (and these are not necessarily just a query in the relational database. At the end, we should not forget to make sure that all charts still show the correct data, especially if the volume of information changed (additional lines are lost in the process).
If the spreadsheet is informative enough, it spreads among the public. Without any control over the copies, without control over the changes. Which one is the right one? Which one is righter? And based on what copy did we actually make the decision?
Today we saved the day with a spreadsheet and were praised. The next time they will again need a spreadsheet to make a decision – will we know how to compose it once again? The expectations of decision-makers are simple – the same as last time, only with fresh data. If we submit an almost identical spreadsheet, we might mislead them.
We have to introduce a solution that will be:
- Connected: With all required data sources, inside and outside the organisation. In spreadsheet form.
- Updated: The data must reflect the actual situation, not a picture of a past situation.
- Correct: The data it presents should be captured according to verifiable rules.
- Agile: When new requirements arise, they can be included without exhausting and lengthy negotiations with the contractors (inside and outside the company)
- Accessible: For all users who need the data. And to nobody else, if possible.
Fortunately, such solutions exist, and not just that – they are affordable and easy to use, they do not require technical knowledge, but only knowledge of the data and understanding what one wants to communicate to whom. And working with them is fun.
Among the best-known solutions in this area, we will mention three:
- Microsoft Power BI from Microsoft.
- Tableau from Tableau Software.
- QlikView from Qlik.
Microsoft Power BI is a cloud-based solution that requires the use of an Azure user account. This is very suitable for business use because it is the first line of defence against data leaks. Apart from that, the philosophy of all three solutions is very similar. They support data collection from a variety of sources, from relational databases to subscribed web sources, to spreadsheets, text files, and web pages. They make it possible to capture data from these sources at any time, and more importantly, to capture them at a defined time. The manufacturers often offer an almost as powerful tool that is free of charge – Microsoft Power BI allows a free user account, Qlik provides Qlik Sense Desktop. For both, data updating is the feature whose missing is the most obvious. You can create an exceptionally attractive and informative report, but if you have to update the data manually each time, you can only wait for the first time when this is forgotten. And for the last time.
All the tools listed above enable the basic operations on data to be executed at each data capture. The Microsoft Power BI solution, which offers a wide range of functions for converting the source data, is especially appealing here. In all cases, all data transformations are part of the report. They cannot be forgotten, and data cannot be changed bypassing them (except at the source).
All three solutions provide access to reports via a web browser. Tableau and QlikView use a special server, and Microsoft Power BI is a cloud performance anyway, so it cannot be any different. The reports can be interactive in all cases – they can filter and drill down data.
The use of these tools is simple and fun – both during the preparation of the report and during the data research. Together with the additional possibility to package the data image together with the report in a notepad, the solutions mentioned above are more than suitable for replacing spreadsheets in reporting within the company, while increasing the reliability and data protection.