Make phone numbers easier to read AND more useful to Excel

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

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

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!

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!

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

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