Excel keeps growing its visualization tools with each version. And the charts are the flagship of those. Although they present lots of customization options, they may become overwhelming when you just need to create a simple bar or column chart. In this guide, we’re going to show you an easy way to create charts with formula in Excel.
REPT Function
We only need a single function called REPT that can repeat a string a given number of times. This ability is a perfect function to create a chart bar or a column. The only trick is to choose a suitable character which can junction with the next character without leaving a space. Otherwise, your bar chart will not be seen as a regular one.
An obvious choice is the vertical line character you can find on your keyboard: | However, you will see spaces between each character by using common font types like Arial or Calibri.
The syntax of the REPT function is simple. All you need is to provide the string you want to repeat and the number of times to repeat.
To overcome this issue, use the Britannic Bold as your font type.
Normalizing repeating number
Now, it looks like a proper bar, you can use it with your real data. The data will be your repeat number. However, using too large or small characters will not be a feasible solution. For example, you do not want to populate a cell or range with thousands of characters. Excel doesn’t allow to use decimal numbers either. Thus, consider determining a factor number to normalize the values in your dataset.
Here is our data and how the bars are looking without a factor.
To make them fit into the designated cells, we need to divide the values. A formula will be like the following for the bar of the cell C18.:
=REPT("|", C18/Factor)
Here you can see how a factor affects the length of the bars.
Choose a value which suits your data and the cell or range you want to populate the bars.
Column Chart
Column charts are nothing more than siblings to the bar charts. You can use the same formula to create a column chart as well. Just set the text orientation to -90 degrees in the Format Cells dialogue.
You can open the dialogue by right-clicking the menu or pressing Ctrl + 1 key combination.
Bonus: Score Cards
Score cards are another cool thing you can achieve by creating charts with formula. You can replace the “|” character with symbols or emojis to generate bars with graphics.
