This time I will show you an interesting use of the REPEAT function and how we can use it as an alternative to using bar charts in Excel. When you are developing a dashboard, also known as , using graphs within cells can be very helpful.
We can improve the visualization of our data and help in the interpretation of the information if we use the REPEAT function to create sparklines of bars in Excel that will be displayed within a cell.
Sparkline bar with REPEAT function
The REPEAT function allows us to repeat a character, or a text, a certain number of times. For example, the following formula will display “XXXXX”:
To get the graphics inside cells, as seen in the previous image, I have applied a font type size 8 for all cells in column C. Now, the formula that we will introduce in that column must repeat the character “|” as many times as indicated in column B. For example, the formula in cell C2 will be the following:
=REPETIR("|",B2) & " " & B2
Based on the number in cell B2, this will be the number of times the character “|” will be repeated. and at the end of the text string the same number from column B will be concatenated to be displayed next to the bars.
Another example of a bar sparkline
There are a large number of different applications for this technique. For example, to graph positive or negative values we can create sparklines in two different columns:
For this example I have used the IF function which will help us to know if the value in column B is positive or negative. In our example, column C will show the sparkline for negative values so I have used a red font for all cells in the column and also applied a right alignment. The formula for cell C2 is as follows:
The condition B2 <0 will only be fulfilled when the value of cell B2 is negative, in which case the REPEAT function will be executed, which will return the sparkline. Notice that the second argument to the REPEAT function has a negative sign (-) to make the value in cell B2 positive to avoid an error.
Now look closely at the formula for cell D2. The condition of the IF function causes that the sparkline is only displayed for positive values:
In this case the second argument of the REPEAT function no longer has a negative sign because, thanks to the condition of the IF function, only positive values will be received. Since in our example cell B2 has a negative value, the only cell that will display the sparkline will be cell C2 and cell D2 will have an empty text string. By copying the formulas downwards we will obtain the corresponding sparklines for all the rows.
You will surely find some useful way to use this technique while designing your dashboards in Excel.