Column Chart in Excel

Column chart in excel is a chart which is used to represent data in vertical columns, the height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right, now if there is a single data series it is very easy to see the comparison.

Types of a Column Chart in Excel

We have a different type of column/bar charts available in MS Excel and these are:

Clustered Column Chart: A clustered chart in excel presents more than one data series in clustered vertical columns. Each data series shares the same axis labels, so vertical bars are grouped by category. Clustered columns make possible the direct comparison of multiple series and can show change over time, but they become visually complex after adding more series. They work best in situations where data points are limited.
Stacked Column Chart: This type is useful for representing the total (sum) because these visually aggregate all of the categories in a group. The negative point about this is that it becomes difficult to compare the sizes of the individual categories. Stacking enables us to show a part of the whole relationship.
100% Stacked Column Chart: This is a chart type helpful to show the relative % of multiple data series in stacked columns, where the total (cumulative) of stacked columns always equals 100%.

How to Make Column Chart in Excel? (with Examples)

You can download this Column Chart Excel Template here – Column Chart Excel Template

Example #1

To make a column chart in excel, first, we need to have data in table format. Suppose we have data related to the popularity of the colors.

Now to make the chart, we first need to select the entire data and then press the shortcut key (Alt+F1) to place the default chart type in the same sheet where the data is or F11 to place the chart in a separate new sheet.

Or we could use the long method that is to click on Insert tab -> Under Charts group -> Insert Column or Bar Chart.

We have a lot of different types of column charts to select. However, according to the data in the table and nature of the data in the table, we should for the clustered column. For which type of column chart should we go? This you can find below by reading the usefulness of various column charts.

The chart in the same sheet by pressing Alt+F1

The chart in the separate sheet by pressing F11

Here you can see in Excel 2016, we have ‘+’ sign on the right side of the chart where we have checkboxes to display the different chart elements

As we can see, by activating, Axes, Axes Titles, Chart Title, Data Labels, Gridlines, Legend trend line, the chart is looking like this.

Different type of elements of the chart are as follows:

We can enlarge the chart area by selecting the outer circle according to our needs. If we right-click on any of the bars, then we have various options related to the formatting of the bar and chart.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

If we want to change the formatting of only one bar, then we need to click two times on the bar and then right-click and we can change the color of the individual bars according to the colors they represent.

We can also change the formatting from the right panel, which is opened when we select any bar.

By default, the chart is created using the entire data selected but we can change the appearance of the chart by using the ‘Filter’ sign on the right side of the chart.

When a chart is selected, two more contextual tabs ‘Design‘ and ‘Format‘ get opened in the ribbon.

We can use the ‘Design’ tab to format the entire chart, the color theme of the chart, chart type, moving chart, changing the data source, the layout of the chart, etc.

In addition, the ‘Format’ tab for formatting individual chart elements like bars, text.

Example #2

Please find below column chart to represent the data of Amazon sales product wise

As we can see, the chart has become visually complex to analyze, so we will now use the ‘Stacked Column Chart’ as it will also help us out to find the contribution of the individual product in the overall sale of the month. To do the same, we need to click on the Design tab->’Change Chart Type’ in the ‘Type’ group.

Finally, your chart will look like this

Pros

Bar or column chart is more popular than another type of complicated chart as it can be understood by a person who is not much more familiar with chart representation but has gone through column charts in magazines or newspapers. Column or bar charts are self-explanatory.
It can also be used to see the trend for any product in the market. Make trends easier to highlight than tables do.
Display relative numbers/proportions of multiple categories in a stacked column chart.
This Chart helps to make a summary of a large amount of data in a visual, easily interpretable manner.
This Chart estimates can be made quickly and accurately. Permit visual guidance on the accuracy and reasonableness of calculations.
Accessible to a wide audience as easy to understand.
This Chart is used to represent negative values.
Bar charts can hold both data in distinct categories and data of one category in different periods.

Cons

It fails to expose key assumptions, causes, impacts, causes. That is why it is easy to manipulate to give incorrect impressions or we can say they simply do not give a good overview of the data.
The user can make comparisons of arbitrary data segments quickly on a bar chart but are not able to compare two slices that are not neighbors at once.
The main disadvantage of bar charts is that it is very easy to make it unreadable or just wrong.
A bar graph can be easily manipulated to give a false impression of a data set,
Bar graphs are not good when trying to display changes in speeds such as acceleration.
Using too many bars in a bar chart looks extremely cluttered.
A line graph in excel is more useful when trying to show trends over a course of time.

Things to Remember

Both the Bar and the Column chart display data using rectangular bars where the length of the bar is proportional to the data value but Bar charts are useful when we have long category labels.
Use various colors for bars to contrast and highlight data in your chart.
Use self-explanatory chart title, axes titles.
When the data is related to different categories, then use this chart otherwise for another type of data like for representing the continuous type of data, we can use a line chart.
Start the graph with zero always as it can mislead and create confusion while comparing.
Don’t use extra formatting like the bold font or both types of gridlines in excel for plot area so that the main point can be seen and analyzed clearly.

Column Chart in excel