Category Archives: Quick tips

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 →

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 →

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 →

Charting shortcut

If you select a range of data and hit F11, Excel will create a default chart on a new sheet based on that data. It's often useful as a throw-away action just to visualise how your data trends. Or you … Continue reading →

Formatting shortcut

CTRL+1 is a very useful shortcut to bring up the Format Cells dialog box.

1900 was not a leap year, despite what Excel might have you think

If you enter 29 February 1900 into Excel, it will recognise it as a date and format it such. But if you can find a calendar dating back that far, you'll discover that 1900 was not a leap year. This … Continue reading →