Conditional formatting is a trap

At first glance, Excel’s built-in data bars (Home > Conditional Formatting) seem like a no-brainer. They take two clicks to apply and look professional.

However, as your workbook grows from a few dozen rows to a few thousand, the cracks start to show. The biggest issue with data bars isn’t the formatting itself—it’s how Excel manages it. Copying and pasting ranges with conditional formatting can sometimes create duplicate rules or misaligned ranges, requiring manual cleanup. There’s also the lag factor. Data bars recalculate whenever their source data changes, which can impact performance in very large datasets with frequent updates.

Excel treats the REPT output as simple text, making it significantly more lightweight and stable than graphical rendering.

The REPT function is the way forward

Excel’s REPT function is one of Excel’s simplest tools, usually tucked away for basic text manipulation. Its syntax is straightforward:

=REPT(text,number_of_times)

Simply choose a character, and use a number, cell reference, or formula to tell Excel how many times to repeat it. Before you know it, you’ve created a bar that lives directly inside the cell as text.

Disclaimer: Visuals vs. performance

Before you ditch conditional formatting entirely, it’s worth noting that Excel’s built-in data bars offer a few premium features that the REPT function can’t replicate alone, like gradient fills and the ability to grow to the left for negative numbers. However, as I explained above, by choosing the REPT function, you’re prioritizing stability and performance over visual finesse.

Example 1: The basic bar

Imagine you’re a manager tracking team performance scores on a simple scale of 1 to 10.

First, select the column where your data bars will go, and change the font to Playbill or Britannic Bold (these squish the individual characters into a solid bar without spaces). Then, in the top cell of the column, insert this formula and press Enter:

=REPT("|",[@[Score]]*5)

The reason the above formula multiplies the score by 5 is that a standard cell is much wider than 10 individual pipe characters. Without the multiplier, a perfect score of 10 would look like a tiny, insignificant sliver. To change the color of your bars, simply select the column and use the Font Color picker in the Home tab.

Example 2: Relative scaling

Now, suppose you’re analyzing regional sales figures that range from the hundreds to the thousands.

To mimic how Excel’s built-in data bars grow and shrink relative to other values in the range, you need to normalize your data using the MAX function. This prevents a value of 5,000 from being rendered as 5,000 individual characters in a single cell.

Change the font in the visualized column to Consolas. Then, enter this formula and press Enter:

=REPT(UNICHAR(9608),([@[Units Sold]]/MAX([Units Sold]))*20)

Here’s how the formula works:

Because every cell in the column is being divided by the same maximum, the bars stay perfectly proportional to one another. If your sales figures change, the MAX function updates automatically, and your bars will shrink or grow relative to the new top performer.

Example 3: The progress tracker

In this third example, let’s assume you’re tracking a software launch and want a “loading bar” that shows both completed work and the work still to be done. While a simple bar shows progress, a dual-character bar provides much more context and looks more professional.

Set the font for your visual column to Consolas, type this formula, and press Enter:

=REPT(UNICHAR(9608),ROUND([@Progress]*10,0))
&
REPT(UNICHAR(9617),10-ROUND([@Progress]*10,0))

Press Alt+Enter to split long formulas onto separate lines as you type. This makes the formula easier to construct, read, and edit.

Here’s what’s happening:

Formula segment	Explanation
=REPT(UNICHAR(9608),…)	This builds the “filled” part by using the full block character (█). It multiplies the percent by 10 so that 100% equals 10 blocks
&	This is the “glue” that joins the progress bar to the background track.
REPT(UNICHAR(9617),10-…)	This builds the “remaining” part using the light shade character (░). It calculates how many spots are left by subtracting the “filled” blocks from 10.
ROUND(…,0)	Rounding to 0 ensures Excel always asks for a whole number of characters. This guarantees the total length of the bar (solid + shaded) is always exactly 10 characters, keeping the right edge of your bars perfectly aligned.

Example 4: The star rating system

Imagine you’re building a summary sheet for e-commerce product reviews.

For qualitative data like customer ratings, symbols work much better than a standard bar. Change the font in your Rating column to Segoe UI Symbol, which can handle more complex Unicode characters without turning them into empty boxes, then type this formula and press Enter:

=REPT(UNICHAR(9733),[@Rating])

This formula uses REPT in its simplest form: it specifies a Unicode character and uses the values in the Rating column to tell Excel how many times to repeat it. You could also swap the star Unicode character for other codes that better match your data. For example, UNICHAR(9679) creates a modern dot plot (•), and UNICHAR(9670) creates a diamond (♦).