Column Chart in Excel

In this Excel tutorial you will teach yourself how to insert and format Column Chart. Column chart is the most popular Excel chart. Let’s see below examples about how to insert and format column chart in baby steps.


Example 1 Basic Column Chart Basic preparation of charts will be presented on the example of the table of sale within 12 months. The first step should be to select a table of data with row and column headers. Then select the tab ‘Insert’ plot ‘Column’ and its first subtype ‘Column group’.Excel chart column clustered insertChart, which will be added to the sheet can drag to the desired location by clicking with the left mouse button and while holding this key down while dragging.

Excel chart column clustered chart drag

We can also resize the chart by dragging any corner or agent of any of the sides (corners and sides of the resources are marked by dots). Standard Excel adds grid lines (horizontal black lines in the figure above), you can delete them by clicking on it (picture below) and pressing the button on the keyboard ‘Delete’.

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

The next step will be formatting the 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.

Excel chart column clustered axis formatting

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

Excel chart column clustered format axis

In the ‘Format Axis’, which is displayed on the “Axis Options” for example, we can change the minimum – the value of which begins with the Y axis chart below is shown that to do so must first click the ‘constant’ and then enter for example . 200th We can also change the main unit, eg from 100 to 200 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 and hit ‘OK’ button.

Excel chart clustered horizontal text alignment

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

Excel chart column clustered custom angle alignment

Next steps will be: – Improving the chart title – Adding data labels to the values – Add trend line. 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.

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

Example 2 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

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.

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. Click any of the bars margin, right-click and go to ‘Change the chart type serial …’.

Excel change chart type

Change the type to ‘Linear’ and the first of its subtypes ‘Linear’, then click OK.

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

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 “Sale and Margin 2011′.

Excel insert chart title

By right clicking on the title can change the font size, I suggest to select the 14th.

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


You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell