Categories
Microsoft Excel

Ultimate Guide to Excel Charts [2021]

Microsoft Excel is everyone’s go to tool for tracking information and performing calculations but too much information in a spreadsheet can be overwhelming. Often, charts are exactly what you need to 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 and customize charts – 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 and customizing the following chart types in Excel:

  • Column charts
  • Bar charts
  • Line charts
  • Pie charts
  • Combo charts

I provide step-by-step instructions for creating each type of chart. 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!

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

Column Charts

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

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

The 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

Chart 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

Bar Charts

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.

Chart Title

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

Chart 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

Line Charts

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.

Chart Title

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

Chart 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

Pie Charts

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

Combo Charts

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.

Chart 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.

The 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

Advanced Topics

Now that we have explored how to create the most popular charts in Excel, let’s turn our attention to some of the advanced tools and techniques you can use to make working with charts easier.

These topics apply to every chart type and will help you build and customize charts in less time.

The Format Task Pane

Up to this point, we have customized charts using the Chart Elements, Chart Styles, and Filters buttons. These controls provide a convenient way to update the appearance of your charts but they’re just the tip of the iceberg.

You have access to a vast number of additional options through a series of task panes. Using the Format Task pane, you can manipulate any chart element and each element has dozens of attributes you can change.

In this section, I’ll provide a few examples to help you understand how these advanced options work. We’ll use a column chart to explore the multitude of options available in the Format Task panes.

Chart Area

The easiest way to display the Format Task pane is to double-click a chart. I’m going to change the appearance of the Chart Area. The Chart Area is the chart’s background. When selecting the Chart Area, try to select it by double-clicking away from other elements like the Chart Title, Plot Area, etc.

Step #1 – Double-click the Chart Area.

The Chart Area is identified by a red circle

Step #2 – Select various options in the Format Chart Area pane. In the screenshot below, I selected the Fill & Line button in the Format Chart Area pane then clicked Gradient fill.

Gradient fill is selected in the Format Chart Area pane

A gradient fill blends different shades or colours to create a visually interesting effect. We could have applied a solid fill, a pattern, or picture fill. Lots of options to play with!

Column chart with a gradient fill applied to the chart area

Data Series

Next, I’m going to add a shadow effect to each data series in the chart. Since the Format Chart Area task pane is already open, you can single click any data series to format it.

A data series describes all of the numbers in a chart that represent a particular category of data. If we click on any one of the blue Baked Goods columns, all of the blue data points get selected. If I click a blue Baked Goods column again, that specific data point will get selected – and we can format that data point independently of the others. So be careful that you don’t click a column twice if you want to select an entire data series.

Let’s add a shadow effect!

Step #1 – Click to select the blue Baked Goods data series. Notice that each column representing the Baked Goods data series is selected.

Column chart with the blue data series selected

Step #2 – Select various options in the Format Data Series pane. In the screenshot below, I selected the Effects button in the Format Data Series pane then clicked Offset: Bottom Right from the Presets menu.

The Format Data Series pane

Look at all of the other options available on the Effects tab in the Format Data Series pane. You can adjust the colour of the shadow, the angle, the distance, the angle… there are a very comprehensive range of settings to explore!

The finished version of the chart shows what it looks like after I applied the shadow effect to all four data series.

Column chart with shadow effect applied to all data series

Recommendations

When it comes to the myriad of options available through the Format Task panes, I have some advice. Do everything you can using options available through the Chart Elements, Chart Styles, and Filter buttons – then use Format Task panes to fine tune the appearance of your charts using settings that aren’t available elsewhere.

How to Copy a Chart

You can copy an Excel chart and paste it into another location. The duplicate chart can be added to the same worksheet, another worksheet tab, a different Excel workbook, or another application entirely. In this example, let’s copy a chart from Excel to a Microsoft Word document.

The process only involves a couple of steps but there are several options you need to understand.

Step #1 – Select the chart and click Copy in the Home > Clipboard group.

The Copy button

Step #2 – Select the location where you’d like the copied chart to go and click the down-arrow under Paste in the Home > Clipboard group.
A number of icons appear.

Down-arrow selected for Paste button

Step #3 – Select an icon in the Paste Options: section under the Paste button.

Here is what the various icons mean.

Use Destination Theme & Embed Workbook
The chart uses colours from the Word document’s current theme and the chart is embedded. Updates to the chart or source data in Excel won’t be reflected in the chart pasted into Word.

Use Destination Theme & Embed Workbook

Keep Source Formatting & Embed Workbook
The chart retains the colours from the Excel workbook and the chart is embedded. Updates to the chart or source data in Excel won’t be reflected in the chart pasted into Word.

Keep Source Formatting & Embed Workbook

Use Destination Theme & Link Data
The chart uses colours from the Word document’s current theme and the chart is linked to the source in Excel. Updates to the chart or source data in Excel will be reflected in the chart pasted into Word.

Use Destination Theme & Link Data

Keep Source Formatting & Link Data
The chart retains the colours from the Excel workbook and the chart is linked to the source in Excel. Updates to the chart or source data in Excel will be reflected in the chart pasted into Word.

Keep Source Formatting & Link Data

Picture
The chart is pasted into Word as a picture. Updates to the chart or source data in Excel won’t be reflected in the chart pasted into Word.

Picture

Change Chart Type

You can change a chart from one type to another. For example, if you had initially created a pie chart but later decided to change it to a column chart, that would be easy to do.

Here are the steps.

Step #1 – Right-click the Chart Area and select Change Chart Type from the shortcut menu.

Change Chart Type

Step #2 – Select the All Charts tab in the Change Chart Type dialog box then select Column and click OK.

Column chart selected in the Change Chart Type dialog box

The pie chart is converted to a column chart.

Column Chart

Save a Chart as a Template

Once you’ve customized a chart and you’re happy with its appearance, you can save the chart as a template. Templates can be used to create new charts or template settings can be applied to existing charts.

To save a chart as a template, right-click the Chart Area, select Save As Template from the shortcut menu, and enter a filename when you’re prompted to do so. In this example, I created a template called XL Basics Custom.

Save as Template

Create a Chart Based on a Template

Creating a custom chart based on a template will incorporate all of the formatting options applied to the original chart used to create the template.

Here is the process.

Step #1 – Select the data you’d like to display in your chart. In this example, I’ve highlighted cells A2:E6. We’ll display data from this range using the template we created in the previous section.

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.

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 Templates from the list of charts, and make sure XL Basics Custom is highlighted, then click OK.

The Insert Chart dialog box

A new chart is created based on settings from the XL Basics Custom template. Notice that the title will need to be manually updated. Templates only capture formatting attributes.

A customized clustered column chart with gradient background and shadow effect

By Michael Belfry

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