Formatting in Excel Based on Another Column. At times, you’ll find yourself needing to format cells or columns based on values from another column. If you’re here for that reason, you’re in luck—this task is easily achievable in Excel. Let’s delve into how to format cells according to another column’s values using a practical example.
Formatting in Excel Based on Another Column
Example: Identifying Months with Sales Above the Average
Consider a dataset detailing sales across various dealerships during different years. The goal is to highlight sales figures in 2019 that surpass those in 2018.
Follow these steps:
- Select the range D2:D12 (2019 Sales).
- Navigate to Home > Conditional Formatting > New Rule.
- Opt for “Use a formula to determine which cell to format.”
- Input the following formatting formula in the formula box: =$D2>$C2
- Choose the formatting style for cells meeting the condition (e.g., I’ve used a green fill).
- Click OK.
Voila! The sales values in 2019 exceeding those in 2018 will now be highlighted with a green fill.
How Does it Function?
It’s straightforward. Initially, select the range where the formula should apply. Then, craft a formula to dictate which cells within that range to format. The formula =$D2>$C2 compares values in columns D and C, applying the formatting based on the outcome of this comparison.
This method allows for flexibility. For instance, if you want to highlight months rather than sales in 2019, simply adjust the “formula applies to” field to range A2:A12.
To make this change:
- Select any cell in D2:D12.
- Head to conditional formatting and choose “Manage Rules.”
- Modify the range in the “Applies to” box to A2:A12.
- Click OK.
The formatting will adapt accordingly. This approach works not just within the same sheet but also across different sheets by specifying the range. Moreover, you can define non-contiguous ranges by using commas in the “applies to” section.
Formatting in Excel Based on Another Column. In summary, conditional formatting in Excel based on another column might seem intricate, but with a little practice, it’s straightforward. This guide aims to clarify the process. If you have any queries, feel free to ask in the comments section below.
