May 7, 2018
10 Excel Functions Every Marketer Should Know
The marketing world is one of creativity, brainstorming, and artistic vision. However, if you’re in marketing, you know that when it comes to the day-to-day operations of a marketing campaign, this is only part of the picture.
A lot of your time as a marketer also goes into analyzing data, checking budgets, and running statistics—which means you likely spend a significant amount of time using Excel spreadsheets.
While spreadsheets can be a necessary half-measure for managing marketing work, they usually fail to keep up as organizations grow. They are also prone to becoming disorganized, complicated, and lengthy, which means finding the information you need can be frustrating and time consuming.
As this happens, consider upgrading to a bona fide work management solution that doesn't require manual data entry and analysis.
But, for those situations where you find yourself using a spreadsheet, we've compiled this list of 10 Excel functions every marketer should know, to make your job a little easier.
1. Table Formatting
What it does: transforms your data into an interactive database.
Table formatting may seem like a basic tool, but it is one that few marketers seem to take advantage of. Table formatting allows you to take your data range and turn it into an interactive database, making it easier to make actionable decisions based on that data.
To set up a table, click on any cell in your dataset, and then select Home>Styles>Format as Table. From there, you can personalize your settings until you create a clean and interactive worksheet.
2. Pivot Tables
What it does: summarizes data and finds unique values.
A pivot table allows you to categorize tabular data, making it easier to evaluate large datasets to pinpoint meaningful trends.
To set up a pivot table, begin by selecting your data and turning it into a table (see tip one). Then select Pivot Table. Open up a new worksheet and use your pivot table builder to sort your data as desired. This video can walk you through the basics of a pivot table.
What it does: creates bar, column, pie, or line graphs based on your data.
Charting lets you take the data you have collected and present it visually. Often, this is a much more appealing way to look at data, especially if you are presenting to clients or other key stakeholders.
To make a basic chart, select all of your relevant data, click Insert from the top menu, choose the type of chart you would like to create, and make appropriate choices from the drop-down menu.
Even a basic chart is better than nothing, but if you want to step it up, check out "10 Simple Tips to Make Your Excel Charts Sexier."
What it does: counts the number of cells in a range that meet specified criteria.
COUNTIFS let you quickly slice your data and get a count of the number of cells within a particular range that meet provided criteria. The syntax for the COUNTIF function is simple: =COUNTIF (range, criteria).
As is the case with any Excel function, the range defines the cells you want to include and the criteria specifies what you are looking for, whether that be a number, a text string, or an expression.
What it does: adds up all the numbers in a range of cells that meet specified criteria.
The SUMIFS function offers another way to extract specific data. SUMIFS is very similar to the COUNTIF feature, but rather than counting the specified data, it sums it up for you. Using SUMIFS, you can specify more than one condition.
For example, you could use the SUMIFS feature to add up all the sales of a particular product made by a specific salesperson. The basic syntax for the SUMIFS feature is =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2…).
We may be getting into slightly more advanced functionalities now, but if you master the most basic functions, you’ll catch on with these quickly.
6. IF Statements
What it does: returns one value if a specified condition is true and another if it is false.
Using the If function, you can quickly determine whether data meets specified conditions. The syntax for an IF function alone is simple: =IF (logical_test, value_if_true, value_if_false). As you may deduce, based on the “if true” and “if false” tests, this function allows for two possible outcomes.
However, you can also use a nested IF. A nested IF allows you to replace the value_if_false portion of the syntax with another IF statement, therefore allowing you to break down data even further.
What it does: combines text from multiple cells into one cell.
The CONCATENATE function is another one that may seem simple, but that can save a lot of time.
Say you have two columns of text that you want to bring together (for example, a person’s first name and their last name). Rather than retype all the information, you can use the CONCATENATE function to bring these two columns together.
Start by inserting a new column where you intend to collect this data. The syntax for this function is =CONCATENATE (cells to be combined). For example, =CONCATENATE (A2, B2) would merge the A2 and B2 cells (in, say, cell C3).
What it does: finds a value in one column and then finds the corresponding value in the same row but a different column.
The VLOOKUP function (which stands for vertical lookup) allows you to search your table for a certain value and then output its associated value. This function saves a lot of time and eliminates the chance of human error when you are searching a large database for a particular value.
To use VLOOKUP, add a column to your spreadsheet where you will display the found data. Select the first blank cell in this column and click Insert>Function, and then type in VLOOKUP. Once selected, a dialog field will appear allowing you to define four values for your lookup.
Here is a quick walkthrough of the VLOOKUP function.
9. Conditional Formatting
What it does: applies a specific format to a range of cells and then automatically changes the formatting depending on the value of the cells.
If you want to identify trends in a dataset, conditional formatting is one of the best ways to do that. Select your data, click Format, and then click Conditional Format. A Manage Rules dialog box will pop up where formatting rules can be selected to your specifications.
See 3 Ways Marketers can Improve Resource Scheduling to find out how you can be more efficient.
10. Keyboard Shortcuts
What it does: allows you to perform functions or bring up windows with a few keystrokes.
No list of Excel functions would be complete without the inclusion of some helpful keyboard shortcuts. These shortcuts can simplify some of the most common Excel functions.
CMD + SHIFT + T finds the sum of an entire column of data, CMD + 1 opens the Format Cells window, CMD + K inserts a hyperlink, and F11 turns selected data into a chart on a new sheet.
You may never know all of the expansive offerings of the Excel program, but this list can give you a good start to understanding the basic features most useful in the world of marketing.
If you find your business is outgrowing spreadsheets you could benefit from a comprehensive work management solution that lets you manage tasks, project data, drafts, proofing, collaboration, and more, all from a single dashboard.
See our white paper called The High Cost of Email and Spreadsheets in Your Agency to find out how email and spreadsheets could be costing you more than $440,000.