Author Archives: Dan

Using TRUE in VLOOKUP

I've used the VLOOKUP function since the cows last came home. I can't imagine my Excel life without it. It's sublime, and while a relatively simple function, it caters for so many needs. (More on the VLOOKUP function here.) It … Continue reading →

MODEIF and MEDIANIF (conditional mode and median)

There's not an inbuilt function to cater for the conditional mode or median. So we need to use an array function. =MODE(IF($A:$A=D1,$B:$B)) If you have supplier names in column A and their delivery lead times in column B, the above … Continue reading →

INDIRECT

Oh what a gem of a formula this is. In essence, it allows you to specify the text that you would like to be used to bring back information. So =INDIRECT("A1") will bring back the contents of cell A1. And … Continue reading →

Highlighting today’s date: a lovely little trick

I came up with a neat trick the other day. With each column indicating a sequential date, it was a way of highlighting the current date. There are two ways of doing this. You can either use conditional formatting to … Continue reading →

Protection in Excel

Protection in Excel is a bizarre beast indeed. Here's a quick introduction into their intricacies and oddities. First of all, the essentials: Protection is applied at one of three levels: workbook, sheet or cell Each level works differently. Let's take … Continue reading →

Excel dice shaker

Here's the spreadsheet you've all been waiting for. The Excel dice shaker. It caters for up to four dice. Simply hit F9 to make 'em shake. Click here to download.

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 →

VLOOKUP and HLOOKUP

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data. But we shouldn't forget about its lesser-used sibling, HLOOKUP. First, VLOOKUP. … Continue reading →

COUNTIFS and SUMIFS

Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that. Since Excel 2007, there have been a … Continue reading →

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 →