Daniel has attended:
Excel Intermediate course
Data validation to show progress bars
Hello,
I have a spreadsheet that tracks the progress of a work order. Staff members fill a column (% complete) in to update the progress of the order. This is measured in percentage. I have a drop down box with 4 options; 0%, 25%, 50%, 75% and 100%. Once they select 100% the next column auto completes to show 100% and an icon set (a green tick). What I would like to happen is a progress bar to appear in the '% complete' column with the following:
0% no progress bar
25%: a quarter of a progress bar showing
50%: half a progress bar showing
75%: 3/4 of a progress bar showing
100%: a complete bar filling the cell
I would like this to be in a green bar or change from red fading up to yellow, orange and green from 0-100 if possible. Or one colour green would be ok.
The formula I have in the next column 'status' that has the icon set tick is:
=IFERROR(IF([@[% Complete]]=1,1,IF(ISBLANK([@[Assigned to]]),"",IF(AND(TODAY()>[@[Assigned to]],[@[% Complete]]<>1),0,-1))), "")
There is a column called Assigned to but I do not know how this relates to the two mention columns above.
Many thanks
Daniel.
RE: Data validation to show progress bars
Hi Daniel,
Thank you for the forum question.
You can create a progress bar based on selected percentage, but you cannot not have in one column.
I have created a worksheet I have attached, but without your if logic. I use 4 columns. First I added conditional formatting to the first column.
I used the formula =$a1=25% and I told Excel to highlight in light yellow. (conditional formatting -> New Rule -> Use a to determine which cells to format)
Then I selected the two first columns and used the formula =$a1=50% and I formatted with a darker yellow.
Then I selected the three first columns and used the formula =$a1=75% and I formatted with a light green.
Then I selected all 4 columns and used the formula =$a1=100% and I formatted with a darker green.
You will have to add your if logic to the formulas.
Be aware of the dollar signs in my formulas are very important to get it right.
I hope this can help you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
con format.xlsx
