 |
| IN-CELL PROGRESS METER IN EXCEL |
Column A - Employee Name
Column B - Target
Column C - Achieved Score
Column D - Achieved % ( Derived Field )
Step 1: Write formula
Put the below formula in cell D2 and drag it down:
=REPT("█",C2)& " " &TEXT((C2/B2),"0.0%")
Step 2: Change column width and alignment
Adjust the width of Column D to make the cells completely filled for 100% score so that your bar does not come out of the cell with the increase in Achieve % and appears as a progress meter. Also, make the contents left aligned.
Step3: Apply conditional formatting
Apply the below conditional formatting rules on Column D. Change the threshold as per your requirement.
CF1: =($C2/$B2)>0.7
CF2: =AND(($C2/$B2)>=0.3,($C2/$B2)<=0.7)
CF3: =($C2/$B2)<0.3
Note - This approach is good if it is applied on small targets like upto 20 or 25. Because if you use it for big targets, you will have to adjust (increase) the column width accordingly which will not come out as an attractive visualization.
Is the post useful?
Kindly put your suggestion in the comment box. Thank you.
