Save content
Have you found this content useful? Use the button above to save it to your profile.
iStock00044927830_Small

Give your reports more oomph 4: Interactivity

by
8th Oct 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst concludes his series on ways to improve management reporting with a look at how to replace printed pages of figures with interactive on-screen reports.

Introduction

You are likely to have to put a lot of work into preparing the information for your Excel reports and ensuring that it is correct, so it would be a shame if your final report fails to deliver the maximum possible clarity and impact. In this series we look at a variety of ways to improve reporting. In previous parts of the series we have covered number formatting, conditional formatting and Sparklines. In this fourth and final part we will consider how to replace printed pages of figures with interactive on-screen reports.

The power of Pivots

So far, we have concentrated on improving the clarity and impact of fairly simple financial reports. Many recent Excel developments and new tools have involved the extension of the Excel PivotTable feature to become the underlying engine for the creation of business intelligence reports. In the past few weeks, this has culminated in Microsoft releasing Power BI as a standalone product in both free and premium editions.

Although it is perfectly possible to produce text and figure based reports using PivotTables, PivotTables also support a range of graphical alternatives from simple charts and interactive dashboards using PivotTables and Power Pivot to the maps and animated charts available in the Power View add-in as well as 3D animated maps in Power Map.

The various Power BI tools and features have been covered extensively as part of the 'Working with Accounting Data' series of articles so we won't go through them in detail here beyond pointing out the power of the Slicer feature introduced in Excel 2010 and extended in Excel 2013. Although at first glance a Slicer just seems to be a more graphical way to implement a PivotTable filter, its real power lies in its ability to be connected to multiple PivotTables. This allows the creation of a dashboard featuring multiple charts and tables based on the same data source, all of which can be filtered using a single Slicer or set of Slicers. In Excel 2013 a new type of Slicer was added: the Timeline. This can only be used (not surprisingly) with date and time fields and presents the available data as a continuous line. In this example we have assembled a set of charts and tables, all based on PivotTables which, in turn, are based on the same data set. We have then added an OrderDate Timeline, and Salesperson and ProductName Slicers. These are connected to multiple PivotTables so that we can control the entire dashboard by choosing items from the Timeline and the Slicers:

(Click image to enlarge)

Other forms of interactivity

Slicers are not the only way to make a report interactive without needing to resort to Visual Basic Code. We'll use our Profit and Loss account from the previous article as an example. Our report just showed the results for a single year but figures for the previous four years were also available and were used to create our Sparklines. We'll adapt our Profit and Loss account to make it possible for someone to choose which year's data it should display:

(Click image to enlarge)

The idea is that we just enter a number in cell B2 and this 'chooses' which year to display by referring to the appropriate column to the right of our actual report. There are several ways we could do this. We could use HLOOKUP() to match the year in E3 with our year headings in I3:M3 but this would mean entering the row index numbers manually or using a relatively complicated formula to calculate what they need to be. We could also use INDEX():

=INDEX($I5:$M5,$B$2)

In its simpler form, INDEX() can take a row or column of data and chooses an item in the row or column based on its position: 1 for the first item, 2 for the second etc. In our example we fix the reference to our row of data to be column I to column M, but leave the row reference as relative so that we can copy it to other rows and it will adjust automatically. We use the value entered in cell B2 to decide which year to pick. If the user enters 2 it will choose the second item in I5 to M5, so J5. The more complicated form of INDEX() allows the entry of a two dimensional range and chooses the value at the intersection of a row and column position.

We could also use OFFSET():

=OFFSET($I6,0,$B$2-1)

OFFSET() starts off with a cell reference and then moves a number of rows and columns to another cell. In this example, we stay on the same row and use the value in B2 (less one because we are already in the starting cell) to move a number of columns.

Using OFFSET() comes with warnings. Many spreadsheet experts advise against the use of OFFSET() because it is a volatile function which means it always has to be recalculated when anything in the spreadsheet is changed, regardless of whether it is actually affected – this can slow down calculations. Another issue with OFFSET() is that it breaks the precedent/dependent chain which can make the spreadsheet more difficult to review and audit:

Here we have used OFFSET() for our Cost of Sales and INDEX() for Sales, then used Formulas, Trace Precedents. We can see that the actual cell that provides our value is not shown as a Precedent for OFFSET() whereas it is included in the range of precedents shown for INDEX:

(Click image to enlarge)

Whichever function we do decide to use, we haven't made things particularly easy for our user. They have to work out which number they need to enter and then type that number into the right cell. We could make this much more interactive and intuitive by using an Excel form control. First of all, we need to ensure that the Developer ribbon tab is visible. In Excel 2007 this is set via an Excel option but in Excel 2010 and 2013 you can right-click in the ribbon, choose Customise the Ribbon and tick the Developer ribbon checkbox. Once the Developer ribbon tab is displayed, you can click on the Insert command in the Controls group to display the list of available controls. We will use a Form control rather than an ActiveX control. Here we have chosen the Scroll Bar control and dragged a 'landscape' shape to get a left/right bar, rather than an up/down bar. We can right-click on our control and use the Format Control option, and then the Control tab, to enter the settings we need. We have to choose between five years so we set the Minimum value to 1 and the Maximum value to 5 and the Incremental change to 1. We link our control to our B2 cell which is the cell our formulae refer to:

(Click image to enlarge)

Our user can now use an intuitive control to choose the year they want to look at, rather than having to type any value into a cell.

Dynamic Sparklines

If you've been watching carefully, you may have noticed that our Sparklines have also adjusted automatically to our choice of years, showing only the years up to the year we have chosen. We can use the OFFSET() function with our B2 value to choose an area as well as a particular cell:

=OFFSET($I5,0,0,1,$B$2)

However, we can't set the data source of a Sparkline to an expression that directly includes a function. The way round this, as we indicated last time, is to allocate our formula directly to an Excel Range Name:

We can then use that Range Name as each of our Single Sparkline's Data source data range:

Conclusion

We have covered a range of techniques in this series. Not all of them will be suitable in all situations. A lot of the skill that goes towards creating effective reports has nothing to do with Excel, but relates instead to an understanding of what sort of report is best suited to the intended audience. Once you have established that, then Excel has an extensive range of features available to help you achieve your objective with maximum clarity and impact.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.