Excel - How to Copy and Paste Conditional Formatting to Another Cell - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula for conditional formatting a cell based on a percentage of another cell, This is for a project Timeline Manager chart so would like the formula to auto copy when adding new projects. C1 = blank H3 = percentage data bar 0-100% I would like: C1 to input "Not Started" if Percentage of H3 is 0% C1 to input "In Progress" if percentage of H3 is between 1-99% C1 to input "complete" if percentage of H3 is 100%
Solved by I. J. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 20/07/2018 - 03:32
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User 20/07/2018 - 03:32
Great i would greatly appreciate it!
Excelchat Expert 20/07/2018 - 03:32
Okay, I've read your concern, and it seems you wish to automate the response of C1 based on the percentage of H3. Is this correct? :)
User 20/07/2018 - 03:33
I am still fairly new with excel, trying to learn just online but i can not seem to figure this one out.
User 20/07/2018 - 03:33
yes that is correct
Excelchat Expert 20/07/2018 - 03:33
Okay I see, I can help with your concern. The formula would be a nested IF statement. Let me provide you the solution to be entered in cell C1 then we can discuss how this formula works.
User 20/07/2018 - 03:34
ok great sounds good thank you!
Excelchat Expert 20/07/2018 - 03:36
Okay, here's the formula you need to enter in cell C1:
Excelchat Expert 20/07/2018 - 03:36
=IF(H3=0,"Not Started",IF(AND(H3>0,H3<1),"In Progress",IF(H3=1,"Completed","")))
User 20/07/2018 - 03:37
ohhh ok that makes sense!! i would put that right on the spreadsheet i wouldn't have to make that a conditional format rule right?
Excelchat Expert 20/07/2018 - 03:38
That is correct! Conditional formatting is only used to edit the "format" of cells (like highlight, text color, bold, italics, etc.) when a certain condition is met.
Excelchat Expert 20/07/2018 - 03:38
In our case, we don't need to use this function of Excel.
User 20/07/2018 - 03:38
And would i have to anchor any of the columns or rows in that formula or will it automatically update itself when i add new projects onto the spreadsheet?
Excelchat Expert 20/07/2018 - 03:39
We just need to know the value of H3 and return the corresponding response in C1.
User 20/07/2018 - 03:39
Ahhhh ok i got it now. that makes sense!
Excelchat Expert 20/07/2018 - 03:39
By adding new projects onto the spreadsheet, what do you mean by this? :)
User 20/07/2018 - 03:40
So on H3 i have a "Data Bar" conditional formatting rule. this will not affect how C1 when looking for the value right?
Excelchat Expert 20/07/2018 - 03:40
That is correct. C1 only reads the value, not the format, of H3.
Excelchat Expert 20/07/2018 - 03:42
If for example you're going to add another data bar on cell H4, and you want to link the response of this in cell C2, you can just drag down the formula from cell C1 to C2 and the references in the formula will automatically adjust.
Excelchat Expert 20/07/2018 - 03:43
Do you have any more concerns regarding the provided solution? :)
User 20/07/2018 - 03:43
I mean if i need to add more rows to add another string of data, i would normally just click and drag the little square at the bottom of C1 and copy the formula down. I am to understand when doing certain formulas i need to add an "Anchor" or "$" before the letter or number.. Example $C$1.. i am not to sure exactly what that means, but for this formula would i need the anchors or will it copy and paste just fine the way we have it written here?
Excelchat Expert 20/07/2018 - 03:44
Oh I see what you mean.
Excelchat Expert 20/07/2018 - 03:45
In the formula provided, when you drag it down to C2, the reference cells will as well be dragged down. So in C1, we have it referenced to H3. In C2, we will have it referenced to H4.
Excelchat Expert 20/07/2018 - 03:45
The role of the anchors ($) is to make a reference as absolute.
User 20/07/2018 - 03:45
ohh ok perfect! :)
Excelchat Expert 20/07/2018 - 03:46
For example, if you have the formula =IF($H$3=0,"Not Started",IF(AND($H$3>0,$H$3<1),"In Progress",IF($H$3=1,"Completed","")))
Excelchat Expert 20/07/2018 - 03:46
instead of the one that was earlier provided, the H3 reference will not change even if you drag it down, up or across.
Excelchat Expert 20/07/2018 - 03:47
So the anchors will make a reference to be "absolute", meaning they will not be changed when the formula is copied elsewhere.
User 20/07/2018 - 03:47
ok so does the $H mean the Column is anchored and $3 means the Row is anchored?
Excelchat Expert 20/07/2018 - 03:47
That is correct!
Excelchat Expert 20/07/2018 - 03:48
So basically, if you want to make it anchored in column H but not on row 3, use $H3. If you want to anchor it on row 3 but on on column H, use H$3.
Excelchat Expert 20/07/2018 - 03:48
If you want to anchor it on both column H and row 3, use $H$3.
User 20/07/2018 - 03:49
ok cool. so i have made a gantt chart using conditional formatting on this project as well and the formulas tell me to use the anchors $M8:$BN75 for my chart area. So this means it will copy down more rows if i need to extend the area but it will not extend to another column?
Excelchat Expert 20/07/2018 - 03:50
That is correct! When you copy it down by one row, it will become $M9:$BN76. However, if you copy it across, it will still remain as $M8:$BN75.
User 20/07/2018 - 03:51
ok awesome thank you so much for your help! this site here is amazing i have been searching for the answer to this problem for like 2 days and you had it to me and more answers in minutes!
Excelchat Expert 20/07/2018 - 03:51
If you don't have any more clarifications at this point, you may end the session by clicking the END SESSION on the upper right of your screen.
Excelchat Expert 20/07/2018 - 03:51
Also, I'd appreciate if you could drop a few lines for your kind and honest feedback on a survey after this session. Many thanks and have a good day!
Excelchat Expert 20/07/2018 - 03:51
Glad to be of help! :)
User 20/07/2018 - 03:52
Thank you so much! Enjoy the rest of your day!!
Excelchat Expert 20/07/2018 - 03:52
You as well! :)

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

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc