5 Common Excel Issues And How To Fix
August 18, 2016
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
5 Tips For The Travel PPC Marketer
I’m going to focus on 5 vital tips for travel marketers. From competitors to seasonality, we have experienced the ups and downs and want to share our experience with you.
Our All-PPC Schedule for LA is Now Live! Save $400 Now»
You can now get the info you need to register before our 25% off Early Bird pricing expires on 1/31. We're adding new sessions daily to our schedule for Hero Conf Los Angeles, and it includes updates that hard-working PPC-ers just can't miss.
Be Smart In 2017: Use Seasonal Budgeting
Spend more when you get more. That's the message that needs to get across. But how do you go about looking into it?
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 Simple Beauty Of A PPC Bid Template
Sometimes the best answer is not only the simplest but also staring you in the face like. Enter the Excel template.
4 Strategies For Improving Your Click-To-Message Ad Extensions
Last year, Google heard the requests for better text tracking tools for text messages and developed the Click-To-Message mobile-only ad extension. Learn how to use!
Get The Lowdown On Amazon Vendor And Seller Central PPC Advertising
Amazon has taken the lead for first product searches amongst online shoppers. Learn about the different advertising options on Amazon and what each type offers.