Excel and data analysis are part of day-to-day digital marketing. Whether you’re auditing backlinks, doing keyword analysis, putting together comprehensive website audits, or simply writing up some fresh title tags, as an SEO you need some ninja Excel skills.

Feeling as though you missed the memo on a few insider SEO tricks? Not to worry, we’ve got you covered.

Click here to check out our Excel Formula Cheatsheet

Here is a list of Excel formulas to help you hack your next excel task:

To add multiples values from different cells:

SUM: The SUM function is used to make large scale addition a breeze.

When to use it:
Whether you’re tallying up the number of inbound links after exporting a CSV report, or just want to know the total search volume for a list of terms after doing keyword analysis, the SUM function is a simple way to go.

Syntax: SUM(number1;number2;…)
Formula Example: =SUM(B2;B7;B13)

Simply type =SUM in the cell you’d like your total to appear. Then open a bracket followed by the value you’d like to add (eg 7) or name of cell (eg C12) with values you’d like to add up. Separate each cell name with a ; and when you’re done, close the bracket and hit enter.

HINT: You can also add a range of values by adding a colon. For example: =SUM (C2; F9; A2:A9) will add the values in cells C2, F9 and all the values from cell A2 – A9.

Click here for examples of the SUM function.

---

To find out the number of characters in a cell:

LEN: The LEN function returns the number of characters in a text string / cell.

When to use it:
The LEN function can be useful when writing your SEO best practice title tags and meta descriptions. It will help you ensure that they’re not too long or too short.

Syntax: LEN(text/cell)
Formula Example: =LEN(A6)

Select an answer cell and type =LEN and open a bracket. Now click on the cell you’d like to know the answer for (or enter its name), close the bracket and hit enter.

You will now be able to see exactly how many characters your selected cell contains, including spaces and all punctuation.

Click here for examples of the LEN function.

---

To change the case of text without having to retype it:

LOWER / UPPER / PROPER: The LOWER, UPPER and PROPER functions are used to change the case of text.

As you might have guessed, LOWER changes all characters to lowercase, UPPER changes all letters to uppercase, and PROPER changes the first letter of each word to uppercase because that’s, um, proper.

When to use it:
When pulling a CSV report from, let’s say, an SEO auditing tool, the case of some characters might not be the way you desire them to be. Sometimes it’s old title tags that are all capital letters, or a list of names that are in lowercase. Whatever the case (pun intended), these functions can help you manipulate the case of characters in a cell.

Syntax: LOWER(text/cell)
Syntax: UPPER(text/cell)
Syntax: PROPER(text/cell)
Formula Example: =UPPER(A2)
Formula Example: =LOWER(A3)
Formula Example: =PROPER(A4)

To use this formula type =Upper in the cell of your choosing, open a bracket and type the name of the cell(s) you want turned into upper case. Now close the bracket and hit enter.

It’s worth noting that you cannot type the formula in the cell you’re trying to change, you’ll have to do it somewhere else and then copy the new text across.

Click here for examples of the LOWER, UPPER and PROPER functions.

---

To combine the data in multiple cells:

CONCATENATE: The CONCATENATE function is used to join multiple text strings / cells so that the content appears as one in a new cell.

When to use it:
We’ve all found ourselves in a situation when we needed to combine multiple cells, or just add some extra text to a list of cells. Like when having a list of relative URLs, but wanting to add the domain to make them absolute URLs. Or simply wanting combine text in Name and Surname columns.

Syntax: CONCATENATE(text1, [text2], …)
Formula Example: =CONCATENATE(A3;B3;C3)

Hint: You can also add additional ‘freestyle’ text to your concatenated cells. For example, if cell A1 says ‘Hi’, cell A2 says, ‘How are you’ and you want cell A3 to say ‘Hi, How are you today, Joe’, you do the following:

=CONCATENATE(A1;A2;” today, Joe”)

Now you’ll get the contents of the two selected cells, as well as your own addition, all combined in one new cell. Magic.

Click here for examples of the CONCATENATE function.

---

To tell if a cell meets certain criteria:

IF: The IF function is used to tell IF a condition is met in a certain cell. You’re basically telling Excel ‘I want to know IF the following cells…(you fill in the blank).

You have a list of months with goal conversions, and simply want to check which months met their monthly goal conversion target.

For example, if I I want find out IF a cell contains a number greater than 12, I’ll use the IF function.

Syntax: IF(logical_test, value_if_true, [value_if_false])
Formula Example: =IF(A2=”>12″;”Yes”;”No”)

logical_test = The set condition.
value_if_true = The value returned if condition is met (e.g YES)
value_if_false (optional) = The value retuned if condition is not met (eg NO).

So just type in =IF(, now enter your criteria in quotation marks. Add a semicolon and YES in quotation marks (or whatever you’re like Excel to show you if the criteria is met). Now add a semicolon and “NO” in quotation marks. Close your bracket and hit enter.

Click here for examples of the IF function.

---

To add multiple values IF they meet certain criteria:

SUMIF: The SUMIF function is used to add up only certain values in a range (i.e. only if they meet specific criteria).

When to use it:
So you’ve pulled a CSV report from Google Analytics, and would like to add up the number of goal conversions (in one column) from a specific country (in another column). This can easily be done with the SUMIF function.

For example, you might have a whole lot of numbers in a range (column C), but you’d only like to add up the numbers if another cell (in that row) is ‘Australia’.

Syntax: SUMIF(range, criteria, [sum_range])
Formula Example: =SUMIF(A2:A13;”Australia”;C2:C13)

Now you’ll see the sum of the numbers greater than 300.

Click here for examples of the SUMIF function.

---

To add multiple values IF they meet more than one criteria:

SUMIFS: The SUMIFS function is used to add up values in a range if they meet more than one specific criteria.

Similar to the example above, you want to add up the number of goal completions from a specific country. Only this time, they need to be from a specific browser and more than 300.

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Formula Example: =SUMIFS(C2:C13;A2:A13;”Australia”;B2:B13;”Google”;C2:C13;”>300″)

Click here for an examples of the SUMIFS function.

---

To count how many cells meet a certain criteria:

COUNTIF: The COUNTIF function is used to count the number of cells that meet your set criteria.

For example, you have a list of keywords with their search volume in another column, and you want to see how many of those keywords have a search volume of more than 450.

Syntax: COUNTIF(range, criteria)
Formula Example: =COUNTIF(B2:B13;”>450″)

By using this formula excel will count only the cells which contain the criteria you set.

Click here for examples of the COUNTIF function.

---

To return a specified value if a formula evaluates to an error:

IFERROR: The IFERROR function helps to ensure errors don’t show, but rather whatever you want it to show.

When to use it:
When using formulas in Excel, we sometimes get errors. Like when dividing two numbers in separate cells, but one of the numbers are 0.

Syntax: IFERROR(value, value_if_error)
Formula Example: =IFERROR((A4/B4);”Incorrect”)

Now Excel will show ‘Incorrect’ if there is a formula that doesn’t work the way that it should. This is particularly helpful when you have multiple cells and multiple formulas.

Click here for examples of the IFERROR function.

AND / OR: The AND function returns TRUE if all conditions are met, and returns FALSE if one or more of them are not met.

For Example: Checks to see if cell C2 is more than 200 AND cell C13 is more than 300.

The OR function returns TRUE if one or more conditions are met, and returns FALSE if one or none are met.
For Example: Checks to see if cell C2 is more than 300, OR if cell C13 is more than 400.

The AND and OR functions are typically used together with the IF function.

Syntax: AND(logical1, [logical2], …)
Syntax: OR(logical1, [logical2], …)
Formula Example: =AND(C2>200;C13>300)
Formula Example: =OR(C2>300;C16>400)

Click here for examples of the AND and OR functions.

---

If you want to find something:

VLOOKUP: The VLOOKUP function is used to find things in a range. Basically, it helps you search for specific items in your spreadsheet or table.

When to use it:
For example, if you had a specific list of domains you’d like to see inbound links from, and another sheet with all domains and the number of links pointing to your website, VLOOKUP would help you find the number of links for your specific list of domains.

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example Formula: =VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

All you have to do is enter =VLOOKUP( in any blank cell, add the name of the item you’re looking for; the cell range (or name of the sheet) that contains the data, the column index number and whether you’re looking for an exact or approximate match (True or false).

Close your brackets, and when you hit enter your answer will appear in that cell.

Click here for an example of the VLOOKUP function – Note that ‘Lookup Table’ is the very last sheet name.

HLOOKUP: The HLOOKUP function is similar to VLOOKUP, but it used to find things in a range by column. The V stands for Vertical, and of course, the H for Horizontal.

Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Formula Example: =HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

Click here for an example of the HLOOKUP function.

---

Extra Excel Tips:

THE DOLLAR SIGN ($)
Placing a dollar sign before the column (cell letters) and the row (cell letters) in a formula tells Excel that you don’t want it to adjust the range / cell reference when you copy the formula. This is called an absolute cell reference.

Eg. =VLOOKUP(A4;’Lookup Table’!$A$2:$D$10;4;FALSE)
Using the above VLOOKUP example, you can copy the formula and the range will remain the same.

TEXT TO COLUMNS
Sometimes when you export a CSV file, all data is crammed up into one row, separated by a comma rather than columns. Using the TEXT TO COLUMNS wizard, you can separate them (by comma). In fact, you can separate cells using any text string. The TEXT TO COLUMNS wizard can be found under the Data tab.

Click here to check out our Excel Formula Cheatsheet

Above are just a few of the many functions of Microsoft Excel. But these are a few that SEOs might find useful. We’ve prepared a print out of the formulas to keep at hand, please find it in the link below.