Excel Tutorials · Lesson 109, Part 3
Excel's built-in Data Bars only fill sideways. When you need a bar that climbs upward, like a thermometer or a KPI tile, a stacked column chart does the job with no add-ins. This lesson covers the two-part data setup, hiding the second series, and adding a percentage label that updates itself.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
A vertical progress bar is a single colored column that fills upward to a height matching a percentage, used on dashboards and KPI tiles in place of a plain number. Excel has no native tool built for this. Conditional formatting Data Bars come close, but they only ever fill left to right, so a bar that climbs instead of stretches has to be built as a chart.
The trick is a stacked column chart with two series per category: a Complete percentage and a Remaining percentage that always adds up to 100. Stack them, hide the Remaining segment by giving it no fill, and what is left is a single bar that visually rises and falls with the Complete value, because the chart plots two invisible-and-visible pieces rather than drawing one shape directly.
Once the fill, gridlines, axis and border are stripped away, the chart no longer reads as a chart at all. It can be resized down to fit inside a single cell, given a percentage data label, and even colored dynamically with a helper formula, so it behaves like a live widget rather than a static picture.
The honest framing: this is a formatting workaround, not a chart type Excel ships with a button for. It takes a few extra minutes to strip down the first time, but once it is built it updates on its own, and the same technique is what most Excel dashboard templates are quietly using under a KPI tile.
Step-by-step, matching the video above.
In three columns, list a category label, a Complete percentage, and a Remaining percentage that equals 100 minus Complete. The chart needs both parts to stack into one column.
Select the data and go to Insert > Charts > Stacked Column. Excel stacks Complete and Remaining into a single column per category.
Click the Remaining series, open Format Data Series, and set Fill to No Fill. Only the Complete segment stays visible, filling upward as its value grows.
Remove the axis, gridlines, chart title, legend and border. What is left should read as a plain colored bar, not as a chart.
In Format Data Series, lower Gap Width to around 20 to 40 percent so the bar fills most of the column instead of sitting thin in the middle.
Right-click the Complete segment and choose Add Data Label, then set its number format to percentage and its position to Inside End.
Shrink the chart to roughly one column wide, then hold Alt while dragging its edges so it snaps to the cell border and reads like a native KPI tile.
| Project | Complete | Remaining |
|---|---|---|
| Kitchen Remodel | 72% | 28% |
| Q3 Onboarding | 45% | 55% |
| Website Redesign | 91% | 9% |
Complete and Remaining always sum to 100%, calculated as Remaining = 100% minus Complete. Chart both columns as a stacked column, then set Remaining's fill to None. Only the Complete segment stays visible, so a project at 91% shows a bar that is nearly full, and a project at 45% shows one just under halfway.
Five settings, and getting any one wrong breaks the illusion of a real progress bar.
Whatever percentage you want the bar to show. Point it at a formula (tasks done divided by tasks total) rather than typing it by hand, so the bar updates as the underlying numbers change.
Always 100% minus Complete. It exists only so the chart has something to stack against, and it disappears the moment its fill is set to None.
Found in Format Data Series. A high gap width leaves a thin, spindly bar; lowering it to 20 to 40 percent makes the column fill most of its plotted width, which reads as a proper bar rather than a sliver.
Added to the Complete series only, formatted as a percentage, and positioned Inside End so it sits near the top of the fill rather than floating outside the bar.
Add a helper column with =IF(B2>=0.9,"green",IF(B2>=0.5,"yellow","red")), then set each data point's fill to match. It is manual per point in native Excel charts, since there is no built-in conditional fill rule for a single series.
Same technique, three common finishes.
Narrow the chart to roughly the width of a single column, stretch it tall, and round the corners with a shape drawn on top. The stacked data underneath does not change at all.
Shrink the chart object, remove the legend and any title, then hold Alt while resizing and dragging so its edges snap to the cell border. It behaves like part of the worksheet from then on.
Repeat the two-column data setup once per row, then either build one chart per row or a single multi-category stacked chart with the bars side by side, useful for comparing several projects at a glance.
Select the finished chart, copy it, then Paste Special as a Linked Picture next to your data. The picture updates live and can be placed anywhere on the sheet without dragging the chart object itself around.
| Method | What it produces | Fill direction |
|---|---|---|
| Conditional Formatting Data Bar | Built-in, one click, no chart involved | Horizontal only |
| Stacked Column Chart | This lesson's technique, fully customizable | Vertical |
| Thermometer Chart | Same stacked chart, narrowed and shaped | Vertical |
Data Bars are the fastest option when horizontal is fine. The moment a design calls for a bar that climbs, the stacked column technique is the only native route, and every vertical-looking gauge or thermometer template you have seen is built on the same trick underneath.
Four things that go wrong on the first attempt.
The Remaining series still has a visible fill. Click it directly (not the Complete series) and set Fill to No Fill, or match it to the sheet's background color if a hard edge looks better.
Gap Width is still at its default, usually 150 percent. Lower it in Format Data Series until the bar fills most of the plotted column.
If it's typed by hand instead of formula-driven, the two will drift out of sync the next time Complete is updated, and the bar will stop reading as a true percentage.
Data label position defaults to Center or Outside End depending on chart type. Right-click the label and set its position to Inside End so it stays anchored to the top of the fill.
Where a vertical bar earns its place over a plain percentage.
One bar per project, colored by how close it is to done, gives a status board that reads at a glance instead of requiring anyone to scan a column of numbers.
Sales quota, savings goal, or headcount target progress all fit the same pattern: a Complete percentage against a fixed target, refreshed automatically as the underlying numbers change.
The same stacked column chart with a hidden series works identically in Sheets. Color-by-value rules just have to be set per data point manually, since Sheets has no per-point conditional fill formula for charts.
If a horizontal fill is genuinely fine for the layout, conditional formatting Data Bars take one click and need no chart at all. Save the stacked-column build for when the design specifically needs vertical.
If Complete and Remaining aren't formula-linked, updating one without the other breaks the 100% total and the bar stops representing a real percentage.
Without setting it to No Fill, the chart shows two stacked colors instead of one bar that appears to rise and fall, which defeats the whole effect.
Excel's default gap width is wide, which leaves the bar looking thin and centered rather than filling the column. Lower it in Format Data Series.
Any of these left on immediately signals "this is a chart" instead of "this is a progress bar." Strip all of them before calling the build finished.
A dynamic color rule needs to be applied point by point. Coloring the entire Complete series just gives every bar the same color regardless of its value.
Dragging a chart's edges freehand leaves it slightly off from the cell grid. Hold Alt while resizing so it snaps cleanly to cell borders and lines up with the rest of the sheet.
A status shouldn’t need a chart rebuilt every time the number moves.
Free 14-day trial. No credit card required.
A stacked column chart formatted so one segment fills upward to show percent complete, styled to read like a thermometer or a KPI tile rather than a chart.
No. Conditional formatting Data Bars only fill left to right. A bar that fills upward requires a stacked column chart instead.
A category label, a Complete percentage, and a Remaining percentage that equals 100 minus Complete, since the chart stacks the two into one column.
Set the Remaining series fill to None so it disappears, then remove the axis, gridlines and chart border so only the colored Complete segment reads.
Add a helper column with an IF formula that returns one color below the goal and another at or above it, then apply that color to the data point directly.
Yes. Add a data label to the Complete series, format it as a percentage, and set its position to Inside End so it sits at the top of the fill.
Resize the chart object down to roughly one column's width, remove the legend and title, then hold Alt while dragging so it snaps to the cell borders.
Yes, with a stacked column chart and a hidden series, though point-level color rules must be set manually since Sheets has no per-point conditional formula for chart fills.
The Remaining segment still has a fill applied. Set it to No Fill, or match it to the sheet's background color, so only the Complete segment is visible.
Yes. The chart is linked to the cell driving the Complete percentage, so the fill height and label update the moment that cell changes, with no extra steps.
A thermometer chart is the same stacked-column technique made narrow and tall, with a rounded shape drawn over the top. The underlying data and formatting are identical.
No. REPT() only produces horizontal bars from repeated characters, and there is no vertical equivalent, so a chart is required for a bar that fills upward.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →