The marketing world is one of creativity, brainstorming, and artistic vision. However, if you’re in marketing, then 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 an Excel spreadsheet. That’s why we’ve compiled a list of 10 Excel functions that every marketer should know.
1. Table Formatting
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 take 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
A pivot table allows you to categorize tabular data, making it easier to evaluate large data sets to pinpoint meaningful trends. To set up a pivot table, begin by selecting your data, turn it into a table (see Step 1), and 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.
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.
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.
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
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.
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 (e.g. 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).
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 of 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
If you want to identify trends in a data set, conditional formatting is one of the best ways to do that. Select your data, click “Format” and then “Conditional Format.” A “Manage Rules” dialog box will pop up where formatting rules can be selected to your specifications.
10. Keyboard Shortcuts
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 allows you to sum an entire column of data, CMD + 1 offers a shortcut to the Format Cells window, CMD + K is a fast way to insert 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 to the world of marketing. Now go and start practicing; it can take a little bit of tinkering before you become comfortable with these shortcuts.
What did we miss? What Excel function would you add to the list?