IN-CELL PROGRESS METER IN EXCEL

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.