• Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

Excel Conditional Formatting Based on Another Column

0 Comments

Excel Conditional Formatting Based on Another Column. Sometimes, you may need to format cells or columns in Excel based on the values in another column. If you’re in such a situation, this guide will show you how to format cells based on another column’s values using an example.

Example: Highlighting months with sales higher than the average

Let’s say we have data on sales made by different dealerships in different months and years. We want to highlight sales in 2019 that are greater than sales in 2018. Excel Conditional Formatting 1

To achieve this, follow these steps:

  1. Select the range D2:D12 (sales of 2019).
  2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
  3. In the New Formatting Rule dialog box, choose “Use a formula to determine which cells to format.”Excel Conditional Formatting 2
  4. In the formula box, enter the following formula for Excel formatting: =$D2>$C2
  5. Select the formatting you want for cells that meet the condition. In this example, we will choose a green fill.Excel Conditional Formatting 3
  6. Click the OK button.

That’s it! All the values in the 2019 sales column that are greater than the corresponding values in the 2018 sales column will be highlighted with a green fill.

How does it work?

It’s simple. First, you select the range where you want the formula to apply. Then, you use a formula to determine which cells within that range should be formatted. In this case, the formula is $D2>$C2. The dollar sign ($) locks the column reference (D) while allowing the row reference (2) to change as the formatting is applied to other cells. The formula compares each cell in the range (D2:D12) with the corresponding cell in the previous year’s sales column (C2:C12). If a cell in 2019 sales is greater than the corresponding cell in 2018 sales, it will be formatted with the chosen style (green fill).

If you want to highlight the months instead of the 2019 sales, you can directly change the “applies to” range to A2:A12.

  1. Select any cell in the range D2:D12.
  2. Go to Conditional Formatting and click on “Manage Rules.”
  3. Change the range in the “Applies to” box to A2:A12.Excel Conditional Formatting 4
  4. Click the OK button.Excel Conditional Formatting 5

You will see that the formatting is now applied to the specified range. Similarly, you can format any range based on the values in any column in Excel. The column can even be on a different sheet; you just need to mention the range. You can also specify non-contiguous ranges by using commas between the ranges in the “applies to” section.Excel Conditional Formatting 6

So, this is how you can apply conditional formatting in Excel based on another column. It might seem a bit tricky, but in practice, it’s quite straightforward. You just need to make a few adjustments. I hope this article has provided enough explanation to help you with conditional formatting in Excel. If you have any doubts, feel free to mention them in the comments section below.

3
45 Views
Microsoft 365 now allows you to sign PDF files on your mobile phonePrevMicrosoft 365 now allows you to sign PDF files on your mobile phoneMay 31, 2023
Guide To Setting Alarms On Windows 10June 1, 2023Guide To Setting Alarms On Windows 10Next

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • AVG Internet Security 2021 1 Device 1 Year Global AVG Internet Security 2021 1 Device 1 Year Global
    Rated 5.00 out of 5
    $11.00
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Avast SecureLine VPN 2021 2 Years 5 Devices Global Avast SecureLine VPN 2021 2 Years 5 Devices Global
    Rated 5.00 out of 5
    $47.00
  • Windows 11 Pro Product Activation Key - Latest version 2021
    Rated 5.00 out of 5
    $11.00
  • AVG Internet Security 2021 10 Devices 1 Year Global AVG Internet Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $30.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.