In-cell Bar Charts Using the REPT Function

✨ Examples, tips, and tricks
1

This section of the forum needed a post, so here’s a trick. You can create in-cell bar charts using the REPT function, like this:

=REPT(“█”,3)

image

or for more robustness, where B6 is the cell containing the number:

=IF(ISBLANK(B6),"-",REPT(“█”,B6))

You can change the color of the bar by changing the font color.

This type of chart (if using dots) would be called a dot plot, so the length of the bar is discrete rather than continuous, but if you are okay with that, you can scale the chart to a length of up to 10 characters by rounding, like this:

=REPT(“█”,ROUND(10*value/MAX(range_of_values),0))

Almost as good as in-cell data bars, but it requires two columns. Still, this lets you use any unicode character you want (instead of the block character).

7 Likes
2

This is so cool :star_struck:

3

A template is now included in the gallery titled “Pros and Cons with Tornado Chart” which demonstrates the use of this technique and the formulas.

3 Likes
4

Here is another application of the in-cell bar chart, used for a progress bar in a column separate from the % column.

image
image1021×519 41.7 KB

The formula in the Progress column (implemented as a Column Formula data type) may be difficult to see in the screenshot. The formula is =REPT(“█”,ROUND(E*10,0)) where column E is the % column.

This template can be accessed from the following page under the template titled “Task Checklist Template”:

1 Like