Let's have a drink and talk spreadsheets

Back in November, I told you about a project I had been working on as part of a team within the ICAEW IT Faculty to produce a set of Twenty Principles for Good Spreadsheet Practice.

My original article and link to the draft principles can be found here.

Well the final version is ready and I wondered if any of you could make it to the launch event.

The launch event is being held at Chartered Accountants Hall, London, EC2R 6EA on 17th June 2014 at 5:30pm, followed by a drinks reception at 6:30pm.

Speakers include Microsoft's UK Head of Technology for Partners who should give us an interesting insight into Microsoft's future plans for Excel.

To book your place, you can either book online (you don't need to be a member but you will need to register on the ICAEW web site), or contact the events team on events@icaew.com or +44 (0)1908 248159.

It would be great to see you there if you are in or around London next Tuesday. I will be around afterwards for a drink too and would love to meet any of you who can make it.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Referring to cells based on the contents of other cells

Obviously, one of the most wonderful things about Excel is that you can refer to the contents of any cell by reference to its sheet, column and location and use those contents in a formula.

But what if you could store the row, column or sheet information in other cells that you could refer to to create the reference?

Well, you can, and, if you've lost me, I'll tell you why you might want to!

The Excel function we can use here is INDIRECT.

This function asks Excel to treat the text between the brackets as a reference. To illustrate what I mean, let us assume that cell A1 contains the number 5 and cell A2 contains the text A1.

If in cell A3, we type the formula:

=A2

cell A3 will show the text A1 (i.e. the contents of cell A2)

If, however, we type the following into cell A3:

=INDIRECT(A2)

cell A3 will now show the number 5.

This is because we have told Excel not to return the contents of cell A2, but to treat those contents as a reference. So because A2 contains the text A1, the function now returns the contents of cell A1, i.e. the number 5!

This is most useful if you wish to build up a reference using text. For a refresher on manipulating text you might want to visit my earlier post:

Excel Tip: Manipulating text in Microsoft Excel

For example, we might want a formula to return the cell A1 from a particular tab, specified in another cell. So, say, cell B1 contains a drop-down of sheet names, our formula would be:

=INDIRECT(B1&"!A1")

This takes the sheet name from cell B1 and adds the text !A1 on the end and then uses the INDIRECT function to treat the result as a reference, so if cell B1 contained Sheet2, then the reference would be Sheet2!A1.

This needs to be slightly more complicated if the sheet name could have spaces in it. When a sheet name has spaces, Excel requires it to be enclosed in single quotes, so Sheet2 is OK, but if you want a space, it must be 'Sheet 2'. Excel will be happy with or without the single quotes when there is no space, so it is safer to include them. The formula would then be:

=INDIRECT("'"&B1&"'!A1")

There are many uses for INDIRECT, but I find manipulating the sheet name in this way to be one of the most useful.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Remove all formatting

Just a really simple tip this week, as the week started with a public holiday, both here and in the US. That means that you're either lucky enough to have the week off and not interested in spreadsheets at all, or, like me, you've got four days to fit in a week's work!

Have you ever inherited (I'm sure you wouldn't have created one yourself) one of those Excel files with wild formatting all over it? 5 or 6 different fonts in as many different sizes and number formats, and twice as many colours?

Here's a simple tip to strip it all away leaving you with the important stuff - i.e. the data in the cells.

First of all, highlight the range you want to clear (click in grey box in the top left corner of the screen, to the left of the A of the column headings and above the 1 of the row headings, if you want to select the whole sheet).

On the Home Ribbon, over to the right hand side of the screen (below AutoSum and Fill), there is a button called Clear (with a picture of an eraser next to it).

Click this and you will be presented with the following options:

• Clear All
• Clear Formats
• Clear Contents
• Clear Comments
• Clear Hyperlinks

Click "Clear Formats" and this will remove all formatting from the selected cells.

That's it. Nice and clean so that you can start again!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Adding the same cell across multiple sheets

Have you ever needed to sum the same cell across multiple sheets?

You might have 12 monthly sheets and an annual summary or 26 department sheets and a consolidation.

I don't normally design spreadsheets that require this as it tends to go against my advice on how to structure your data.

I would generally advise one data input sheet that covers all months (or all departments) and a report that will present the information for any particular month (or department).

However, I see many spreadsheets that are structured like this and there are times when it is appropriate. In this post, I will show you a quick way to do it.

A reader asked me about this last week as she thought I had written about it in the past, and I had to look it up (as I said, I don't normally have a need for it).

Before I looked it up, I would enter a formula such as:

=Worksheet1!A1+Worksheet2!A1+Worksheet3!A1+Worksheet4!A1+Worksheet5!A1

...to add cell A1 from 5 different worksheets.

This could get very tiresome, of you had, say, 100 worksheets!

The much more efficient way to do it is:

=SUM('Worksheet1:Worksheet5'!A1)

Note that it is the position of the worksheets that matters, not their name. The formula will sum all worksheets between Worksheet1 and Worksheet5 inclusive - left to right.

The reader, when I showed her this, remembered this useful tip from wherever she had read it. Use 'bookend' worksheets if the worksheets to add may change, e.g. have worksheets named First and Last, and place any sheets to be included in the sum in between them.

My thanks to the post that helped me to answer the question:

http://www.ozgrid.com/forum/showthread.php?t=73877



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".