# Progress Bar (%) Hopefully quick and simple

1. ## 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,

2. ## Re: Progress Bar (%) Hopefully quick and simple

what happens for say 9%
but for 10% increments
try
=COLUMNS(\$D\$1:D1)/10<=\$Y\$2

3. ## Re: Progress Bar (%) Hopefully quick and simple

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

4. ## Re: Progress Bar (%) Hopefully quick and simple

Attached is a solution using conditional formatting. (Next time it would be very helpful if you attached your file.)

5. ## Re: Progress Bar (%) Hopefully quick and simple

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

6. ## Re: Progress Bar (%) Hopefully quick and simple

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.

7. ## Re: Progress Bar (%) Hopefully quick and simple

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?

8. ## Re: Progress Bar (%) Hopefully quick and simple

@Muzza,

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

Pete

9. ## Re: Progress Bar (%) Hopefully quick and simple

Muzza,

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

10. ## Re: Progress Bar (%) Hopefully quick and simple

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

11. ## Re: Progress Bar (%) Hopefully quick and simple

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.

12. ## Re: Progress Bar (%) Hopefully quick and simple

well this works

13. ## Re: Progress Bar (%) Hopefully quick and simple

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.

14. ## Re: Progress Bar (%) Hopefully quick and simple

the other one with multiple bars..

17. ## Re: Progress Bar (%) Hopefully quick and simple

Originally Posted by martindwilson
I stand corrected

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