12 Really Useful SEM Excel Functions

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 😉

Click for larger image

=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!

This entry was posted in Advertising Stuff and tagged , , , , , , . Bookmark the permalink.

7 Responses to 12 Really Useful SEM Excel Functions

  1. My top tip for Excel (which you don’t mention here) is that you can use “&” in stead of the concatenate function.

    For example “=A2&A3” will concatenate A2 and A3

  2. Matt says:

    Hi Richard. Yep, that is a good tip, I actually remember first finding that you could use ‘&’ instead of concatenate in an Excel test I had to do years ago (possibly the only time Excel Help has actually provided any useful help at all). Either way you do it, being able to join the contents of two different cells quickly is a godsend when working in PPC! 🙂

  3. Gerry White says:

    Auto Filters (the ones that create the selector) & conditional formatting including duplicates are my two favourites

    • Matt says:

      Hi Gerry, thanks for your comment. Those two are paricularly useful as well. My post was really just concentrating on the functions behind the fx button. There are loads of other great functions in Excel that I could touch on but then my post would have been huge! It’s also good to see that from 2007 onwards Excel also has had ‘Remove Duplicates’ & ‘Text To Columns’ buttons, these have definitely made some jobs a lot easier as well!

  4. Damian says:

    you don’t need to have anything sorted in any order for =vlookup. problems are always with differences in the way that data is listed between the two columns you’re comparing. Once you’ve checked for extraneous spaces / charactors, if you really cannot see differences, then it’s worth resorting to the ‘pasting each column into a text document and then back into the spreadsheet again’ option, just to make sure that the formatting is all identical.

    it is super useful, though, as is =hlookup.

    the other favorite function is =countif; this allows you to count the number of entries in a list that match a given set of criteria

  5. Pingback: My Highlights from SES London Part 1: Introduction to Paid Search | Screaming Frog

  6. Crysatl says:

    Great reminders here! Another great one is the =datedif function. Like your work day function but counts all days, espeically useful for comparison time frames, etc. Formula is =datedif(date1, date2, “d”)

Leave a Reply

Your email address will not be published. Required fields are marked *