This week, Simply Measured delivered a springtime gift to anyone who’s been yearning for Excel and social media strategy to get married and make beautiful babies (i.e. anyone who’s got a happy hankering for easy social media data analysis). So what is it? Drum roll, please.
Our new guide Excel Functions Every Community Manager Should Know highlights some key Excel functions and know-how from our team of expert social media analysts, and is ready to take a prime position on your digital shelves. Download the guide, and get to know Senior Analyst David Lukas – who helps build our fabulous reports in Excel – while you’re at it.
What I did before Simply Measured:
I worked in finance – most recently, investment banking.
Alton Brown and Ron Swanson.
A day in the life:
I design and build the reports that are the end deliverable to our customers alongside our incredibly smart, passionate Simply Measured team.
Me + Excel =
I learned rudimentary Excel from my Dad, and mastery developed out of necessity during my career in finance. Here at Simply Measured, our reports are built in Excel, which is kind of crazy. They’re not web-based dashboards that give you a CSV dump you can open in Excel. They begin as Excel reports, and then we render them on the web. The calculations, charts, tables…all done in Excel.
Excel’s super power, IMHO:
Probably scenario modeling. The ability to build complex models, and to quickly and easily see how their outputs respond to varying inputs. I shudder to think how much work just a simple model would have required before spreadsheets. The ubiquity of Excel improves the quality of business decision-making tremendously.
Something I’d like to learn:
I want to learn to code. There’s so much more cool stuff you can do.
Fellow Simply Measured analyst I’d like to be stuck in an elevator with for 24 hours:
Agasthya was my mentor here at SM, and we’ve turned out some pretty awesome solutions together over the last couple years. I feel like between the two of us we could get out of that elevator within about 27 minutes, give or take. And on the off chance we couldn’t, we would probably argue the merits of AL vs. NL baseball for 24 hours and hopefully he would finally come to the realization that pitchers have no business swinging bats.
Favorite Social Media Channel:
Favorite Seattle Restaurant:
Kisaku or Skillet Diner
Favorite Musical Artist:
Favorite Simply Measured Memory:
Climbing Mount St. Helen’s with three other Simply Measured peeps.
What inspires me:
Working with amazing people who have a shared vision.
Most-Used Excel Function(s):
A lot of what a digital analyst does is quantitative, but virtually every piece of content we deal with is at least partially text–whether it’s a Tweet, an Instagram caption, a YouTube video title, or a username. A few basic tricks in dealing with text can make your life a lot easier.
That’s where we use LEN and SUBSTITUTE. These two functions are great to have in your toolbox. LEN returns the number of characters in a string of text.
SUBSTITUTE replaces a sequence of characters with another sequence of characters. It requires three inputs (called “arguments”): your reference text, the sequence of characters being replaced, and the sequence of characters you’re replacing it with.
Be careful, though—SUBSTITUTE is case-sensitive in deciding which strings to replace. So we’re having lots of fun measuring and replacing text strings, but if you put them together, LEN and SUBSTITUTE can do some pretty heavy lifting.
For example, what if you want to know how many times a word or phrase occurs in a bunch of text? There isn’t a straightforward way to do that in Excel. SEARCH can tell you whether or not your word or phrase occurs, and what its relative position is, but it will only find the first occurrence. COUNTIFS can check for a text string across a range of cells and return a count of the cells that include the string, but again, it won’t tell you anything about multiple occurrences in a single cell. But check this out:
I know! I could have sworn Jim Mora said “playoffs” a dozen times, but it was only three! What we’re doing here might look confusing, but the hardest part is really just keeping the different sets of parentheses straight. It’s pretty simple if you break it into steps:
=LEN(B2) tells us the total number of characters in our original text.
=LEN(SUBSTITUTE(LOWER(B2),C2,””)) is taking our original text in B2, making it all lowercase using LOWER (to avoid the case-sensitivity issue mentioned above), searching it for the string in cell C2 (“playoffs”), and replacing every instance of that string with “”, which is essentially deleting it (yes, SUBSTITUTE can do that!).
Then we subtract 2) from 1). This number is equal to the total mentions of “playoffs” times the length of the word “playoffs.” Finally, we just divide the whole thing by LEN(C2), the number of characters in “playoffs”, to get the number of times our search term was mentioned.
There are a ton of other ways you can use these two functions for operations that don’t directly involve string length or substitution. If you find yourself floundering with text parsing in Excel, and you intuitively know what it is you’re trying to accomplish but just can’t think of how to get there, there’s a decent chance that LEN, SUBSTITUTE, or both will be part of your solution.
To learn more from David and our other awesome analysts about how Excel can make your daily social media management go more smoothly, check out our guide Excel Functions Every Community Manager Should Know – it’s downloadable for free right here, right now.
Download this FREE guide today!