How to Make a Column Chart in Excel

Column charts are a popular and effective way to visualize data in Excel. In this Excel tutorial, you learn how to insert and format a Column Chart.

Basic Column Chart

Selecting Data

The first step should be to select a table of data with row and column headers. Then navigate to the ‘Insert’ tab, select ‘Column’, and choose the first subtype, ‘Clustered Column’.

Excel chart column clustered insert

This is the first version of your Column Chart.

Excel chart column clustered chart drag

Customizing the Chart

Resize the chart by dragging it (corners and sides of the resources are marked by dots). Standard Excel adds grid lines (horizontal black lines in the figure above). You can remove them by clicking on the gridline (refer to the picture below) and then pressing the ‘Delete’ key on your keyboard.

Excel chart column clustered lines delete

In our case, the legend (blue box and the inscription sale) is completely unnecessary because the chart is only one category of data is described in the title. To delete it, click on the left button once, it will select it and hit ‘delete’.

Excel chart column clustered chart legend delete

Chart plot area (the area with blue bars) will automatically extend to filling the position vacated by the legend.

Excel chart column clustered legend delete

Formatting Axes

After selecting the axis (single left mouse click), we can change the font of labels, size and format using the icons on the ‘Home’. The same applies to the other chart elements – the legend, title, labels, etc.

See also  Chart with confidence intervals

Excel chart column clustered axis formatting

By clicking on the axis of the right mouse button and selecting ‘Format Axis’ we get access to more options.

Excel chart column clustered format axis

In the ‘Format Axis’ pane, under ‘Axis Options’, you can change the minimum value from which the Y-axis begins. To do this, first click on ‘Fixed’, and then input the desired value. We can also change the main unit if we consider that the labels on the axis is too much.

After checking how these options propose to return to automatic settings by clicking the ‘Auto’. On the other tabs of the ‘Format Axis’ find many text formatting options, some of them will be discussed in subsequent examples, the function of other easy to guess from their name.

Excel chart column clustered format axis dialog box

Change the format of the horizontal axis, the axis X. On the ‘Alignment’ dialog ‘Format Axis’, we can determine the position of the text, we determine them to the horizontal.

Excel chart clustered horizontal text alignment

Let us set the parameter ‘custom angle:’ to zero. The easiest way to click once located at the up arrow and down arrow once.

Excel chart column clustered custom angle alignment

Enhancing the Chart

Changing the title is 2 single clicks on it and entering the correct title.

Excel chart column clustered title

To add data labels right click on any column and choose ‘Add data labels’.

Excel chart add data labels

When you click the left mouse button on any of the labels, we can format all the labels using the commands available on the ‘Home’.

Excel chart column data labels formatting

Two single-click in the selected labels will allow us to format only the one selected label. It is used to draw attention to the person who will be watching our chart on our elected post. I propose to enlarge the font and add a bold effect.

See also  How to use Sparklines in Excel

Excel chart single data label formatting

Exactly in the same way we can change the color of all bars (one click, and select a new color) or only one of the selected bar (two single-click and select a new color).

Excel chart single column formatting

I propose to change the color bar with the data for October on the blue lighter. When choosing colors, avoid saturated colors and contrast connections.

Excel chart column single column change colour

To add a trendline click on the column again right-click and choose ‘Add Trendline’.

Excel chart add trend line

Window appears ‘trend line Formatting’ tab ‘Options trend line’ to select the type of trend. For many types of data, especially those of comparable (eg sales in subsequent years) will be the best linear trend. In our example, the more we want to determine sales profile (seasonality), so we use the 6 degree polynomial trend.

Excel chart format polynominal trendline

Before you click ‘Close’ even change the width and type of trend line. These options can be found on the ‘Line Style’.

Excel chart trendline dash style

As a result of the above changes to get the chart, which should look like below.

Excel column chart

Advanced Column Chart

To get the best possible way to show data in the table below for a chart, use chart on 2 axes. We’ll start by selecting a table with headers. A common mistake is to select the amount of data, the chart contains the total will be unreadable because the other bars are too small to be able to properly assess the differences between them. The cards ‘Insert’ choose plot ‘Column’ and its first subtype (clustered column).

Excel chart insert clustered column chart

Adding Secondary Axis

Chart prepared by Excel automatically will be far from what we want to achieve. Margin percentage is shown on the same axis as the sales and posts of it coincide with the axis X. When you move the mouse pointer column margin, which requires precision and patience sometimes, the message shown in the figure below.

See also  How to graph a sine wave?

Excel chart problem with scale of data

Click any column margin of the right mouse button and choose the command ‘Format Data Series’.

Excel chart problem with scale of data formatting

In the ‘Format Data Series’, on the ‘Options series of’ change ‘main axis’ to ‘axis of auxiliary’ and click ‘Close’ button.

Excel chart secondary axis

The effect of this change is not impressive, there was a second Y axis on the right side of the chart, but the stakes stakes Sales margin cover.

Changing Chart Type

Click any of the bars margin, right-click and go to ‘Change Series Chart Type’.

Excel change chart type

Change the type to ‘Linear’ and the first of its subtypes ‘Linear’.

Excel chage chart type line

Finally, the graph that we obtained is close to that as it is to look like. Let’s delete now the major grid lines. After these changes, the chart will look like the picture below.

Excel chart without grindlines

Adjusting Chart Title

Left only to add a chart title. After selecting the chart on the ‘Layout’ click on the ‘Chart Title’ and select ‘Centered overlay title’.

Excel chart overlay title

After the string ‘Chart Title’ appears on the chart. Click on it and enter the title.

Excel insert chart title

By right-clicking on the title can change the font size.

Excel chart title font size

Legend looks better on the bottom. Right-click on the legend, choose Format legend and click Bottom.

Excel format chart legend

The chart can be considered finished.

Excel column clustered chart

Please find attached Excel file for reference.