Microsoft Office Forums Excel Conditional Formatting Question

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-09-2019, 06:39 AM
sjp1966 sjp1966 is offline Excel Conditional Formatting Question Windows XP Excel Conditional Formatting Question Office 2007
Novice
Excel Conditional Formatting Question
 
Join Date: Mar 2012
Posts: 21
sjp1966 is on a distinguished road
Default Excel Conditional Formatting Question

Hi All



I wonder if the below is possible. I have a Excel 2013 spreadsheet with around 50 rows, and about 5 columns like so....



essentially column E shows the difference in percentage between the numbers in column B and Column C.

What I would like to do is make an individual cell either another colour or bold depending on if that percentage is above a certain value, so for example... I set it so colum E will highlight in green everything over 25%, I would then liek to automatically bold and/or colour the cells in column A to reflect that.



Is this possible?
Reply With Quote
  #2  
Old 07-09-2019, 01:56 PM
Logit Logit is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 246
Logit is on a distinguished road
Default

A3: =E3>=50% and use paintbrush to apply remainder of column.

E3: Format only cells that contain … ,
Cell value / greater than or equal to / =0.5
Reply With Quote
  #3  
Old 07-10-2019, 04:41 AM
sjp1966 sjp1966 is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2013
Novice
Excel Conditional Formatting Question
 
Join Date: Mar 2012
Posts: 21
sjp1966 is on a distinguished road
Default

Thanks for your reply, however I dont know how to actually implement this in an excel document. Ill needs the A Column to keep the name of the item in there, So in A3 there maybe Widget 1, I need the word widget 1 to stay there but be highlighted if A3 is over the threshold set.
Reply With Quote
  #4  
Old 07-11-2019, 02:50 AM
ArviLaanemets ArviLaanemets is offline Excel Conditional Formatting Question Windows 8 Excel Conditional Formatting Question Office 2016
Expert
 
Join Date: May 2017
Posts: 455
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

The formula is entered as conditional formatting rule condition, not into worksheet cell! Select the range with data (e.g. A3:A12), then activate Home menu, select Conditional Formatting and then New Rule (or when some rules are defined, then Manage Rules). For new rule select Use a Formula ... - and now enter the condition formula for left topmost cell of selection (be aware of use $ signs for cell references - in your case it must be $CR, like $E3). Then click Format and set there how cell is displayed when the condition is TRUE, and press OK until done!
Reply With Quote
  #5  
Old 07-11-2019, 12:12 PM
sjp1966 sjp1966 is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2013
Novice
Excel Conditional Formatting Question
 
Join Date: Mar 2012
Posts: 21
sjp1966 is on a distinguished road
Default

Thre is something I am not doing right here.


  • Ive tried various methods but in this example I clicked A to select the entire column.
  • Home > Conditional Formatting > new rule
  • Select Use a formula to determine which cells to format
  • Selected the E column ( as that is where the data is)
  • Format the cell to turn yellow.
  • No cells turn yellow
If I select a single cell A15 for example
  • Home > Conditional Formatting > new rule
  • Select E15 and =$E$15 appears in the formula field
  • use formatting to turn the cell yellow based on the E column which is 56%
  • Cell turns yellow
If I do that same formula on a cell that is under 50% then that cell doesnt turn yellow


So on an individual cell the formula that I have entered works. How can I get that to work over Multiple cells, where the % in column E differs (I hope ive explained that correctly)


ive tried using format painter but that doesnt work
Reply With Quote
  #6  
Old 07-11-2019, 04:10 PM
p45cal p45cal is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

See 2 conditional formatting in columns A and E of the attached.
Attached Files
File Type: xlsx msofficeforums42919.xlsx (9.9 KB, 1 views)
Reply With Quote
  #7  
Old 07-12-2019, 04:42 AM
sjp1966 sjp1966 is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2013
Novice
Excel Conditional Formatting Question
 
Join Date: Mar 2012
Posts: 21
sjp1966 is on a distinguished road
Default

Hi, Thanks for this and I can see the conditional formatting and it does indeed work.

I am trying to understand the formula in order to learn how to do them better, no matter what cell I click in on the A Column, when I look at the formatting for that cell is says

Rule
=$E3>25%

Applies to
=$A$3:$A$12

I notice there isnt a $ to the left of the E under rule so does that mean the rule is looking always at column E but the number will change on each subsequent cell, so E4, E5, E6 etc, even though the conditional formatting on each cell only shows E3
Reply With Quote
  #8  
Old 07-12-2019, 05:51 AM
p45cal p45cal is offline Excel Conditional Formatting Question Windows 10 Excel Conditional Formatting Question Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by sjp1966 View Post
I notice there isnt a $ to the right of the E under rule so does that mean the rule is looking always at column E but the number will change on each subsequent cell, so E4, E5, E6 etc, even though the conditional formatting on each cell only shows E3
Yes it does.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Conditional Formatting Question Conditional Formatting deleted by excel when saving and reopening file sazetts Excel 7 02-09-2017 10:52 AM
Excel Conditional Formatting Question Question re: Conditional formatting (cell occurs after another cell) Saywarder Excel 1 04-20-2015 11:29 AM
Excel Conditional Formatting Question Conditional formatting question based on cell date Cosmo Excel 2 04-08-2013 12:12 PM
Conditional Formatting Problem Excel 2007 namedujour Excel 6 08-04-2011 10:52 AM
Excel Conditional Formatting Question Conditional formatting of cell, Excel 2003 Scaffold Excel 2 05-01-2010 08:56 AM


All times are GMT -7. The time now is 01:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft