When reading reports and dashboards, we often find ourselves spending more time than we’d expect analysing the charts and tables tying to comprehend the data. In many cases that’s due to inconsistencies in the presentation, or poor design. Ideally a report should convey the message clearly and quickly.
One of the underlying rules of IBCS is maintaining visual integrity; this forms the ‘Check’ component of IBCS’s ‘SUCCESS’ formula. It requires reports and dashboards to present information in the most truthful and easily understood way. This involves avoiding misleading visuals including improper scaling and manipulated representations. When interpreting data, information that looks the same should also mean the same. Consistency is important and helps users to understand the report quickly and easily.
Avoid Manipulated Axes
Charts are used to visually compare numeric data. Manipulated axes are often very misleading when interpreting data.
An example of this is truncated axes, i.e. when the baseline of a graph is not at zero. This is especially problematic for column and bar charts, where you naturally compare the length of the columns or bars. Take a look at the graphs below:
By starting the value axis at 100 in the first graph, Group B visually appears roughly double Group C, whereas in truth it’s only c. 13% larger. Starting the graph at zero gives an accurate representation of the data.
Other manipulated axes to avoid are logarithmic axes and using different class sizes as they do not allow accurate visual interpretation of numerical data.
Using the Same Scales
When presenting multiple charts of the same unit on the same page, we should use the same scale to allow for comparison between the charts and to avoid invalid comparisons.
Take a look at the charts above displaying the sales amount for various products. At a quick glance, it is not clear that Products A-C have much higher values than Products D-F. It is only after we read the data labels (or refer to the axis if the chart were formatted differently) that we know this to be the case. Keeping a consistent scale will allow users to make a valid visual comparison across the charts. This is where we introduce XLCubed’s ‘Match To’ property:
‘Match – To’ Property
Give each of the charts a name – e.g. “Chart 1”, “Chart 2”. We will make the scales of the second chart match to the first one. Right click on Chart 2 and navigate to Properties > Axis Scales > Match To. Here, select “Chart 1” in the dropdown.
Click OK and now the two charts have the same scale as seen below:
It is now easy to make a quick visual comparison across the charts, and instantly obvious that Products A-C have a much higher value than Products D-F.
Using Different Chart Types:
When we are presenting multiple different chart types, it is important that we understand what each chart type represents and thus decide whether it is appropriate to match the scales.
Take a look at the charts below. We have a small multiple waterfall chart representing the Profit and Loss Statement for 2017 and 2018, an absolute variance chart representing the absolute difference between the two years and a Relative Variance chart displaying the percentage variance between the two years.
Here we have matched the scales of the first two charts, i.e the waterfall chart and the absolute variance chart as they are both displaying the same unit. For the third, relative variance chart, it would not make sense to match the scale as this is representing the percentage difference.
Resizing Charts
We can use the range picker to resize the charts. By resizing the charts to a specific Excel range, we can easily align the other charts sharing the same scale.
Handling of Outliers
Extreme values, or outliers, can skew the chart scales so that the majority of the data points are barely visible, and the overall chart becomes less useful. In many cases the outliers are not important for the business, and can be caused by things like a small initial value for a relative (%) variance. Most of the time, if an outlier is not important, then it is best not to scale the whole chart to fit the anomaly. In the two charts below, the first one has been scaled to fit the outliers ‘+983%’ and ‘+391%’ and the second has clearly indicated the outliers with arrows and scaled the chart on the other values.
In the first chart, the data appears squashed and is difficult to compare, other than by reading individual labels. The second chart allows us to make comparisons, while clearly depicting the outliers as being beyond scale.
For relative variance charts XLCubed automatically determines outliers which would affect the scaling, and indicates them with arrows. This can be configured or removed as required in the context of a specific report.