Category Archives: Advice

Linking spreadsheets: don’t do it

Spreadsheets are linked when formulae in one spreadsheet reference cells in another. There are two circumstances in which spreadsheets become linked: consciously and unconsciously. First, let's deal with conscious linking. My general rule of thumb on this subject is that … Continue reading →

Freeze Panes vs. Window Split

Often in Excel, you'll want columns or rows to continue to appear when you scroll off to the right or down the page. You may have a table of employees with names down the left-hand side, with data pertaining the … Continue reading →

Don’t over-engineer when industrial strength is not warranted

I would estimate that 80–85% of my spreadsheets are throwaway. I create them for a specific purpose: to understand some data; to create an import file for one-off use; to prove someone wrong. (Ha!) After its creation date, I'll never … Continue reading →

Danger: copying and cutting in a filtered range

If you're cutting or copying content from a filtered range, beware. Here are some behaviours that may catch you out. First of all, copying. If you copy from a filtered range, Excel will only copy those cells on display. Which … 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 →

Analysis principle: keep your data consistent and together

One of the most important principles for managing data in Excel is this: keep your data consistent and together. Excel is vast. In the 2007 version, each sheet contains enough cells to store three pieces of information about every person … Continue reading →

Starting out with formulae in Excel

There are three posts you should read, in this order, if you're starting out with formulae in Excel. Formula fundamentals: this will give you some basic principles that will serve you well How to construct formulae in Excel: this will … Continue reading →

Working with dates

A lot of people get confused when working with dates. But they're really rather simple when you get to know them. All dates and times are stored as numbers, the unit being days. Specifically, they're stored as the number of … Continue reading →

How to construct formulae in Excel

Formulae are created by combining up to six types of object: operator condition value or reference range informational logic helper Each will be described in detail here. The subsequent formula descriptions will refer back to these, so it’s important that … Continue reading →

Using dollars in cell references

Dollars are inserted into cell references to influence what happens when that cell reference is copied from one cell to another. It only affects behaviour when the cell reference is copied and pasted elsewhere. It does not have any effect … Continue reading →