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.
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.
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 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.
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.
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.
September 17, 2014
September 23, 2014
September 30, 2014
1:00pm ET/10:00am PT