3 Essential Excel Practices for Formatting

Spread the love


Sometimes I need to do a really simple task about 1,000 times. Although I’d love to say I have the bandwidth to pour over my accounts line-by-line, I simply don’t. Fortunately, I’ve crowdsourced some useful functions that help me maintain sanity and get things done quickly. I’ll share these processes with you and expand on where they’ve served me best.

If/Then for Logical Decision Making

This has come in handy for ad copy management. I have a massive account that has a lot of duplicate ads running and I’m working through to pause off the duplicates.

Here’s how it works

In this example, all we care about is that there are content duplicates. We’re not interested in the metrics (yet) of how one duplicate is doing compared to its doppelganger.

Step 1: Download ads that are eligible to run

Step 2: Add a column that you can concatenate your ads into

Concatenate Campaign, Ad Group, Headlines, Descriptions, and Final URLs. I often don’t include the paths as I’m more interested in the meat of the ad being redundant.

Step 3: Sort Concatenate column by ascending

  • This is insanely important for your formula to work!

Step 4: Add a Status column

Step 5: Apply the formula =IF(Concat Cell = Above Concat Cell,”Paused”,”Keep”

What’s this mean?

  • If the ad you chose matches the ad above it, pause that ad. If the ads don’t match, keep it.
  • If you’ve sorted your concats correctly, this will follow a logical succession and cover all of your ads. The first test is a control. You are guaranteed that your first ad’s concatenated cell does not match the column header “Concat”. You’ll know the test is working if it shoots back the “Keep” label.

Step 6: Check your work

  • Run through and make sure the new status labels make sense. Don’t blindly trust the machine!!

Step 7: Make an upload sheet

  • Google Ads Editor won’t recognize “Keep” as a status, so you’ll want to filter for your paused ads and put them in a separate upload sheet. You can also remove your concatenate column, as Editor will not recognize it.

Step 8: Label the duplicates you’ve just paused so you have a record of what happened.

I wait to apply this label once the updates have been made in Editor because I’ve often found that Editor will try to apply my upload as new ads if there is a label it doesn’t recognize.

You can apply this same logic for ad performance in general. Again, as long as you’ve sorted your columns first, you can apply the If/Then statement to quickly weed out and pause underperforming ads (If CTR < 3%, Pause, Keep). I find this test formula tremendously helpful for repetitive tasks and I hope it serves you well too!

Substitute

Formatting keywords can take forever and a day if you don’t use formulas. I particularly hate formatting broad match keywords because Excel is typically a total spaz about text vs. general formatting. Cue =SUBSTITUTE! Following this formula, I can quickly transform my keywords into the appropriate match type.

Step 1: Highlight your Keyword column and change your formatting from General to Text (for good measure)

Step 2: In an adjacent column enter =SUBSTITUTE(“+”&Keyword Cell,” “,” +”)

This formula seriously changed my life.

What’s happening?

  • Concatenate a plus sign to the beginning of my keyword, then substitute spaces for space plus

Step 3: Copy and Paste Special (as values) into your keyword column and move on with your life.

READ ALSO  Moz Founder Says Google Knows Everything and is Now Relying on Behavioral Data for Search Rankings

Concatenate to build Exact & Phrase Match keywords

Hey, you may have noticed that I love to concatenate things. Concats are essential for formatting Exact and Phrase Match keywords in your upload docs and you can set them up in seconds.

Step 1: In an adjacent column, insert a bracket header and a bracket closer individually

Step 2: Write out your concatenate formula using the “&” symbol (or try =CONCATENATE or =CONCAT)

Remember to anchor your bracket cells with $!

Step 3: Drag your formula down and Paste Special (values) the results over

Final Thoughts

There are a lot of different ways to skin a cat. These are just a few tools that help me pick up the pace in production work so I can get beautiful campaigns up and running. What types of Excel tricks do you use? Hit me up @ad_jennarator and @PPCHero to keep the conversation going!





Source link


Spread the love

No Comments, Be The First!

Your email address will not be published.