Solved

Use a formula to apply conditional formatting in Excel

Posted on 2014-09-13
5
193 Views
Last Modified: 2014-09-23
Hi everyone,

I would like to use conditional formatting in Excel to Bold certain cells based on their value.

I have tried to use excels built in conditional formatting function but it seems awkward to have to format every single cell since I am using different formatting rules for each cell. Then I would have to manually do this for the entire table, thus it becomes very cumbersome to do manually.

For instance, the attached file cells are to be formatted as follows:

Cell #                               Format Bold if ...

C6     ......................         greater than 0
D6    ......................          negative number
E6    ......................          negative number
F6    ......................          negative number
G6    ......................          negative number
H6    ......................         greater than 0
I6      ......................         greater than 0

Is there a formula for this formatting?
EE-CellFormatBasedonValue.xlsx
0
Comment
Question by:Pedrov664
  • 2
  • 2
5 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 250 total points
Comment Utility
In C6 select Conditional Formatting and go New Rule - Use formula to determine which cells to format. In the formula bar enter =C6>0. Select Format - Font Style-Bold. Select OK. Copy C6, highlight H6 & I6, right-click H6 and select Paste Special. Select Formats then OK. Do the same for D6, just use the formula =D6<0.

Flyster
0
 

Author Comment

by:Pedrov664
Comment Utility
Yes, but there is a formula there already. Which is how I get the result you see. I would like to know how to use a formula without erasing the formula already there. It also must be able to copied to other cells so I can fill the table with same format.
0
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
A formula in a cell is different from a formula in conditional formatting. You can also use multiple formulae in conditional formatting.
0
 

Author Comment

by:Pedrov664
Comment Utility
Flyster,

As I stated above I used "excels built in conditional formatting function but it seems awkward to have to format every single cell since I am using different formatting rules for each cell".

I tried highlighting a column and applying conditional formatting that way but selecting an entire column does not seem to work the way I want it to.

It is a bit cumbersome but it seems to work. I was hoping for an easier way to apply the format since I am doing an only several cells in an entire table.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
Comment Utility
Don't know if you still needed this solution, but instead of entering a formula in the conditional formatting rule you could specify to format cells whose value are greater/less than zero.

1) Select all the cells you'd like to apply the conditional format (use [Ctrl]+click to select multiple cells).
For example, select cells C6, H6, I6 (and maybe also K6, P6, and Q6)
2) Select from the menu Home - Conditional Formatting, New Rule
3) Select "Format only cells that contain" from the top
4) change "between" to "greater than"
5) enter "0" in the box to the right
6) Click the "Format" button and select Bold for the font. Click OK.  The dialog box should look like this:
conditional formatting - greater than zero7) Click OK.

That's it; one rule for all the cells greater than zero.  You can repeat this process for cells to format (bold) with values less than zero also.  Lastly, if you call up the "Manage Rules" option under conditional formatting, you can edit the formula criteria and the ranges affected.
conditional formatting - all criteria
Regards,
-Glenn
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now