SEARCH

How-To Geek

How to Create Progress Bars in Excel With Conditional Formatting

image

Progress bars are pretty much ubiquitous these days; we’ve even seen them on some water coolers. A progress bar provides instant feedback on a given process, so why not bring some of that graphical pizzazz into your spreadsheet, using Excel’s Conditional Formatting feature?

Progress Bars in Excel 2010

“Bar-type” conditional formatting has been around since Excel 2007. But Excel 2007 would only make bars with a gradient – the bar would get paler and paler towards the end, so even at 100% it wouldn’t really look like 100%. Excel 2010 addresses this by adding Solid Fill bars that maintain one color all throughout. These are ideal for creating progress bars.

Creating The Bar

The first thing you have to do is enter a numeric value into the cell you’d like to format. You can either enter the value directly or use a formula. In our case I’ll just type it in.

image

Note how I made the column wider; you don’t necessarily have to do this, but it does help make the bar look more like a “bar” (and not just a colored cell).

Now click Conditional Formatting, select Data Bars and click More Rules.

image

In the New Formatting Rule dialog check the box that says Show Bar Only (so the number doesn’t appear in the cell). Under Minimum and Maximum, select Type as Number. Then, set the Value to the minimum (beginning) value of your scale and maximum (the top of your bar, the end of the process). We’ll just go for 0 and 100, but you could set this to anything that works for you.

Now let’s configure the Bar Appearance. Make sure the Fill is Solid Fill and select the color you’d like to use. When you’re done, the dialog should look similar to this:

image

Now click OK, and you’re done! At this point you should have a beautiful, crisp progress bar adorning your spreadsheet.

A technical writer for Tibbo Technology by day, Erez is obsessed with customizing anything and everything. After years of using Litestep and Blackbox, switching to a custom keyboard layout (Colemak), extending Word and Excel with elaborate VBA, losing weight with an AutoHotkey script he developed and spending countless hours tweaking Foobar2000 to get it to look "just right", Erez decided the time has come to share some of this obsession with the world at large.

  • Published 03/11/11

Comments (17)

  1. Yoshiyah

    Neato

  2. Richard

    You can do something similar with older versions of Excel using the REPT function. In a new worksheet, put the value you want in cell A1 and then in B1 put the following formula:

    =REPT(“|”, A1)

    You’ll get the | repeated the number of times in A1, which looks close to a bar. You can change the character to make it more bar like if you want. If you find that the number of bars is too much, then you can always change A1 to A1/2 to half the number.

    If you want to have a different character for filled to unfilled then you can do something like this:

    =CONCATENATE(REPT(“#”, A1), REPT(“.”, 20-A1))

    Which will work with values from 0 to 20 and show however many in A1 with “#” and anything left over with “.”. You’ll probably want to change the font to a fixed width one to make this look right.

    Admittedly, not as pretty as the ones in 2010 – but you don’t have to buy a new version of Excel and it is backwards compatible with older versions.

  3. David Levine

    Good article. It would be nice to see a screenshot of final result.

  4. Erez Zukerman

    @David Levine: Thanks! That’s the screenshot heading the post.

  5. Screwtape

    Thanks for the tip for those of us using older versions of Excel. Works great!

  6. Andy1309

    Excellent, but why if you have the range from 0-100 and the cell value is 100, does that colour select not go right to the end, but leaves a small amount of white, which to me does not indictate the highest value?

  7. Erez Zukerman

    @andy1309: Works for me, see http://ipic.tk/s/scre.jpg . What version of Excel are you using? Does your dialog look exactly like the one on the last step above?

  8. mrhammerstein

    has the data bar changed from 2007? i recall that at a 0 value, there would be a portion of the cell shaded and that at 100, the cell wasn’t fully shaded. i would have to create three rules for the cell- 1) at zero make cell empty (fill none) 2) data bar and 3) at 100%, fully shaded cell.

    from Andy1309 remarks, i guess i will have to keep doing it this way when my company gets 2010.

  9. Menzies

    Hey I use excel 2007 on a 2 year old Vista computer
    I managed to create the bar no problem but there was no option for a fill bar, instead it faded at the end rather then your picture shows a clear end to the progress bar.
    thanks!

  10. Joey

    This is an awesome trick, but now I wonder what I could use it for. If I want a progress bar for a project and I have a table of data that concerns the project, can I somehow anchor the total data to a progress bar such that when I update the data it will lower or raise the progress bar?

  11. mrhammerstein

    @Joey
    That is what I use it for. Instead of just typing in a percentage, the cell can contain a formula. Then tie that information into the data bar. You don’t have to use percentage, you could use project costs, dates, etc. Just be sure to change the min/max values to whatever makes sense to you.

  12. Dave61

    A flashier alternative to the solution offered by Richard is to create a pie or bar chart with a single data point, based on the cell value that you wish to represent graphically. Delete the title, axis names & values and key then tweak the other settings until you have exactly what you need.

    If you have a table of values to summarise, e.g. a balanced scorecard, then a bar chart alongside it with a data point for each row. If you need different colours to show progress then a stack bar can be used. Convert the base data into appropriate ranges using =IF. To keep the sheet neat I hide the mechanics either on another sheet or underneath the chart.

  13. Micky Avidan

    I would suggest using: =REPT(“g”,A1) formatted by Webdings font.
    This will present a progressing-bar similar/close to what “Excel 2010″ Conditional Formatting, has to offer.

    Have a look at:http://img15.imageshack.us/i/nonamedn.png/

    Michael (Micky) Avidan
    “Microsoft®” MVP – Excel

  14. DarknessChild

    Very nice! I played with it and made a really neat one.
    http://store.picbg.net/pubpic/C0/28/37e7ff0a642ac028.png

  15. dawn

    Hi htg
    absolutely love this trick, will definitely use this this trick

  16. GeekinFtlauderdale

    this is extremely helpful just the additional visual interest I was looking to include in the progress to date for various project tasks.

  17. Christopher

    Nice and easy. To bad there is not a way to hook this to the worksheet calculation status.

Enter Your Email Here to Get Access for Free:

Go check your email!