Excel, while a fabulous tool for PPC, is not without its occasional hang-ups. In this post, we’ll go through some common problems, and how to fix them. To kick things off, it may be helpful to review Excel errors and what they mean.
| The Error | The Meaning |
The Fix |
| #NAME? | Excel doesn’t recognize text in a formula | Ensure your formulas are typed correctly, and that any text is enclosed in quotation marks |
| #VALUE! | Your formula has the wrong type of argument (i.e., a mix of text and numeric fields) | Use functions instead of operators & check that you’re referencing the correct cells |
| #DIV/0! | You tried to divide by zero, oops! This isn’t possible! | Insert the IFError function before your formula |
| #REF! | A cell that the formula is referring to is not valid | Adjust the formula |
| #N/A | The formula did not find a matching value | This is common in VLOOKUP functions. Either there is no matching value, or something is up with your formula (likely if all VLOOKUPs return #N/A) |
If you do end up with an error, Excel will provide a menu of help options.
Simply click “Help on this Error” option to go to a support page with tips to fix the error.
1) My VLOOKUP Isn’t Working
Ah, the good old vlookup function. Helpful for compiling data, but frustrating when a mistake is made.
Most of the errors I see are with the table_array and the col_index_num parameters. Make sure that you are selecting the entire table that you want the formula to reference and not just the column that your lookup value is contained in.
In the example above, the second column that contains the value we want to return isn’t included in the table array. Whoops, there’s that pesky #REF! error!
2) My Pivot Table Values Are Incorrect
When using pivot tables for calculated metrics such as click-through rate, conversion rate, average CPC, etc, it’s important to use a calculated field rather than using the column that you pull from AdWords. The scenario below illustrates why.
Simply put, taking the average of all of the individual CTRs gives you a much different value than the calculated CTR.
3) Help, Index Match Isn’t Working
Index match can be a tricky set of nested functions to get familiar with, but once you do it’s easy peasy. The first item to remember is that you specify the array, the row, and then the column. The order is important.
Another area where mistakes can be easily made is the match type field. This can be -1, 0, or 1, with the default being 1. Here’s what those numbers mean (from Office support).
1 (or omitted): MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_arrayargument must be placed in ascending order.
0: MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1: MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_arrayargument must be placed in descending order.
Helpful hint – if you need to match the row with multiple criteria, you can do so like below.
There are a couple other options for looking up data with multiple criteria, which you can check out here.
4) I Sorted Data With A Column And Now My Formula Is Referencing The Wrong Cell
Let’s say you’re counting the number of ads in each campaign. You generate a list of campaigns and use a simple CountIF function. After you write your formula, however, you decide to sort your campaign names. Oh no! Lo and behold, your count formula is now referencing the wrong cell.
What happened is that the count formula kept the reference to that particular cell, but didn’t filter along with it. So, how do we fix it?
The first step is to create a named range for the Campaign column (aptly named: “Campaign”). From here, we simply use this named range in place of the individual cell reference.
5) These Numbers Don’t Make Sense
Sometimes when formulas aren’t returning values that you’re expecting to see, you may have the wrong data selected. Luckily, the fix is simple. Double click your formula to have Excel highlight the cells it’s referencing. From here, you can grab the highlighted area and drag it to another location, or manually type in the correct cell references. Named ranges are a great tool to avoid this problem.
Closing Thoughts
While Excel is not without its difficulties and flaws, with a little practice (and maybe an hour or two of reading through support forums) you’ll be an Excel pro in no time.
