EXCEL TIP: Using data in sentences

Microsoft Office users will tend to use Excel to handle numbers and data, and Word to handle text - which, of course, is how it should be.

Although, I know many accountants who would use Excel to write letters if they could - and many Office users who are not au fait with the workings of spreadsheets that will stretch Word's data handling capabilities well beyond what is decent!

There are, however, legitimate reasons for using text in Excel and some very useful functions that make it work well.

In particular, I am thinking of those times where we want to write sentences that use data.

Some examples are:

• Spreadsheet headings that might include a date, or a department name;
• Notes to accounts that might refer to actual money values within a sentence.

I am sure you can think of many more.

Excel has a really simple way of handling this. and at the heart of it is the ampersand character (&).

The ampersand character can be used to join any pieces of text together in a formula. This text can be actual text (included in quotation marks), or references to text.

So, for example:

="My spreadsheet"&" won't work" returns My spreadsheet won't work

or more usefully:

="Balance Sheet as at 31st March "&A1 returns Balance Sheet as at 31st March 2012 if cell A1 contains the current year (2012)

="The Creditors balance of £"&A1&" includes £"&A2&" due to Group Companies" returns The Creditors balance of £35623 includes £5261 due to Group Companies - where cells A1 and A2 contain those numbers

You can even force the format of the data by using the TEXT function. In the example above, replacing A1 with TEXT(A1,"#,##0") will format it as 35,623. The TEXT function obeys all of the standard rules for formats - including dates, etc.

Now you can stop all that fiddling around with the text elements of your reports and have Excel populate that too!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".