A column chart is a graphic visualization of data using vertically placed rectangular bars (columns). Usually, each column represents a category, and all columns are drawn with a height proportional to the values they represent. The types of data column charts can display vary greatly, as the structure allows for using column charts pretty much with any type of data. Numeric, text, or even date data can be displayed on the horizontal axis, and this flexibility makes column charts a staple in dashboard applications. Excel, being the go-to software for all data tasks supports all kinds of bar and column charts with extensive customization options. Let’s take a closer look!
Column Chart Basics
A column chart has 5 main sections:
- Plot Area: This is the area where the graphic representation (i.e. columns) is shown.
- Chart Title: As the name suggests, this is the title of the chart. Using a short but descriptive text is always a good practice.
- Vertical Axis: The axis that represents the measured values, also known as the y-axis.
- Horizontal Axis: The axis that contains the categories of data, also known as the x-axis. The data series can be grouped like shown in the sample chart above.
- Legend: The legend is an indicator that helps distinguish data series from each other.
There are 3 types of column charts:
- Clustered: Each column for data series is clustered on the horizontal axis. This type is suitable to compare each individual value.
- Stacked: Data series rectangles are stacked on top of each other to form a single column for each category. The column heights are equal to the combined values of the categories. The stacked column charts are great for highlighting the differences between categories. However, it's difficult to compare the relative size of the rectangles against each other.
- 100% Stacked: You can choose a 100% stacked column chart to see the relative ratio of multiple items. All columns are set to 100% length. This type is best used for comparing the contribution of each item within a category. However, the actual values are omitted.
Feel free to download our sample workbook below.
Insert a Column Chart in Excel
To create a column chart in Excel, begin by selecting your data and include the data labels in your selection so that they can be recognized automatically. You can always change them later.
Next, go to the INSERT tab in the Ribbon, and click on the Column Chart icon to see the column chart types. Click on the chart type of your liking. We will be using Clustered Column in our example.
Clicking the corresponding icon will insert the default version of that chart. Now, let’s take a look at customization options.
Customize a Column Chart in Excel
You can edit almost everything you see in a chart. Excel provides tons of options to personalize charts and make them fit nicely into any presentation. There are two ways to do this, by double-clicking the chart elements, or by right clicking them.
Double-clicking on any item will pop up the side panel, where you will find the options specific to the element you clicked. Keep in mind that once the side panel is open, you don't need to double-click any of the options there – this was just to bring up the menu! The side panel includes element specific options as well as generic ones like those for colors and effects.
Right-Click (Context) Menu
Right-clicking an element will display the contextual menu containing configuration settings. You can modify basic styling properties (like changing chart colors), delete specfic items, or activate the side panel for more options. To display the side panel, click the options that begin with “Format” . For example, Format Data Series…
Chart Shortcut (Plus Button)
If you’re using Excel 2013 or newer, you can use chart shortcuts to add/remove elements, apply predefined styles and color sets, and filter values with a few clicks.
Another useful feature is that you can now see the effects of your actions on the fly without actually applying them. For example, in the image below, we held the mouse over the Data Labels item and Excel displays the chart labels.
Ribbon (Chart Tools)
Whenever you activate a ‘special’ object, Excel adds a new tab(s) to the Ribbon, and the same goes for charts. You can see chart specific tabs in a different column under the name CHART TOOLS. There are 2 tabs: DESIGN and FORMAT. The DESIGN tab allows adding new elements, applying different styles, modifying the data, and customizing the chart itself. The FORMAT tab, on the other hand, contains more generic options that are shared with other objects. Briefly, the chart tabs in the ribbon are the only menu where you can find all options in one place.
Preset Layouts and Styles
Preset Layouts and Styles can help improve visuals of your charts, and streamline the process. You can find styling options in the DESIGN tab under CHART TOOLS, or clicking the brush icon in the Chart Shortcuts. Below are some examples:
Applying Quick Layout:
Updating the Chart Style:
Changing chart type
You can change the type of your chart at any time from the Change Chart Type dialog. To change the type of your chart, click on the Change Chart Type item from the Right-Click (Context) Menu or DESIGN tab.
In the Change Chart Type dialog contains options for chart types and will also give you a quick preview. Select a chart type to continue.
Excel usually assumes that horizontal labels are categories, and vertical labels are data series. If your data is structured the other way around, click the Switch Row/Column button in the DESIGN tab, when your chart is selected.
Move a chart to another worksheet
By default, charts are created in the worksheet with the underlying data. If you need to move your chart to a new sheet, or another existing sheet, you can use the Move Chart dialog. To open the Move Chart dialog, click Move Chart… under the DESIGN tab, or when you’re in the chart right-click menu. Please keep in mind that you need to right-click an empty place on the chart area to see this option, as this option will not be available when you click a chart element.
In the Move Chart dialog, you have 2 options:
- New sheet: Select this option and enter a name to create a new sheet and place your chart there.
- Object in: Select this option and select an existing sheet from the dropdown to move your chart onto that sheet.