Author Archives: Dan

WEEKDAY

Summary Weekday is a very useful little function. Quite simply, it works out which day of the week a date represents. Given that dates are simply formatted numbers, it can be used against any cell that contains a number. But … Continue reading →

Calculating basis points

Here's a handy spreadsheet to help you calculate basis points, and prorate their charge as necessary. Simple, but useful.

Converting a column of data into a row

If you have a column of data and want to instead display this across a single row, you can do so via the Paste Special tool. First, copy your column (CTRL+C or via the Copy button). Then select the leftmost … Continue reading →

Custom Views: a little-known treasure

I stumbled upon Custom Views in Excel by accident a number of years ago, in the pre-ribbon era. I use them very occasionally, but when I do, they offer lots of power and options. Custom Views allow you to have … 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 →

F2: the most useful key in Excel

F2 is probably the most useful key in Excel. Below's a description of what it does. It toggles between Edit mode and Enter mode. If you're navigating across the spreadsheet, hit F2 when you've settled upon a cell, and the … Continue reading →

Number to text converter: most useless spreadsheet ever

I can’t remember why, but I set myself a personal challenge some time back to create a spreadsheet that could convert numbers to text. Here it is. Type in a number between 1 and 999,999 in the red cell and … Continue reading →

Check which entries of one list appear in another list

Challenge: find out which entries in one list appear in another list. This problem comes up time and time again in Excel. In fact, it's probably the most common challenge that people come to me to solve. Here's how to … Continue reading →