MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Status Bars

Posted by Jim Bubb on February 02, 2000 12:31 PM

I have a spreadsheet that tracks a number of project tasks which has a "percent complete" column for each task. I was wondering if anyone knew of a slick way to insert horizonal "percent complete" bars in those cells. I have built a fudgy one with individual small bar charts, but I was wondering if there might be a more striaghtforward way to do this. Thanks.

Jim Bubb

Posted by judi on February 03, 2000 2:45 PM


Try using conditonally formatting:

Suppose you have a percent complete number in cell B2 (for instance 60 for 60%). In cell C2, select Format, Conditional Formatting. Change Conditon 1 to "Formula is" and in the formula bar, put the following: =(100-$B$2)<=90
Select the Format button and on the Patterns tab select a color for your bar.
Repeat these steps for cells D2 to L2, but decrease the last number in the formula each time by 10. (For instance =(100-$B$2)<=80 in D2)

When you are done you can adjust those 10 columns to be smaller. This will give you a percent completion bar for increments of 10%. If you want one for smaller increments just increase the number of columns and formulas accordingly.

I hope this helps!