The Most Important Excel Functions for Marketers and Market Researchers

It’s everyone’s favorite topic: spreadsheets! OK, this isn’t going to be glamorous or provocative, but if you ever use Excel, hear me out. Whether you’re a power user or just starting out with spreadsheets, functions are the keys to productivity. They are the first step toward automating your spreadsheets and bringing your data to life. The following functions will help you be more efficient in all your spreadsheet endeavors.

The Basics

Sum =SUM(number1, [number2],…) – returns the sum of the numbers in a given range.

Count =COUNT(value1, [value2], …) – returns the number of cells that contain numbers in a given range. If you want to include text in addition to numbers in that calculation, use the COUNTA() function.

Average =AVERAGE(number1, [number2], …) – returns the arithmetic mean of a set of numbers.

Median =MEDIAN(number1, [number2], …) – returns the median of a set of numbers.

These are some of the most basic functions Excel has to offer, but they are also some of the most frequently used. They are essential to unearthing the story your data are begging to tell.

Keep in mind that “number1, [number2]” can also be expressed as a range of cells (i.e., A1:D10).

Logical Functions

And =AND(logical1, [logical2], …)

Or =OR(logical1, [logical2], …)

These three functions might not appear too flashy, but when used in combination with each other and other formulas, they can be extremely useful for data cleaning and manipulation. For example, if a teacher has two columns of grades and wants to quickly know if each student passes or fails the course, the following formula would automatically populate a cell with “Pass” or “Fail.”

=IF(AND(A1>=50,B1>=50),“Pass”,“Fail”)

This formula tells Excel that if a student’s first score (A1) AND their second score (B1) are greater than or equal to 50, they pass; otherwise, they fail.

If and Ifs

SumIf =SUMIF(range, criteria, [sum_range])

SumIfs =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

CountIf =COUNTIF(range, criteria)

CountIfs =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

These functions will save you massive amounts of time in any sizable data set if you are looking to add or count only values that meet one “If” or more “Ifs” criteria. Let’s say you have a column of numbers and you’d like to promptly find out how many of them are greater than 100. This might be easy if you only have a handful of rows, but in a large data set, you’ll want to use the following formula to give you your answer.

=COUNTIF(A1:A800,“>100”)

Be sure to explore AVERAGEIF and AVERAGEIFS for similar results!

VLOOKUP

VLOOKUP =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

One of the most popular and beloved functions of Excel, VLOOKUP allows the user to specify a unique value which Excel will use to look up and display a different piece of information that is in the same row.

Bonus

Toggle absolute/relative reference – F4

This one is a bonus because it is not a function, but rather a keyboard shortcut that every spreadsheet user should know and love. The F4 key toggles a cell reference in a formula between absolute and relative. An absolute reference (which is signified by the dollar sign in front of the column letter and row number – i.e., $A$1) means that no matter where that formula moves within a worksheet, it will reference the same cell. A relative reference, on the other hand, allows the formula to reference different cells depending on the location of the cell containing the formula.

F4 simply toggles between a relative cell reference (A1), an absolute column reference ($A1), an absolute row reference (A$1), and an absolute cell reference ($A$1).

If you have a function or shortcut you think should be included – and there are many more out there! – feel free to share it in the comments section below.