IF statements – multiple levels

Posted on 28 August 2013 by spreadsheetexpertblog
IF statements really bring power to your spreadsheets. And multiple IFs, even more so.

Before 2007, the limit was 7 nested IFs in one statement. My advice was to avoid anything more than 3, because it would make your head spin.

Multiple levels are difficult to follow, meaning it is easy to make mistakes, and a real problem trying to debug them.

It is also very easy to put a comma in the wrong place and obtain a completely different result from that which was intended.

This combined with the issue that logic doesn’t always behave as expected (particularly where negatives are involved) increases the potential for error.

And, even if you get it right, it can make future changes and development tricky for the same reasons.

And from 2007 on, the limit has increased to 64!

As a simple rule of thumb, if you find yourself going beyond 3 levels, then there’s probably a better way of doing it (eg Vlookup, Index).
Posted in Uncategorized | Tagged , , | Leave a comment

Clarity beats prettiness

Posted on 27 August 2013 by spreadsheetexpertblog

You’re looking to produce an impressive report or set of charts from Excel, but don’t forget that a good-looking chart or report is meaningless if you haven’t used the right data or calculations.

Before that final step, where looks are important, the most important thing is to lay the data out well, and ensure that the logic is clear and repeatable.

Have you included all the rows? (missing out rows is a simple mistake to make, but can produce wildly different results).
Has the information been formatted consistently (for example, some percentages might appear as 12% (in which case Excel stores it internally as 0.12), while others might appear as 12 in a percentage column (and Excel will store it 12, 100 times bigger than you mean it to be).

Can you trace your working easily through the spreadsheet? (someone verifying your results needs to be able to do this. That someone may even be you after several months.
This is also important if you wish to develop the spreadsheet further or debug it).

Do those stripy tables add anything to the understanding of the data? (if you’ve made good use of Conditional Formatting then stripes will just be confusing.

Don’t merge cells except in the final output, where you know no calculations will be performed on the cells.

It’s best to leave the prettifying until the final stages, other you can waste a lot of time to find that something needs changing again.

Posted in Uncategorized | Leave a comment

Conditional Formatting: highlight items automatically

Posted on 25 August 2013 by spreadsheetexpertblog

Conditional Formatting changes the font, border or colour of a cell depending on the criteria you have chosen. For example you might set a cell to go red if it went over 32 or green if a certain date is only three weeks away. This is very useful for highlighting records, eg, a temperature threshold exceeded or forthcoming subscription renewal.

Conditional formatting is a very useful tool in 2003, which has become even more powerful in 2007, because it’s not restricted to three criteria per cell.
However, the extra flexibility of 2007 also means it is easier to set up conflicting rules.
Worse than that, Excel also sets up duplicate rules in the background because they refer to a different range. This is a particular problem when one is developing rules, a process which is best done step by step (conditional formatting doesn’t always behave in the way one expects). Unfortunately having developed it for one cell, copying it forward over a range of cells, retains the original test, so there are now two rules.

I think two main rules apply with conditional formatting:
1/ Don’t have too many – it just gets confusing
2/ Define your own rules and don’t use Excel presets, because then you know what is going on

Posted in Uncategorized | Tagged , | Leave a comment

Automatic Date and Time in Excel

Posted on 17 August 2013 by spreadsheetexpertblog
If you want to put today’s date into your spreadsheet, then use =today().
But remember this is dynamic, which means it will change. Thus it’s not much use for dating a document, because tomorrow’s another day!
To get the current date and time, use =now().
This like =today() is dynamic, but even more so. Because it includes the time it will update almost every time a value in the spreadsheet is changed.
If you wish to insert today’s date as text, then type Ctrl+; and for the time type Ctrl+:
These can be combined in a single cell.
However, if you want Excel to automatically insert the date and/or time as text, then you’ll have to use VBA.
Posted in Uncategorized | Tagged , , | Leave a comment

Make phone numbers easier to read AND more useful to Excel

Posted on 5 August 2013 by spreadsheetexpertblog
Put gaps or hyphens into telephone numbers, otherwise Excel thinks they are ordinary numbers, and removes the first zero.
Gaps also make it easier for people to read the number.
(Gaps between words were introduced by Irish scribes in the seventh and eighth centuries, leading to far greater readability of the Latin texts. You might think: “but that’s so eighth century”, but stick with it, it could take off!).
Posted in Uncategorized | Tagged | Leave a comment

The Σ (Sum) button in Excel

Posted on 5 August 2013 by spreadsheetexpertblog
When using Sum (Σ) in Excel, it tries to guess what you want to add. This is shown by the ‘marching ants’ around the cells. If the guess is correct, then press to accept; if not drag to create your own range, and press .
Posted in Uncategorized | Tagged , | Leave a comment

Don’t be sloppy when entering information

Posted on 5 August 2013 by spreadsheetexpertblog
Well-structured information is the key to making the most of Excel.
Each different data type should appear in its own column. Generally, it’s best to think of each row as a record (client names, sales records, inventory items, etc), with the columns containing information [attributes] on those records (eg First Name, Surname, Post Code).
The information is easier to understand and easier to manipulate.
You can do this horizontally, rather than vertically, but, the powerful Filter tools are not available; the tables are much more difficult to read; and you are greatly restricting the number of records.
Posted in Uncategorized | Tagged , | Leave a comment

Date formats – don’t waste valuable information!

Posted on 28 July 2013 by spreadsheetexpertblog
Don’t enter dates with full stops (eg 10.11.13) because Excel doesn’t store them as dates, meaning a lot of useful information is unavailable.
Instead, use a slash (eg 10/11/13) or hyphen (10-11-13) when you enter dates.
Excel then stores them properly, and can manipulate them, eg: matching of dates, difference between dates, conditional formatting, selection of ranges.
However, if you do have full stops, all is not lost.
Make sure all new dates use slashes or hyphens when you add them.
Existing values can be converted to valid dates, meaning the power will be unleashed!
Posted in Uncategorized | Tagged , , | Leave a comment

IF statements – very useful, but take care!

Posted on 28 July 2013 by spreadsheetexpertblog
IF statements really bring power to your spreadsheets. And multiple IFs, even more so.
There are two main problems:
Firstly, they can get very difficult to understand, particularly if you are working on someone else’s spreadsheet, or even your own if you come back to it after several months.
Secondly, because of the simple layout of IF [IF(test, action if TRUE, action if FALSE)], where only a comma separates each term, it is easy to get confused.
If you’re lucky, Excel won’t accept the mistake.
Unfortunately if the formula is logically correct it will be accepted, but with unexpected results.
Posted in Uncategorized | Tagged , , | Leave a comment

Adjusting column width in Excel

Posted on 28 July 2013 by spreadsheetexpertblog
The column width can be adjusted by going to the top of the column and dragging the cursor left or right.
This manual method means you’re only judging by the few rows you can see on the screen.
However, double-clicking the cursor instead of dragging makes Excel fit the column width to the widest entry, wherever that may be.

For an example please see the video page.
Posted in Uncategorized | Tagged , | Leave a comment