Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to use XLOOKUP & FILTER Functions to apply conditional formatting to our Sales Dataset. Let’s dive in.
Video
Sample Data
In the screenshot below, we have Sales Data in the XLOOKUP and FILTER sheet tab. Our goal is to use XLOOKUP Function to format the entire rows that meet the criteria we are going to specify. For example, we have a small table in columns H and I respectively that contains the unique Cities and the Target.
XLOOKUP Formula
We want to write the formula in column F before we copy the formula and perform the conditional formatting. Therefore:
- In cell F2, execute the formula: =$D2>=XLOOKUP($C2,$H$2#,$I$2:$I$6)
Note, we have applied absolute cell reference to H2 and I2:I6. This is required in order not to allow the reference to move down when the formula is copied down. In addition, we applied mixed cell reference to cell D2 and C2 respectively by making the columns D and C to be absolute while row 2 remains relative cell references
- Click Enter and copy down the formula
Apply Conditional Formatting
Next, we want to use the formula created to apply Conditional Formatting to our dataset.
- Copy the formula in cell F2
- Select the entire dataset: A2:D50
- In the Home tab, select Conditional Formatting and select New Rule
- In the Conditional Formatting task pane to the right, select Formula from the Rule Type dropdown
- Paste the formula in the box below the Formula Rule Type
- Select any desired Format. In this example, we selected Green fill with dark green text format
- Click Done
From the caption below, we can see that the rows that met the criteria were conditionally formatted. For example, the Target sales for London is $110,000 from the lookup table. In row 2, we recorded $116,637 which is above the target. So, the row was highlighted. Same applies to other rows that met the criteria.
Next, we want to apply red text format to rows that do not meet the criteria – Total that is below the Target amount
To do that, we are going to modify the formula in cell F2 by applying NOT logical function to reverse the Trues and Falses
- Therefore, in cell F2, modify the formula as follows: =NOT($D2>=XLOOKUP($C2,$H$2#,$I$2:$I$6))
- Copy the formula and select the data set from A2:D50
- In the Conditional Formatting task Pane, click on the + icon to add a new rule
- Select Formula as the Rule Type
- Paste the formula in the Formula Rule Type
- Select Light red fill with dark red text
- Click Done
From the screenshot below, we can see that all the Total that are below the Target were conditionally formatted on rows basis
FILTER Formula
We are going to write the Filter Function formula in cell F2 and then use it to conditionally format our dataset
- In cell F2 of the FILTER sheet tab, execute the formula: =$D2>=FILTER($I$2:$I$6,$C2=$H$2#)
Again, we have applied absolute cell reference to I2:I6 and H2# respectively. In additional, we applied absolute cell reference to column D and C respectively which will give us the highlighting on each rows that meet the criteria
- Click Enter
- Copy the formula using CTRL + C
- Select the Data from A4:D50
- In the Conditional Formatting dropdown from the Home tab, select New Rule
- In the Conditional Formatting task pane, select Formula from the Rule Type dropdown
- Paste the formula in the Formula Rule Type box
- Select Green fill with dark green text format
- Click Done
From the caption below, we can see that the rows that met the criteria were highlighted appropriately
Finally, we are going to apply Light red fill with red text to the rows that do not meet the criteria. To do that, modify the formula in cell F2 and wrap it inside NOT function
- Click Enter
- Copy the formula and select the data set from A2:D50
- In the Conditional Formatting task Pane, click on the + icon to add a new rule
- Select Formula as the Rule Type
- Paste the formula in the Formula Rule Type
- Select Light red fill with dark red text
- Click Done
See you in the next tutorial