Question description:
This user has given permission to use the problem statement for this
blog.
Hi. I am trying to apply a conditional formatting rule that I have for one column to a range of columns. Can you please assist?
Solved by A. J. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
29/08/2018 - 07:00
Hello, Welcome to Got it Pro.
Excelchat Expert
29/08/2018 - 07:00
How may I help you today?
User
29/08/2018 - 07:00
Hello!
User
29/08/2018 - 07:01
I am a research assistant at a University
User
29/08/2018 - 07:01
I am also a student
Excelchat Expert
29/08/2018 - 07:01
Ohh Nice
User
29/08/2018 - 07:01
Yep!
User
29/08/2018 - 07:01
Basically, I am using conditional formatting to highlight any cells that have values, one standard deviation greater than the mean
Excelchat Expert
29/08/2018 - 07:02
Can you forward me the sheet
Excelchat Expert
29/08/2018 - 07:02
So that I can have a look and can assist you better
User
29/08/2018 - 07:02
Currently, I am going column, by column, and setting a conditional formatting rule so that if the cell is great, it can be highlighted
User
29/08/2018 - 07:02
I unfortunately am not allowed to send the spreadsheet since it has confidential data
Excelchat Expert
29/08/2018 - 07:03
No problem, you can paste the sample data at the right side of the screen.
User
29/08/2018 - 07:03
Do you know how I can essentially copy the formula to the other column, so that I don't have to do it one by one?
User
29/08/2018 - 07:03
I tried format painter, but that did not work...
Excelchat Expert
29/08/2018 - 07:03
Format painter only copies the format of the cell not the formula
User
29/08/2018 - 07:04
So cell A19 is 1 SD above the mean
Excelchat Expert
29/08/2018 - 07:04
ok
Excelchat Expert
29/08/2018 - 07:05
A1 to A18 is the mean values?
Excelchat Expert
29/08/2018 - 07:05
and A19 is Std Dev?
User
29/08/2018 - 07:06
So basically, I want to highlight anything in A1:A16 that is greater than A19
User
29/08/2018 - 07:06
You can pretty much ignore A17 and 18 for purposes of this example
Excelchat Expert
29/08/2018 - 07:06
ok
Excelchat Expert
29/08/2018 - 07:07
you want this only in this column? you were saying you have multiple columns
User
29/08/2018 - 07:07
And I know how to do that for this one column. It's just a matter of applying the rule to all the colums
User
29/08/2018 - 07:07
But each column has its own SD
User
29/08/2018 - 07:08
So I just added another example column
Excelchat Expert
29/08/2018 - 07:08
ohh, if the values are different in SD for different columns, you have to apply multiple rules for different columns.
Excelchat Expert
29/08/2018 - 07:08
let me still give it a try
User
29/08/2018 - 07:08
Ah, see that's my dilemma
User
29/08/2018 - 07:09
I was trying to not have to go column by colum
User
29/08/2018 - 07:09
Yes, the SD is different for each column
Excelchat Expert
29/08/2018 - 07:10
How we communicate to excel which cell has to format with some color based on SD... Excel goes with that formula and procedure of steps. because we have multiple criterias for different cells hence we have to create multiple rules for range of cells.
Excelchat Expert
29/08/2018 - 07:11
Only possibility to handle this is with macro coding.
Excelchat Expert
29/08/2018 - 07:11
but got it pro does not support the macro/ vba yet.
User
29/08/2018 - 07:12
Gotcha. To calculate the average for the column, I did =AVERAGE(A1:A16). Then, I click and dragged that formula so that it would apply to all columns (but of course, take the average of the correct column, rather than them all being from column A). I was hoping I could do the same sort of thing with the conditional formatting/high lighting
Excelchat Expert
29/08/2018 - 07:12
stay tuned, I am giving it a try, if I can still do something.
Excelchat Expert
29/08/2018 - 07:13
yeah dragging works in excel cells
Excelchat Expert
29/08/2018 - 07:13
but in conditional formatting we have to define the cells and criteria.
User
29/08/2018 - 07:13
Well I appreciate you trying!
Excelchat Expert
29/08/2018 - 07:13
I am trying to create something Indirect, see if that can work. Be with me
Excelchat Expert
29/08/2018 - 07:16
Nopes, Excel doesnt work that way
Excelchat Expert
29/08/2018 - 07:17
Individual rules has to apply to each column
Excelchat Expert
29/08/2018 - 07:17
Anything else I can help you with ?
Excelchat Expert
29/08/2018 - 07:19
Please provide the 5 star rating. I will also provide a 5 star rating for you so that you get the best expert next time. Thanks for using Got it Pro. Looking forward to help you further.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
