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.
Excel Column Chart (Table of Contents)
Column Chart in Excel
The situation where we can use ‘Column Chart’ is as if we want to know over the time, how the customer is using our product whether its sales are increasing or decreasing. Depending on the type of data you are visualizing and the story you want to tell, bar charts may be very a very efficient solution. Moreover, they are easily understandable in many areas – from business to life sciences.
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 present 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 is that it becomes difficult to compare the sizes of the individual categories. Stacking enables us to show a part to 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?
Column Chart in excel is very simple and easy to use. Let understand the working of with some examples.
Excel Column Chart 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 type of column chart 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 chart in excel.
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.
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 colour of the individual bars according to the colours 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 ‘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.
Excel Column Chart Example #2
Please find below column chart in excel 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 overall sale of the month. To do the same, we need to click on Design tab->’Change Chart Type’ in ‘Type’ group.
Finally, your chart will look like this
Pros of Column Chart in Excel
- Bar or column chart in excel is more popular than another type of complicated charts 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 newspaper. 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 in excel.
- 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 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 of Column Chart in Excel
- It fails to expose key assumptions, causes, impacts, cause. 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 is more useful when trying to show trends over a course of time.
Things to Remember
- Both the Bar and the Column chart in excel 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 type of gridlines for plot area so that main point can be seen and analyzed clearly.
This has been a guide to Column Chart in Excel. Here we discuss its uses and how to create Column Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Examples of Gantt Chart
- How to Build an Excel Chart Wizard?
- Column Lock Excel
- Excel Charts and Graphs | Top 8 Types
- Excel Data Filters
- How to Highlight Other Row in Excel?
- How to Delete Checkbox in Excel?
- Radar Chart in Excel (Spider Chart)
- Pareto Chart in Excel
- Area Chart in Excel
- Gantt Chart in Excel