Adding Interactive Text to Excel Reports

One of the major problems with monthly reporting, is keeping figures correctly aligned in Board Reports say in Word Format and the accounts say in Excel. Invariably last minute revisions are made to the accounts and not the Word document and also human transposition error creeps in.

In my video showing the reporting model for my imaginary company BAR Fruits Ltd, I made extensive use of interactive text within Excel, which both eliminates the chance of error, and shows the correct figures for each month chosen.

The procedure is quite straightforward, simply joining your text using & and formula or text within quote marks. I would recommend, that you transfer the calculations to a calculations sheet, and use named ranges wherever possible.

Experiment yourself, but the following Formulas, especially in combination, are very powerful:

LARGE returns the n-th largest value in a range i.e. LARGE(array,n) and SMALL the reverse. I have used this to find the highest, second highest and lowest regional sales, and then combined with VLOOKUP to return the Regions name in text.
RANK returns the relative position of a number in a list i.e. RANK(number,array,order). I have used this to find how a regions is ranked in terms of its gross margin performance relative to the other regions.
MAX returns the largest value in a range i.e.MAX(array) and MIN the opposite. I have combimed this with HLOOKUP to return the name of the best sales month for a region over the year found by MAX.
ROUND will help format the number for inclusion in the text i.e. ROUND(Number, number of digits)
IF allows you you to choose different narrative for above or below budget i.e. IF(test,value if true,value if false)
ISERROR allows you to eliminate values when the formula might return an error, and can be combined with IF, i.e.ISERROR(Number,value if an error)

The following is an example of interactive text which is linked to the underlying figures and automatically updates on a monthly basis or when you make any revision to the figures in the Bar Fruits reporting model.

Interactive reporting text

See a Mindmap click here and press + to expand narrative on any branch.

View Richard Colley's LinkedIn profileView Richard Colley’s profile

This entry was posted in Excel Tips and Tricks, News and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s