Solved

Use a formula to apply conditional formatting in Excel

Posted on 2014-09-13
5
205 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
ID: 40321324
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
ID: 40321330
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
ID: 40321332
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
ID: 40323254
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
ID: 40338134
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

792 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