Tips to Get More Out of Microsoft Excel Right Away

by | Mar 21, 2016 | Uncategorized

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

The Brand Optimization Checklist

Looking for a Brand Optimization Checklist? Every so often, it can be useful to step back and evaluate how well your brand is defined and what, if any…

Read more

If =IF(logical_test, value_if_true, [value_if_false])

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.

  • Select category:

Subscribe today to get our latest content delivered to your inbox
Your subscription could not be saved. Please try again.
Your subscription has been successful.

Follow us

Six Degrees
Six Degrees uses psycho-sensory tools and techniques to build more successful national and global brands. Brands are rooted in human perception. And our psycho-sensory approach is designed to identify deeper and richer insights from human perception and then develop brand communications that change suboptimal perceptions or reinforce the right perceptions. More than 80 percent of the information humans process is nonverbal, making it essential that brands manage the sensory signals they send out. Our people are passionate branding experts wielding powerful psycho-sensory tools to build stronger and more successful brands across the globe.

Related blog posts

Ready to talk?