Conditional formatting

[mojo1227]

I have a large spreadsheet for accounting purposes. For each month there is a budgeted dollar amount and actual amt. Is their a way to format a the spreadsheet that if the actual amount is larger it bolds the number? Each row is a specific account, and each acct has its own budget for that specific month

 

[mianmazher]

Goto Format >Conditinal Formating>

change to Formula is ( place this formula there )

if(actualamt>budgeted amt)

replace teh actual amt and budgeted amt with your actula data address

change the format to bold



I think It will work


Regards


Mian Mazher

 

[mojo1227]

Will I define the entire column as budget or actual?

Thanks for your help

 

[HalfAce]

Hi mojo, welcome to the MrExcel board,
Let's say your budget figures are listed down column B and the actual figures are down column C.
In C2 go to Format > Conditional Formatting. In the first feild you want "Cell Value Is". In the second feild choose "Greater Than" and in the third feild enter "=B2" (without the quotes) and format as bold.
Then copy C2 down column C as far as you would like the formatting to be applied.
Now if you enter a number in Col.B and a larger number in Col.C of the same row, the number in Col.C should be bold.

This help?

 

[mojo1227]

Thanks for the help, but no it doesn't format. When I copy the format down it doesn't change the reference cell to the corresponding cell. I did the special paste and that didn't work

 

[HalfAce]

Hi,
I get the impression that the formatting copied, but all of Col.C is comparing itself to cell B2. (right?)
If this is correct, check the formatting to see that there are no $ characters. (ie. C2's formatting is "Value Is > greater than > =$B$2) If this is what it has, then change it to "Value Is > greater than > =B2. And then copy down Col C again.
This should work for what you want.
Dan

 

[mojo1227]

It worked!!!! Thanks for your help

 

[HalfAce]

Most welcome. Glad it worked.
For the sake of explanation, you've just experienced the difference between absolute and relative cell referencing. The formula =$D$5 will look at Cell D5 no matter what cell you copy it to. (That's an absolute reference.) Where as the formula =D5 (when entered into say cell F5) will always be looking at the cell 2 columns to its left, no matter where you copy it to. (If you copy it to cell N20, it will now be looking at L20. - This is relative referencing.)

That help?