Charts help you display your data visually. While there are over a dozen different chart types in the current version of Excel, a column chart is often the best choice.
Column charts use vertical bars to represent data. Categories are displayed along the horizontal axis while values are shown on the vertical axis. You can choose a 2D, 3D, or stacked variation of a column chart and customize the appearance of your chart using a variety of options.
A column chart is a good choice, any time you need to:
- Show changes in data over time.
- Compare categories of data.
This article explains how to create column charts in Microsoft Excel and provides an overview of various ways to customize these charts.
Creating a Column Chart (Step by Step)
Creating a column chart is fairly straightforward. Let’s look at an example.
Step #1: Select the data you’d like to represent in your chart. In my example, I selected cells A2:E5.
Step #2: Click the Insert Column or Bar Chart button from the Insert > Charts group on the ribbon and select one of the available options. I clicked Clustered Column.
Step #3: Move or resize the chart, as necessary. I moved my chart below the source data.
Representing this data in a column chart makes it easy to analyze.
For example, we can see that revenue from Baked Goods increased over the first three fiscal quarters, but sales were down sharply in Q4.
Also, Baked Goods as a category brought in more revenue than Hot or Cold Drinks.
Customizing a Column Chart
Even though you might be happy with the overall look of this chart, there are still some small changes you can make to enhance its appearance.
For this chart, I would:
- Add a Title.
- Make the data labels bold on the horizontal axis.
- Increase the font size used in the legend.
As you can see, these minor tweaks give the chart a more polished appearance.
If you want to make additional changes to the column chart, you can choose an option from the Quick Layout gallery or use Add Chart Element to add, remove, or modify individual chart attributes.
Important: You always need to select the chart before making changes. All of the controls related to updating or formatting the chart are “contextual”. They only appear when the chart – or a specific chart element – is selected.
The Quick Layout Gallery
Options in the Quick Layout gallery are kind of like a “combo meal” at a fast food restaurant. Making a selection from the menu at a restaurant is easier because the things most people want are already included in the combo.
Let’s look at an example.
If I click to select the chart, then choose Layout 1 from the Quick Layout gallery on the Chart Design tab, the chart is updated with a collection of settings.
The legend is moved from below the chart to the right side and spaces between the vertical bars in each grouping are eliminated.
Selecting Layout 5 from the Quick Layout gallery results in the addition of a vertical axis title placeholder and data table.
In the screenshot below, I entered “US Dollars” in the vertical axis title placeholder. The data table displays values for each data point represented in the chart, making it easier to analyze those numbers without referring to another part of the worksheet.
Add Chart Element
While the Quick Layout gallery lets you apply several settings at once, the Add Chart Element button allows you to add or remove individual elements.
For example, if I no longer want the data table to be displayed, I can click to select the chart then select None from the options in Add Chart Element > Data Table.
In the final version of the chart (below), I removed the data table and added data labels above each vertical bar using the Outside End setting under Add Chart Element > Data Labels. I also added the legend back in below the chart.
Chart Styles are collections of settings related to the appearance of a chart.
In the example below, I selected my chart then clicked Style 9 from the Chart Styles gallery.
After applying Style 9 to the chart, the text in the title place holder was converted to “all caps” and each data series had a gradient effect applied providing more visual interest to these elements.
The Format Tab
Buttons on the ribbon’s Format tab let you change one formatting attribute at a time.
In the example below, I created another column chart to display total income by quarter.
To change the colour used in the chart to green, simply select any one of the vertical bars then click Green from the Shape Fill control in the Format > Shape Styles group.
To further enhance the look of the chart, you could add a shadow behind the green columns.
Choose one of the options under Shape Effects > Shadow.
Here is the final version of the chart.
With each new version of Excel, Microsoft has streamlined the process of creating and customizing charts.
As long as you select the right data before inserting a column chart, you can add or remove elements as needed and easily change the appearance of your chart.
I hope you’ll join me for the next article where we look at how to create a pie chart in Excel.