Author Topic: help me with excel formulas and conditional formatting (Read 771 times)

pch · April 26, 2016, 07:09:45 PM

so i'm doing a thing with stocks in class and i have to track the stocks of 10 companies. for brown townyzing the numbers after we record them for a month, i decided i'd have the values highlight green or red based off the change from yesterday. with some conditional formatting magic, i came up with this

however, i had to make it definitive to columns B and C. meaning every time i fill out a new column for the day, i'll have to reapply these formulas for those columns involved. is there any way i can do the whole range of dates without having to redo my formulas each time?

also by the way the very first values are just gray cause theyre not being compared to anything

Ad Bot · Advertisement

pch · April 26, 2016, 08:04:59 PM

pls. there has to be someone on here who is good with excel :(

SeventhSandwich · April 26, 2016, 08:24:59 PM

Is this what you want?

https://fredqa.stlouisfed.org/2013/03/26/quickly-repeating-a-formula-in-microsoft-excel/

Ravencroft� · April 26, 2016, 08:29:23 PM

I use excel every day with work and school. I'm not sure what the deal with your excel is, but the columns should automatically adjust to the equation themselves.

You could also just apply the equation to a single cell and extend the handle down the entire column.

pch · April 26, 2016, 08:47:35 PM

Quote from: SeventhSandwich on April 26, 2016, 08:24:59 PM

Is this what you want?

https://fredqa.stlouisfed.org/2013/03/26/quickly-repeating-a-formula-in-microsoft-excel/

Quote from: Ravencroft· on April 26, 2016, 08:29:23 PM

I use excel every day with work and school. I'm not sure what the deal with your excel is, but the columns should automatically adjust to the equation themselves.

You could also just apply the equation to a single cell and extend the handle down the entire column.

since what im doing is conditional formatting and not a formula, i guess my title didn't really make sense. its a conditional formatting rule, not a formula that i need to apply across columns

as far as im aware, i cant use the fill handle with conditional formatting rules. i tried using the format painter but that just copied the cells look, not the rules

Pastrey Crust · April 26, 2016, 08:56:34 PM

IIRC, you can't copy the formatting rules but you can apply them to many cells at once. Try that.

pch · April 27, 2016, 05:08:59 PM

so i resolved this today in class and i'll explain where my error was and then lock this.

my original formula was like =$B3<$C3 and then repeated for > and =. my issue was i made B and C definitive and didn't realize that i didn't have to. i changed the formulas to look =B3<C3 and then selected the region of the cells that i wanted to have the conditional formatting rules

it immediately applies the formatting to all cells even if theres nothing entered, which is why it looks a bit odd. it'll assign the correct colors to positive, negative and equal values once entered. and thats it