Column Chart in Excel ( Table of Contents )
Column Chart in Excel
COLUMN CHARTS are “vertical bars to represent data”.
Excel Column chart represents the data in vertical bars looking across the chart horizontally. Like other charts, column chart to has X-axis and Y-axis. Usually, X-axis represents year, periods, names etc… and Y-axis represents numerical values. Column charts have used to display a wide variety of data to exhibit the report to the top management of the company or to an end user.
The simple difference between Column chart and Clustered Chart is the number of variables used. If the number of variables is more than one then we call it a “CLUSTERED COLUMN CHART”, if the number of variables is limited to one then we call it a “COLUMN CHART”.
One more major difference is in the column chart we are comparing one variable with the same set of other variables. However, in the clustered column chart we compare one set of the variable with other sets of variables as well as within the same variable also.
Therefore, clustered column chart tells the story of many variables while the column chart shows the story of only one variable.
COLUMN CHART in excel is very useful to tell the story of a small amount of data. For example, you want to show your monthly sales numbers you can create a COLUMN CHART.
“The Simpler the chart the better it is”. Excel Column chart straight forward tell the story of a single variable and does not create any sorts of confusion in terms of understanding.
In this article, we will look into the ways of creating COLUMN CHARTS in excel.
Types of Column Chart in Excel
There are totally 5 types of column chart available in excel. Go to Insert tab and click on COLUMN.
- 2-D column and 2-D stacked column chart
- 3-D column and 3-d stacked column chart
- Cylinder column chart
- Cone column chart
- Pyramid column chart
How to Make Column Chart in Excel?
Column Chart is very simple to use. Let us now see how to make Column Chart in Excel with the help of some examples.
Excel Column Chart – Example #1
I have sales data from Jan to Dec. I need to present the data in a graphical way instead of just a table.
I am creating a COLUMN CHART for my presentation. Follow below steps to get insight.
Step 1: Set up the data first.
Step 2: Select the data from A1 to B13.
Step 3: Go to Insert and click on Column and select the first chart.
Note: The shortcut key to create a chart is F11. This will create the chart all together in a new sheet.
Step 3: Once you click on that chart it will insert the below chart automatically.
Step 4: This looks an ordinary chart. We need to make some modification to make the chart look beautiful.
Right click on the bar and select Format Data Series.
Step 5: Go to Fill and select Vary colors by point.
Step 6: Now your chart looks like this. Each bar colored with a different color to represent the different month.
Step 7: Remove Legend by selecting them and pressing delete key option.
the Legend will be deleted
Step 8: Rename your chart heading by clicking on the title
select the Gridlines and click on Format Gridlines.
Click on Line Color and select no line option
Gridlines will be removed.
Step 9: Click on Format Chart Area.
Select Fill option. Under Fill, Select Solid fill and select the background color according to your wish.
Finally, my chart looks like this.
Excel Column Chart – Example #2
In this example, I am going to create a user-defined chart i.e. based on the clicks you make it will start to show the results in the column chart.
I am taking the same data from the previous example.
Step 1: Insert a “Horizontal Scroll Bar”. Go to developer tab > Click on Insert and select ScrollBar.
Step 2: Draw the scroll bar in your worksheet.
Step 3: Now right click on the Scrollbar and select Format Control.
Step 4: Now go to Control and make the changes as shown in the below image.
Current Value: This is the present value now. I have mentioned zero.
Minimum Value: This is the bare minimum value.
Maximum Value: This is the highest value to mention. I have chosen 12 because I have only 12 months of data to present.
Incremental Change: This is once you click on the ScrollBar what is the incremental value you need to give.
Cell link: This is the link given to a cell. I have given a link to the cell C1. If I click on the scroll bar once in the cell C1 the value will be 1, if I click on the scroll bar twice it will show the value as 2 because it is incremented by 1.
Now you can check by clicking on the scroll bar
Step 5: Now we need to rearrange the data to create a chart. I am applying IF formula to rearrange the data.
In the cell E5, I have mentioned formula as If the cell C1 (scrollbar linked cell) is greater than or equal to 1 I want the value from the cell B2 (that contains Jan month sales).
Similarly, in cell F5, I have mentioned if the cell C1 (scrollbar linked cell) is greater than or equal to 2 I want the value from the cell B3 (that contains Feb month sales).
So like this, I have mentioned the formula for all the 12 months.
Step 6: Now insert a COLUMN CHART as shown in example 1. But here select the rearranged data instead of the original data.
Step 7: Now the COLUMN CHART has created. Based on the clicks you make on the scroll bar it will start to show the bars in the column chart.
As of now, I have clicked the scroll bar only 3 times so it is showing only for 3 months. If you want to see for remaining months, you can click the scroll bar up to 12 times.
Advantages of Excel Column Chart
- Easy to make and understand.
- Show differences easily.
- Each bar represents only one series only.
Things to remember in Dynamic Chart
- Arrange the data before creating a Column Chart in Excel.
- Use the Scroll Bar option to make the chart look attractive.
- If the single series has many data then it becomes Clustered Chart.
- You can try many other column charts like a cylinder, pyramid, 3-D charts etc…
This has been a guide to Excel Column Chart. Here we discuss how to create Column Chart in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –