-
Recent Posts
Archives
Categories
Meta
Author Archives: spreadsheetexpertblog
There’s history and history!
The other day I saw an article on the web by a writer who seemed to think that VLOOKUP was new to Excel 2013. Strange, I thought: I was using it in Lotus 123 in 1990. Now that is only … Continue reading
Relative and Absolute References
One of the great attractions of spreadsheets when they started was their ability to quickly replicate complex calculations, once those calculations had been developed for one category. An example might be calculations worked out for one month , which could … Continue reading
Conditional formatting – Hierarchy
In 2003, the hierarchy is very important in Conditional Formatting: if you have that wrong some conditions may never be tested. Supposing you want the cell to go red if less than 50% in an exam, amber between 51 and … Continue reading
Posted in Uncategorized
Leave a comment
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