Excel Tips & Tricks

Excel does not measure column width and row height in the same units. Normally the width is in characters and the height in points. This is explained in Change the column width and row height.

The following function searches in the cell A6 in a case insensitive manner for certain letters and then returns the corresponding city:
=IF(IF(ISERROR(SEARCH("bh", A6)), -1, SEARCH("bh", A6)) >=0, "Birmingham", IF(IF(ISERROR(SEARCH("sh", A6)), -1, SEARCH("sh", A6)) >=0, "Sheffield", IF(IF(ISERROR(SEARCH("lon", A6)), -1, SEARCH("lon", A6)) >=0, "London", "Unknown")))
Not the cleanest code but it works and does the job!

If you have a time in a cell and want another cell 10 minutes later then you just need to add the time on, however it is not so obvious how you do this but the following does the trick:

Convert Centimetres to Imperial

I wanted to convert centimetres into "feet and inches", however most calculators do it to one or the other and I always forget how many inches are in a foot! So here is a handy formula that does it all in one go.
=CONCAT(INT(A1/2.54/12)," feet, ", TEXT(MOD(A1/2.54,12), "0.0"), " inches")
=CONCAT(INT(A1/2.54/12),"' ",TEXT(MOD(A1/2.54,12),"0.0"),"""")
Assuming you have the cell A1 populated with the number of centimetres, the second version uses a single quote for feet and a double quote for inches.

Named Ranges

Find a blank cell and press F3, this will display all your named ranges and then "Paste List" will insert their definitions into the current cell and ones below. In addition you can press F5 to locate the range definition from a cell that is using the named range.

Number Formatting

Most people find the basic formatting straightforward enough, it is when you need to do something more complex with custom number formats that things get interesting. How to set a number format and a summary of the standard formats is explained at Available number formats in Excel - Excel. When you then move on to using custom number formats read and watch Create a custom number format - Excel which is very helpful. Finally it is Review guidelines for customizing a number format - Excel that explains in detail how custom number formats work.

Handy Sites

It is worth checking out Online Excel Formula Beautifier which can only help you write your formula! However do note that it does not work with Internet Explorer.

Useful Functions

There is an argument to say they are all useful but I am just going to highlight some I use or have seen used and look very helpful.

  • VLOOKUP - classic lookup function, which I use loads
  • MAXIFS - so this is a new one in Excel 2016 which returns the maximum value given a set of conditions


I often find myself looking for help with specific functions in Excel or needing to find one that might help me, hence the following are useful links: