Excel goes Silent Witness

David Winch's recent Any Answers question about using a data visualisation to present information to a jury provoked dozens of responses.

Winch needed to show a jury whether there was a relationship between amounts transferred out of one bank account and amounts transferred into another bank account. Of course, if the same amounts were transferred on the same dates, the correlation or otherwise would be obvious but Winch's presentation needed to allow for changes in amounts and a variable delay between the transfers in and the transfers out.

Many different approaches were suggested, from various types of Excel chart, to the use of PivotTables and the use of statistical functions.

However, the image that kept running through my mind was the frequent DNA match, beloved of TV crime forensics, where DNA profile charts are moved into place and suddenly all the peaks and troughs line up and another impossible puzzle is solved. This is my attempt to use a range of relatively simple Excel techniques to create just such an epiphany moment.

We'll assume that we have four columns of data. A date and an amount for our transfers out and a date and amount for the transfers into the other account. We'll ignore the normal accounting conventions and treat all the amounts as positive to make setting up our chart easier.

It would be quite straightforward if we didn't have to worry about the potential date offset, but we need to be able to move one of our lines backwards and forwards across the date axis to see if we can find a position where our lines match best. One way to achieve this would be to create two separate charts, each based on the respective date column and amount column. We could then make the background of one of our charts transparent and superimpose it over the first chart.

For this to work, we would have to ensure that the axis minimums and maximums were set to the same values for each chart in order to prevent them adjusting automatically. We would also need to remove most of the chart elements from one of our charts to avoid the elements being overlaid. We will look in detail at an alternative method which is to use one chart with two horizontal axes.

It's common and easy to create a chart with a secondary vertical axis, but adding a secondary horizontal axis is less obvious. Not least, because you have to create a secondary vertical axis first. Here we have clicked in our four column block of data and created a line chart:

Source: Simon Hurst

Currently, columns A and B contain the same set of dates, with column C showing the amounts transferred out of one bank account and column D showing the amounts transferred into the other account. We want to be able to advance the dates in one of our columns to see if the outs match the ins of a few days previously.

In our chart, we are going to link the dates in column A with the amounts in column C and link column B with column D. This will allow us to advance the dates in column A to see if amounts are paid into the second account some number of days after they transferred out of the first account. To do this, we need to create a secondary horizontal axis. The easiest way to do this depends on the version of Excel.

In Excel 2013 and 2016 you can go to the Chart Tools, Design Ribbon Tab and choose Change Chart Type; then select Combo, make sure both Series are set to Line and then just click in Secondary Axis for series B:

Source: Simon Hurst

In version 2010 through to 2016 you can double-click on the line and click Secondary Axis directly, but in Excel 2007 double-clicking on chart elements doesn't work and you need to right-click and choose the Format option from the menu instead. Once you have your secondary Axis you can double-click on the axis itself (again, in Excel 2007, right-click) to edit the properties. We want to set the maximum value for both the primary and secondary axes to 10000 rather than them being automatic. This will ensure that both series values will be plotted consistently:

Source: Simon Hurst

Now that we have a secondary vertical axis, we can choose to display a secondary horizontal axis. Again the way to do this varies across versions. In Excel 2007 and 2010, with the chart selected, go to the Layout Ribbon tab and from the Axes dropdown choose Secondary Horizontal Axis. In Excel 2013 and 2016, select the chart and click on the + icon to the top right of the chart. Hover over the Axis option and a right-pointing arrow head will appear, click on this and the choose Secondary Horizontal Axis:

Source: Simon Hurst

As with the vertical axes, we need to change some of the properties of both horizontal axes. We will set both to be specifically of the type Date and set minimum and maximum values to 01/01/15 and 30/06/15 respectively:

Source: Simon Hurst

We now need to allocate each of our horizontal axes to different columns. To do this, select the chart and then, from the Design Ribbon tab, click the Select Data command. Click on series A in the Legend Entries pane and then click the Edit command option in the 'Horizontal' pane. For Series A, set the range to column A and then repeat the process for series B to set the range to column B:

Simon Hurst

This will allow us to change column A to use the date in column B, but with a variable number of days added. We will enter the number of days in cell B1 and then set A4 to the formula:

=B4+$B$1

Note that we need to make the reference to B1 absolute so we can copy the formula down to the other rows in column A. We can now enter a value in B1 and our chart will move our line to the right by the number of days entered. We can try different values to see if there is any apparent correlation in our data. To make the process easier, we have made sure the Developer Ribbon tab is visible, and then used the Insert command to insert a Scroll Bar Form Control. We can right-click on the control to set its properties and to link it to cell B1. We have set it to scroll between 0 and 10 in increments of 1:

Simon Hurst

Note that there is currently a strange bug in Excel 2016 that causes the scroll bar control to sometimes keep on moving on its own after you have clicked on the arrows at either end. If this becomes a major problem, you can use the Spin Button Form Control instead, but this works up and down rather than left and right so might not be so intuitive.

Here we have gradually increased the number of days to move our blue line to the right, relative to our fixed orange line. When we get to 5, it does seem that there is some match between the amounts transferred out of one account and the amounts transferred in to the other:

Simon Hurst