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.
Participants who take our Microsoft Excel training are always eager to learn how to create charts.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
- Baked Goods
- Hot Drinks
- Cold Drinks
- Deli
Yet, in the bar chart, the categories were listed the other way around!
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…
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.
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.
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.
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 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.
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.
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.
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.
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 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.
In the Format Data Labels pane, select the Category Name checkbox then select (New Line) from the Separator drop-down menu.
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.
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.
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.
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.
The combo chart is displayed with default options selected.
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.
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.
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!
