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.
Table of Contents
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’.
This is the first version of your Column Chart.
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.
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’.
Chart plot area (the area with blue bars) will automatically extend to filling the position vacated by the legend.
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.
By clicking on the axis of the right mouse button and selecting ‘Format Axis’ we get access to more options.
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.
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.
Let us set the parameter ‘custom angle:’ to zero. The easiest way to click once located at the up arrow and down arrow once.
Enhancing the Chart
Changing the title is 2 single clicks on it and entering the correct title.
To add data labels right click on any column and choose ‘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’.
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.
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).
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.
To add a trendline click on the column again right-click and choose ‘Add Trendline’.
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.
Before you click ‘Close’ even change the width and type of trend line. These options can be found on the ‘Line Style’.
As a result of the above changes to get the chart, which should look like below.
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).
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.
Click any column margin of the right mouse button and choose the command ‘Format Data Series’.
In the ‘Format Data Series’, on the ‘Options series of’ change ‘main axis’ to ‘axis of auxiliary’ and click ‘Close’ button.
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’.
Change the type to ‘Linear’ and the first of its subtypes ‘Linear’.
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.
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’.
After the string ‘Chart Title’ appears on the chart. Click on it and enter the title.
By right-clicking on the title can change the font size.
Legend looks better on the bottom. Right-click on the legend, choose Format legend and click Bottom.
The chart can be considered finished.
Please find attached Excel file for reference.
