Customizing an Excel table with a progress bar allows you to quickly visualize the progress of a project, the achievement of sales goals, or the tracking of recurring tasks. Rather than leaving a simple numeric value, you can transform a cell into a graphical indicator, install a colored signal that immediately captures attention. In this article, we will detail two main approaches: conditional formatting and the use of stacked charts, before discussing tips and best practices to go further.
Overview of available methods
There are mainly two techniques to add a progress bar in Excel:
- Conditional formatting: quick to set up and very flexible for simple percentages.
- Stacked chart: more visual, allows displaying legends and mixing multiple data series.
The choice will depend on your customization needs, the level of detail, and the desired aesthetic. We will then see how to combine these bars with formulas so that they update automatically.
1. Progress bar with conditional formatting
1.1 Prepare your data
Imagine a “% completed” column in B, ranging from 0% to 100%. In C, you want to display the visual bar. You can, if needed, merge or split cells correctly so that the bar occupies several columns without disrupting the layout.
1.2 Apply the formatting
- Select the range (e.g., C2:C20).
- In the “Home” tab, click on “Conditional Formatting” > “Data Bars” > select a style.
- Choose a gradient or solid fill according to your preference.
- In “Manage Rules…”, adjust the minimum (0) and maximum (1 or 100%) values.
Conditional formatting automatically translates the percentage value into a bar. If your cells contain a number between 0 and 1, leave the default settings; if they contain 0–100, call the Advanced menu to adjust the scale.
1.3 Customization tips
- To display the percentage and the bar at the same time, enable “Show Bar Only” in the format options.
- Play with colors to signal thresholds: green above 80%, orange above 50%, and red below.
- You can even overlay an IF formula to change the text color according to progress: see the IF formula.
2. Progress bar via a stacked chart
2.1 Data table structure
This second method requires two series:
- Progress: your actual percentages.
- Remaining: calculated by
=100%-Avancement.
For example, in B2 you have 45%; in C2 enter =1-B2 if you are using fraction (0–1), or =100%-B2 in percentage 0–100.
2.2 Creating the Chart
- Select the range B2:C2 (or the entire column if you have a list).
- In the “Insert” tab, choose “Bar Chart” then “Stacked Bars”.
- Once generated, reverse the order of the series if necessary (right-click on the series > Select Data > Reverse order).
- Remove the color of the “Remaining” part to keep only the progress bar, or choose a very light gray.
You get a horizontal bar whose length corresponds to the percentage. For multiple rows, select your entire table and use a “stacked bars” chart with multiple records.
2.3 Advanced Customization
- Modify the thickness of the bar by adjusting the “Overlap” and the width in the format options.
- Add a data label that displays the value: right-click series > Add labels > Position them inside.
- Use text functions to enrich the label: for example, combine REPT and LEFT to precisely format the legend. (discover LEFT, RIGHT, MID)
3. Comparison and Summary Table
| Method | Simplicity | Flexibility | Visual | Automation |
|---|---|---|---|---|
| Conditional | Very simple | Limited colors & styles | Discreet | Updates automatically |
| Stacked Chart | More manipulations | Rich palette and labels | Strong impact | Requires calculation formula |
4. Automate with Excel Formulas and Functions
To go further, you can generate the bar directly via a character string. For example:
=REPT("█",ENT(B2*20)) & REPT("░",20-ENT(B2*20)) & " " & TEXTE(B2,"0 %")
This formula uses REPT to repeat a filled or empty character, with a total of 20 characters. You can combine a conditional calculation of your sum to automatically generate the value in B2, fed by your tracking criteria.
5. Best Practices and Tips
- Favor a consistent scale throughout the workbook to visually compare multiple indicators.
- Keep your source data separate from your presentation: maintain a “Raw Data” tab in the background.
- Document your formulas with Excel notes or a “Legend” tab. A small comment on the cell is enough to explain your code.
- Test the display in print mode: some conditional bars may disappear if the option is not checked.
FAQ
- Which method to prefer for quick reporting?
- Conditional formatting is the fastest, without adding a chart, ideal for a simple table.
- Can the progress bar be animated in Excel?
- No, Excel does not offer native animation; however, you can simulate step-by-step via macros or PowerPoint.
- How to manage multiple bars on the same sheet?
- Use a stacked chart with different series or duplicate the conditional formatting on several ranges.
- Can data bars be printed?
- Yes, provided you check “Print conditional formatting” in Excel’s print options.
By combining these solutions and adapting each method to your context (project, KPI tracking, or dashboard), you transform a simple set of numbers into a clear and engaging visual indicator. Don’t hesitate to test both approaches and mix styles and formulas for a tailor-made result!