Excel’s conditional formatting data bars seem easy to use, but they can cause more problems than they solve, creating a “rule rot” mess and causing a massive lag in large worksheets. Switching to the REPT function keeps your file size small and your performance snappy.
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.
Stop wrestling with text in Excel: These 8 tools are game-changers
Fix “fake” numbers, strip hidden web spaces, and join text professionally using Excel’s built-in legacy and modern tools.
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.
Always use formatted Excel tables (Ctrl+T) to ensure your formulas bleed down into new rows as you add data. The default table style in Excel uses banded rows, which can diminish the impact of your visuals, so after creating your table, open the “Table Design” tab and choose a plain style.
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)
On standard U.S. keyboards, the pipe character (|) is paired with the backslash (\) and requires you to hold Shift. On other keyboards, it’s next to the Z or Enter key.
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:
|
Formula segment |
Explanation |
|---|---|
|
UNICHAR(9608) |
This creates the full block character (█), which is a solid rectangle that occupies the entire width and height of the character space. When entered using the monospaced Consolas font, it’s the perfect building block for a bar that looks like a custom UI element. Using the UNICHAR code ensures the formula works in all versions of Excel. |
|
([@[Units Sold]]/MAX([Units Sold]) |
This creates a percentage. If your top salesperson sold 5,000 units and the current row is 2,500, the result is 0.5. |
|
*20 |
You then multiply the percentage by 20 to tell Excel that the 100% bar should be exactly 20 characters long. |
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.
How to Set Minimum and Maximum Values in Excel
Excel can produce results that are capped to a maximum number or return a result which has a minimum value.
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))
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. |
How to Concatenate in Microsoft Excel
Join two or more values by typing or using cell references in your spreadsheets.
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 (♦).
How to Add Emojis to Excel (And Have Them Change in Response to Your Data)
No, Excel isn’t just for boring people.
Standard text-based bars are faster to calculate and easier to maintain, but they’re just one way to enhance your spreadsheet’s appearance. You can also use Excel to create heat maps to highlight trends and outliers across datasets, giving you a complete toolkit for professional, high-performance reporting.
