Data bars in a cell using character repeat

by | Oct 24, 2022 | Excel | 0 comments

Today, we are going to talk about cre­at­ing charts like data bars in cells using for­mu­las. We want to cre­ate some­thing like this (Fig. 1)

Data bars in a cell using char­ac­ter repeat
Charts from data bars
Fig. 1 Charts from data bars

Let’s start with cre­at­ing a prop­er for­mu­la. We have to use the REPT (repeat) func­tion. It will repeat a text, one or more chars, as many times as we write in the sec­ond argu­ment. Let’s start with a pipe (a ver­ti­cal line). We want to repeat it as many times as there are sun­ny days in a giv­en month. Our for­mu­la looks like this (Fig. 2). 

=REPT(“|”,B2)

REPT formula with a pipe
Fig. 2 REPT for­mu­la with a pipe

After enter­ing and copy­ing down, we have data bars. How­ev­er, the dis­tance between indi­vid­ual signs is too big (Fig. 3) 

Data bars with too long distances
Fig. 3 Data bars with too long distances

We have to nar­row it by using, e.g. a dif­fer­ent font that works with this sign. We are select­ing our data bars and chang­ing the font name to Script (Fig. 4). Now, it looks much better. 

Font change
Fig. 4 Font change

Let’s try, how­ev­er, a dif­fer­ent font. With Bri­tan­ni­ca Bolt, the signs look like real data bars (Fig. 5). 

Font change again
Fig. 5 Font change again

Let’s take the last exam­ple, which is the Haet­ten­schweil­er font. In my opin­ion, it looks the best, so I’m stay­ing with this one (Fig. 6)

The best font
Fig. 6 The best font

Apart from repeat­ing a pipe, we can repeat oth­er signs. If they are wider than the pipe and if the num­ber of repeats is too big, we may have a prob­lem (Fig. 7)

=REPT(“*”,B2)

A formula with an asterisk
Fig. 7 A for­mu­la with an asterisk

We can see, that the aster­isk string is too long (Fig. 8)

Too long
Fig. 8 Too long

The solu­tion is divid­ing the num­ber, and the REPT func­tion will only look at the inte­ger part of the num­ber. Let’s divide our num­bers safe­ly by two (Fig. 9). 

=REPT(“*”,B2/2)

Number division
Fig. 9 Num­ber division

When there is a small­er num­ber of repeats, it looks quite good (Fig. 10) 

Shorter strings
Fig. 10 Short­er strings

We have to remem­ber that an aster­isk is a sim­ple sign, and we can use what­ev­er sign we like. Let’s take, for exam­ple, a star (Fig. 11) 

=REPT(“★”,B2/2)

A formula with a star
Fig. 11 A for­mu­la with a star

We can see that the strings are very long (Fig. 12). We can divide them by even a greater num­ber, e.g. 3. 

=REPT(“★”,B2/3)

Dividing by 3
Fig. 12 Divid­ing by 3

After putting the for­mu­la, it looks good (Fig. 13). 

Shorter strings
Fig. 13 Short­er strings

In our last exam­ple, we are going to repeat the let­ter n (Fig. 14)

=REPT(“n”,B2/3)

Repeating n
Fig. 14 Repeat­ing n

Now, we can see many ns (Fig. 15) 

N string
Fig. 15 N string

How­ev­er, we can change our font into Wind­ings, and the let­ter n changes into squares, which looks quite good as data bars (Fig. 16)

N into squares
Fig. 16 N into squares

Sum­ming, up, when we use dif­fer­ent signs and dif­fer­ent num­ber of repeats, we can cre­ate quite good data bars using a formula. 

https://www.youtube.com/watch?v=XYs6Eu-0hZw

Related Posts

Submit a Comment

Your email address will not be published. Required fields are marked *