Excel Event: 20 Principles for Good Spreadsheet Practice from the ICAEW, one year on

You may remember that last year I told you about the 20 Principles for Good Spreadsheet Practice from the Institute of Charterered Accounts in England and Wales (ICAEW).

I was part of the committee that produced the 20 principles which were launched at an event at Chartered Accountants' Hall in Central London last June.

Well, the principles have been out "in the wild" for a year now and the ICAEW are holding another event to look at how it is being used.

The event is being held at the same location on 8th July 2015 and will offer the opportunity to not only hear about practical experience of applying the principles and Microsoft's plans for Excel, but also from world-renowned researcher into spreadsheet controls, Professor Ray Panko of the University of Hawaii.

There's also a drinks reception afterwards so maybe we could meet up too.

You can find out more, and book your place at the link below:

http://www.icaew.com/events/2015/july/titfsem150708-twenty-principles-for-good-spreadsheet-practice

I hope to see you there.

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: Colour-coded tabs make life much easier

This week's tip is a really simple one, but can make using your spreadsheet so much easier.

It is something we have used with paper files for decades, if not centuries, but is sadly not used anywhere near as much in spreadsheets. This is a shame, because it is really simple and very effective.

I'm talking about colour-coding your worksheet tabs, i.e. the little tabs along the bottom of the screen for switching between your different worksheets.

We can change the colour of a tab simply by right-clicking on the tab name and selecting Tab Color (the menus have the US spelling)., we can then select our chosen colour from the palette.

Despite how easy it is to do, used well it can make a huge difference to the usability of the spreadsheet.

By 'used well' I mean the following:

• Use a small number of colours to identify particular types of worksheets. For example I often use red to identify sheets that contain external data that must be refreshed, green for output reports and orange for data entry.
• Order the tabs sensibly. You can move tabs simply by clicking and dragging them. I find ordering them in typical sequence of use is useful (left to right), so in a reporting spreadsheet we might have external data sheets that need refreshing first, followed by the reports that are generated from them. If there are also data entry sheets, I would tend to include these last if they are just standing data that it is not always necessary to update, or if they need editing every time, include them at the appropriate place in the workflow. Combined with the colours and well-chosen tab names, this can make the spreadsheet quite intuitive to use.
• Keep tab names short but meaningful. The shorter they are, the more that will fit on the screen without scrolling (thanks to Jim's comment on last week's post for this simple tip). 

My suggestions above are just that, play with this and you will find what suits you and the particular spreadsheet, but don't ignore this ability to add very clear visual cues to your spreadsheet.

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: Make the best use of your screen space

This is a slightly different kind of post this week. I thought I'd share a few simple tips to help when you're a little short of screen space.

This may be because you are working on a laptop screen, or just dealing with a huge spreadsheet that you want to see as much of as possible without scrolling.

There are a few little things you can do to make the most of what you've got.

Firstly, since Excel 2007, the Ribbon can take up quite a sizable percentage of the screen, particularly if you are working on a laptop.

You don't need to have it visible all of the time.

Just click the ^ at the bottom right of the ribbon (see image below)

and it will fold away until you click on one of the menu headings. You can get it back more permanently (when you're on a larger screen) by clicking the pin symbol that has now replaced the arrow. This setting is a personal setting that does not affect any particular spreadsheet, just how you choose to view Excel in general.

You can also zoom out to see more of the screen by using the slider at the bottom right of your screen:

Just click the minus on the left to reduce the zoom % and see more of your screen. You can usually get away with 80% reasonably easily. Note that this change only affects the current worksheet and is saved with it.

If you are really trying to get as every pixel of space, you can hide the formula bar and the row and column letters and numbers for a particular worksheet by unticking Formula Bar and Headings on the view ribbon:

Like the zoom, this affects the current worksheet, and is saved with it.

By combining all of the above, you can make a lot better use of a small screen, when you need to.

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: Nested IF Statements - Decisions dependent on other decisions

In last week's post I discussed AND and OR, as ways of combining criteria in an IF statement. These are perfect if each test that you wish to apply is independent of the others.

What about, if you wish to apply additional tests based upon the results of other tests, resulting in more than to possible outcomes?

In real life this might be something like:

"If it is sunny today, then if all of the family agree we will go for a walk, but if they don't, we will go for a picnic - however, if it is not sunny, then if there is a football match on tv, we will watch that, otherwise we will watch a film."

This kind of thing is easier than you might think, if you keep your head!

If you need a refresher on the IF statement before we start then visit my earlier post:

EXCEL TIP: The IF Statement made simple

The answer to this kind of problem is the use of something called Nested IF statements. In Excel, we can 'nest' functions inside other functions. This means using a function as an argument inside another function. To address the kind of problem above, we can nest IF functions inside of other IF functions to produce the logic that we want.

These can lead to pretty complicated-looking formulae when you are finished, but need not be complicated to build if you take them step-by-step.

For our example let us say that we define the following cells as range names:

A1 as sunny
A2 as familyagreewalk
A3 footballontv

Each cell can contain either Y or N, for Yes or No.

We could just use the cell references but it will be easier to see the logic of the formula with the defined names.

I sometimes find it useful to do one IF statement at a time, placing dummy answers where I will later place a further if function. Taking this approach we can apply the first text as follows:

=IF(sunny="Y","AAA","BBB")

This will simply return AAA if it is sunny, or BBB if it is not. We can then replace "AAA" with the test that we wish to apply if it is sunny, which is IF(familyagreewalk="Y","Walk","Picnic"), this makes our formula:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),"BBB")

We can the replace "BBB" with the test that we want to apply if it is not sunny, i.e. IF(footballontv="Y","Football","Film"), so our formula becomes:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),IF(footballontv="Y","Football","Film"))

We can keep adding nested IFs in this way. We may want to replace "Film" with a rule that determines what kind of film we will watch, for example.

Since Excel 2007, you can actually have up to 64 Nested IFs in one function, although your formula would get pretty insane well before that! Excel 2003 only allowed 7, which is already starting to get a little too complex. Usually when you have more than around 4 or 5, there is usually a better way of doing it. Lookups can be helpful, for example.

You can comfortable build up 4 or 5 if you use the approach above. Trying to go straight into writing the whole function, you can very quickly get your brackets in a knot!

Good Luck!







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".