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||
|#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.
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.
Browse By Category
The PPC Newsflash: Take Advantage of the New Google Text Ad Changes
In this podcast, Hanapin experts Matt Umbro and Jeff Allen discuss the new Google Ads update, how it will affect the PPC industry, and give you ways you can take advantage of this extra ad space.
LinkedIn Ads: How Videos are Performing
LinkedIn’s release of video ads gives advertisers an opportunity to test the informational power of video in a B2B focused environment.
Amazon Updates Seller Central & AMS Campaign Manager Features
Amazon is back at it again with more updates to the campaign manager on both the Seller Central advertising platform and Vendor Central AMS platform.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
The PPC Newsflash: Exclude Your Ads from Mobile Apps in the GDN
When we heard the news about the targeting changes, our analysts got to work...and we have a solution!
The Missing Ingredient to Your PPC Strategy: Non-Branded Campaigns
Learn how to employ a non-branded campaign strategy that will increase traffic, capture new customers, and beat the competition.
Is AI the Secret to Google Ads Success?
Stay in the running with your competitors on Google Ads, but don't stop there! Conquer them!
Load More »