Since Microsoft Excel 2007, Excel’s formulas have become more readily accessible. This has opened up a number of pretty powerful Excel formulas, that if you can spend a little time getting to know, can save you a LOT of time in the long run.
Here are ten great formulas to help you save time and get the most out of your data.
Want MORE than 10?! Check out this post describing finding highest and lowest numbers with Excel.
Top 10 Powerful Excel Formulas
How many messy spreadsheets do you have where data has been entered in a hurry? One common mistake is that names of people or companies are all in lower-case
Using the PROPER formula will quickly convert all your text to begin with capital letters.
Example: If cell A1 contains “colin welch” you can enter =PROPER(A1) in to another cell and it will display “Colin Welch” instead.
Another tip! If do this with a big list of names, and you want to copy then back in to the original cells – copy them all, and then use Paste Special > Values
2) UPPER / LOWER
Two formulas for the price of one here!
These both work the same as the “Proper” formula above – but convert all text in to upper or lower case case – e.g “COLIN WELCH” or “colin welch”
The IF statement is one of the most popular formulae because of its versatility, and can be combined with a number of other formulas (see below) to get a number of different results. It can be tricky to understand at first – but very powerful once you’ve cracked it!
The formula looks like this: IF(logical_test, [value_if_true], [value_if_false]).
If the data meets your given criterion (or logical test), the result you’ve put in for [value_if_true] will display in the cell. If not, it will be the result for [value_if_false]. Simple!
Example: Put this formula in to a cell =IF(A8>100, “Over 100″, “Under 100″)
If you put a number greater than 100 in to A8, the cell you have put this formula in would display the text “Over 100″, if not, it would display “Under 100″
The AND formula evaluates data according to given conditions, and returns the results “True” if all the results are true or “False” if any are false. It is often teamed with the IF formula to assess whether data meets all of a number of criteria.
The syntax is =AND(logical1, logical2, …) – you can list up to 255 conditions that must ALL be met if the formula is to return a TRUE result.
Example: Put this formula in to a cell =AND(A8>100, A9>100)
If you enter a number greater than 100 in A8 and in A9, the cell you have the formula in will display TRUE – if either of the numbers in A8 or A9 are less than 100 the cell will display FALSE
OR is very like the AND formula. It also evaluates data according to given conditions, but while all conditions have to be true for AND to return “True”, OR will return “True if one condition is fulfilled and “False” only if none of the conditions are met. This also works well teamed with IF, since it will allow the data to be assessed according to whether it meets some of the given criteria.
Example: Put this formula in to a cell =OR(A8>100, A9>100)
Now if you enter a number greater than 100 in cell A8 or in A9, the cell you have the formula in will display TRUE – if neither of the numbers in A8 or A9 are less than 100 the cell will display FALSE
SUMIF is a variant of the IF statement, which finds all the data that meet your criterion and returns the sum of them. SUMIFS, which is available in Excel 2007, does exactly the same but allows a number of criteria to be given and only sums the data which meets all conditions.
The syntax is =SUMIF(range, criteria, sum_range)
Example: Enter this formula in a cell =SUMIF(A4:A7, “>100″, A4:A7)
Bit more complicated now – but read the formula through step by step. The first section is the range - we’re asking Excel to look at all of the values in cells A4 to A7 and to see if they meet our criteria. The next part is our criteria - we’re looking for numbers that are greater than 100. Finally, the sum-range is the cells we’re asking Excel to add up, provided the criteria is met.
Try it out! Put the formula above in to a cell, and then put some numbers in cells A4 to A7, some lower than 100 and some higher. Excel will add up only those that are higher than 100 and display the result in your formula cell.
COUNTIF is also a variant of the IF statement, but instead of adding the data, like SUMIF, it gives you the number of cells that meet your criterion. COUNTIFS does the same, but for a number of criteria.
Example: Enter this formula in to a cell =COUNTIF(A4:A7, “>100″)
This is similar to the example above -but we’re just asking Excel to count the number of values that are higher than 100. Again, put some values in to cells A4-A7, some higher than 100 and some lower, and Excel will count how many are higher than 100 for you.
VLOOKUP will search a column of data (it’s vertical, so “V”), to find a given value. It will then return the value in a specified column to the right of the result. This is a really great formula for large spreadsheets, especially when you need to find your data in a hurry.
Example: OK – So let’s say you’ve got a massive list of people’s names, and in the column next to their name there’s a letter code that indicates which city they live in. Problem is – you don’t know which letter corresponds to which city – and that information is stored somewhere else in your spreadsheet (in another table, say).
So – in the column next to the letter, we can enter a VLOOKUP formula that will reference that other table, and fetch the result for you.
The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
That looks scary – but it isn’t – here’s what each bit means! I’ve also uploaded a very simple VLOOKUP example for you to download and look at to clarify.
lookup_value is the value we’re going to go and look up in our other table – so in our example, here you would but the address of the cell with the city code you want to look up
table_array is the location of where your lookup table is in your spreadsheet (i.e the table that’s contains all of the city codes, and the full name of the city), in this case, that table is in cells J3:K7. Importantly, if you’re going to be copying this formula to lots of other cells, then you need to use absolute cell addresses (using the $ symbol), because the lookup table is always going to be in the same place – so in this case we use $J$3:$K:$7
col_index_num is the column in your lookup table where the value you’re looking for is – in this case the city name. In our example, this is in the 2nd column of the table, so we simply enter the number 2.
Finally, the range_lookup is always either TRUE or FALSE. If you want Excel to lookup the exact value from your table – always use FALSE – if you use TRUE, Excel will always try to find the next nearest value – which we don’t want.
So our formula in this Excel VLOOKUP example is:
=VLOOKUP(B4, $J$3:$K$7, 2, FALSE)
HLOOKUP does the same as VLOOKUP, except that it searches in a horizontal (“H”) list of data for the given value, and returns a value in a specified row in the same column as the result.
So – if your lookup table had its values in horizontal rows instead of vertical columns, you’d use this.
So that you can compare the two – here’s a very simple Excel HLOOKUP Example to download
CONCATENATE takes the content from two or more cells and joins them together in a single cell. This works for both numbers and text. If you’re worried about misspelling the long and little-used word, you can use a “&” sign instead, inserted between the cell names in your formula – e.g. =(A1&A2) will combine the text from cells A1 and A2.
Example: The obvious application for this is if you have first-names in one column and surnames in another, you can use this formula to display full names in another cell.
So, if I have “Colin” in A1 and “Welch” in A2, I can use =(A1&A2) in another cell to put the two values together.
BUT – this will actually return ColinWelch – with no space. To force Excel to put a space in, use =(A1&” “&A2)
These formulas won’t make you an expert at using Microsoft Excel, but they should give you a few more useful things to do with it before you are ready to tackle its more difficult formulae.