INDEX and MATCH are two of the most versatile functions within Excel.   We will look at these functions individually to begin with but the real value comes from combining the two of them. They can serve as a useful alternative to VLOOKUP because they can look up in either direction – VLOOKUP is restricted Read More …

When you want to search for some specific text rather than an exact match, you might be used to using wildcard characters in other programs. For example, the asterisk * is the wildcard in Microsoft Access and can be used alongside the Like operator. On a list of US states if you added criteria of Read More …

When you import data from an external source, you will often find it isn’t in a desirable format for you to analyse. You will need to clean your data up first and thankfully there are lots of in-built Excel functions which can help.     CLEAN can be used to remove non-printable characters you’ve imported. Read More …

A reader contacted me asking how I displayed formulas on the squaring numbers post (see image below) and it got me thinking about adding this short post. I was asked whether I typed in the text in columns D and E from each formula manually or whether there was a quicker way and the answer was I Read More …

You may want to return the value from the last populated cell in a column for a number of reasons. Perhaps you have a dashboard which needs to return the ‘Current Champion’ but it needs to lookup against a dynamic list of champions so you can’t fix to one cell reference.   In this example we Read More …

You can use the POWER function to square a number, or set it to the power of any given value. These examples show how you can square or cube a number.     If you wanted to find the square root of a number, use the SQRT function .   E.g. =SQRT(81) will return 9.

COUNT returns the amount of cells in the range which contain a number. COUNTA (count all) returns the amount of cells containing any value. COUNTBLANK returns the amount of cells containing no value. COUNTIF returns the amount of cells in the range which meet your chosen criteria.  

Excel has a built-in function allowing you to add hyperlinks to a cell. You can also give the hyperlink a user-friendly name rather than just displaying the file path.   E.g. =HYPERLINK(“E:\Documents\Finance\Financial Calendar.xlsx”,”Finance calendar”)

The SUMPRODUCT function can be used to multiply an array of cells and then sum the total of those calculations.   To demonstrate, firstly here is an example of the long way of doing it, without SUMPRODUCT:     To calculate total number of chocolate bars sold, each row is calculated one-by-one and then all Read More …

Some basic examples of how to calculate percentages using Excel formulas.   Percentages as a proportion The formula in D3 is: =B3/C3   Calculate percentages backwards to find a starting value The formula in D3 is: =B3/(1+C3)   Percentage increases and decreases The formula in D3 is: =(C3-B3)/C3 Although both differences are based on the Read More …