Technology

Color code row based on cell value

6

Learn to shortly change the colour of your entire row based mostly on a single cell’s worth in your Excel worksheets. Suggestions and formulation examples for quantity and textual content values.

Final week we mentioned tips on how to change the background shade of a cell based mostly on its worth. On this article you’ll learn to spotlight complete rows in Excel 2016, 2013 and 2010 based mostly on a price of 1 cell, and likewise discover a couple of ideas and formulation examples that can work with numerical and textual content cell values.

Reading: Color code row based on cell value

  • Change a row shade based mostly on a quantity in a single cell
  • Apply a number of formatting guidelines with the required precedence
  • Change row’s shade based mostly on textual content worth in a cell
  • Spotlight row if cell begins with particular textual content
  • Change a cell’s shade based mostly on one other cell worth
  • Change the colour of a row based mostly on a number of circumstances

Methods to change a row shade based mostly on a quantity in a single cell

Say, you will have a desk of your organization orders like this: Source data - a table of the company

You could wish to shade the rows in several colours based mostly on the cell worth within the Qty. column to see a very powerful orders at a look. This may be simply carried out utilizing Excel Conditional Formatting.

  1. Begin with choosing the cells the background shade of which you wish to change.
  2. Create a brand new formatting rule by clicking Conditional Formatting > New Rule… on the Dwelling tab.
  3. Within the “New Formatting Rule” dialog window that opens, select the choice “Use a formula to determine which cells to format” and enter the next formulation within the “Format values where this formula is true” discipline to focus on orders with Qty. bigger than 4:

    =$C2>4 Open the

    And naturally, you should use the lower than (<) and equal to (=) operators to search out and spotlight rows which have Qty. smaller than 4 or equal to 4:

    =$C2<4 =$C2=4

    Additionally, take note of the greenback signal $ earlier than the cell’s handle – it’s wanted to maintain the column letter the identical when the formulation will get copied throughout the row. Really, it’s what does the trick and applies formatting to the entire row based mostly on a price in a given cell.

  4. Click on the “Format…” button and change to Fill tab to decide on the background shade. If the default colours don’t suffice, click on the “More Colors…” button to choose the one to your liking, after which click on OK twice. Click the

    You may also use another formatting choices, such because the font shade or cells border on the opposite tabs of the Format Cells dialog.

  5. The preview of your formatting rule will look much like this: The preview of your formatting rule
  6. If that is the way you wished it and you’re pleased with the colour, click on OK to see your new formatting in impact.

    Now, if the worth within the Qty. column is larger than 4, your entire rows in your Excel desk will flip blue. The background color of entire rows is changed based on value in the specified cell.

As you may see, altering the row’s shade based mostly on a quantity in a single cell is fairly simple in Excel. Additional on, you’ll discover extra formulation examples and a few ideas for extra advanced eventualities.

Methods to apply a number of guidelines with the precedence you want

Within the earlier instance, you might wish to spotlight the rows with totally different values within the Qty. column in several colours. For instance, you may add a rule to shade the rows with amount 10 or larger. On this case, use this formulation:

Read more: How to change battery samsung s6

=$C2>9

After your second formatting rule is created, set the foundations precedence in order that each of your guidelines will work.

  1. On the Dwelling tab, within the Types group, click on Conditional Formatting > Handle Guidelines… .
  2. Select “This worksheet” within the “Show formatting rules for” discipline. If you wish to handle the foundations that apply to your present choice solely, select “Current Selection”.
  3. Choose the formatting rule you wish to be utilized first and transfer it to the highest of the record utilizing the arrows. The end result ought to resemble this: Use the up and down arrows to set the rules

    Click on the OK button and the corresponding rows will instantly change their background shade based mostly on the cell values that you just laid out in each formulation. The entire rows colored based on two conditional formatting rules

Methods to change a row shade based mostly on a textual content worth in a cell

In our pattern desk, to make follow-up on orders simpler, you may shade the rows based mostly on the values within the Supply column, in order that:

  • If an order is “Due in X Days”, the background shade of such rows will flip orange;
  • If an merchandise is “Delivered”, your entire row shall be coloured in inexperienced;
  • If an order is “Past Due”, the row will flip crimson.

Naturally, the row shade will change if the order standing will get up to date.

Whereas the formulation from our first instance might work for “Delivered” and “Past Due”(=$E2=”Delivered” and =$E2=”Past Due”), the duty sounds a bit trickier for “Due in…” orders. As you see, totally different orders are due in 1, 3, 5 or extra days and the above formulation will not work as a result of it’s purposed for actual match.

On this case, you’d higher use the SEARCH perform that works for the partial match as effectively:

=SEARCH(“Due in”, $E2)>0

Within the formulation, E2 is the handle of the cell that you just wish to base your formatting on, the greenback signal ($) is used to lock the column coordinate, and >0 signifies that the formatting shall be utilized if the required textual content (“Due in” in our case) is present in any place within the cell.

Create three such guidelines following the steps from the primary instance, and you’ll have the beneath desk, because the end result: The rows

Spotlight row if cell begins with particular textual content

Utilizing >0 within the above formulation signifies that the row shall be coloured irrespective of the place the required textual content is situated in the important thing cell. For instance, the Supply column (F) might include the textual content “Urgent, Due in 6 Hours”, and this row shall be coloured as effectively.

To alter the row shade when the important thing cell begins with a specific worth, use =1 within the formulation, e.g.:

=SEARCH(“Due in”, $E2)=1

Read more: how to move a partition in windows 10

on this case, the row will solely be highlighted if the required textual content is discovered within the first place within the cell.

For this conditional formatting rule to work appropriately, ensure that there are not any main areas in the important thing column, in any other case you may rack your mind making an attempt to determine why the formulation doesn’t work 🙂 You should utilize this free software to search out and take away main and trailing areas in your worksheets – Trim Areas add-in for Excel.

Methods to change a cell’s shade based mostly on a price of one other cell

In truth, that is merely a variation of adjusting the background shade of a row case. However as an alternative of the entire desk, you choose a column or a spread the place you wish to change the cells shade and use the formulation described above.

For instance, we might create three such guidelines to shade solely the cells within the “Order number” column based mostly on one other cell worth (values within the Supply column). Changing a cell

Methods to change row’s shade based mostly on a number of circumstances

If you wish to shade the rows within the identical shade based mostly on a number of values, then as an alternative of making a number of formatting guidelines you should use the OR or AND capabilities to set a number of circumstances.

For instance, we are able to shade the orders due in 1 and three days within the reddish shade, and people which are due in 5 and seven days within the yellow shade. The formulation are as comply with:

=OR($F2=”Due in 1 Days”, $F2=”Due in 3 Days”) =OR($F2=”Due in 5 Days”, $F2=”Due in 7 Days”)

Use the =OR formula to change a row

And you should use the AND perform, say, to alter the background shade of rows with Qty. equal to or larger than 5 and equal to or lower than 10:

=AND($D2>=5, $D2<=10) An example of using the =AND formula

Naturally, you aren’t restricted to utilizing solely 2 circumstances in such formulation, you’re free to make use of as many as you want. For instance:

=OR($F2=”Due in 1 Days”, $F2=”Due in 3 Days”, $F2=”Due in 5 Days”)

These are just a few of many doable methods to zebra stripe your Excel worksheets based mostly on a cell’s worth that can reply to alter of information in that cell. For those who want one thing totally different to your information set, drop us a remark and we are going to attempt to determine this out.

Read more: How to take a screenshot on a samsung galaxy j3

You may additionally be taken with

  • Excel conditional formatting based mostly on formulation
  • Icon Units, Information Bars and Shade Scales
  • Excel conditional formatting for dates
  • Spotlight each different row or column in Excel
  • Video: Excel conditional formatting based mostly on one other cell worth

0 ( 0 votes )

HowToAll – Tips and tricks in life

https://howtoall.net
Share knowledge, tips, tricks about everything in life

Readers' opinions (0)

Leave a Reply

Your email address will not be published.

NEW

Best golf irons for seniors 2021

1 hour 15 minutes ago 1