Create Column Charts in Excel With Just a Formula

February 23, 2022

by howtoexcel

charts functions

In Excel, you can create quick and easy visuals with only a formula. You don’t need to insert charts or worry about if they are set up correctly. In this post, I’ll show you how you can quickly create both bar and column charts.

For this example, I’m going to use data from Apple’s most recent earnings report, to see the split between sales of its different categories. Here’s what the data looks like from its most recent filing:

To create a simple bar chart, I’m going to use the REPT function, which allows me to repeat text. The character I’m going to repeat is the “|” line, which on most keyboards is the button above the enter key. Holding shift and that key should give you that line. The number of times I want to repeat the character will be the value in column B. But because it’s too large, I’m going to divide it by a factor of 100. Here’s how that formula will look:

=REPT("|",B4/1000)

If I copy this down, my bar chart remains a work in progress:

Using the REPT function to generate repeating values.

One way to make this look like more of a bar chart is by changing the font. In column C, I’m going to change it to Britannic Bold. And now, this looks like a proper bar chart:

Using the REPT function to produce bar charts.

If I sort the values from largest to smallest, then it’s easier to see the progression:

Bar chart using REPT function, when sorted from largest to smallest.

This is good, but this is also still a bar chart. To convert this into a column chart, I need to make a couple of changes. The first thing is I need to arrange the data differently so that the fields and the values are going horizontally rather than vertically. To do this, you can just transpose the data. You can do this using the TRANSPOSE function. Now, my data is better suited for a column chart:

Now, I’ll add back the REPT function and use the same font. Except for this time, I will modify the cells so that the alignment is vertical:

Now, after compressing the columns, I have a column chart set up:

Column chart in Excel using the REPT function.

Here’s a quick video showing the steps:

If you liked this post on How to Create Column Charts in Excel With Just a Formula, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.