+ Reply to Thread
Results 1 to 4 of 4

percentage based colour bars and conditional formating

  1. #1
    Registered User
    Join Date
    05-08-2019
    Location
    Wellington, New Zealand
    MS-Off Ver
    16.23 Excel for Mac
    Posts
    2

    Question percentage based colour bars and conditional formating

    Hi there,

    I'm a conditional formatting n00b and can't seem to find an elegant solution to my problem - I'm sure its trivial, but its eluding me at the moment.

    What I'm doing is tracking projector bulb life over time.
    in my spreadsheet, I have several columns, each representing a different projector. The second row is the theoretical maximum bulb life of the projector - then each month is the current hours that the projector is up to

    something like this:

    Epson
    Max Bulb Life (hours) 3000
    Jan 200
    Feb 400
    Mar 550

    So I wanted to jazz up the sheet with some conditional formatting by overlaying some colour bars that are a percentage of the listed max bulb life - to help indicate how close to the max hours each projector is up to, but I'm not familiar enough with the syntax to make this work elegantly.
    I can get the effect in an adjacent column, but not on the column of hours itself - I'm just a little unsure of how to embed that formula into the conditional formatting.

    eg
    colourbars.PNG

    What method(s) do I need to use to create the effect I'm after - and also be able to keep adding on new rows each month and have the formatting carry on.

    Appreciate any hints / tips / ideas

    regards

    Malcolm

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: percentage based colour bars and conditional formating

    seems you have the correct concept, just instead of doing the CF on adjacent column do it on the column in question

    picture says a thousand words
    Step1.JPG
    step2.JPG

    https://imgur.com/a/nzhj4nA

    i dont know what colour you have in your post so i just used purple

    Basically, Format style you want is Data BAR

    Minimum number set to 0
    Maximum number set to cell B2
    Apply to the area you want
    Rest is just formatting to what you want

    ps just note with gradient scale of this nature
    it wont look like 100% filled bar even at 100%
    change to solid fill to change that...though solid fill just doesn't as "nice" (IMO)
    Last edited by humdingaling; 08-28-2019 at 10:02 PM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-08-2019
    Location
    Wellington, New Zealand
    MS-Off Ver
    16.23 Excel for Mac
    Posts
    2

    Re: percentage based colour bars and conditional formating

    Thankyou so much - I knew I was close, but that has got me over the line and its working for me now.

    In the maximum value field, If I don't pop the $ in front of the column designator, can I then copy conditional formatting across all my other columns, or do I need to set up each column one by one?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: percentage based colour bars and conditional formating

    If I don't pop the $ in front of the column designator, can I then copy conditional formatting across all my other columns
    yes it should ....keep the $ infront of row though (ie B$2) otherwise it will go pear shape

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting with Data Bars - Based on Completion Percentage
    By benwahchang in forum Excel General
    Replies: 9
    Last Post: 11-14-2014, 03:38 PM
  2. Replies: 5
    Last Post: 03-14-2014, 04:03 AM
  3. [SOLVED] Conditional Formating and Colour filtering
    By Vibro in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2013, 02:00 AM
  4. CONDITIONAL FORMATING with percentage
    By aacod in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-01-2013, 03:13 PM
  5. change colour of bars in graph based on conditions
    By saurabhlotankar in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-27-2013, 12:29 AM
  6. Replies: 12
    Last Post: 02-27-2011, 05:53 PM
  7. Replies: 0
    Last Post: 05-25-2009, 06:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1