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:
|VLOOKUP||Searches a list for a value in the left most column and returns the corresponding value from adjacent columns.|
|CONCATENATE||Joins several text strings into one text string.|
|Pivot||Simplifying the large data into one pivot to analyze.|
|IF & AND||Combination of IF & AND helps in fetching the required data to the given criteria.|
|IF(ISNUMBER(SEARCH||Combination of formula to avoid VLOOKUP to get the required category by predefined string.|
|AVERAGEIFS||Finds the average (arithmetic mean) for the cells specified by a given set of conditions or criteria with multiple options.|
|SUMIFS||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.|
|COUNTIF||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.|
|INDEX and MATCH||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).
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.
3. Pivot Table
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
Example 2: Nested IFs
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 2: To check the duplicates from the given list.
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.
11. Array Formulas
Array formulas can perform multiple calculations on one or more items in an array.
In general, array formulas use standard formula syntax. They all begin with an equal (=) sign, and you can use most of the built-in Excel functions in your array formulas. The key difference is that when using an array formula, you press Ctrl+Shift+Enter for the formula to work and get the required data.
In the below example, an array formula is used to get the highest conversions based on creatives.
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.