How to Make Graphs in Excel (Expert Chart Guides)

This guide will teach you how to make a graph in Excel. This article includes the basics that are important for making graphics in Excel for you.

Currently, there are several types of charts in Microsoft Excel such as Column, Line, Pie, Bar, Scatter Plot, and so on. Before you go on the adventure of creating more complex Graphs, it’s a good idea to understand the basics of making charts in Excel first.

In these Guides, you will learn how to create charts, change types, change rows/columns of data, and create data labels. Please look point by point to get a good understanding. Let’s learn more

Step by Step Make Graphs in Excel

For example, I have Facebook (FB), Apple (AAPL), and Microsoft (MSFT) Stock Price data from 2012 to 2020 as shown:

Pro Tips: To create graphs, please organize your data in tabular form. You may use the Format-Table or Named Range or not use any format (such as using a border)

As you can see, in this example, data is arranged in 4 columns. Each Row shows data based on years. And to create a graph in Excel from this data set, please follow these steps:

1. Select a Data Range. Select the data range to graph it (including table headings). In this example is Range A₃:E₁₂.
   
2. Open “Insert Chart Window”. For starters, I prefer to use “suggested chart” to make your graphics. You can start by clicking on the Insert tab on the ribbon and then clicking the recommended graph.
   
3. Choose the Best Graph type. In the Insert Chart Window, Excel provides recommended charts that are appropriate for your data. Choose the most suitable for you. In this example, I chose a line chart.
   
4. Confirm Your Choice. When you’re done, please click Ok to add your chart and see the results…
   

From these results, there are some very important Chart Element (Default) such as Chart Title, Vertical Axes (Y-axis), Horizontal Axes (X-axis), and Legend.

Pro Tips: In some cases, there are small errors that often occur in Horizontal Axes. For example, when a graph in Excel displays data that shouldn’t be displayed.

I will describe the 4 chart elements. However, if you feel this is less important, please skip to the next section.

#1 Chart Title

To change the Chart Title, please Double click on the Current Chart Title, then change it according to your needs. For example, I changed the title chart into a “stock price”.

#2 Vertical Axes (Y)

For Vertical Axes, Excel automatically retrieves a range of values from the data (highest and lowest).

In this example, the highest value is 273.16 and the lowest value is 19.01. Then Excel rounds the values (273.16 to 300 and 19.01 to 0) to make the chart look prettier.

Isn’t true? Let’s prove it. Please change the highest value in the data (273.16 to 300.00) and the lowest value (19.01 to -125). Then see the results:

#3 Horizontal Axes (X)

The Horizontal Axes appear below the Chart and are designated as X-axis. These values are also retrieved automatically from the Data Table.

In this example, Excel retrieves the data based on the row in Column A. And it does this by default. So far the chart looks good and easy to read. However, in certain circumstances, Excel may use incorrect data for the Horizontal Axes like this…

Excel includes data for the year (Column A) in the chart and uses the numbers 1, 2, 3, 4, 5, etc. As a Horizontal Axis.

Can these graphs be used? I don’t think so. Because it doesn’t describe the actual data (in this example).

But, I have a solution… If this happens to your data, please skip to the Switch Row / Column Data section. But if that is not the case, please continue reading to get an in-depth understanding of this guide.

#4 Legend

Take a look at a map of the world. You will see some information related to the map. Such as mountains, hills, seas, rivers, etc. This information is usually described in the form of an icon or color.

Likewise with charts in Excel. You will see a Legend explaining the difference on the chart. For example, the blue line represents data for FB and the gray line represents data for MSFT. By default, Legend Position is under Graph. However, this position can still be changed.

Next, if you feel that you don’t fit the Line Chart, you can change the chart type without deleting the chart.

Change Chart Type

For example, I want to convert the Line Chart to a Clustered Column Chart. Please follow how to change the chart type in Excel below:

1. Click on an existing chart (any section).
2. Click the Design tab then click Change Chart Type
3. In the Change Chart Type window .. On the left side there is a Category Chart available in Excel. Please select a Column.
4. There are 7 Chart Types in the Column category. I chose the clustered column.
5. Click on the available chart design. Currently there are 2 choices. Select one, then click Ok to see the results:

It’s easy, right? From this result, the chart is grouped by data row (year). While the Horizontal Axes classifies charts based on Data Columns that appear with different colors.

Wait … wait … what do you think if I change the chart grouping based on rows of data to grouping by Data Column? I’m sure this chart will provide a different insight into your data. Let’s try that…

Switch Row / Column Data

To switch a row of data to a column of data on the graphs, please click anywhere on your chart. Then click on the Design Tab and Click on Switch Rows / Columns and see the result…

Ouch !! It turns out the graphics are not quite right! Let’s look at the first group, namely years…

Supposedly, the Data in the Year Column should not be entered into the Chart, but instead, become a Legend that differentiates data based on color.

It’s a small mistake that happens a lot. But there is a solution…

The trick is to delete the data in Cell A₃, namely the column headings for the year data. Because I don’t want the data in the column it will be included in the chart.

Then, click Switch Row / Column 2x or more. Most importantly, see the chart changes every time you click Switch Row / Column. I just clicked 2x and got the right results.

See, the legend has used the data in Column A. Meanwhile, the year data (column A) is no longer included in the chart.

This is just one trick for the clustered column chart type. Maybe in other circumstances, you will encounter different problems. The solution will depend on how your data is organized.

Change the Position of the Legend

To change the position of Legend, please click on Chart Elements (Icon +). Then click the right arrow icon on the legend. Then select the most suitable placement of the legend (Option Right, Top, Left, Bottom, or More…).

Add Data Labels

It’s not difficult to add data labels to a chart. But if you’re not careful, your graph can look messy.

Instead of displaying all data labels, it is better to display only part or some of the data labels. This method is often used to highlight certain charts to show how much detailed value is on that chart.

For example, I just want to display data labels for Facebook Stock Prices in 2017. For this purpose, please follow these steps:

1. Click on Blue Chart (FB) in 2017. By default, all blue charts will be selected.
   
2. Click the chart again. This second click will only select 1 chart, namely FB 2017.
   
3. Add Data Labels. Please click Chart Elements, then Check Data Labels and see the results
   

It’s easy, right?

This, your chart looks more professional than adding all the data labels.

You can add data labels to other column charts. For example, for the FB chart in 2020 as the highest value and AAPL in 2012 as the lowest value. Please repeat the same steps to add this data label.

I believe there are many examples using graphics in Excel. Next time, I will compile a special guide on how to make all graphics in Excel (such as Columns, Line, Bar, Pie, Scatter Plot, Gantt Charts, etc.)