Progress Bar in Google Sheets: REPT Formula vs Conditional Formatting
Color scales and data bars are popular, but they do not travel when you copy a formula to a new sheet. REPT bars do.
Quick answer
Conditional formatting paints the cell background based on value rules. REPT puts the bar inside the cell as text. Use CF when the bar is decorative color on existing numbers; use REPT when the bar itself is the readable output.
At-a-glance comparison
| Category | REPT text bar | Conditional formatting |
|---|---|---|
| What you copy | Formula string with visible bar characters | A number plus separate formatting rules |
| Travels to new sheets | Paste formula anywhere; bar comes with it | Formatting rules must be recreated or copied separately |
| Shows in formula bar | Full REPT formula visible for editing | Cell shows the number; bar is formatting only |
| Custom characters | Any text, emoji, or CHAR symbol in the bar | Color gradients and Google data bar styles |
| Bulk editing | Find-and-replace formulas across columns | Edit one rule; applies to the whole range |
| Best for | Template products, shared trackers, formula-first workflows | Quick visual polish on an existing numeric column |
Side-by-side formulas
REPT percent bar
Shows both the bar and a percent label in one cell.
=REPT("█",MIN(10,MAX(0,ROUND(A1/B1*10,0))))&REPT("░",MAX(0,10-MIN(10,MAX(0,ROUND(A1/B1*10,0)))))&" "&ROUND(A1/B1*100,0)&"%"
████████░░ 80%
Conditional formatting data bar
Apply Format > Conditional formatting > Color scale or data bar to the numeric column.
=A1/B1
0.8 (with blue data bar background)
REPT text bar
A REPT formula calculates how many filled characters to show, then appends empty characters for the remainder. The bar is the cell value, so it copies with the formula and shows up in exports as text.
Conditional formatting
Conditional formatting applies color scales, data bars, or custom rules to cells that already hold numbers. The number stays separate from the visual bar. Rules are sheet-level formatting, not cell formulas.
Details
When REPT beats conditional formatting
REPT wins when the progress display must be part of the cell value: template marketplaces, client handoffs, and any sheet where recipients paste formulas into their own copies. Text bars also support emoji themes that CF data bars cannot replicate.
When conditional formatting beats REPT
Conditional formatting wins when you already have a column of numbers and want fast visual polish without rewriting formulas. Color scales are ideal for heat maps; data bars work when the number should stay editable separately from the bar.
Mixing both approaches
Some dashboards keep the raw percent in column B, a REPT bar in column C, and CF color on column D for at-a-glance RAG status. They solve different jobs on the same row.
Choose REPT text bar when…
- •You ship templates where buyers paste formulas
- •The bar must appear in CSV export or printed tables
- •You want emoji, blocks, or CHAR symbols as the bar
- •You need one formula that works in any copied tab
Choose Conditional formatting when…
- •You already have numeric data and want quick color bars
- •The cell value must stay a plain number for sorting
- •You are building a heat map across a wide range
- •You prefer editing one formatting rule instead of many formulas
Related pages
Common questions
- Can conditional formatting copy with a formula?
- No. When you paste a formula to a new sheet, conditional formatting rules do not always follow. REPT formulas carry the full bar in the cell value.
- Which looks better in a dashboard?
- Conditional formatting data bars look polished on numeric columns with minimal setup. REPT bars look better when you need themed characters or text that matches your brand palette.
- Can I sort by the bar?
- With REPT, sort on a helper column of the ratio (A1/B1) or the numeric percent. With conditional formatting, sort on the underlying number since the bar is formatting only.
- Which is better for habit trackers?
- REPT is usually better for habit trackers you copy week to week. Each row gets the same formula pattern and the bar travels with it.
Copy a REPT bar in seconds
Browse 200+ progress bar formulas with live preview. Click any card to copy the formula, then paste into your sheet with values in A1 and B1.