-
Recent Posts
Archives
Categories
Meta
Author Archives: spreadsheetexpertblog
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
Clarity trumps 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
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
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
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
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
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
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
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
