Author Archives: spreadsheetexpertblog

About spreadsheetexpertblog

Working as a computer professional since 1983, on projects varying in size from one person to multi-million pounds. Working on Excel projects since the mid 1990s.

IF statements – multiple levels

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 … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

Clarity beats prettiness

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 … Continue reading

Posted in Uncategorized | Leave a comment

Conditional Formatting: highlight items automatically

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 … Continue reading

Posted in Uncategorized | Tagged , | Leave a comment

Automatic Date and Time in Excel

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 … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

Posted in Uncategorized | Tagged , | Leave a comment