Excel Conditional Formatting Icon Sets with Relative Reference (2 Examples)

What Is Relative Reference?

In Excel, a cell reference is typically relative. This means that the reference adjusts based on the cell’s location. There’s no dollar ($) symbol, and it usually combines the column and row. When the target object moves or its relation changes, the reference adapts accordingly.

Example 1 – Using IF Function and Icon Sets

Suppose we have a dataset with items and units sold in two specific months. We want to compare these units using the IF function and visualize the results with icon sets.

• IF Function for Comparison:
  • Select the cell where you want to compare relative columns (e.g., cell E5).
  • Enter the following formula:

• • Press Enter.

• • • If C5 is greater than D5, it returns 0; if C5 is less than D5, it returns 1.
  • Drag the Fill Handle down to apply the formula to the entire range.

• • The result of the comparison will show in column E.

• Conditional Formatting Icon Sets:
  • Select the entire column E.
  • Go to the Home tab on the ribbon.
  • Under the Styles group, click the Conditional Formatting drop-down menu.
  • Choose Icon Sets and select the Directional icons (first option).

  

• • Now you’ll see icons representing the comparison results in column E.

Remember, icon sets provide a visual way to highlight differences between data points.

Suppose we want to see the icons only; we don’t want the numbers of comparison.

• Select the Column:
  • Choose the column where you want to apply the formatting.
• Access Conditional Formatting:
  • Go to the Home tab on the ribbon.
  • Click the drop-down arrow in the Styles group.
  • Select Conditional Formatting.
• Create a New Rule:
  • Choose New Rule.

• • This will open the New Formatting Rule dialog box.
• Define the Rule:
  • Select Format all cells based on their values under Select a Rule Type.
  • In the Edit the Rule Description dialog, set the necessary criteria.
• Choose Icon Sets:
  • From the Format Style drop-down menu, select Icon Sets.
  • Then, choose Directional Icons from the Icon Style drop-down.
• Show Icons Only:
  • Check the Show Icon Only option.
• Specify Values:
  • In the Values field, enter 2 and 1 sequentially in the >= box.
• Apply the Rule:
  • Click OK to complete the process.

• Now, only the icons will be displayed.

Read More: Excel Conditional Formatting: Add Custom Icon Sets 

Example 2 – Comparing Relative Rows with Conditional Formatting Icon Sets Using Excel’s SIGN Function

In this example, we’ll utilize the SIGN function to compare relative rows. The SIGN function returns the sign of an integer: it outputs 1 for positive numbers and -1 for negative values.

Here are the steps:

• Dataset Overview:
  • We have a dataset with months and corresponding units sold each month.

• Calculate the Difference:
  • Select a cell and insert the following formula:

• • Press Enter to display the result in that cell.

• • Drag the Fill Handle down to copy the formula across the range or double-click the plus (+) sign to duplicate it.

• • The resulting column will show the calculated values.

• Visualize with Icon Sets:
  • Choose the entire column D.
  • Go to the Home tab on the ribbon.
  • Select Conditional Formatting from the Styles group.
  • Choose the Directional icons option under Icon Sets.

• • Icons will appear next to the results.

• View Icons Only:
  • If you want to see only the icons without the comparative figures:
    • Select the column.
    • Go to the Home tab.
    • Choose Conditional Formatting from the Styles group.
    • Select Manage Rules from the menu.

• • • In the Conditional Formatting Rules Manager dialog, click Edit Rule.

• • • In the Edit Formatting Rule dialog, adjust the values (as shown in the screenshot) and checkmark Show Icon Only.
    • Click OK.

• • • Confirm by clicking OK again.

• • Now you’ll be able to see only the icons.

Read More: Excel Conditional Formatting Icon Sets Based on Another Cell

Things to Keep in Mind

• Cell References:
  • When writing conditional formatting criteria, remember that cell references are positioned relative to the top-left cell of the applicable range.
  • Always include the first row of data to ensure accurate formatting.
• Enhancing Data Visibility:
  • Take advantage of conditional formatting to make data more visually apparent.
  • Construct rules that define how cells should be formatted based on their values.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel Conditional Formatting Icon Sets More Than 3
• How to Change Conditional Formatting Icon Set Color in Excel
• Conditional Formatting Icon Sets Based on Text in Excel
• Excel Conditional Formatting Icon Sets Based on Percentage

<< Go Back to Icon Sets | Conditional Formatting | Learn Excel