Mark your data set first
To insert a chart, highlight the entire table including the headers.
Go to Insert tab and select a chart type.
If you are not yet sure what type to add, select Recommended Charts.
Excel gives you some proposed charts which you can select from.
Select one and click OK.
This will insert a new chart in its default layout using your source data.
Insert a Blank Chart
A new chart can also be inserted without having to highlight your data beforehand.
To do this, go to an empty cell.
Go to the Insert tab, and under Charts, select a type you prefer.
In this example, I will be using a column chart.
After selecting a chart type, you will notice that it is an empty canvas.
This is because there is no data selected.
Notice that in general, when a new chart is inserted more features are made available.
This is found in the Chart Tools tab.
In Excel 2013 and 2016, there are two tabs: Design and Format.
There are multiple ways to introduce the data to the empty chart:
- Select the empty chart and go to the Design tab under Chart Tools. Under Design tab, click on Select Data.
2. Another way is to right click on the empty chart and select Select Data.
Once you are in the Select Data window, you will need to specify your chart values. There are also different ways to do this:
- Under Legend Entries (Series), click on Add.
The Edit Series window will ask you to specify the different elements of the chart.
For Series name, you can either hardcode the name or you can do a cell reference to the table header, B5.
For Series values, highlight the values in the data table.
After adding the series values, you will notice that the labels (A6:A10) are not yet displayed on the chart.
To add these, click on Edit under the Horizontal (Category) Axis Labels.
In this window, you can add new series, edit the existing series or remove them.
2) Instead of adding the series details one by one, you can also just specify a Chart data range by selecting the text box and highlighting your data table.
In this case, A5:B10.
Excel figures out where to plot the given data range and generates a chart.
Use shortcut keys to insert a Chart
An even faster way to do this is to highlight your source data A5:B10 and then hit the ALT + F1 key.
This automatically inserts a new chart based on whatever type is set as default.
To change the default chart type, go to the Insert tab and click on the small icon at the bottom right of the Charts section to bring up the Change Chart Type window.
In the Change Chart Type window, find the chart you are most likely to use the most.
Right click on it and select Set as default.
Adjusting the chart
A chart is comprised of different chart elements, such as grid charts, chart area, title and so on.
You have the option of adding or removing them as needed by clicking on the + sign found at the top right of the chart.
The list of Chart Elements also contains arrows on the right which summarizes the most used options and you can select your preference.
For Data Tables, you can select the alignment or click on More options to customize it further.
Clicking on More options for the Data labels element displays the Format Data labels on the right hand side where you have full control of all of the options of that element.
Depending on the element you select, there are different options.
There are various ways to open the sidebar to format a specific chart element:
- Double click on an element on the chart
- Use the shortcut kay CTRL + 1.
- Right click on a chart element and select Format…
Once the Format sidebar is activated, this will continue to show up and show the corresponding options when you click on the other chart elements.
By default, bar charts add the given data in reverse order—where the first row in your data table is the last bar on the chart, while and last row on the data table shows up at the top of the bar chart.
In situations where this needs to be rearranged to follow the actual arrangement of the data table, do the following:
- Double click on the chart axis to display the Format Axis
- Under Axis Options, select the last tab.
- Expand the first section, Axis Options.
- Tick the box: Categories in reverse order.
The most important options of the chart elements are found in the first section of the last tab.
For the Series elements, these are the Series Overlap and the Gap Width.
Removing an element
There are two ways to remove chart elements:
- Click on the chart element you want to remove and press the DELETE key.
- Untick the corresponding box at the Chart Element list that shows up when clicking the + button.
Improving the chart’s appearance
There are various ways to format the appearance of the chart elements:
- Click on a chart element and tweak the settings in the Format side bar.
2. Click on the chart element you want to format. Go to the Format tab and customize the options under Shape Styles.
If you find that you are using these options a lot, you have the option of adding them to your quick access toolbar.
To do that, right click on the option and select Add to Quick Access Toolbar.
3. Right click on the chart element and specify the colors you prefer.
Axis lines
For instances where you want to thicken axis lines, you can do so by specifying the line thickness and color.
To do this, activate the Format Axis sidebar.
Under Axis Options, go to the Fill and Line tab (first tab).
Under the Line section, specify the color and the width.
Format axis labels
This can be done directly on the Home tab.
Click on the axis labels and specify the font, size, color and formatting.
Add new series to the chart
There will be cases where you will need to add a new data series into the chart.
As an example, if you want to add the Budget column for the sake of comparison, there are different ways to do it:
- Click on the chart. You will notice that the chart data range is highlighted. Hover over the dot between the Sales header and data. You will notice your cursor changing to a double-arrow. Click and drag this to include the Budget column.
Note that you have to click on the right element before you expand this selection—in this case, the chart area. If the bars are selected before the data range is expanded, you will not be able to expand the entire data set or might just be able to expand the header but not the data.
2. Another way is to right click on the chart and select Select Data.
This will display the Select Data Series window and you can manually add the series as mentioned above.
Under Legend Entries (Series), click on Add.
This will display the Edit Series window where you will need to specify the Series name and the Series values.
Change the series order
If you have multiple series, you have the option of rearranging them on the chart.
As an example, I have added a third series, budgetduplicate.
To rearrange the series, click on the series you want to move up or down and then click on the arrows beside the Remove button.
Missing data series
There are also cases where you add a data series but it does not appear on the chart.
There could be a number of reasons.
It could be that it has no fill.
To find it, go to the Format sidebar.
Click on the dropdown and you will see a list of all your chart elements that are active.
Click on the series that seems to be invisible and reformat it as you see fit.
This dropdown also lets you switch across all your chart elements when you want to format them one by one.
Creating combination charts
There is also an option to display the data series using different chart types.
It is possible to have both the bar series and line series in one chart.
For this example, we have two series: Sales and Budget which are both displayed as bars.
If we were to convert the Budget series into a scatter plot:
- Click on the bars on the chart. Right click and select Change Series Chart Type.
- Excel takes you directly to the Combo view where it displays the options for a combined chart.
3. Change the chart type by clicking on the dropdown beside the data series.
This feature is readily available for Excel 2013 and 2016.
However, for 2010 you have to manually click on the series on the chart before you right click and change the chart type.
If you don’t right click on the series, but instead do the right click on the chart area, you will see different options.
Selecting the Change Chart Type will bring you to the option of changing the entire chart type.
To change the type of each series individually, go to the Combo section at the bottom.
The advantage of right clicking on the series itself is that it takes you right away to the Combo section.
4. For the Budget series, select the Line with markers.
5. Format the new series. In this case, you can remove the line by selecting No Line under the Format Data Series
6. You have the option of customizing the markers for the chart under the Marker tab, which includes the marker type, size, borders and fill.
Adding data labels
Right click on the series and select Add Data Labels.
From the Format Data Labels sidebar, you can select where you want them displayed, and then change its colors from the Home tab.
In this example, I placed the Sales series data labels on the Inside Base, while the Budget series data labels are placed Above.
Adding the series legend
Click on the + sign at the top right of the chart and tick the box for Legend.
Specify where you want it displayed.
In this example, I want it to be displayed on top.
The legend displayed are the series names.
To change the legend name, you simply have to change the name of the data series.
In this case, that is the header of the table.
Adding a chart title
Tick the Chart Title element on the list and type in the title you want.
In this example, I used “Sales Actual vs Budget”.
I hope this article helped you become more familiar with Excel Charts.
It’s now your turn to practice!
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
