13 Microsoft Excel Formulas Every Digital Marketer Should Know

October 1, 2019

Analyzing data effectively is at the core of any good digital marketing strategy.


Marketers need to combine analytical thinking with the right tools to move the needle forward. Even though new marketing tools are constantly being pitched as the next “big thing” to “do more at scale,” most companies without a big data solution rely on Microsoft Excel to analyze, evaluate, record and display data in a structured form. It can be an invaluable tool to maximize productivity and performance within current analysis and optimization activities. In addition, complex data collected from multiple sources can be combined and analyzed to achieve better cross-platform performance. 


However, mastering Excel for complex marketing analysis requires a bit of a learning curve. Not sure where to start? Below are 13 useful Excel formulas that many of our agency managers use to provide meaningful business insights to our clients: 



Searches a list for a value in the left most column and returns the corresponding value from adjacent columns. 


Joins several text strings into one text string. 


Simplifying the large data into one pivot to analyze. 


Combination of IF & AND helps in fetching the required data to the given criteria. 


Combination of formula to avoid VLOOKUP to get the required category by predefined string. 


Finds the average (arithmetic mean) for the cells specified by a given set of conditions or criteria with multiple options. 


Adds the values specified by the set of conditions or criteria with multiple options. 

Change % 

To get the change % comparing the current period vs. previous period. 

Dates (MTD, Last MTD, YTD, Qtr Start, Weekday) 

Individual formulas to get the dates as mentioned. 


Counts the number of cells within a range that meet the given condition with multiple options. 

Conditional Formatting 

Helps in formatting to give the data a visual look. 

Array Formulas 

This can perform multiple calculations on one or more items in an array. 


Combination of both Index and Match to get the required value/text, a step ahead of VLOOKUP. 

Data Validation 

Pick from the list of rules to limit the tables of data into one. 


VLOOKUP takes advantage of vertically aligned tables to quickly find data associated with a value the user enters. This is one of the most frequently used formulas in the industry. 
Apart from the normal VLOOKUP, we can also use VLOOKUP to get the part of string from the cell.  

VLOOKUP is one of the most powerful formulas in Excel. However, it has its limitations. Microsoft is coming up with XLOOKUP soon (currently a beta feature). 

XLOOKUP can look both vertically and horizontally (yes it replaces HLOOKUP, too). In its simplest form, XLOOKUP needs just three arguments to perform the most common exact lookup (one fewer than VLOOKUP). 

=XLOOKUP (lookup_value,lookup_array,return_array) 


CONCATENATE allows you to combine text from different cells into one cell. In our industry, this is a very helpful and commonly used formula to get the exact creative per campaign and ad set. You can also try with the TEXTJOIN formula. 


The best part about pivot tables are that they’re easy to use. You can easily summarize data by dragging the columns to different sections of the table. The columns can also be re-arranged as you wish with a click.  You can also add formula columns to get the accurate numbers for the metrics CTR, CR%, CPC, CPM, etc., 

Example: “Fields, Items, & Sets” available from “PivotTable Analyze” tab to apply formulas as required.  

4. IF & AND 

If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE, and (optionally) do something else if the result of the test is FALSE. 

Example 1: IF & AND


This formula is a combination formula to avoid VLOOKUP and get the required category by a predefined string. It’s useful for getting the geo name we are targeting or creative names that we are running in the account. 


The Microsoft Excel AVERAGEIFS function returns the average (arithmetic mean) of all numbers in a range of cells based on a given criteria. 

Example 1: To get the average for the cost data above $50. 

Example 2: To the average for the conversions by negating 0 values. 


SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers and text. 

Formula in the below tables refer to the main table. 

Example 1: To get the data for the respective creatives from the ad set “Pros.” 

Example 2: To get the data for the respective creatives from the ad set not pertaining to “RTG.” 

Example 3: To get the data for the ad sets which contain the creative text “know.” 

Example 4: To get the data for the respective ad sets for the mentioned date range. 

8. Change %

The formula used here to get the Change % comparing the current period vs. the previous period. We can use this to get the change % for the Current MTD vs. Last MTD, Current Week vs. Last Week, Current YTD vs. Last YTD, etc. 

Formula for Change % – Current period/Previous Period-1 

9. Dates (MTD, Last MTD, YTD, Qtr. Start, Weekday) 

There are multiple formulas to get the required dates based on the provided current date. The below table provides a comprehensive list of various dates formulas that can be used based on the desired results using the current date (highlighted in yellow). 

10. COUNITF with Conditional Formatting 

Counts the number of cells within a range that meet the given condition. The COUNTIF formula, along with conditional formatting can help visualize the data that meets the given condition. Below are some examples of the same. 

Example 1: To check the repeated text from the given range by applying the formula. 

Example 3: Highlight the text mentioned on the top of the table.

Example 4: To find unique/duplicate values for each row.

Example 5: To highlight the numbers based on the KPI criteria mentioned in the cell in yellow. 

12. INDEX and MATCH 

Combination of both INDEX and MATCH formula, to get the required value/text, a step ahead of VLOOKUP. 

The key difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference, while INDEX MATCH uses a dynamic column reference. 

The main benefit of using INDEX MATCH over VLOOKUP is that, with the INDEX MATCH, you can insert columns in your table array without distorting your lookup results. 

Example: In the below example, we are getting the result from column H by matching the respective Concatenate column and Date column for each row. 

13. Data Validation 

Data validation is a feature in Excel used to control what a user can enter within a cell. For example, you could use data validation to make sure a text entry is less than 25 characters. You can also find some other validation options to create from the “Data” tab. 

Example: From the first table we can have control over the cell to select the text/value 

Below is the example table, referring from the first table. 

Once data validation is created based on the Source provided in the list, we have control over the cell J3 to select Pros or RTG to get the cost and conversions totals by Ad Sets, used the SUMIFS formula. 

With marketing becoming more data-driven than ever, marketers need the ability to handle complex data sets in order to compete. From simple calculations to complex formulas, Excel can help marketers analyze data more effectively to improve advertising performance of our clients. 

Follow ForwardPMX

Our Newsletter

Sign up to receive our monthly insights.

  • By submitting your Email Address, you are agreeing to all conditions of our Privacy Policy
  • This field is for validation purposes and should be left unchanged.

You May Find These Interesting

The Third-Party Cookie Lives to See Another Year

But we’re looking forwards, not backwards, and continuing to test and prepare for the future of first-party, first. It turns out that third-party cookies are sticking around a little bit longer after all. This is per Google’s June 24th blog An Updated Timeline for...

read more

Three Things All Nonprofit Marketers Should Know About CTV

If you’ve kept a close eye on shifting consumer behavior over the last 12-14 months, you know that Connected TV, or “CTV”, is more a part of consumers’ lives than ever before. For those of you who may need a refresher, CTV refers to any TV that can be connected to the...

read more

Our Visit to the Forest For Change

As humans, we’ve historically loved to dominate and take ownership of things. We saw something, free-formed, colored outside the lines, and said, “Nope, I didn’t say that was allowed.” So, it makes sense then, that back in the 18th century a man named William Chambers...

read more