A Few Basic Excel Functions To Help Analyze Social Data

If you do any sort of social media analytics work, you’ve probably got a few free or paid tools to help you crunch the mountains of data your networks spit out and translate it into something legible and actionable. Tools like that are great, but if you really want to dig into your data, the best way to do it is to roll up your sleeves and wade into an Excel spreadsheet.

Excel can be intimidating at first, but you can do a lot with even a few basic commands. Best of all, you’ll feel empowered to start building your own metrics, and you can look for new ways to analyze your numbers. Here are a few to get you started if you've never worked with Excel before.

SUM and AVERAGE

These are exactly what they sound like – they’ll spit out sums and averages of chunks of data. Let’s use the SUM function as an example.

Here, I’ve got about 150 rows of numbers that I just need a total for. It would obviously take a long time to add all those up by hand, so I’ll use the SUM function to do it in just a few seconds. First, I type =SUM( into the cell at the bottom of this column of data: 

That changes my cursor to a plus sign (+) and highlights any cell I hover over in a blue border. From there, I just need to click and drag over the cells I want totaled:

Hit enter, and voila!

To get an average of those numbers, I’d do the same thing and just replace SUM with AVERAGE.

Here’s a cool bonus trick: See that little square in the lower right of my total cell? If I have other columns of data adjacent to the one I just totaled, and I want those totaled, too, I can just click and drag the square:

LEN

I like this one for editorial calendars (yes, I still build editorial calendars in Excel) because it counts characters, which is key for Twitter. All I have to do is type =LEN( and then type in or click on the cell I want a character count for: 

Hit enter and…

Looks like I’m good to go on this tweet. (Don’t forget the click-and-drag trick if you’ve got multiple entries!)

This can also be useful if you start paying attention to what character counts work best on your other social network posts. With Facebook for example, you can pull all your post data, count the characters on each post and compare those numbers to your number of Engaged Users. That can give you an idea of the ideal length of a Facebook update. From there, you can use the LEN function again to make sure you come as close to that number as possible when you’re building your content calendar.

TEXT

This one requires you to have some data in the first place, at least for the way I'm describing it here. I use it a lot with Facebook Insights. Basically what it does is pulls text out of a chunk of data that exists in numeric form; the best example is dates.

Here’s a cell in my Facebook post Insights that’s showing me when my posts were published:

I want to be able to see what day of the week those posts were published on in an adjacent column. What I’ll do is add a blank column and call it “Day of Week.” Then, I’ll use the TEXT function to pull that data out of the cells in the E column. Here’s what the command looks like:

The d’s tell Excel that I want to see days of the week, and I typed in seven of them because I want the full day spelled out. (If I wanted, like, “Mon” for Monday, I’d just type in =text(e2, “ddd”).)

I hit enter, and then drag my little square down in the Day of Week column so I have days of the week for all my posts listed:

Now I can get a better idea of when I tend to post, and when my followers are engaging with my posts (based on Engaged Users on those days).

Keep going!

These functions just scratch the surface of what Excel can do. After you’ve gotten comfortable with them, it’s a lot easier to experiment with combinations of functions and to branch out into other Excel features.

Have questions or feedback? Leave a comment or message us on Facebook or Twitter.