This is probably going to be one of the more geekier posts on this blog but as I use Excel pretty much all day, every day I tend to use a lot more than just the standard SUM function and so I thought I would jot a few down & hopefully help others in the same position as myself. If I’ve missed any formulae that you think should be on this list, let me know in the comments!
I’ll start with a few of standard formula you can find under the fx button & then move on to some more advanced ones after, if you’re not as geeky as me feel free to bail now
=SUM: This formula does pretty much what it says on the tin, by using it you are able to calculate the sum of a selection of cells.
=AVERAGE: Again, this is another of those cells where you can work out the outcome by its name, but in the off chance you haven’t worked it out this formula will tell you the average value from a given set of cells.
=LEN: Returns the number of characters (including spaces) within selected cells. As a PPC’er this formula comes in VERY handy when creating new ads
=CONCATENATE: This function allows you to join several text strings into one text string. In versions of Excel prior to 2007 this was limited to a maximum of 30 separate strings but this limit has been raised in 2007 and newer.
=TRIM: Removes all spaces from text except those between words (i.e. additional ones at the start or end of the text string)
=LOWER: Changes all text into lower case. (You can also use =UPPER to put the text into UPPER CASE
=PROPER: Returns text in Sentence Form. (It Capitalises The First Letter Of Each Word)
=MIN: This will return the smallest number in a set of values. If you want the largest you can use =MAX
=LEFT: This will give a number of characters from the start of the text string that you specify. (If you want the opposite & characters from the end of the text string you can use =RIGHT)
=IF: This function allows you to check whether a specific condition is met. It will return one value if it is true and another if it false. These are the standard responses and you can amend them to state anything that you like. (You’re also able to use ‘AND’ ‘OR’ functions to add further arguments into t the initial test conditions to add further into the formula). NOTE: You used to only be able to ‘nest’ a maximum of 6 IF functions into one formula (run all at once), but I don’t know if this is still the case with Excel 2007 & newer.
=SUBSTITUTE: Substitutes specific text in a text string to another. Particularly useful when re-coding URLs
=VLOOKUP: This allows you to find a value within a table of data. It will look for the value in the 1st column of your table & then it will return a value from the same row of a different column (which you specify). I’ve seen/ heard a few people have problems with this function and 99% of the time it’s because of sorting issues. For this to work properly your table needs to be sorted in ascending order.
=NETWORKDAYS: This function will give you the number of whole workdays between two dates. This is particularly useful if you’re trying to calculate daily campaign budgets for campaigns that are only active during the working week.
Another great feature in Excel is the ability to create Pivot tables, these tables are very powerful & enable you to analyse large amounts of data very easily & quickly. As with the other functions mentioned in this post there is also a wizard that will help you create the table, so there’s no need to worry.
None of the functions above are exclusive either so you can nest various different functions together to get even deeper results from your data.
Hopefully you’ll find these formulas/ functions useful. As I said earlier, as someone who works in excel pretty much all day I use a lot of these functions on a daily basis when building PPC keyword lists, ad copy etc. and without them everything would take a lot longer to do, and no-one wants that!