How to create a progress bar in Excel

Start with a clean percentage

The sheet tracks a launch: each workstream has a number done and a target, and column D holds =IF(C2>0,B2/C2,""), formatted as a percentage. The IF matters. Vendor contracts has no target yet, and without the IF that row would show #DIV/0!, which breaks every bar built on it.

Two rows are deliberately awkward. Donor pledges are at 132% of target, and vendor contracts is empty. Real trackers always have both, and they are where most progress bars break.

Method 1: a text bar with REPT

REPT repeats a character a set number of times. One full block per 10% and a light shade for the rest gives a 10-segment bar that works in every Excel version, survives copy and paste into email, and prints in black and white.

To get the block characters, copy them from this page, or use =UNICHAR(9608) for the full block and =UNICHAR(9617) for the light shade (Excel 2013 and later). Block characters are not the same width in every font. If bars on different rows do not line up, set the column to a monospaced font such as Consolas. A plain "|" works in any font if you prefer a thinner bar.

This simple version works for website pages at 75%. It fails on the donor pledges row: 132% asks for 13 full blocks and -3 light ones, and REPT returns #VALUE! for a negative count.

A text bar that never breaks

Three fixes make the bar safe. MIN(1,...) caps the value at 100% and MAX(0,...) stops negative values, so the counts stay between 0 and 10. IF(D2="","",...) leaves the bar empty when there is no percentage. That last part is easy to forget: MIN ignores the empty text in D7, so MIN(1,D7) returns 1 and the vendor contracts row would show a full bar, as if the work were finished.

Use INT instead of ROUND for the number of blocks. With ROUND, 48% gets 5 blocks and looks half done, and 96% gets all 10 and looks complete. INT only fills a block when that 10% is really done: 4 blocks for 48%, 9 for 96%. The bar is full only at 100%.

Adding the percentage as text at the end keeps the real number visible, so 132% still shows as 132% next to a full bar. For a finer bar, use 20 segments: multiply by 20 and subtract from 20.

Change the bar colour when it is done

A text bar takes the font colour of its cell, so conditional formatting can colour it. Select the bar column (say E2:E8) and add a rule that turns the font green when the percentage reaches 100%. Add a second rule with =AND($D2<>"",$D2<0.25) and a red or orange font to flag workstreams that have barely started.

The $ before D keeps every row pointing at its own percentage in column D.

1. Select E2:E8.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Type =AND($D2<>"",$D2>=1).
4. Click Format, pick a green font colour on the Font tab, and click OK twice.

Method 2: data bars

Data bars draw a real bar inside the cell, behind the number. They are the quickest option, but the default settings mislead. With Automatic minimum and maximum, Excel scales the bars to the values in the selected range: the longest bar is the largest value, not 100%. Here donor pledges at 132% would get the full width and data migration, which is finished, would look unfinished.

Setting Minimum to 0 and Maximum to 1 fixes the scale, because 1 is 100% in Excel. Values above 1 show a full bar. Empty text cells such as vendor contracts get no bar, which is what you want. Tick Show Bar Only if you want the bar without the number, and choose Solid Fill if the gradient makes small bars hard to read.

1. Select D2:D8.
2. Go to Home > Conditional Formatting > Data Bars and pick a Solid Fill colour.
3. Go to Home > Conditional Formatting > Manage Rules, select the Data Bar rule and click Edit Rule.
4. Set Minimum to Type Number, Value 0, and Maximum to Type Number, Value 1.
5. Optional: tick Show Bar Only. Click OK twice.

Method 3: a progress bar chart

For a dashboard or a slide, a stacked bar chart looks cleaner. It needs two series: the percentage done and what is left. Put =MAX(0,1-D2) in a Remaining column and fill it down. The MAX keeps the over-target row at 0 instead of a negative number, which would draw a bar going backwards.

Chart the done percentage capped at 100% as well, =MIN(1,D2), or the over-target bar runs past the end of the track. Put the real percentage in the data labels or next to the chart.

1. Select the workstream names, the capped done column and the Remaining column.
2. Go to Insert > Insert Column or Bar Chart > Stacked Bar.
3. Click the Remaining series, then Format > Shape Fill and choose light grey.
4. Right-click the horizontal axis, choose Format Axis and set Bounds Maximum to 1.
5. Right-click a bar, choose Format Data Series and set Gap Width to about 50% for thicker bars.

Which method to pick

For a tracker you work in every day, use data bars with a fixed 0 to 1 scale: no extra column, and they update instantly. For something that gets pasted into email, printed, or opened in other spreadsheet apps, use the REPT text bar with MIN, MAX and IF. For a report or presentation, use the stacked bar chart. Whichever you pick, build it on a percentage that already handles a missing target.