Author Archives: Dan

The ROUND function works both sides of the decimal place

Most people who have used the ROUND function will have used it to the right of the decimal place. =ROUND(A1,2) This will round cell A1 to two decimal places. So 3.1415 will become 3.14. And 2.718 will become 2.72. But … Continue reading →

Text to columns: saving your file layout

The Text to columns function in Excel is useful. If you have a bunch of data in a single column, it can be used to split it into separate columns. Found on the Data ribbon, you can either specify delimiters (for … Continue reading →

Conditional minimums

A friend asked me the other day how to return the lowest positive value from a row of data in Excel. Given that there’s no MINIF function, at first I struggled. Then I re-phrased the question: how do you return … 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 →

SUMPRODUCTIF

I recently found out how to do the equivalent of a SUMPRODUCTIF, a formula that doesn't exist in Excel. Here’s the detail on how. First of all, some context. SUM does exactly what it says on the tin. It sums … Continue reading →

Variable vlookup offsets

Challenge: I want to future-proof the column number returned in my VLOOKUP formula. VLOOKUP is a hugely useful formula in Excel. Apart from the basic mathematical operators and the IF statement, there’s nothing that touches it in terms of how … Continue reading →

Dynamic named ranges

Huge power can result from combining the OFFSET function with Named Ranges in Excel. Here's how. OFFSET itself is phenomenally powerful. It allows you to refer to a range of a chosen height and width that is a chosen number … Continue reading →

Useful custom formats

Some neat Excel custom formats A few nice custom Excel formats for you to use. <;;;> Stores a value, but doesn’t display it <00000> Always stores five digits. So 453 becomes 00453. Useful for US zip codes <[Black] General> To … Continue reading →

Paste Special between Excel instances

If you have two instances of Excel open, you can’t Copy | Paste Special between the two. Which can be a bit of a pain. Instead, create a new spreadsheet in the destination instance, paste into that, and then Copy … 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 →