The Good All-Rounders: Column and Bar Charts

The Good All-Rounders: Column and Bar Charts

Compare numerical data using column and bar charts, good all-rounder charts suitable for most types of data. 

When to use

We use a column or a bar chart whenever we want to compare numerical data or if we have a larger dataset. 

Instructions

The most common chart types we encounter in Excel are column and bar charts. This is because they are the most flexible charts and are capable of displaying large amounts of data clearly. 

In general, column and bar charts are best for comparing numerical data but they are good all-rounder charts. 

What type of data might we display in a column or bar chart?

• Sales by Region.
• Profit by Quarter.
• Revenue by Sales Agent and Month.
• Population by Country. 

Create a 2-D Clustered Column Chart

In this example, we are going to create a 2-D Clustered Column Chart that shows the Revenue by Sales Agent and Month. 

There are two approaches you can take when creating a chart: select the data first OR create the blank chart and add the series. 

In general, I prefer to select the data first. 

• Select the cell range B4:N10 in the table. 
• From the Insert tab, in the Charts group, click the drop-down arrow next to column chart. 
• From the 2-D Column category, select the first chart. 

Now, let's imagine that our manager wants us to modify the chart to only show the revenue for the two sales agents he manages: Claire and Sarah. 

• From the Chart Design tab, in the Data group, click Select Data. 
• In the Legend Entries area, deselect all sales agents except Claire and Sarah. 
• Click OK. 

Now, maybe we only want to display the data for Q1. 

• From the Chart Design tab, in the Data group, click Select Data. 
• In the Horizontal (Category) Axis Labels area, deselect all months except Jan, Feb, and Mar. 
• Click OK. 

Formatting

Once we've created a chart, it's really then down to formatting. We can format any chart to very specific requirements. Let's take a look at a few formatting options. 

Change Chart Title

Adding a title to our chart gives it meaning. Chart titles are just text boxes that can be edited and positioned using drag and drop. A chart title text box will be added to any chart we create by default. 

• Double-click the chart title text box. 
• Type 'Revenue by Agent for Q1'. 
• Select the text and press CTRL+B to make it bold. 

Change Colors

We can change the color of any element in our chart. We can do this by using one of the built-in palettes or by choosing specific colors. The built-in palettes will show a variety of color palettes based on the theme we are using in Excel. 

• From the Chart Design tab, click Change Colors. 
• Choose a color palette. 

Add a Legend

• Click the + (plus) next to the chart. 
• Click the arrow next to Legend and choose Top. 

Data Labels

Data Labels display the value on the actual chart. It can be helpful to display data labels in the column so we don't have to refer to the axis. We can choose the position of data labels. 

• Click the + (plus) next to the chart. 
• Click the arrow next to Data Labels and choose Inside End. 
• Click on any of the data labels to select them all. 
• From the Home tab, in the Font group, change the Font Color to white. 

Change the Gap Width

Changing the gap width will increase or decrease the width of the column in the chart. If we add data labels, it might be that we need to increase the width of the columns to accommodate the label. 

• Click on the columns. 
• Press CTRL+1 to open the Format Series pane. 
• Drag the Gap Width slider down. 

Delete the Vertical Axis

If we use data labels in a chart, often we do not need the vertical axis as the values are displayed on the chart instead. 

• Click on the vertical axis. 
• Press the Delete key. 

Create a Bar Chart

A bar chart is the same as a column chart except it runs horizontally using bars instead of vertically using columns. 

In this example, we are going to duplicate the chart we just created an modify it. 

• Select the chart and press CTRL+D to duplicate. 
• From the Chart Design tab, in the Type group, click Change Chart Type. 
• From the All Charts tab, click on Bar. 
• Choose Clustered Bar from the gallery. 
• Click OK. 

Create a Stacked Column Chart

The final chart type you might come across is the Stacked Column Chart. This is a column chart but the series are stacked on top of each other as opposed to being side by side in columns. 

• Select the bar chart and press CTRL+D to duplicate. 
• From the Chart Design tab, in the Type group, click Change Chart Type. 
• From the All Charts tab, click on Column. 
• Choose Stacked Column from the gallery. 
• Click OK. 

NOTE: There is also a 100% Stacked Column Chart that shows series as a percentage of 100. 

Hints & tips

• Drag the resize handles to change the width and height of the chart. 
• When we duplicate a chart, all formatting is carried across to the new chart.