Categories
Excel

How to Create a Chart in Excel (Step by Step Guide)

Microsoft Excel is everyone’s go to tool for tracking information and performing calculations but too much information in a spreadsheet can be overwhelming.

Charts can greatly simplify the presentation of your data.

Charts represent your data visually, making it easier to analyze numbers and identify trends. Microsoft Excel includes all of the tools you’ll need to create a chart – and with all of the chart types included in the application, chances are you’ll find one that works well for you.

This guide provides an overview of creating charts in Excel, while focusing on the following chart types:

I provide step-by-step instructions for creating charts in Excel. I also present options for customizing charts and I cover a bunch of time-saving shortcuts along the way. While some of my recommendations apply to all chart types, others are specific to the type of chart being discussed.

I hope you’ll find this guide useful. Let’s get started!

How to Create a Column Chart in Excel

Let’s look at the process of creating a column chart in Microsoft Excel.

Creating a column chart involves 3 steps.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A2:E6. Selecting this range will let me compare sales from different product categories across all four fiscal quarters.

Simple Excel worksheet summarizing food and beverage sales by fiscal quarter

Step #2 – Click the Quick Analysis button and select the Charts tab. The Charts tab displays recommended charts, based on data in the range I selected.

The Quick Analysis button

Step #3 – Point to each recommended chart for a preview then click the one you want. In the screenshot, I clicked on the first Clustered Column icon in the Quick Analysis tool.

Clustered Column chart
Clustered Column Chart

Column charts display your data using vertical “columns”. A clustered column chart displays associated data in groupings to help users understand that certain categories belong together. In the screenshot (above), you can see that the data for Q1 is grouped together, followed by Q2, Q3, etc.

Other types of column charts include stacked column, 100% stacked column, 3-D clustered column, 3-D stacked column, and 3-D column. I recommend taking a look at each column chart type to find one that represents your data clearly.

Customizing a Column Chart

After you create a column chart, there are many options you can customize. Customizing your chart will make it easier to analyze your data and present the information in a logical format.

Here are the most common options people want to change.

Moving and Resizing

If you need to move the chart, drag it to a new location. Just point to the white chart background, press and hold your left mouse button, and drag the chart to reposition it. In the screenshot, you can see the Chart Area tooltip when I point to the chart’s background. If you click any other element – the chart title, the legend, or one of the data points – you’ll end up selecting and potentially moving that element in the chart. Make sure you select the Chart Area when you want to move the chart.

The mouse pointer positioned above the Chart Area in a clustered column chart

To resize the chart, click anywhere to select it then position your pointer over one of the chart’s resize handles. Your mouse pointer will be displayed as a double-headed arrow. Press and hold your left mouse button as you drag to make the chart bigger or smaller.

This screenshot shows my pointer positioned above the resize handle in the chart’s lower right corner.

Double-headed arrow positioned above a resize handle on the chart

Title

Another option people frequently want to customize is the chart title. Simply click the existing chart title (or the Chart Title placeholder), type a new title, then press Enter.

If the Chart Title placeholder isn’t displayed, select the Chart Elements button and click the checkbox beside Chart Title to enable it.

Chart Title checkbox selected in the Chart Elements menu

Legend

The legend identifies each data series in your chart. You can enable or disable the legend and you can decide where it should be displayed.

Select the Chart Elements button, point to the Legend, then click on the black arrow to reveal locations where it can be displayed.

Legend checkbox selected in the Chart Elements menu

Styles

Styles provide a convenient way to change the appearance of your chart. Click the Chart Styles button, verify that the Style tab is active, then click a style to make a selection.

In the screenshot, I selected Style 9. It has a pleasant gradient effect applied to each data series chart element.

Chart Styles

Filter

You can apply a filter to your chart to exclude data. For example, I could exclude revenue from the baked goods and deli categories to focus on hot and cold drinks.

In the screenshot, I clicked the Filter button, cleared the check boxes for Baked Goods and Deli, then clicked Apply to update the chart.

The Filter control

More: The Complete Guide to Basic Formatting in Excel

How to Create a Bar Chart in Excel

The process for creating a bar chart is very similar to the process of creating a column chart. Here are the steps.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A3:B7. This range will let me compare the sales for different product categories.

Simple Excel worksheet showing a summary of sales by product

Step #2 – Click the Quick Analysis button and select the Charts tab. The Charts tab displays recommended charts, based on data in the selected cells.

The Quick Analysis button

Step #3 – Point to each recommended chart for a preview then click the one you want. In this screenshot, I clicked on the Clustered Bar icon in the Quick Analysis tool.

Clustered Bar chart
Bar Chart

Bar charts are very similar to column charts only instead of having a vertical orientation, a bar chart displays data along a horizontal axis.

Even though I chose a clustered bar chart, the data displayed in the chart is very simple and the notion of “clustering” doesn’t apply. We’re analyzing total sales not sales distributed over four fiscal quarters.

Other bar charts we can choose from include stacked bar, 100% stacked bar, 3-D clustered bar, 3-D stacked bar, and 3-D 100% stacked bar. Experiment with the different chart types to find one that represents your data clearly.

Customizing a Bar Chart

Many of the options for customizing a bar chart are the same as they were for column charts.

Title

In this screenshot, I selected the title placeholder and typed Total Sales by Category.

Styles

Next, I clicked the Chart Styles button and selected Style 3 from the Style tab. I like the appearance of Style 3. It features a grey background and shows values for each category of data just inside the end of each bar in the chart.

Chart Styles

Why Are My Excel Bar Chart Categories Backwards?

Did you notice that the categories were listed in reverse order in the chart? In the source data, categories were listed in the following order:

  1. Baked Goods
  2. Hot Drinks
  3. Cold Drinks
  4. Deli

Yet, in the bar chart, the categories were listed the other way around!

Product categories highlighted in the worksheet and chart

I believe the reason this happens is because charts are designed to list categories, in order, radiating out from the point where the x and y axis cross. For a more in-depth explanation, check out this article by Jon Peltier from November 2009.

If you want to display your categories in the same order as your source data, here is the process.

Step #1 – Select your chart then click the Chart Elements button.

Step #2 – Point to the Axes element and click on the black triangle then select More Options…

The Chart Elements button is selected

Step #3 – Verify that the Vertical (Category) Axis is selected in your chart then click to select the following options: At maximum category and Categories in reverse order. See the screenshot (below) for details.

The Format Axis pane for the selected chart is displayed

How to Create a Line Chart in Excel

Creating a line chart involves 3 steps.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A2:E6. This range will let me compare sales from different product categories across the four fiscal quarters.

Simple Excel worksheet summarizing food and beverage sales by fiscal quarter

Step #2 – Click the Quick Analysis button and select the Charts tab. The Charts tab displays recommended charts, based on data in the selected cells.

The Quick Analysis button

Step #3 – Point to each recommended chart for a preview then click the one you want. In the screenshot, I clicked on the Line icon in the Quick Analysis tool.

Line Chart
Line Chart

Line charts represent your data in a way that’s easy to understand and allows you to analyze and compare several categories over time.

Additional variations available include stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line.

Customizing a Line Chart

Many options for customizing a line chart are the same as they were when we customized column charts.

Title

In this screenshot, I selected the title placeholder and typed Sales by Category.

Styles

I also clicked the Chart Styles button and selected Style 12 from the Style tab. Style 12 adds markers to each data series providing an additional formatting detail to emphasize the value represented for each fiscal quarter.

Chart Styles

Data Labels

Lastly, I clicked the Chart Elements button and selected the checkbox beside Data Labels. This resulted in values from the source data being displayed in the chart.

Data Labels checkbox selected in the Chart Elements menu

How to Create a Pie Chart in Excel

Creating a pie chart involves 3 steps. Just remember that a pie chart is only capable of displaying one data series.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A3:B7. This range will let me compare the sales for different product categories.

Simple Excel worksheet showing a summary of sales by product

Step #2 – Click the Quick Analysis button and select the Charts tab. The Charts tab displays recommended charts, based on data in the selected cells.

The Quick Analysis button

Step #3 – Point to each recommended chart for a preview then click the one you want. In the screenshot, I clicked on the Pie icon in the Quick Analysis tool.

Pie Chart
Pie Chart

Pie charts always represent portions of the “whole”. The attempt to answer questions like what percentage of total sales do Baked Goods represent?

Additional variations on the default pie chart include 3-D pie, pie of pie, bar of pie, and doughnut. Try out different types of pie chart to find one that best represents your data.

Customizing a Pie Chart

Because they’re only able to display one data series, Pie charts are simpler than the other charts we’ve looked at. There are fewer options available in the Chart Elements menu.

Data Labels

In this example, I’m going to display category labels directly on the pie chart and turn the legend off.

To enable Data Labels, click the Chart Elements button and select the Data Labels checkbox.

Then, to enable specific data labels, select the black arrow beside Data Labels and click More Options… from the fly-out menu.

More Options... selected in the Chart Elements menu

In the Format Data Labels pane, select the Category Name checkbox then select (New Line) from the Separator drop-down menu.

The Category Name checkbox selected in the Format Data Labels pane

Legend

With data labels displayed directly on the pie chart, a legend is no longer necessary. To remove the legend, select the Chart Elements button then click to clear the Legend checkbox.

The Chart Elements button is selected

How to Create a Combo Chart in Excel

Here are the steps for creating a combo chart.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A3:C7. This range will let me compare sales from different product categories and show the number of units sold.

Simple Excel worksheet showing a summary of sales and units by product category

Step #2 – Click the Quick Analysis button and select the Charts tab. The Charts tab displays recommended charts but there is no entry for a combo chart.

The Quick Analysis button

Step #3 – Point to each recommended chart for a preview then click the one you want. In the screenshot, I clicked on the More Charts icon in the Quick Analysis tool.
The Insert Chart dialog box appears.

Step #4 – In the Insert Chart dialog box, select the All Charts tab, click Combo from the list of charts, then click OK.

Combo chart selected in the Insert Chart dialog box

The combo chart is displayed with default options selected.

Combo chart
Combo Chart

Combo charts provide an effective method of visualizing data by combining elements from two different chart types. Most commonly, Excel users will incorporate elements from a column chart and line chart.

The default chart in this category is clustered column – line but you can also select clustered column – line on secondary axis, stacked area – clustered column, and custom combination.

Customizing a Combo Chart

After creating a combo chart, there are a few items you’ll want to customize.

Title

Like any other chart, a combo chart should have a descriptive title. Simply click the Chart Title placeholder, type a new title, then press Enter.

In this example, I added the title Sales / Units by Category because both data series are represented in the chart.

Legend

The legend identifies each data series in your chart. You can enable or disable the legend and you can decide where it should be displayed.

To move the legend, select the Chart Elements button, point to Legend then click on the black arrow to reveal locations where it can be displayed. In this example, I selected Top. The legend is displayed above the chart.

The Legend checkbox is selected in the Chart Elements menu

Data Table

A data table lets you display values from the source data on the chart.

To add a data table, select the Chart Elements button and click to select the Data Table checkbox.

The Data Table checkbox is selected in the Chart Elements menu

Are You Ready to Create a Chart in Excel?

I hope you’ve found my article on creating charts in Excel useful. The chart tools that come with Excel can help you simplify your data and present in a more visual way.

See you next time!

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.