How to Create a Column Chart in Excel

01
of 06

How to Create a Column Chart in Excel

Excel 2013 Column Chart
Excel 2013 Column Chart. © Ted French

The steps to creating a basic column chart in Excel are:

  1. Highlight the data to be included in the chart - include row and column headings but not the title for the data table;
  2. Click on the Insert tab of the ribbon;
  3. In the Charts box of the ribbon, click on the Insert Column Chart icon to open the drop-down list of available chart types;
  4. Hover your mouse pointer over a chart type to read a description of the chart;
  5. Click on the desired graph;

A plain, unformatted chart - one that displays only the columns representing the selected series of data, a default chart title, a legend, and axes values - will be added to the current worksheet.

Version Differences in Excel

The steps in this tutorial use the formatting and layout options available in Excel 2013. These differ from those found in early versions of the program. Use the following links for column chart tutorials for other versions of Excel.

A Note on Excel's Theme Colors

Excel, like all of Microsoft Office programs, uses themes to set the look of its documents.

The theme used for this tutorial is the default Office theme.

If you use another theme while following this tutorial, the colors listed in the tutorial steps may not be available in the theme you are using. If not, just choose colors to your liking as substitutes and carry on.

02
of 06

Entering the Chart Data and Creating a Basic Column Chart

Entering Data for the Excel 2013 Column Chart
Entering the Tutorial Data. © Ted French

Note: If you do not have data at hand to use with this tutorial, the steps in this tutorial make use of the data shown in the image above.

Entering the chart data is always the first step in creating a chart - no matter what type of chart is being created.

The second step is highlighting the data to be used in creating the chart.

  1. Enter the data shown in the image above into the correct worksheet cells
  2. Once entered, highlight the range of cells from A2 to D5 - this is the range of data that will be represented by the column chart

Creating the Basic Column Chart

The steps below will create a basic column chart - a plain, unformatted chart - that displays the three series of data, a legend, and a default chart title.

After that, as mentioned, the tutorial covers how to use some of the more common formatting features, which, if followed, will alter the basic graph to match the one shown at the top of this tutorial.

  1. Click on the Insert tab of the ribbon
  2. In the Charts box of the ribbon, click on the Insert Column Chart icon to open the drop-down list of available chart types
  3. Hover your mouse pointer over a chart type to read a description of the chart
  4. In the 2-D Column section of the list, click on Clustered Column - to add this basic chart to the worksheet
03
of 06

Adding the Chart Title

Adding the Title to the Excel 2013 Column Chart
Adding the Title to the Column Chart. © Ted French

Edit the default Chart Title by clicking on it twice - but don't double click

  1. Click once on the default chart title to select it - a box should appear around the words Chart Title
  2. Click a second time to put Excel in edit mode, which places the cursor inside the title box
  3. Delete the default text using the Delete/Backspace keys on the keyboard
  4. Enter the chart title - The Cookie Shop 2013 Income Summary - into the title box
  5. Place the cursor between Shop and 2013 in the title and press the Enter key on the keyboard to separate the title onto two lines

At this point, your chart should resemble the example shown in the image above.

Clicking on the Wrong Part of the Chart

There are many different parts to a chart in Excel - such as the plot area that contains the column chart representing the selected data series, the legend, and the chart title.

All of these parts are considered separate objects by the program, and, as such, each can be formatted separately. You tell Excel which part of the chart you want to format by clicking on it with the mouse pointer.

In the following steps, if your results do not resemble those listed in the tutorial, it is quite likely that you did not have the right part of the chart selected when you added the formatting option.

The most commonly made mistake is clicking on the plot area in the center of the cart when the intention is to select the entire chart.

The easiest way to select the entire chart is to click in the top left or right corner away from the chart title.

If a mistake is made, it can be quickly corrected using Excel's undo feature to undo the mistake. Following that, click on the right part of the chart and try again.

04
of 06

Changing the Chart Style and Column Colors

The Chart Tool Tabs on the Ribbon in Excel
The Chart Tool Tabs. © Ted French

The Chart Tools Tabs

When a chart is created in Excel, or whenever an existing chart is selected by clicking on it, two additional tabs are added to the ribbon as shown in the image above.​

These Chart Tools tabs - design and format - contain formatting and layout options specifically for charts, and they will be used in the following steps to format the column chart.

Changing the Chart Style

Chart styles are preset combinations of formatting options that can be used to quickly format a chart using a variety of options.

Or, as is the case in this tutorial, they can also be used as a starting point for formatting with additional changes being made to the chosen style.

  1. Click on the chart background to select the entire chart
  2. Click on the Design tab of the ribbon
  3. Click on the Style 3 option in the Chart Styles section of the ribbon
  4. All the columns in the chart should have short, white, horizontal lines running through them and the legend should move to the top of the chart beneath the title

Changing the Column Colors

  1. Click on the chart background to select the entire chart if necessary
  2. Click on the Change Colors option located on the left-hand side of the Design tab of the ribbon to open a drop down list of color choices
  3. Hover your mouse pointer over each row of colors to see the option name
  4. Click on the Color 3 option in the list - the third choice in the Colorful section of the list
  5. The column colors for each series should change to orange, yellow, and green, but the white lines should still be present in each column

Changing the Chart's Background Color

This step changes the background of the chart to light gray using the Shape Fill option located on the Format tab of the ribbon identified in the image above.

  1. Click on the background to select the entire chart and to display the Chart Tool tabs on the ribbon
  2. Click on the Format tab
  3. Click on the Shape Fill option to open the Fill Colors drop down panel
  4. Choose Gray -50%, Accent 3, Lighter 40% from the Theme Colors section of the panel to change the chart's background color to light gray
05
of 06

Changing the Chart Text

Changing Colors in an Excel 2013 Column Chart
Changing the Column Chart Colors. © Ted French

Changing the Text Color

Now that the background is gray, the default black text isn't very visible. This next section changes the color of all text in the chart to green to improve the contrast between the two using the Text Fill option.

This option is located on the Format tab of the ribbon identified in the image on the previous page of the tutorial.

  1. Click on the chart background to select the entire chart, if necessary
  2. Click on the Format tab of the ribbon
  3. Click on the Text Fill option to open the Text Colors drop down list
  4. Choose Green, Accent 6, Darker 25% from the Theme Colors section of the list
  5. All the text in the title, axes, and legend should change to green

Changing the Font Type, Size, and Emphasis

Changing the size and type of the font used for all the text in the chart, will not only be an improvement over the default font used, but it will also make it easier to read the legend and axes names and values in the chart. Bold formatting will also be added to the text to make it stand out even more against the background.

These changes will be made using options located in the font section of the Home tab of the ribbon.

Note: The size of a font is measured in points - often shortened to pt.
72 pt. text is equal to one inch - 2.5 cm - in size.

Changing the Chart Title Text

  1. Click once on the chart's title to select it
  2. Click on the Home tab of the ribbon
  3. In the font section of the ribbon, click on the Font box to open the drop down list of available fonts
  4. Scroll to find and click on the font Leelawadee in the list to change the title to this font
  5. In the Font Size box next to the font box, set the title font size to 16 pt.
  6. Click on the Bold icon (the letter B) below the Font box to add bold formatting to the title

Changing the Legend and Axes Text

  1. Click once on the X axis (horizontal) labels in the chart to select the cookie names
  2. Using the steps listed above for changing the title text, set these axis labels to 10 pt Leelawadee, bold
  3. Click once on the Y axis (vertical) labels in the chart to select the currency amounts on the left-hand side of the chart
  4. Using the steps above, set these axis labels to 10 pt Leelawadee, bold
  5. Click once on the chart's legend to select it
  6. Using the steps above, set the legend text to 10 pt Leelawadee, bold

All text in the chart should now be the Leelawadee font and dark green in color. At this point, your chart should resemble in the chart in the image above.

06
of 06

Adding Gridlines and Changing Their Color

Adding and Formatting the X Axis Line in the Excel 2013 Column Chart
Adding and Formatting the X Axis Line. © Ted French

Even though the horizontal gridlines were initially present with the default column chart, they were not part of the new design that was chosen in step 3, and, therefore, removed.

This step will add the gridlines back into the plot area of the chart.

In the absence of data labels that show the actual value of each column, gridlines make it easier to read the column values from the currency amounts listed on the Y (vertical) axis.

The gridlines are added using the Add Chart Element option on the Design tab of the ribbon.

  1. Click once on one of the plot area of the chart to select it
  2. Click on the Design tab of the ribbon if necessary
  3. Click on the Add Chart Element option on the left side of the ribbon to open the drop down menu
  4. In the drop-down menu, click on Gridlines > Primary Horizontal Major to add faint, white, gridlines to the plot area of the chart

Making Formatting Changes Using the Formatting Task Pane

The next steps of the tutorial make use of the formatting task pane, which contains most of the formatting options available for charts.

In Excel 2013, when activated, the pane appears on the right-hand side of the Excel screen as shown in the image above. The heading and options appearing in the pane change depending upon the area of the chart that is selected.

The first step will change the color of the gridlines just added above from white to orange in order to make them more visible against the gray back ground of the plot area of the chart.

Changing the Gridlines' Color

  1. In the graph, click once on the $60,000 gridline running through the middle of the graph - all the gridlines should be highlighted (blue and white dots at the end of each gridline)
  2. Click on the Format tab of the ribbon if necessary
  3. Click on the Format Selection option on the left side of the ribbon to open the Formatting Task pane - the heading at the top the pane should be Format Major Gridlines
  4. In the pane, set the line type to Solid line
  5. Set the gridline color to Orange, Accent 2, Darker 25%
  6. All of the gridlines in the plot area should change to dark orange in color

Formatting the X Axis Line

The X axis line is present above the X axis labels (cookie names), but, like the gridlines, it is difficult to see because of the gray background of the chart. This step will change the axis color and line thickness to match that of the formatted gridlines.

  1. Click on the X axis labels to highlight the X axis line
  2. In the formatting task pane, as shown in the image above, set the line type to Solid line
  3. Set the axis line color to Orange, Accent 2, Darker 25%
  4. Set the axis line width to 0.75 pt.
  5. The X axis line should now match the chart's gridlines

If you have followed all the steps in this tutorial, your column chart should now match the example displayed on at the top of this page.