Home  › Search › Paid Search
realtime

5 Quick Excel Tips for Marketers and SMBs With No Time

  |  August 22, 2013   |  Comments

Save time and improve your reporting skills with these important tips.

When a skilled analyst or marketer opens Excel, their quick, graceful movements may appear to be magic. But if you break down their movements you'd see a series of keyboard shortcuts, skillful uses of formulas, subtle clicks of the mouse, and habits built over years of practice.

If you're a small business, digital marketer, or analyst, chances are you open Excel a few times a week (if not every day). If you pick up just one tip from this column, it may save you hours each year.

Here are five Excel tips for digital marketers to help save you time and improve your reporting skills.

  • Use the TEXT() function for days of week and much more
  • Quickly group by months or weeks in pivot tables
  • Adjust column width with a double-click
  • Use wildcards to find words in ads or keywords
  • Get rid of #DIV/0! in CPA calculations for improved accuracy

Excel Tip No. 1: Use TEXT() to Convert 10/1/2012 to "Monday" or "Oct-01"

When creating a performance report, have you ever wanted to see if CPCs or conversion rates on Mondays were different than Saturdays? Or maybe display 10/1/2013 as "Oct-01"?

There are a few ways to do this, but a very quick and flexible way is to use the TEXT() function. TEXT() converts a number into text values, which can be formatted into dates, days of the week, months, or even fractions like 5 ½. All the recognized formats can be found here. Check them out because this one formula is a timesaver whenever you have data over time to analyze.

textformulaexcel

1-date-to-text

Excel Tip No. 2: Easy Grouping by Date in Pivot Tables

If you frequently pull performance reports with a daily grain of data, you might miss a handy pivot table feature called "Group." Use this trick for a fast way to move from daily summary into months or even weeks with just a few clicks.

In a pivot table, move your "date" data into the rows of the pivot table, and add a value. Then within the pivot table, right-click any date, and in the open dialog box choose how to show your data by time segments.

In the animation below we'll convert 30 days' worth of data into seven-day chunks:

2-date-grouping

Excel Tip No. 3: Adjust Column Width With a Double-Click

If you've ever opened a .CSV file in Excel you'll notice all the column widths are fixed. Always. This isn't helpful when you're trying to read ad copy or find keywords in a report because the data appears cut off. Excel has a feature that will quickly adjust a column's width to the widest entry.

Just select the column or columns you want to expand, and then double-click the dividing line between them. Here's how to use it:

3-column-width

Many of you know this trick, but it often goes overlooked.

Excel Tip No. 4: The Power of Wildcards and COUNTIF()

Likely an underutilized tool in your Excel toolkit is a set of three characters called wildcards. They're incredibly useful when you're looking for variations of keywords, ads, misspellings, and much more.

4-wildcards-table

A useful application of wildcards is to count the number of ads or keywords that contain your brand terms, or lower ROI phrases like "free" or "log in." Surfacing these insights is easy when you use the COUNTIF or SUMIF formula with wildcards. For example:

4-countif-table

This formula becomes very useful as the list of keywords you want to find grows. You can adapt the formula to find an increasing number of keywords easily, as seen below:

4-wildcards

Excel Tip No. 5: Defeating Division by Zero Errors in Pivot Tables

A problem often experienced using pivot tables is the dreaded #DIV/0! error (this just means you can't divide by zero). This can happen when using calculated fields to identify opportunities by CPA (your ad spend/conversions), for instance. If you ignore the error, you're missing out on valuable information - a keyword can spend thousands and still not convert a single time.

The solution to this problem is adding the IFERROR() formula to the calculated field. Add the IFERROR() formula and make sure to return "spend" or "cost." Open the Calculated Fields dialog again, go to the CPA field, and change the formula to:

=IFERROR(Spend/Conversions,Spend)

5-iferror

Time saved and errors avoided.

Great Resources for Excel Tips

If you've made it this far, here is a reward: 100 Excel tips to dive into from one of my favorite Excel resources. And for your reference, here are a few blogs and Twitter feeds from Excel experts to keep track of:

There are so many capabilities built in to Excel, I doubt there is anyone who knows every trick. I hope you've found something new that will save some time and help make your daily work a little easier.

If you have a great tip that you'd like to share, please tell us about it in the comments! Please share them, pretty please!

Image on home page via Shutterstock.

Tags:

ClickZ Live Chicago Learn Digital Marketing Insights From Leading Brands!
ClickZ Live Chicago (Nov 3-6) will deliver over 50 sessions across 10 individual tracks, including Data-Driven Marketing, Social, Mobile, Display, Search and Email. Check out the full agenda, or register and attend one of the best ClickZ events yet!

ABOUT THE AUTHOR

John Gagnon

John Gagnon is a Bing Ads Evangelist (aka “search nerd”) at Microsoft. He has worked for both Bing Ads and Google AdWords, and is a frequent speaker at digital marketing conferences such as SES, SMX, and others. He has advised hundreds of clients ranging from Microsoft teams like Windows and Internet Explorer to small businesses like local garage door repair shops just getting started.

COMMENTSCommenting policy

comments powered by Disqus

Get the ClickZ Search newsletter delivered to you. Subscribe today!

COMMENTS

UPCOMING EVENTS

UPCOMING TRAINING

Featured White Papers

Google My Business Listings Demystified

Google My Business Listings Demystified
To help brands control how they appear online, Google has developed a new offering: Google My Business Locations. This whitepaper helps marketers understand how to use this powerful new tool.

5 Ways to Personalize Beyond the Subject Line

5 Ways to Personalize Beyond the Subject Line
82 percent of shoppers say they would buy more items from a brand if the emails they sent were more personalized. This white paper offer five tactics that will personalize your email beyond the subject line and drive real business growth.

WEBINARS

    Information currently unavailable

Jobs