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.
Also read 3 Crazy Excel Formulas That Do Amazing Things
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:
Also read
- Advanced PivotTables: Combining Data from Multiple Sheets
- 10 Helpful Excel PivotTable Tips for Quick and Efficient Data Analysis
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:
Also read Excel Count Functions Illustrated
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)
Time saved and errors avoided.
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!
Also read
- Top 10 Tips & Best Practices to Optimize and Speed-up Excel Formulas
- Work Faster in Microsoft Excel: 10 Secret Tricks
Courtesy: clickz.com