How to Use an IF Statement in a Calculated Field of a Pivot Table

Formulas using if statements help us use conditions to format our data. Imagine you have a salary sheet where you determine the bonus depending on whether the employee has worked more than a certain number of hours. You will need the IF formula to determine that in Excel. In a pivot table, calculated fields are used to apply formulas. In this article, we will learn how to use pivot table calculated field if statement.

Key Takeaways

➤ Click on any cell of the pivot table so that the PivotTable Analyze tab gets visible.
➤ Navigate to that tab and choose Fields, Items, & Sets > Calculated Field from the Calculations group.
➤ In the new window, change the Name (Remarks in this case), and add this formula:
=IF(‘Actual Spent ($)’>’Budget ($)’, 1,0)
➤ Customize the formula according to your needs.
➤ Sequentially press Add and OK.

In this article, we will go beyond the key takeaway and show you how to properly use an if statement in a calculated field with real-life examples. Let’s begin with the dataset.

Download Practice Workbook

IF Statement in Calculated Field

We have the monthly budget data for a few months in our dataset today. Some of the amounts spent are higher than the budget, and we need to figure out which ones using a calculated field and the IF statement.

Here is the pivot table generated from this data:

➤ Select a cell from the pivot table to enable the PivotTable Analyze and Design tabs. These tabs will help us create the calculated field.
➤ Head on to the PivotTable Analyze tab. Find the Calculations group and select Calculated Field from the dropdown menu of Fields, Items, & Sets.

➤ From the Insert Calculated Field window, change the Name and write the formula mentioned below:

=IF('Actual Spent ($)'>'Budget ($)', 1,0)

Explanation

The IF statement returns a specific result if the condition is met, else it returns some other result determined in the same function. Here, the first parameter is the condition, Actual Spent ($)>Budget ($), the second parameter is the result that is returned when the condition is met, and the third parameter is returned otherwise. Therefore, the full formula returns 1 when the actual spent amount is bigger than the budget, and 0 if it’s not.

➤ Press Add and OK to add the calculated field.

➤ The pivot table will have the field added automatically.

Using IF Statement with Text Fields

Pivot table cannot parse if statements that return text or use text as part of the condition. As a workaround, we can use a helper column in the source table. Here is how it works:

➤ Go to the source table, and a column called CategoryInt. Write the formula in the second row of the column (Because the first row is the heading):

=IF(A2="Rent", 1, IF(A2="Transport", 2, IF(A2="Utilities", 3, IF(A2="Groceries", 4,0))))

Explanation

As pivot tables won’t parse anything with text, this formula does that in the source column. It chains a bunch of IF statements to set a numeric value to the categories of the budget.

➤ Upon pressing Enter, the whole column will be automatically filled with the formula.

➤ Refresh the pivot table by right-clicking on the table and selecting Refresh.

➤ Select the CategoryInt field from the PivotTable Fields panel.

➤ This is the output pivot table at the end:

Frequently Asked Questions

Can I use Sumif in a PivotTable?

You can use the spreadsheet in the pivot table normally. Therefore, you can put any formulas in any cells besides the pivot table, even using the data from the pivot table.

How to give a condition in a PivotTable?

Like any dataset, you can use the Conditional Formatting from the Styles group of the Home tab and create custom rules for the pivot table formatting.

How to write formula for calculated field in PivotTable?

Formulas, unlike in a regular data range, cannot use cell references in a calculated field. Instead of using absolute references, you can use the field names to write a formula for a calculated field in a pivot table. If you are confused about using the field names, use the Insert Field option to add the field names.

Can you use if statement in calculated field?

Yes and No. You can use the IF statement only if the field references and the results are numbers. You cannot use the IF statement if the output or the fields are text values.

Can you use vlookup in an if statement?

You can use VLOOKUP in an IF statement, but you cannot use conditions inside the VLOOKUP function. Here is an example:

=IF(VLOOKUP(D1, $A$1:$B$4, 2, FALSE), VLOOKUP(D1, $A$1:$B$4, 2, FALSE), “Not Found”)

Here, two VLOOKUP functions are used inside the IF statement.

Wrapping Up

In this article, we learned how to add an IF statement in the calculated field of a pivot table. If you have any other solutions to the issue solved in this article, leave a comment so that you can help out others. Remember to download the spreadsheet we have used in this article to practice. We will see you in another tutorial soon.

Post Views: 19