# Excel - COLUMN Function Problem - Expert Solution

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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at