Behind the Scenes: 11 Excel Functions That Will Make Your Life Easier

By Sarah Lokitis | Apr 5, 2013
More Articles by Sarah


I typically write about things I am working on, like LinkedIn advertising or the implications of search and social. This time, I thought I’d give you a behind the scenes look into how we do our work at Search Mojo and what we use to make the data we have useful to us and to our clients.

So, on that note – we are big Excel nerds. We use Excel to organize the data gained from advertising online to make smart decisions – something most (if not all) marketers need to do on a daily basis. I took a quick poll of the Search Mojo office to find out what everyone’s favorite function is and how they use it at work. Jeff, our IT manager, argues that “Excel is one of the best pieces of software out there because its ability to interface with so many data sources and provide a VBA (Visual Basic for Applications) environment, so you can turn that data into information.”

If you don’t like Excel, then this blog post may not be for you – or perhaps it will convince you to give it a try. You just may find these 11 Excel functions and features will improve your efficiency. And, everyone has time for that! 

CONCATENATE: Joins several text strings into one text string

Concatenate-in-ExcelAmanda, Nelson and Scott picked concatenate as their favorite. If you’ve ever used this function, it is easy to see why this is such a popular choice. Concatenate saves so much time performing URL engineering projects by combining multiple cells together. Especially when you are appending dynamic landing page content parameters or other tracking parameters at the keyword destination URL level, concatenate saves a lot of time.

LEN: returns the number of characters in a text string

Heidi has two favorites, but she joins Michelle with recognizing =LEN as one of the top functions for our work. LEN is awesome for writing ad copy as you can ensure you are following guidelines of character limits. Bing ads, Google ads, Facebook ads and LinkedIn ads all have different character limitations, so it’s important to make sure we are following the right guidelines. Here’s an illustration of how the =LEN formula works:

LEN example

HYPERLINK: creates a shortcut or jump that opens a document stored on the internet.

Adam, our Director of Technology, quickly called out the HYPERLINK function as his favorite. “Pretty self-explanatory, it creates a hyperlink :D”

This function doesn’t just make hyperlinks. Let’s say you have a spreadsheet of product information with id (column A) and product name (column B). If you know how the website works, you could create a link to the product page using the product name as the anchor text. In this example, HYPERLINK joins CONCATENATE: HYPERLINK ( CONCATENATE(“http://www.pretendstore.com?product_id=”;, A1), B1)

PIVOT TABLE: data summarization tool

When it’s time to slice and dice data (“in amazing ways”), Janet loves turning to Pivot Tables. Kat, our finance manager, also is a fan of the Pivot Table because it helps makes the data more legible. Without pivot tables, it would take forever to sort down from thousands of lines of raw data to key pieces of information.

challengeaccepted

VLOOKUP: Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Casey enjoys VLOOKUP because of the ability to make connections between two sets of data using one common column. For our work in SEO, VLOOKUP allows you to take two sets of data: 1) a list of keywords and the URL that is optimized for each keyword and 2) a list of keywords and their respective search rankings. After creating a ranking column, Casey can “lookup” the ranking value for each keyword.

vlookup

SUMIF: adds all numbers in a range of cells, based on a given criteria.

Jeff, our IT Manager, LOVES Excel. He has used SUMIFs a lot, which is one of the reasons it is his favorite. The syntax for SUMIF is: SUMIF( range, criteria, [sum_range] ). For this function, range is the range of cells that you want to apply the criteria against and criteria is used to determine which cells to add.

Also, he is a big fan of the IF function, in general, just because so many things someone needs to do in Excel start with that kind of question.

studyexcel

SUM function: provides a quick way to sum columns or rows of numbers in an Excel worksheet.

Our designer mostly stays out of Excel –  it’s mostly InDesign and web development programs for her. However, I’m sure you can agree with Jenny that the SUM function is wonderful because “really, who wants to do simple math if they don’t have to.” And with Excel, it’s always right!

SUM

Sort/Filter: manipulate data based on given set of criteria.

Tad, Kat and Sarah Bonner love setting up filters to quickly sort data. With Google or Bing PPC campaigns, this is useful to see what is working and what needs improvement. The ability to sort down to see the exact variation of the data you need is extremely helpful, says Sarah. Within filters, you can also use the search functionality to find all of the keyword phrases that contain a specific word.

Freeze or lock rows and columns: keeps an area of a worksheet visible while you scroll to another area of the worksheet

Alex’s first and Heidi’s second favorite is Freeze Panes. “As with most Excel functions, it just makes your work easier and faster, especially when trying to analyze a lot of data” says Heidi.

Alex, our PR Manager, does a lot of blogger outreach – which requires a lot of research and list-building. When navigating through a sea of contacts, freezing the top row helps her input the data in the correct cell. Using this same idea, using the sort option can help quickly alphabetize the cells with a click of a button!

Duplicate Values Conditional Formatting

Kari, our Content Marketing Manager, creates and works with a lot of marketing lists in Excel. The Duplicate Values Conditional Formatting allows her to highlight duplicates in a list for easy de-duping.  Conditional Formatting can even be used to highlight dates or other values you specify, to make sorting through data a snap.

Duplicate Values

COUNTIF: Counts the number of cells within a range that meets the given criteria.

Sean finds COUNTIF very useful because it helps you identify the number of occurrences of a value, or range in an array of data. This process is helpful if you need to analyze data to find customers who use a specific product, spend a specific amount of money, are in a specific age range, etc.

What’s your favorite Excel function?

Have you used all these features and functions before? Some may call some of these processes made for “lazy people”, but we’d argue that anything that can make you more efficient is smart, not lazy. Why waste time organizing data, when you can organize the data quickly and have time to analyze and act on what the data tells you? Share your thoughts below or find me on Twitter @Lokitis.

Share this article

Share on LinkedIn Share on Twitter

Receive Monthly Digital Marketing Tips

Subscribe to monthly updates from the Marketing Mojo Blog to get the latest digital marketing tips, best practices and insights - hot off the presses and straight to your inbox!

 

Blog Search