Progress Bar (%) Hopefully quick and simple

I am trying to create a progress bar which i am manually creating by simple formatting the following range of cells with a border - so basically displaying a rectangle with no fill

d5:m5

i have tick boxes on my spreadsheet - i have already set up the calculations in another cell so that when 7 out of 10 boxes are ticked - y2 will display 70% etc. that's all done.

What i need now is to somehow tell my 10 cells to change colour according to the number in Y2.

e.g. if Y2 says 70% i would like the first 7 cells of d5:m5 to change green - creating the illusion of a percentage bar filling up.

is there an easy way to conditional format this or script this?

or is their another easier way to do this///.



thanks,

Highlight cells D5:M5, click on Conditional Formatting | New Rule | Use a Formula... , then enter this formula in the dialogue box:

=(COLUMN(D$5)-3)/10>=$Y$2

Then click the Format button, choose the Fill tab and select your colour. Then OK your way out, and Excel will automatically adjust the cell references to suit your highlighted cells.

Hope this helps.

Pete

appreciate all replies. I will try them out shortly. thanks

When I enter the formulas shown above (both). it highlights the cells the colour I want.. but when I change y2 to ANYTHING they all just go "no fill". they only stay green when y2 is empty. I was expecting when y2 said 50% for half the cells to be green etc in horizontal way.

Could you not condition the first cell of the "percentage bar" so that when 10% is displayed this goes green. Then the first two cells of the "percentage bar" formatted so that when 20% is displayed they turn green, and then the first 3 cells so that when 30% is displayed they turn green etc, etc, etc.

There would be 10 conditional formats total. Or have I missed part of your original query?

@Muzza,

Attach your workbook then we can see for ourselves what is going wrong.

Pete

Muzza,

Please see my attached file.

In A1 type 1 or 2 or 3.

If this helps then just apply this to the other cells, for 4, 5, 6, 7, 8, 9, 10 etc

Originally Posted by martindwilson

you still havent answered what happens at 9% or even 11%

He said there are tick boxes which when ticked show 10%, 20% etc. So I assume what ever progress this is measuring, it is part of a 10 step process perhaps? Therefore no need for less than 10% increments. Although I could be wrong, obviously.

Sorry guys, been busy. Thanks for replies.

The reason its a little trickier than simple conditional formatting is that not every tick box will be worth a standard percentage of the total.

e.g. there maybe 10 tick boxes but that doesn't mean every tick box = 10%. the first tick box could be worth 30% with some others worth 5% etc eventually totalling 100%.

plus I am creating multiple percentage bars - a couple for individual progress and a final bar for overall percentage completion

I have attached how I eventually did it.... I think it works? hope this makes it more obvious what im trying to do.


p.s. I don't think 1% or 11% etc will be necessary. But if I need it. I can just decrease the size of the cells and use 50 columns etc instead of the 20 or so I am now, as im using 1 cell = 5% currently. if that makes sense.

the other one with multiple bars..

I hadn't made that yet. calm down.

Originally Posted by martindwilson

so why didn't you ask that in your original instead of feeding us info piecemeal?

I stand corrected