Automagic! Simple Excel tips for metrics nerds

Let’s get one thing out of the way first: This is a post about Excel tricks, but I am by no means an Excel whiz. Quite the contrary—while I love, love, love, mucking around in online metrics (sing to me, response rates!), I have nearly always done so with a minimum of Excel wizardry.

But this January’s reporting may have taken me to the next level of spreadsheet geekery. As I finished up some year-end analysis, I ran across a few tips that—while they might make a true pro yawn—totally blew my mind.

So, because Excel beginners deserve kick-ass metrics too, I’m sharing a few tips I found particularly helpful:

Tip 1—Minimizing data entry by pulling from other sheets.

This is so easy it’s embarrassing. Refer to data on any other sheet in your Excel document by putting an equals sign in the destination cell, and then going to the sheet you want it to pull from, selecting the cell, and hitting return. Oh, the hours I’ve wasted copy and pasting data across sheets before now!

– Solution: This year I limited my metrics data entry to my message by message breakdown of email response metrics, and set up the formulas so that nearly all other analysis (benchmark comparisons, past-year comparisons, etc.) automatically populated on other tabs in my report.

Tip 2—Uploading weirdly formatted lists of names into your database.

This gets the prize for the most bizarre Excel problem I’ve tackled, but I was so excited to have conquered it, I’m sharing it anyway. In the past, the “Data > Text to Columns” feature always helped me format large files of supporter data so they could easily uploaded into a Customer Relationship Management (CRM) system like Convio or Salsa. But when a client received the names of supporters not only in all caps, but also with the middle initial stuck onto the first name (Ex: “RACHELH ALLISON”) I was stumped.

– Solution: To erase the middle initial no matter how long the first name, use this formula to refer to the original first-middle name cell: =LEFT(A1,LEN(A1)-1) And how to get rid of the caps problem? Refer to the all-caps name cell with: =PROPER(D1) Magic!

Tip 3—Putting metrics in context.

Here at Big Duck, we typically measure how a campaign fared to the previous year’s efforts, as well as how it compared to industry benchmarks. So how do you make those comparisons automatic and easy to scan visually?

– Solution: The formula for comparison is a snap (=This year’s data/Last year’s data-100%, or = your data/benchmark-100%) and by using conditional formatting (Format > Conditional Formatting…) to make percentages greater than zero display in green and percentages less than zero display in red, each metric comparison is made easier to scan visually.

Tip 4—Copying tables of data from HTML.

Sometimes, I’ll run a report in Convio and it will display on screen as a lovely table. But when I try to copy this table into Excel—not even “Text to Table” can sort the pasted cells into the right order.

– Solution: Credit goes to Farra for this one. When HTML tables won’t go directly into Excel, try Word. Select, copy, and paste the HTML table into Word, then under the “Table” menu, select “Convert > Text to Table” Then indicate the original number of columns and voila! It should display in a table. And unlike the HTML table, your Word table should paste correctly into Excel.

Tip 5 – Looking smart in front of colleagues.
As I said before, I am no Excel whiz. But I get so excited about spreadsheet formulas that sometimes people think I’ll know answers to their questions anyway. Silly Ducks, my knowledge comes from a higher source than actual k…

– Solution: Ask Google. Seriously. If there’s one thing more automagic than Excel, it’s Google. Just type your Excel question into the search bar and almost certainly you’ll find the answer pretty quickly on one of the bijillions of Excel forums out there. (Just last week I found the formula to find the highest and lowest values in a series this way. Thanks, internet!)

Again, as a self-confessed Excel beginner—these tricks may be low-level, but they’ve reinforced just how powerful even simple office tools can be in analyzing your campaign’s performance.

How do you use technology to help you track and analyze metrics? We’d love to hear your own tips in the Comments section!