Excel Functions Every Community Manager Should Know
Tips for data manipulation from our Analysts

Excel Functions Every Community Manager Should Know
Tips for data manipulation from our Analysts Excel can be intimidating
Let’s face it, without functions Excel is just a wonderful way to store information. With functions, however, Excel becomes an amazing tool for manipulating and making sense out of large amounts of data. That’s why we love Excel so much. With a few little functions we can take a massive range of data, mine a wealth of information out of it, and turn the data into informed action items. Of course, the thought of trying to string together complex functions in Excel is enough to send anybody into a cold sweat. We couldn’t provide the depth of data or the level of insight offered in our reports without our awesome team of data Analysts who bring it all together. That’s why we asked them to walk us through some of the basic Excel functions they use on a daily basis – so that data ranges are easier to manipulate even if you’re not an Excel wizard. We created this list with community managers in mind, and we broke each function down in a way that’s easy to understand.

Our Analysts’ guide to Excel functions
So what exactly are Excel functions? Functions show a relation between a set of inputs and a set of outputs, where each input has exactly one output. In Excel, functions take the data from your workbook (the input) and turn them into information (the output) through a specific syntax. For example, you may be familiar with something like SUM.

1

The syntax of the function SUM is:

Syntax refers to the way the function is ordered, so that it returns a result. Each function starts with an equal sign (=) and are followed by the name of the function itself.

=SUM(this, plus this)

Example: =SUM(A1, A2)
This means that the cell you entered this function into will add the contents of cell A1 and A2. Pretty easy, right? Now, we’ll try something a little more complex. Say you want to find out how many days are in a given reporting period. You want to do this to figure out the average engagement within that period – say, Q1 of 2014. Without using a function, you could do this by entering the following into an Excel sheet:

The Syntax of the function DATE is:

=DATE(2014,4,1)-DATE(2014,1,1)

The Syntax for the DAYS function is:

Here, you’re telling Excel to find out how many days there are between 1/1 and 4/1. You already have the dates in date format in certain cells, and Excel is just subtracting one date from another. Here, Excel accounts for the number of days in February (there were 28) and returns the value 90. There were 90 days in your reporting period. You can also do this using the DATE function. You might also use the DAYS function.

=DAYS(“4/1/14”,”1/1/14”)

Note the specific way each date is formatted. In the first case, the date reads: year, month, day. In the second case, you must put the date in quotation marks.

2

These functions are all particularly useful when you’re trying to get a day count to average something like engagement over a period of time. You would use a function similar to:

=A1/(DAYS(“4/1/14”, “1/1/14”))
Where A1 is your engagement number and your date range is from January 1 to April 1. You can do the same thing with hours.

The Syntax for the HOUR function is:

=HOUR(“start time”, “end time”)

In the example above, the hour function returns the value 14 – the number of hours between 8:00 am, and 10:00 pm. The cells that are a time value are formatted as a time value in the number formatting options. Alternately, enter your time range into the function using quotation marks around the numbers like this:

=HOUR(“8:00 AM”,”10:00 PM”)
Now what if you want to find the week number of the year to compare two weeks worth of engagement? Use the WEEKNUM function to find out what number week (1-52) a certain day is on.

The Syntax for the WEEKNUM formula is:

=WEEKNUM(“date”)

3

3

Here, the date 3/14 falls in week 11 and the date 3/31 falls in week 14 shown on the calendar below.

WEEK

WEEK

The MONTH function can be used in a similar way. For a reporting period, MONTH is the function that allows you to, for example, determine that your highest engagement happened in “March”. These functions are pretty easy and chances are you’ve used them before; but you could do so much more with functions. You could tell Excel to discover the maximum engagement within a time frame for your Facebook Page, or ask for the handle of the Twitter user who has the most engagement. How do you do that?

Let’s ask the Analysts
4

The Analysts

Functions Lori Uses:
IF is probably the function I use most. Use this function when you have True/False criteria and you want to do one thing when the criteria are True and something different when the criteria are False.

Example: =IF(A1>10, “yes”, “no”)
In the above example, if the value in cell A1 is greater than 10, the function would return the word “yes”. This comes in handy when you want to find specific data. For instance, there is a report setting in the Simply Measured app where our customers can decide if they want to include User Posts in their Facebook Engagement. I use an IF statement to decide if I should use the function that includes User Posts in Engagement, or if I should use the function that excludes User Posts in Engagement. This comes in handy when you want to count or add cells based on specific data. COUNTIF lets you count items based on a set of criteria and SUMIF lets you sum items based on a set of criteria.

Who: Lori Williams
Her story: Hi, I’m Lori! I took a long and winding road to end up at Simply Measured. It involved a LOT of school, a little bit of nonprofit work, and eventually giving in to the fact that I am a data nerd and a tech geek. I love building reports that make social data easier to understand and more useful for our customers.

Example: =COUNTIF(A2:A5,”apples”)
Here, Excel will count how many cells in the A column contain the value “apples”.

The syntax of the IF function is:

Example: =SUMIF(B2:B25,”>5”)
In this function, if the value of a cell in the B column is greater than 5, Excel will add those values together.

=IF(logic test, value if true, value if false)
The Syntax for SUMIF is:

=SUMIF(range, criteria)

5

5

You could find out how many Tweets you sent between April 10 and April 15 with COUNTIF. If you wanted to find the total engagement on those same Tweets, you would add up all the retweets, mentions, @replies and favorites in between those two dates using a SUMIF.

The Syntax for IFERROR is:

IFERROR tells Excel what value to show if there is a problem with a function. We use IFERROR so our reports look nice even when there isn’t much data. Say you have a calculation for the percent of Tumblr posts that were quotes, but for the time period you’re looking at, you didn’t make any posts. In Excel, 0 divided by 0 gives a #DIV/0 error. You would use IFERROR to show “0” instead of “#DIV/0”. Try it yourself, substituting “#DIV/O” for “value”, and “O” for “value_if_error”.

=IFERROR(value, value_if_error)

The Syntax for NETWORKDAYS is:

NETWORKDAYS counts the number of workdays between two dates. I use this one to find out things like how many days did it take us to create our last project? Or how many days have I worked at Simply Measured? The holidays value is optional; it’s a way to exclude dates whether or not they’re actually holidays. To do this, you would list those dates in a column, and add them as a range in parentheses to exclude them.

=NETWORKDAYS(start_date, end_date, [holidays])

These Excel functions are easy to use and allow you to pinpoint data that you can build on later!

6

Functions Deepti Uses:
MAX/MIN and LARGE/SMALL are some of the simplest Excel functions. MAX allows you to find the highest number in a range while MIN returns the lowest number. Let’s say you have the daily count of fans gained by a Facebook Page and want to find out the highest and lowest gain in fans. You can easily calculate that using the functions MAX and MIN. If the fans gained are listed in rows A1 to A30, the function for highest gain in fans would be =MAX(A1:A30) and the function for lowest gain in fans would be =MIN(A1:A30). LARGE and SMALL do something similar to MAX and MIN, but go a step further. Using LARGE and SMALL, you can choose where in the range you want to find your value. For example, if you wanted to find the 3rd largest number in a range, the function would be =LARGE(A1:A30, 3) and it would return the 3rd largest number in the range. Small does exactly the opposite. =SMALL(A1:A30, 3) returns the 3rd smallest number in the range. This is helpful in finding out data like which campaigns drove the most fan acquisition, or which Tweets had the highest engagement via mentions and retweets. The best part is: they’re easy to use!

Who: Deepti Dhanjani
Her story: I went from being a Simply Measured customer who used our reports to building awesome reports for others to use. I’ve always been better with numbers than with words and I love that I get to manipulate data into meaningful insights for our customers.

The Syntax for these functions is easy:

=MAX(range) =MIN(range)
Use the following syntax for these functions:

=LARGE(range, sort criteria) =SMALL(range, sort criteria)

7

7

Functions Vikram Uses:
The tools that I use quite often are actually a collection of similar functions: LEFT, RIGHT, MID, and SEARCH.

Who: Vikram Nayani
His Story: Hey there! My name is Vikram and I’m the newest addition to our incredible Analyst team. My main focus at Simply Measured is helping to create the reports that our customers love so much. Understanding our customer’s pain points and creating solutions that help alleviate them is an awesome feeling, and I love that I help make their lives a little easier.

for example: =RIGHT(“Simply Measured is the best”, 4) returns the word “best” =RIGHT(A1,4) returns the last four characters in the cell A1 =LEFT(“Simply Measured is the best”, 6) returns “Simply”
Here’s a visual example of how these functions work. =LEFT(I25,16) returns the first sixteen characters in the cell I25.

These same rules apply to the =LEFT function:

Similarly, you can choose to isolate text or data from the middle of the cell using the MID function.

=LEFT(from this text, this many letters)
The Syntax for RIGHT is:

=RIGHT(from this text, this many letters)
Syntax of MID:

Examples of Excel MID function: =MID(“hello”,2,3) returns “ell” =MID(“hello”,2,100) returns “ello” Obviously “hello” contains fewer than 100 characters. The MID function in our example would return up to 100 characters.

=MID(from this text, start here, return this many letters)

8

You can also search for specific criteria within cells using the function SEARCH.

Examples of Excel SEARCH function: =SEARCH(“e”,”hello”,1) returns a value of 2, which is the location of “e” in the string “hello”. =SEARCH(“m”,”hello”,1) returns an “ERROR” warning because there is no “m” in “hello”)
So how are these helpful? Let’s use a real world example: by now we are all familiar with Gmail’s new tabbed interface. What if we wanted to tweak some of our emails to customers to make sure that they get put in the “Primary” tab and not the “Promotions” tab? The first step would likely be figuring out which of our contacts are Gmail users. To do this, we’ll use LEFT/RIGHT/MID and SEARCH. Any email ID contains 2 parts – user name and domain name. For example, in this email id - ImportantCustomer@gmail. com - Important Customer is the username and gmail.com is the domain. The domain always starts after “@”, so we need to figure out a way to rule out that portion of the text. This is where our friends RIGHT and SEARCH come in (with a special appearance by LEN). LEN returns the length of any string of text.

The Syntax for this function is:

=SEARCH(this, in this text, [start here]).

Example: =LEN(“Important.Customer@gmail.com”) will return 28.
If you remember from the description, the Excel RIGHT function will let you cut a portion of text from the right side of the text.

Example: =RIGHT(“Long”,2) will give you ng.
To get the email domain name, we need to get all the letters to right of email ID after the“@” symbol. How do we find that position in text? We use the SEARCH function. The SEARCH function gives the location of one text in another.

9

9

Example: =SEARCH(“@”, “Important.Customer@gmail.com”,1) will return 19.
19 is the location of the “@” in “Important.Customer@gmail.com, when you start counting from position 1. SEARCH will return an error (#VALUE!) if the text you are trying to find is not available.

Example: =SEARCH(“c”, “Vikram”, 1) will return #VALUE! because there is no “c” in Vikram.
Now we know how these work, let’s go find the domain name from the e-mail address. Assuming cell A1 contains “Important.Customer@gmail.com”, the function for getting the domain name is:

=RIGHT(A1,LEN(A1)-SEARCH(“@”,A1,1)) This will return the value gmail.com.

(Go ahead and try it! Open up Excel and type the e-mail address in cell A1, and then the function anywhere else in the sheet.) So what did we just do? The LEN function gave us the total number of characters in our text. The SEARCH function then told us what position the @ symbol was located at in our text. If we subtract the two, we will get the total number of characters after the @ symbol, and then we put that into RIGHT in order to get the domain name (the number of characters after the “@” symbol). These functions have helped me out of plenty of text related Excel jams (they continue to do so, actually), and hopefully they’ll do the same for you. Happy Excel-ing!

10

Functions David Uses:
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. Let me introduce you to LEN and SUBSTITUTE. These two functions are great to have in your toolbox. LEN returns the number of characters in a string of text.

Who: David Lukas
His Story: I’m a Senior Analyst at Simply Measured. Before I came here, I worked in a variety of analyst roles in investment banking and asset management. I love being part of a company that is building real products that solve real problems, and the fact that we’re doing it with data and analytics pretty much makes it my dream job. I’m a Seattle native; I went to UW (woof!), and Alton Brown and Ron Swanson are two of my heroes.

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.

The Syntax for LEN is:
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:

=LEN(text)
The Syntax for SUBSTITUTE is:

=SUBSTITUTE(text, “value to replace”, “value to replace it with”)

11

11

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: 1. =LEN(B2) tells us the total number of characters in our original text. 2. =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!). Now 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.

12

Functions Alex Uses:
We build all of our reports natively in Excel, so we have to use every tool in Excel’s workbench to make our reports as robust as possible. One of tools that we use constantly is the function combination INDEX/MATCH. If you’re familiar with the VLOOKUP function you already basically understand how INDEX/MATCH works. It looks for a value in one column of an array and finds the user-defined corresponding value in an adjacent column. However, this where the similarities end. The primary difference between the two operations is that INDEX/MATCH is really two combinations rather than one, and in almost every case INDEX/MATCH is superior to either VLOOKUP or it’s Luigi, HLOOKUP. Let’s start with the syntax of a typical INDEX/MATCH operation. Array is the column that you want to return data from, and row number will be the MATCH function. Lookup value is the key of the thing you are using to find corresponding information for, and lookup array is the column or array where that key value resides. The match type is optional, but usually we set it to 0 to specify we want an exact match. When we replace the row numbers argument in the INDEX function with MATCH, the result looks something like this:

Who: Alex Snider
His Story: My name is Alex Snider and I’m one of the Analysts here at Simply Measured. I’ve loved looking at charts and data since as long as I can remember and I came to Simply Measured with a background in electoral research and analysis. My (amazingly cool) job at Simply Measured is to help design and build reports that are fully dynamic and scalable. This means that our customers can guide their digital marketing programs with informed data.

=INDEX(array, MATCH(lookup value, lookup array, match type))
The MATCH function will return the row of the key that we want to find corresponding data for, and the INDEX will take care of actually moving across the row to find the corresponding data. This can be a bit confusing but TRUST ME, once you understand INDEX/MATCH you will never go back to VLOOKUP again! Let’s think of an example.

The syntax for INDEX is:

=INDEX(array, row numbers)
The syntax for MATCH is:

=MATCH(lookup value, lookup array, match type)
13

6.897 million

3.899 million

1.5957 million

Imagine a spreadsheet with two columns: in Column A are all 50 of the United States in alphabetical order. In Column B we have the population of each state. Now let’s say we want to know the population of Washington, but we are feeling particularly lazy today and can’t be bothered to scroll all the way down the page to find Washington so we want to use INDEX/MATCH to perform the work for us.

The function for this problem would read: =INDEX(B:B, MATCH(“Washington”,A:A,0))
The important thing to remember is that you always enter the column of data you want back FIRST. So why is INDEX/MATCH so much better than VLOOKUP if they essentially perform the same function? First, because INDEX/MATCH typically only looks at two columns to find the lookup data rather than an entire multi-column array, INDEX/MATCH can substantially lower the processing load on Excel. Another benefit is that, unlike VLOOKUP, INDEX/MATCH can work in any direction; you don’t need to have your identifying key on the left and then your lookup data on the right. INDEX/MATCH can work left-to-right, right-to-left, inside-to-outside, across space-time, etc. All you have to do is follow the rules (put the data you want returned FIRST) and it should work like a dream. Finally, because INDEX/MATCH doesn’t rely on the static column number arguments inherent in VLOOKUP, you never have to worry about adding/subtracting/rearranging columns and messing up your lookup argument. INDEX/MATCH couldn’t care less about the space between your key and what you’re trying to find, it just hones in on the data you want like a shark to blood. We use INDEX/MATCH extensively in our reports in a ton of different ways. A good use-case example that could be useful to a community manager is to help find the Tweet with the most engagement. Assume we have a column of all of our Tweets and a column with the engagement numbers of each Tweet.

14

The function for this example would be: =INDEX(tweet.engagement.column,MATCH(MAX(tweet.text.column),tweet.text.column,0))
By working in a MAX function into the MATCH element of the function, we can return the text of the Tweet that had the most (MAX) engagement. If you really want to be an Excel baller and step your data analysis up to the next level, INDEX/MATCH is an absolute must know!

Why use Excel tools?
As our Analysts have shown, Excel does an excellent job (pun intended!) of sorting and finding data, reducing the time it takes to analyze large amounts of information. Our reports are built in Excel because not only can you store significant amounts of data, but Excel will also help you pinpoint data trends that you otherwise may not have known. Excel gives you the option to build charts and graphs based on your data, in a way that tells your story across all your social media networks. Of course, don’t just take our word for it; try your hand at Excel magic on one of our free reports.

15

SIMPLY MEASURED SAMPLE REPORTS
Simply Measured provides a measurement and reporting solution that aggregates social media data and web analytics from more than a dozen sources and presents it in a single dashboard. We’ve provided samples of reports used by more than 30 percent of the top 100 global brands and over 80,000 users who are Simply Measured customers. Answer questions about Facebook Page performance and engagement, as well as fans, posts, “Likes”, comments and engagement trends. Dig into the most effective posts and the impact they’ve had on your Page and your audience.

Request a Free 14 Day Trial

Want to try Simply Measured?

16