Home  › Search › Paid Search

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.



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:


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:


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.


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:


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:


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:



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.


ClickZ Live Chicago Join the Industry's Leading eCommerce & Direct Marketing Experts in Chicago
ClickZ Live Chicago (Nov 3-6) will deliver over 50 sessions across 4 days and 10 individual tracks, including Data-Driven Marketing, Social, Mobile, Display, Search and Email. Check out the full agenda and register by Friday, August 29 to take advantage of Super Saver Rates!


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!



Featured White Papers

IBM: Social Analytics - The Science Behind Social Media Marketing

IBM Social Analytics: The Science Behind Social Media Marketing
80% of internet users say they prefer to connect with brands via Facebook. 65% of social media users say they use it to learn more about brands, products and services. Learn about how to find more about customers' attitudes, preferences and buying habits from what they say on social media channels.

Marin Software: The Multiplier Effect of Integrating Search & Social Advertising

The Multiplier Effect of Integrating Search & Social Advertising
Latest research reveals 68% higher revenue per conversion for marketers who integrate their search & social advertising. In addition to the research results, this whitepaper also outlines 5 strategies and 15 tactics you can use to better integrate your search and social campaigns.



    • Chinese Speaking Copywriter
      Chinese Speaking Copywriter (Agora Financial) - BaltimoreDo you speak Chinese? Are you interested in economics and finance or want to learn more...
    • Video/Digital Media Designer
      Video/Digital Media Designer (Confidential) - Delray BeachFull service corporate video production studio looking to hire a creative, and highly...
      HEAD OF SALES (OZONE MEDIA) - Santa Clarawww.ozonemedia.com HEAD OF SALES POSITION Reporting to the founder & CEO, Kiran Gopinath, the Head...