How to Create and Manage a Chart in Excel

Add to Favorites
Author:

In this tutorial I am going to introduce you to creating and managing charts in Excel. Before you create a chart you need to get your chart data organized correctly. In the below example I have a set of sales stats for a few different states:

Notice how I have my data labels in a column on the left and the data I wish to display in a column next to it. I also have a column heading so that my data set has a name.

Now that I have my data organized correctly, I can now insert a chart. To do this, select the data (A1:B11) and then navigate to the Chart section of the Insert tab:

You can select a chart from one of the dropdowns or you can view all available charts by clicking on the bottom right of the chart section (Highlighted by the red box above). Alternatively you can select All Chart Types from one of the dropdowns:

The following pop-up window appears:

Just select a chart and click ok. For this example I have inserted a column chart:

If I am unhappy with the chart type I have initially selected, I can easily change it by selecting the chart then clicking the Change Chart Type button in the Type section of the Design tab. This opens up the full chart selection window.

(Note: The Chart Tools tabs, which includes the Design tab, will only appear when the chart is selected - you can see them below on the right side of the ribbon.)

Select a new chart type in order to change the chart. For this example I have changed my Column chart to a Bar chart:

Once you are happy with the chart type, you can move it where you want by simply clicking and dragging the chart to your preferred location within your currently selected worksheet (To move the chart to a new worksheet, click the Move Chart button on the Design tab).

You can also resize the chart the same way you resize basic shapes. Select the chart you wish to resize then click on 1 of the corners or the middle of 1 of the charts sides. My bar chart isnt long enough to fit the axis labels so I am going to increase its length:

 

Should you want to remove your new chart, just select it and hit the delete key.

Changing a Charts Appearance in Excel Design Tab

Now that I have covered the basics of inserting of a chart, I will look at how to further customize how a chart looks.

When you select a chart, 3 extra tabs are displayed in the ribbon and I will look at each 1 in turn. First the Design tab:

The Chart Layouts section allows you to change the layout of the chart elements. There are various options here for axis label positioning, chart titles and data labels etc. Each layout button has an image to give you a rough guide of what the new layout will look like.

The Chart Styles section then allows you to adjust the shading and colors of your chart. This includes both the bars/columns etc. and the chart background. So for example, I have changed the layout of my chart to have no title and include data labels as well as changing the chart coloring from blue to red:

There is also a Data section of the Design tab which allows you to alter what data is selected by your chart. Simply click the Select Data button to open up the following window:

This window shows where your data is coming from and how it is labelled. As this is only a simple chart, the default data selection is good enough here for what I want but I will cover this window in detail in a later tutorial.

Changing a Charts Appearance in Excel Layout Tab

The next tab is the Layout tab:

This is a further expansion of the Layout section in the Design tab. It allows more detailed customization of the chart layout.

Just click on one of the buttons to get a dropdown of options.  Each dropdown alters how a specific part of a chart is displayed, where it is positioned and how it is orientated (depending on what part of a chart you are editing).

There is a large focus on the chart and axis labelling as well as options for adding analysis for your chart with Trendlines and Error Bars. As the data used in this example is simplistic there isnt much benefit from adding such features as Trendlines. These come in handy with large datasets that require statistical analysis.

As an example I am going to add Axis Titles to my chart:

Notice how when you are editing layout options to do with the Axis you choose which Axis you wish to adjust. There are also options to adjust how the each of the Axis is labelled, particularly if the data is numerical you can adjust whether the axis labels are in thousands:

You can also add Gridlines to your chart to make it easier to interpret. You can even get further customization layout and design options by clicking on the More Options button at the bottom of the dropdown. When you do this, a pop-up window will appear like so:

With the pop-up window I have added Gridlines and change their color to red:

The Properties section allows you to give a name to your chart:

By default, the chart name will be Chart followed by a number. Chart names like this can get very confusing the more charts you have so it is better practice to name them.

If you open up the Selection Pane (Home tab -> Find & Select -> Selection Pane) you can see all the charts in your currently selected worksheet. As I have renamed the main chart I am using in the tutorial, the selection pane allows me to easily select it as I can clearly see which chart it is.

If all the charts were just Chart 1 etc. it would get very confusing. Named charts are also useful when referencing them in macros / VBA code but this is a more complicated topic to be discussed in a future tutorial.

Changing a Charts Appearance in Excel Format Tab

The last tab to look at is the Format tab:

This is an expansion of the Styles section of the Design tab. The Shape Styles section provides further detail for coloring, shading and outlining various parts of a chart. Just select a part of the chart you wish to format and then choose from the various options.

For further customization options you can click the button in the bottom right corner of the Shapes Styles section to open up a pop-up window with expanded options:

You can also get to these pop-up windows via the Current Selection section of the Layout / Format tabs:

Just select the chart section from the dropdown and click Format Selection.

Alternatively you can right click the part of the graph you wish to format and select Format Chart Area

There are also sections for WordArt and Arrangement options to make resizing and moving your chart easier. As an example I have changed the colors of the bars and the background of my chart:

I have also made it Snap to Grid so that I can align my chart to any cell - this feature resets every time you close and open the workbook though so add it back by selecting the chart, going to the Format tab, clicking the Align button and selecting Snap to Grid. (Try moving the graph around in the accompanying Excel workbook to see how this works) 

Note: Play around with the design, layout, type and format of the charts in the accompanying Excel file to get a better understanding of the formatting features of charts.

There is also another chart type in the sample workbook for this tutorial and you can play around with that to see how you can represent data in different ways in Excel.

This tutorial should get you a long way in understanding how to add, edit, and manage charts in Excel.  Make sure to read the other chart tutorials to get an even better understanding!


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Change the Name of a Chart in Excel
Tutorial: How to change the name of a chart in Excel. This allows you to use a more intuitive char...
Insert and Manage Page Breaks in Excel
Tutorial: How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and...
Add Image Background to a Chart in Excel
Tutorial: How to use an image for the background of a chart in Excel. Simple but fun way to spruce...
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
Guide to Creating Charts with a Macro in Excel
Tutorial: How to add, edit, and position charts in Excel using VBA. This tutorial covers what to do ...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course