10 Excel Functions, Tools, and Tips Every Marketer Should Know

The marketing world is full of big ideas and artistic vision. However, when it comes to the day-to-day operations of a marketing campaign, creativity is only one piece of the puzzle.

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 functions within 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 bloated, which means finding the information you need can be frustrating and time-consuming

Sound familiar? If so, you should 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 a list of Excel functions, tools, and tips every marketer should bookmark, to make their job a little easier.

In this excel functions guide you will discover,

• Quick reference chart
• Table formatting
• Pivot tables
• Charting
• COUNTIFS
• SUMIFS
• IF statements
• CONCATENATE
• VLOOKUP
• Conditional formatting
• Keyboard shortcuts
• Frequently asked questions

Excel functions — a quick reference chart.

Read on below for a full overview of each function of MS Excel.

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.

What it’s useful for: table formatting is a key Excel tool for any marketer, as it organizes and formats your data specifically to your requirements.

2. Pivot tables.

What it does: summarizes data and finds unique values.

A pivot table allows you to categorize tabular data. This reorganizes your 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. Then select Pivot Table. Open up a new worksheet and use your pivot table builder to sort your data as desired.

When setting up your pivot table you will four options to choose from:

• Report Filter — select certain rows from your dataset to view.
• Column Labels — names of the columns used within your selected dataset and can be used as headers in your dataset.
• Row Labels — names of the rows used within your selected dataset
• Value — determine how your data will appear, depending on how you want to view your data. Will it be a sum, count, average, min or max value?

What it’s useful for: you can organize your products and view your profits based on categories or departments. Similarly, it lets you categorize your campaigns into platforms and content type to give a total of ROI. It’s the ideal Excel tool for marketers.

This video can walk you through the basics of a pivot table.

3. Charting.

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.

What it’s useful for: visualizing trends in your data, using a simple Excel tool that is particularly useful when presenting data to stakeholders and to monitor a project life cycle.

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.

4. COUNTIFS.

What it does: counts the number of cells in a range that meet specified criteria.

COUNTIFS extend the function of IF Statements and let you quickly slice your data to 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).

The variables within this formula include:

• Range: The cell range you want the formula to cover.
• Criteria: The number or text you want Excel to count. For text always place this in quotation marks.

As is the case with most Excel functions, 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’s useful for: it lets you organize your audience and customer datasets based on specific data, such as location.

5. SUMIFS.

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.

What it’s useful for: you can track the progress of your sales team, or profits from a product range, which could be used within a business case to present forecasted financial gains.

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).

Based on the “if true” and “if false” tests, this function allows for two possible outcomes; True, or False.

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, such as “High”, “Low”, “Poor”, or “Good”, therefore allowing you to break down data even further.

What it’s useful for: within marketing, this Excel function will be most useful for visually displaying data such as conversion rates, for instance if your rate is “poor” or “good”.

7. CONCATENATE.

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’s useful for: when using excel in marketing you may need to analyze specific data sets. In this case, concatenate would be useful for organizing customer data pulled from an e-Commerce store or CRM platform.

8. VLOOKUP.

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 excel 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.

What it’s useful for: when combining two datasets, VLOOKUP can be used to import specific data rows or columns into an existing spreadsheet to avoid duplication.

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 or areas for improvement, conditional formatting is one of the best ways to do that and see your results visually. 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.

When it’s useful: instantly spot ROI within campaigns and advertising with formatted cells that are colored green to red to visualize performance.

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 excel functions or bring up windows with a few keystrokes.

No Excel functions list would be complete without the inclusion of some helpful keyboard shortcuts. These shortcuts can simplify some of the most common Excel functions and help you speed up the organization of your data.

You may never know all of the expansive offerings of Excel program, but this list can give you a good start to understanding the basic features most useful in the world of marketing.

What it’s useful for: keyboard shortcuts can help transform any use of excel for 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.

Frequently asked questions for Excel Functions.

What is an Excel function?

An Excel function is a formula you can apply to perform certain functions in your sheet. For example, you might want to combine the values of two cells or set a character counter for writing text. Excel is a great tool for marketers who want to manage their data.

What is Excel used for in marketing?

When it comes to marketing, excel has many functions. Some of the most common uses of Excel within digital marketing include:

• Analyzing web statistics
• Analyzing audience metrics
• Analyzing ROI within campaigns and advertisements
• Managing products, profits, conversion rates and budgets
• Managing aspects of customer/audience data

The functions of MS Excel are versatile, making it the ideal program for managing all marketing datasets.

What Excel skills do you need for marketing?

When it comes to Excel the functions list is limitless, meaning the skills required to use Excel for marketing are vast. This does not mean you have to be an Excel pro to effectively use Excel for your marketing purposes, in fact, just using the top 10 functions within this article will enable you to proficiently organize your datasets. The level of skill required will be based on your willingness to expand your knowledge of Excel, alongside what is required in order to effectively manage your marketing workflow.

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.