In this Excel tutorial, you will get to know how to
-Create clustered column chart, stacked column chart, and 100% stacked column chart.
-Format column chart.
We have used Microsoft 365 while preparing this article. But you can also apply the methods in this article in the other versions.
In column charts, the values are typically aligned along the vertical axis and the categories are typically aligned along the horizontal axis. Column charts are used to display comparisons between several data categories and how data changes over time.
Download Practice Workbook
What is Column Chart in Excel?
A column chart in Excel is a type of graph that uses vertical bars or columns to represent the values of the data. It is usually used to observe changes over time or to compare and visualize data from many categories. In a column chart, each column represents a specific category and each column’s height reflects the value it stands for.
Types of Column Chart in Excel
In Excel, there are several types of column charts such as clustered column chart, stacked column chart, and 100% stacked column chart, and each chart has 2-D and 3-D forms. Have a look at the general definition of some of the most used column charts in Excel.
- Clustered Column Chart: In an Excel clustered column chart, vertical bars that represent various data series or categories are shown side by side. The columns for each series are unique, and the bars within each series are organized into groups.
- Stacked Column Chart: In the stacked column chart, the columns are placed on top of one another to indicate the total value for each category. This type of chart is helpful for comparing the contributions of several categories to the overall structure and visualizing how each one is made up.
- 100% Stacked Column Chart: In a 100% stacked column chart, the columns are stacked on top of one another and the height of each column indicates the corresponding percentage of each category. The individual segments inside each column show the percentage of each data series compared to the total.
How to Create Column Chart in Excel?
We can insert every form of chart available in Excel from the Insert tab. The column chart is not an exception. Excel has Insert Column or Bar Chart option available in the ribbon. From there, we can insert all forms of column charts. We have also included details for creating each chart in their sub-section.
We will be using the following dataset as an example to demonstrate methods in this article. The dataset is representing some company’s total revenue, total cost, and profit of a specific year.
1. How to Create Clustered Column Chart in Excel
You can create a Clustered Column Chart from the Insert tab after selecting sufficient and appropriate data.
- To create a clustered column chart with our dataset, first select range B4:E9.
- Go to the Insert tab.
- Click on the Insert Column or Bar Chart drop-down menu and select Clustered Column.
- This will create a Clustered Column Chart as follows.
You can see from here, the blue, orange, and gray columns represent the total revenue, total cost, and profit values respectively. By analyzing the height/length of each column, you can easily get an idea of each company’s business strategy.
2. How to Create a Stacked Column Chart in Excel
From the Insert tab’s Charts group, you can create a Stacked Column Chart.
- To create a stacked column chart, select range B4:E9.
- Click on the Insert tab.
- Go to the Insert Column or Bar Chart menu.
- Select Stacked Column.
- A Stacked Column Chart will be created as follows.
From this chart, you can easily analyze each company’s total revenue, total cost, and profit variation over time.
3. How to Make a 100% Stacked Column Chart
To create a 100% stacked column chart, select your data then go to the Insert tab’s Charts group and utilize the Insert Column or Bar Chart menu. Follow the steps below to learn more-
- Select range B4:E9.
- Go to the Insert tab.
- Click on the Insert Column or Bar Chart menu.
- Select 100% Stacked Column.
- Following this will create a 100% stacked column chart as follows.
From this chart, you can easily compare the percentage of each company’s total revenue, total cost, and profit as a total. This type of chart allows you to analyze how the percentage of each value that contributes changes over time.
How to Format a Column Chart in Excel?
After creating a column chart in Excel, it offers you several features that allow you to customize your chart in your way. You can add or remove chart elements, modify chart titles, customize chart design and color, and much more.
1. How to Add and Remove Column Chart Elements?
After creating a column chart, you can add or remove several chart elements. Some of these elements are the chart axes, title, data labels, data table, legend, gridlines, etc. After selecting the created chart, utilize the plus (+) icon to do this.
- At the top rightmost of your chart, click on the plus (+) sign.
- From here, you can add a chart element by marking it and remove a chart element by unmarking it.
- Let’s, unmark the Gridlines and mark the Data Labels, our chart will look as follows.
Read More: How to Create a Column Chart in Excel
2. How to Customize Chart Title?
You can easily customize your chart title after creating it. You can simply change the text of your title and change the text’s font style from the context menu.
- As you can see here, we have set the title of our chart as Column Chart. Just simply click on the title and set the title as you want.
- You can modify the style of your chart title.
- Just right-click on the title and you will have several options that will allow you to customize your chart title.
- Let’s say, we have clicked on the Style option and selected the Gold Subtle Effect.
- Our chart title will appear as follows.
3. How to Modify Chart Design?
After creating a chart, a new tab Chart Design will be created by default in the toolbar. From there, you can modify your chart style in many ways. Use the Chart Styles group in the Chart Design tab for this.
- Let’s assume, we have clicked on Style 3 and our chart will look as below.
Read More: How to Change Width of Column in Excel Chart
4. How to Change Column Colors?
From the Chart Design tab, you can change the colors of each column. Utilize the Change Colors drop-down menu to do this.
- We have selected the chart first.
- Then from the Chart Design tab, click on Change Colors.
- Here we have selected Color Palette 4. The columns are now appearing as follows.
5. How to Customize Background Color of the Chart?
After creating a chart in Excel, another tab named Format will be added to the toolbar. From there, you can utilize the Shape Fill menu to customize the background color of your chart.
- To customize the background color of your column chart, first click on the chart.
- Then at the toolbar, go to the Format tab.
- Click on the Shape Fill menu and choose any color you want.
- Here, we have chosen the Gold, Accent 4, Lighter 80% color.
- And our chart’s background is changed as follows.
6. How to Adjust Chart Texts?
You can customize the texts of your chart also from the Format tab’s WordArt Styles section.
- Select the chart and go to the Format tab.
- In the WordArt Style group, click on the arrow symbol.
- A sidebar titled Format Chart Area will appear on the right of the worksheet.
- Using the options there, you can customize the texts of your chart.
- Let’s assume, you want to change the colors of the texts.
- Click on Text Fill & Outline.
- Mark Solid Fill.
- Click on Fill Color and choose any color you want.
- We have selected Navy Blue color here.
- Finally, the texts of your chart will look as follows.
7. How to Change Legend and Axes Texts?
You can customize chart legends as well as chart axes texts. Simply click on the plus (+) icon then mark the Axis Titles and Legend. After that, use the Format Chart sidebar to customize those.
- In our chart, we already have the legend marked.
- Now let’s mark the Axis Titles from the chart elements.
- Now, change the Axis Titles texts by simply clicking on them and retyping.
- We have named the Y-axis title as Dollars and the X-axis title as Companies.
- Now, if you click on the legend of your chart, the Format Legend sidebar will appear as follows.
- From there, you can modify the legend of your chart.
- Suppose, you want to set the legend position at the bottom of your chart. Click on Legend Options from the Format Legend sidebar.
- Mark Bottom.
The legend is now at the bottom of the chart.
8. How to Customize Gridlines in the Chart?
Click on the plus (+) icon after selecting your chart then mark Gridlines. After that, select any gridline and use the Format Gridlines sidebar to customize those.
- Let’s assume, you want to add gridlines to your column chart.
- First, click on the chart’s top right-most plus (+) icon.
- Mark the Gridlines.
- If you hold the mouse cursor over it, more options will appear. From there mark the Primary Major Horizontal and Primary Major Vertical from the Chart Elements.
- After adding Gridlines to your chart, you can customize those.
- Click on the gridlines.
- Format Major Gridlines sidebar will appear on your worksheet.
- From there you can customize your gridlines as you want.
- Like here, we’ve created dashed gridlines with 1 pt width.
How to Move a Chart to a Different Worksheet?
You can move your created chart from one sheet to another easily. Select your created chart then from the Chart Design tab, use the Move Chart option in the Location group to move the chart to a different worksheet.
- Go to the Chart Design tab.
- Click on Move Chart.
- A dialog box titled Move Chart will appear on your screen.
- Mark the New sheet option and set the name of the worksheet where you want to move your chart. Here we have named the new worksheet Chart1.
- Click on OK.
- You will see that the column chart is moved to a new worksheet named Chart1.
What Are Benefits and Drawbacks of Column Chart in Excel?
In Excel, a column chart is a common visualization tool that offers a simple representation of data. The following are some benefits and drawbacks of Excel column charts:
- Column charts offer a clear representation of data and are really aesthetic. You can easily compare values and analyze your data efficiently by just looking at a column chart.
- Column charts are great for displaying patterns over time. Finding patterns, changes, or growth trends is made simpler by showing data points in a certain order.
- Column chart in Excel allows you to add data labels, data table, legend, gridlines, axes, and much more to the graph. Those make it easier to analyze the values represented by each column. This enhances the understanding of your data.
- Column charts are excellent for comparing data across various categories or time periods. The vertical bars make it simple to compare values quickly.
- A stacked column chart shows the proportions or relative quantities of various groups. Additionally, negative numbers are represented using these types of charts.
- Not all forms of data are appropriate for column charts. They perform best when given numerical or categorical information that may be simply represented by vertical bars. Other chart types can be more suitable for complex data sets or non-numeric data.
- Column charts can become hard and challenging to understand when large datasets with many data points are used. The chart may get complicated and lose its usefulness if there are too many categories of data.
- Though column charts can visualize data trends, it is not as useful as line charts for that.
Frequently Asked Questions
1. What is a column chart and line chart?
A column chart uses vertical bars or columns to represent the data, with each bar’s length corresponding to the value it represents. You can utilize it for analyzing data from various categories that change over time.
On the other hand, in a line chart, lines that are connected represent the data points. It is primarily for showing the relationship between two variables or to demonstrate trends and patterns over time.
In general, column charts and line charts are both common chart styles with different uses. While line charts are useful for demonstrating continuous data or highlighting trends and patterns, column charts are useful for comparing different categories.
2. What is the use of column bar in Excel?
There are several uses of column bars in a chart in Excel. These column bars give users a better visualization of data comparison, data trends, and data analysis. These columns present data in an effective way to the audience so that they can easily understand the whole thing.
3. What is column chart in data visualization?
A column chart is a type of data visualization that shows data values as vertical bars or columns. It is used to compare data from various categories that change over time. The height or length of each column is equal to the value it reflects and each column represents a single data category. Column chart simplifies data in a way so that the user can easily visualize, analyze and compare large data.
The use of column chart in Excel is for simple data comparison, trend analysis, and data visualization. In this article, we have learned how to create several types of column charts in Excel and formatting features for those.
This includes the creation of three types of column charts- clustered, stacked, and 100% stacked. For formatting features, we have included customization of column chart’s title, design, gridline, legends, etc.
Hope, you have got whatever you were looking for. Visit our site ExcelDemy for more relevant articles.