Tag Archives: Conditional format

Conditional Formatting: How to

First: what is Conditional Formatting? Conditional Formatting is a feature that allows you to change how a cell looks based on the contents of your workbook. You may have a cube of random numbers, and want to shade everything over … Continue reading →

Risks and issues registers in Excel

There are two big problems with most risks and issues registers: They’re difficult to read They’re difficult to maintain. Let’s start with the readability. Usually, they’re a lifeless grid in a tiny font with lots of columns and, depending on … Continue reading →

Most useful spreadsheet ever? Excel-based year calendar

I created an Excel year calendar recently. Here's a link to it. It's completely dynamic, allowing you to show the calendar for any year between 1900 and 9999. The only user interaction is to enter the year. The rest of … Continue reading →

Using a single column for percentages and currency amounts

This morning I received a slightly quirky requirement. The client had a spreadsheet containing people's salaries—a row per person—and wanted to be able to input either a percentage increase or an uplifted salary, and for a separate column to show … Continue reading →

A cute Excel conditional formatting quirk

Let's say I conditionally format cell A10 based on the contents of cell A1 using the formula bit of the conditional formatting feature and leaving A1 undollared. I then copy A10 in its entirety to A9. Because Excel can’t base … Continue reading →

Hiding errors

Challenge: I want to hide error values. Error values are pretty ugly. #N/A, #VALUE, #REF etc. When the eye sees them, it triggers an ever so slight wince. They occur when something's awry: when lookups don't yield any results, when … Continue reading →