Category Archives: Quick tips

Where there’s a MIN there’s a MAX

It's an odd one, but wherever you need to apply a minimum in Excel, you'll often find yourself using the MAX formula. Let's say you need to charge for transactions (stored in cell A1) at a rate of £20 each, … Continue reading →

Cross-sheet data validation

One of the annoying limitations of Excel is that Data Validation does not work from one sheet to another. If you have a list of items that you want to use as a pick list for a specific cell, the … Continue reading →

Alphabetical ranking

Today I faced the challenge of having to find the rank of a text string in a range of text strings. Or put another way, in what position would the entry sit alphabetically? The RANK function only works on numeric … 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 →

LEFT and RIGHT default to 1 character

The LEFT formula takes two arguments: the thing you're taking the left-hand side of, and the number of characters you want to take. So the following formula takes the first five characters of cell A1. =LEFT(A1,5) But if you only … Continue reading →

F4: repeat last action

For many things, F4 repeats the last thing you did, whether it's put a border on a cell, change its colour, insert a row, right-align etc. It's a bit hit and miss—it doesn't, for example, repeat data entry. But it's … Continue reading →

Editing cells

The easiest way to edit the content of a cell, be it a formula or a value, is to navigate to the cell and hit F2. Instead of moving around the cells in a sheet, the left and right cursor … Continue reading →

Conditions don’t care about case

If you're writing a formula that involves comparing two strings of text, beware. Two identical strings of text, one in uppercase, the other in lowercase, will be considered to be the same. Use the EXACT function if you want to … Continue reading →

In-cell carriage returns

Excel allows you to wrap text within a cell or within a merged set of cells. But it also allows you to force line-breaks within cells or merged cells. If you want to create the equivalent of a carriage return … Continue reading →

Toggling between values and formulae

For most people, the only way to see the formula in a cell is to click on the cell. You can then see the formula in the bar immediately below the ribbon. Hit F2 and you can start editing that … Continue reading →