3D Business Spreadsheet

Tables are still the ultimate solution when it comes to storing information – yet they are not particularly suitable when it comes to presenting it. In this blog we want to demonstrate the options available (above all the visual ones) for processing data based on tables. These options support the user in the form of  diagrams in order to analyze data and understand it fast.

Tables are a very powerful form of data management. However, a table can be mind-boggling for  people, particularly if it contains very many datasets with a lot of columns. With table views, we lose the opportunity to easily identify  the connections between datasets. The human brain is not capable of grasping a lot of different information at first sight.

Here’s one example:

According to scientific research, people can only recognize four (to five) objects at the same time without having to count them. This is known as subitizing (You can read more about this in Wikipedia) . People have learned how to deal with this “weakness” and have found means and ways to deal with this in their daily lives:

  •  We  draw a tally of four lines and put a diagonal line through them so we can see instantly that it is a block of five.
  •  On a six-sided dice the points are always arranged in the same order so that they are  identifiable as a symbol.

So when we work with numerous data, yet still want to get a clear overview and comprehend it quickly, we have to find another way to present numbers  which is easier for us to grasp.  Here are some alternatives which can be of  assistance:

 Tables

The table is the starting point for all views because data can be stored here easily and in a structural manner and it can also be analyzed.  Large data tables can quickly become confusing – but you can do something about that:  search and filter functions limit the datasets displayed, categories make tables clearer (we already described this is in our  blog entry Everybody needs a mask) and by means of conditional formatting (see Conditional formatting to spice up your views) even basic connections become evident by using colors or icons. And despite these, a lot of information, in particular the connections between several datasets, remains hidden at first sight. The following visualizations can help:

Charts

Charts are a very popular method of presenting data as they offer a lot of different options. Charts let you compare different kinds of data easily and establish the link between them. There is a suitable chart for every requirement. Here is just a small number of options:

  • Bar charts:  for comparing quantities of values
  •  Pie charts:  for the composition of values
  • Line charts: for the development of data (eg: over a period of time)
  •  A combination of charts is also possible. For instance, bar and pie charts can be combined and simultaneously provide information on quantities and composition.

Charts are best if you want to provide a general overview where exact values aren’t a priority.

 Map view with charts

Data with geographical contexts is presented on maps. It is easy to see  what the main focuses are and how they are put together. As  diagrams, all types of diagrams are conceivable but of course not all of them are suitable for every purpose (see  charts). You have to bear in mind that the  presentation of the data needs to be very focussed. Therefore, only little information can be displayed so that the graph doesn’t appear cluttered. When choosing the maps (provider) it is important to consider  the  requirements the map should meet and what is to be represented on it:

  • Should national borders/ cities/ roads be presented?
  • Are elements such as green areas/urban areas/ expanses of water relevant?
  • In which language should they be in? The local language or the language of  the user?

In our example we have categorized the sales forecasts for the individual products  in accordance with countries and displayed them on the map in a pie chart. The user can quickly see whether a product is in demand in a certain country and how the aggregate demand for the various products is  made up. To compare, we have also presented the relevant data for the map view in a table.

 Map with references

Maps may also be used to present geographical references such as transport or affiliations. Depending on the use case, the objects are cateogrized (e.g markets instead of individual customers or  distributors or factories instead of individual production stations).

Our example shows the connections between  the factories and markets. You can see at a glance which factories supply which markets.  To compare,  we have also presented the relevant data  in a table.

Maps with references are of an advantage when the focus of the view lies on the connections and specific values only play a secondary role. In this view the choice of data is very focussed and you should consider which data is actually relevant and must be presented.

Graphs

If the geographical position of objects is less relevant and you merely want to present the structure, then a graph is a recommended alternative. This is basically a map with references which can be arranged freely instead of geographically. The advantage here is that locations can be displayed based on criteria rather on than their geographical position. In this way, they can be cateogrized based on other factors (e.g. production possbilities or quantities). The challenge of a graph is that you have to keep redirecting your focus whereas people are familiar with the map layout and categorizing is easier. Automatic functions to arrange objects support the user as regards appropriate configuration.

Gantt charts

Gantt  charts present the underlying data as bars on a time axis. In this way, the time flows of different production lines can  be identified and  viewed quickly.  Empty containers are also easy to identify. Typically, every row is a production site (factory, line or similar) and the columns  are the time components (e.g. hours or days). The products which are manufactued are entered as bars.  This is a very specific type of presentation  and only  used to present time flows.

Pivot charts

Pivot charts  allow you to analyze data in a chart without having to alter the output data. Here, fields are selected for rows and columns and  data is entered which has a third dataset. The chart or relevant table can be changed slightly by the user and in doing so  he/she can determine the different connections between the data. The data shown can be reduced so that a result is  presented in detail. As an example: the sales  forecast for every product has been set in relation to the sales market (this is the same information as in the example  of maps with  charts) and presented  in a doughnut chart. One disadvantage is that using pivot tables seems to be complicated at first sight, but don’t let that deter you!

Master/Detail view

And for  those of you who still can’t get enough of  tables, despite the alternatives we described, we can go one better: The master/detail view contains a reduced data table with few columns or just one (master view). The details belonging to every dataset in the master view can be shown  in data fields in the detail view but not simultaneously for several datasets.  Here, the data can be arranged more easily thanks to the layout of the fields (perhaps also distributed on several riders or categorized in areas) and processed.

Zusammenhänge zwischen Datensätzen werden allerdings nicht erkennbar. This view allows you to focus on many details of a dataset (even more than would be possible in a table!) and find the dataset easily. Connections between datasets, however, cannot be  identified.

To conclude…

In general, it is not advisable to replace a table with a  chart but in many situations, visual representation can be understood far more easily. It is important to check which is the best solution in each case.

Of course, all these views and charts are available in OPTANO. What experience have you had with views and which ones do you think are an absolute must? We look forward to hearing from you!

Other topics you may find interesting….