3 Common Excel Formulas Translated into Salesforce Row-Level Formulas

Learn how to translate common Excel formulas like CONCATENATE(), IF(), and SWITCH() into Salesforce row-level formulas for enhanced reporting.

Introduction

When starting out using Row-Level Formulas in Salesforce reporting, it can be hard to know what's possible if you're new to Salesforce functions. Luckily, many Salesforce functions are similar to those used in other tools such as Microsoft Excel. Here are three common Excel formulas that can be translated into Row-Level Formulas:

• CONCATENATE()

• IF()

• SWITCH()

Below, I'll explain how each of these Excel formulas can be used in Salesforce reporting with a bit of reformatting.

Quickstart: Row-Level Formulas in Salesforce

If you already know how to use Row-Level Formulas, feel free to skip this section! This is meant to provide a quick overview of how to get started with Row-Level Formulas.

From the Salesforce report editing screen, click the arrow to the right of the "Columns" section. Once the dropdown has opened, select "Add Row-Level Formula" to get started!

*One thing to note is that you are only able to reference 5 different fields in a Row-Level Formula.

CONCATENATE() Function

The CONCATENATE() function is used in Excel to combine text from different cells into a single cell.

For example, if you had the above two cells in Excel with text that you wanted to combine into a single cell, you could use the following formula:

=CONCATENATE(A1&" "&B1)

This would result in a third cell (C1) that looked like this:

Translating CONCATENATE() to a Row-Level Formula

To translate the CONCATENATE() function to Salesforce, we'll first need to decide which field values we want to combine and how we'd like to format the data.

In this example, I used Account 'Industry' and Account 'Billing State/Province' in an Opportunities report to see how much revenue has been won in each combined Industry & Billing State category.

Once I've opened the Opportunities report type and filtered the report to Stage = Closed Won for the desired Close Date range, I opened the Row-Level Formula creator. Using the field search on the left, I pulled in 'Industry' (displays as INDUSTRY) and 'Billing State/Province' (displays as ADDRESS1_STATE). I also make sure that the Formula Output Type = Text.

Then I added a few things to the formula:

1. I put the TEXT() function around the Industry field since it is a picklist

2. I added &" - "& between the two fields I had pulled into the formula.

   1. The & is used to let Salesforce know that you'd like these values to be displayed next to each other.

   2. The " - " is used to create space and add a dash between the two values to make the data easier to read.

Once I applied the Row-Level Formula to the report, I grouped the report by the Row-Level Formula column and added a Donut chart sliced by that value as well.

Using this chart, you're easily able to identify the amount attributed to each industry/state combination. This formula type can be used for modeling different potential Sales territories, market segmentation, etc.

IF() Function

The IF() function is used in Excel to perform a logical test and return values based on true or false conditions.

For example, if you had the above cells in Excel and you wanted to identify which rows had the same value in both Column A and Column B, you could use the following formula and drag it down for all relevant rows:

=IF(A2=B2, "Match", "-")

This would result in a third column ("Result") that looked like this:

You could then use the third column to identify where the values are the same in Column A and Column B because the Result would show "Match".

Translating IF() to a Row-Level Formula

The IF() function within Salesforce looks very similar so in this example, I've used a very similar use case.

In this example, I used Account 'Billing State/Province' and Account 'Shipping State/Province' to determine where an Account 'Billing State/Province' did not match an Account 'Shipping State/Province'.

Once I've opened the Accounts report type and narrowed the report results using the filter 'Billing State/Province' = USA, I opened the Row-Level Formula creator. I used the Column Name "State Match" and also made sure that the Formula Output Type = "Text".

Using the field search on the left, I pulled in 'Billing State/Province' (displays as ADDRESS1_STATE) and 'Shipping State/Province' (displays as ADDRESS2_STATE).

Then I added a few things to the formula:

1. I put the IF() function around the entire formula.

2. I added an = sign between 'Billing State/Province' (displays as ADDRESS1_STATE) and 'Shipping State/Province' (displays as ADDRESS2_STATE) so the logic will only return the true condition if both fields match.

3. I added "Match" as the true condition result if the fields are equal and "No Match" as the false condition result if the fields are not equal.*

Once I applied the Row-Level Formula, I was easily able to tell where the 'Billing State/Province' field and the 'Shipping State/Province' field values did not match.

*In this example, I used "Match" and "No Match" but you could use numbers to represent a match (1 = match, 0 = no match) based on how you want it to calculate. You would just need to change the Formula Output Type to "Number".

SWITCH() Function

The SWITCH() function is used in Excel to evaluate one value against a list of several values and return the result of the first matching value.

For example, if you had the above cells in Excel and you wanted each US State value in Column A to return the proper region value, you could use the following formula in Column B and drag it down for all relevant rows:

=SWITCH(A2, "New York", "Northeast", "Washington", "West", "Texas", "Southwest", "Minnesota", "Midwest", "California", "West"," ")

This would result in the "Region" column being populated with the correct value based on the "State" value in Column A.

Translating SWITCH() to a Row-Level Formula

To translate the SWITCH() formula to a Salesforce Row-Level Formula, we'll need to use a function called CASE() instead.

For the Salesforce example, instead of grouping states by region, I used the Account 'Type' field to group the values of the 'Type' field into different categories.

Once I've opened the Accounts report type, I opened the Row-Level Formula creator. I used the Column Name "Direct/Channel" and also made sure that the Formula Output Type = "Text".

Using the field search on the left, I pulled in 'Type'.

Then I added a few things to the formula:

1. I put the CASE() function around the entire formula.

2. I added "Customer - Direct" as value1 and "Direct" as result 1

   1. "Customer - Direct" is the Account 'Type' value and "Direct" is the value I wanted to show in the Row-Level Formula column.

3. I added "Customer - Channel" as value2 and "Channel" as result2.

   1. "Customer - Channel" is the Account 'Type' value and "Channel" is the value I wanted to show in the Row-Level Formula column.

4. I added "Other" as the value to show if the Account 'Type' field value doesn't match value1 or value2. This is a catch-all for all other values.

Once I applied the Row-Level Formula, I was able to see the new values I had entered for each of the 'Type' values.