This is a complete tutorial on How to Make or Plot a Graph or Chart in Excel. To make a complex chart like the Gantt Chart, you have to know the basic principles of making a chart. Though we used Excel 2013 initially and then used Excel 365 to make explain all features of charts, if you use Excel 2007 or 2010, you can also use this guide. In some cases, you might stumble to find the same commands of Excel 2013 in Excel 2007 or in Excel 2010.
1. Making a Graph or Chart in Excel from Scratch
In this method :
- You will learn how to create an Excel chart or graph by selecting the data series manually.
- And you will be introduced to the “Insert” Chart dialog box.
In this section, we shall learn in detail how to create Excel charts. You know: No charts, no data visualization. Without visualizing data, people cannot make better decisions. In this method, we shall learn how to make an Excel chart from scratch. Basically, you will not use this approach practically to create charts in Excel. But this approach will help you to understand how Excel actually creates charts.
In this worksheet named Chart from Scratch, you see a Time column and a Temperature column.
Let’s create the chart now from scratch.
- Firstly, click on the Insert tab. In the Charts group of commands, the first command is the “Recommended Charts” command.
Here, if we click on the Recommended Charts, Excel shows me a warning message, as Excel did not find any data to recommend a chart. As, my cell pointer is referring to a cell that has no data.
- So, cancel the message and select a cell within this data range.
- And now, click on the Recommended Charts command again.
Subsequently, Excel shows four recommended charts in the Insert Chart dialog box. Here, on the left, you see the chart previews, if you click on the previews then you will see the detail chart on the right. On the top, you see the chart category name: Clustered Column chart, at the bottom; this is a brief description about the Chart category, we get these details about the Clustered Column chart: “A clustered column chart is used to compare values across a few categories. Use it when the order of categories is not important.”
To view more charts, click on the “All Charts” tab here.
First, you will find Recent. If you click on it, you will find the charts here that you have used recently. Then the Templates, if you click on it, you will see the Templates folder is blank now. We shall learn later how to create chart templates.
After that, the Column charts, so many options are under this part; Clustered Column, Stacked Column, 100% Stacked Column, 3-D Clustered Column, 3D Stacked Column, 3D 100% Stacked Column, and finally the 3D Column.
All these charts have their subcategories. Then the Line chart, there are so many options here you can work with. Then the Pie chart, Bar chart, Area chart, X Y (Scatter) chart, the Combo chart and so on. Here, the Combo chart is not actually a unique type of chart; it is just a combination of two or more charts. But we canceled this Insert Chart dialog box.
- Following, we place the active cell pointer outside the data range and click on the Scatter dropdown.
- Then, we select the Scatter with Straight Lines and Markers chart.
At this time, you see a blank chart area appears in the worksheet. There is nothing in the Chart.
- Now, right-click on the Chart Area, and click on the Select Data command in the drop-down list.
As a result, the “Select Data Source” dialog box appears.
- The first option in the dialog box is Chart data range, if we select this data range, Excel will automatically fill the Legend Entries, and Horizontal Axis labels.
At this point, we get two new terms: Series and Category. The values that are set in Y-axis are called series, and the values that are set in X-axis are called Categories, remember these two Excel Chart-related terms very well.
- After that, we click OK, and you see the chart is created.
Let’s make the chart in a little bit different way: right-click, select data, delete data range, and remove the series 1. The dialog box is blank again.
- Now, click on the Add button.
Here the “Edit Series” dialog box appears. The first option is the Series name.
- As our series name is Temperature, we select the Temperature column heading.
- Then, the next option is the Series X values. Series X values are the Time values. We select the values of the Time column. Be careful, don’t select the column heading “Time”, only the series values.
- As Series Y values, we select the Temperature values. Again warning, don’t select the column heading Temperature, only the values.
- Lastly, click OK.
- And click OK.
As a result, you see the chart is created. Actually, this is the way you can create Excel charts in an extremely basic way.
Furthermore, you can increase the size of the chart; decrease the size of the chart, you can move the chart from one location to another location in the worksheet.
Moreover, this chart does not have any Axis titles, and there is no legend. The chart does not show any trend line. So, you got that how to make or plot a Graph or Chart in Excel.
Now, let’s add them.
- Firstly, click on the Chart Elements icon.
- Secondly, select Axis Titles, Legend, and Trendline.
Here, you see Axis Titles are added, though Axis titles are showing generic names. Additionally, we change this X-axis title to Time and this Y-axis title to Temperature. Also, you see Legend and Trend Line are added in the chart. Finally, you may get that how to make a graph or chart in Excel with trendline.
So, you just learned how to create a basic Excel chart from scratch. You can customize this chart in many ways. In our next few methods, we shall learn how to customize Excel charts hugely. This basic knowledge will help you in many ways to learn advanced charting in Excel. Remember it: No charts, no data visualization, remember it.
2. Using Recommended Charts Tool in Excel
From this method:
- You will learn in detail how to create an Excel chart using the Recommended Charts command.
- And you will learn how to apply Chart Styles on a chart.
Excel 2013 has added a new feature to work with Excel charts; the feature is Recommended Charts. Here, by clicking on the Insert tab, you will get the Recommended Charts command in the Charts group of commands. Now, you can create Excel charts more easily using this Recommended Charts feature. This worksheet shows some sales data of a company according to Region and Year. The Total row and Total column calculate the total sales according to Region and Year.
- Firstly, you have to select the data that will make your chart. Say we select a cell in this data region.
- Secondly, click on the Recommended Charts command.
At this time, the Insert Chart dialog box appears with some chart recommendations.
- Thirdly, just click on OK.
As a result, you see a chart is created and all the data in this region is used to make the chart. Now, you may get that how to make a graph or chart in Excel.
But we don’t want to include the Total row and Total column data in the chart. So, you must be careful when you select data to create charts.
- Now, select the data.
- Then click on the Insert tab and click on the Recommended Charts command.
Again, the Insert Chart dialog box appears with 1 2 3 4 5 6 7, a total of seven recommended charts for the selected data.
Firstly, the first recommended chart is a Line chart; you can easily find out the trends of sales in different years and in different regions using this chart. In addition, the Blue line represents the sales of the West Region, the Red line represents the sales of the Mid region and the Gray Line represents the sales of the East Region. So, it is clear from the chart that Sales is increasing in the West Region, Sales is in a downtrend in the East region and Sales is almost flat in the Mid region.
Secondly, the next recommended chart is a Clustered Column chart. This chart shows the sales data in columns. You can also use this chart, but if you need to understand the underlying trend of your sales data, then this chart will not serve your purpose.
Thirdly, the next Recommended Charts is a Stacked Area chart. Also, this recommended chart is not a good choice as the chart shows that all the sales data are in an uptrend, but it is not true. Additionally, we shall show you later how you can use the Stacked Area chart effectively.
Fourthly, the next one is a Stacked Column chart, it is also not a good recommendation by Excel.
Fifthly, the next one is a Scatter chart with Straight Lines and Markers. This one is actually the same as a Line chart. The only difference is: in this chart data points are marked with Markers. In the Line chart, you see there are no markers to mark the data points. It is a good idea to use the Scatter chart with Straight lines and Markers because the data points are easily visible with the markers.
Sixthly, the next recommendation is a Bubble chart, this is totally a bad recommendation by the Excel Recommended charts feature.
Lastly, the final recommendation is a Funnel chart, this is absolutely a bad recommendation.
So, from this discussion, you have realized that every chart recommended by Excel is not accurate.
Thus, you have to filter the good recommendations from the bad ones. In that way, you can make better use of the Recommended Charts feature. Here, I choose the Scatter with Straight Lines and Markers Recommendation. Now, by clicking OK, you see the chart is inserted in the worksheet. So, you got that how to make or plot a Graph or Chart in Excel.
And immediately Excel shows some other Chart Styles in the Design contextual tab of Chart Tools.
- Now, move your mouse pointer over the Quick Styles. Here, we choose Style 10. It seems like a nice chart style to me.
So, this is how you can create an Excel chart by just selecting the data and then using the Recommended Charts feature. Now, it is clear that how to make a graph or chart in Excel.
Furthermore, you can change the location of the chart in the worksheet. Also, you can resize the chart size. Observe one thing, when the chart is selected, the data that makes the chart is also selected.
3. Creating and Customizing an Excel Chart
In this method, you will learn:
- How to create an Excel chart using the commands under the Charts group.
- How to change the color of a chart.
- How to change the Layout of a chart.
- How to switch between Row and Column.
- How to change the Chart type.
- How to create a default Excel chart using the keyboard shortcut.
- How to create a chart in Chart Sheet using a keyboard shortcut.
- And how to change the default Excel chart.
Here, you see, this worksheet shows some data. Basically, the data is about Customer satisfaction by Age Group in different months.
Furthermore, Satisfaction is measured in percentage: the higher the percentage, the higher the customer satisfaction. Additionally, the first column shows the satisfaction of the people who are under 30, the second column shows the satisfaction of the people who are between 30 and 49. And the last column shows the satisfaction of the people who are above age 50.
- Now, to create a chart, the first job is to select the data that you will use to create a chart. We can select the whole range, or we can just select a single cell.
Here, the first Chart in the Charts group of commands is: Column Chart. So, if you click on the Column Chart drop-down, you will get four sections. Which are 2-D Column charts and 3-D Column charts, 2-D Bar, and 3-D Bar.
In addition, under the 2-D Column, you will get more Chart Types: Clustered Column chart, Stacked Column chart, 100% Stacked Column chart, and under 3-D section you will get chart types:
- 3D Clustered Column
- 3D Stacked Column,
- 3D 100% Stacked Column, and regular 3D Column chart.
Similarly, under the 2-D Bar, you will get more Chart Types:
- Clustered Bar chart,
- Stacked Bar chart,
- 100% Stacked Bar chart,
Also under the 3-D section, you will get chart types:
- 3D Clustered Bar,
- 3D Stacked Bar, and
- 3D 100% Stacked Bar chart.
Furthermore, to get more column charts: click on More Column Charts. Insert chart dialog box appears with All Charts Tab activated.
Then, the next Chart type in the group is Hierarchy Chart. After that Waterfall, Funnel, Stock, Surface, and Radar chart command. Then, Line Chart, following Statistic Chart, then Combo Chart. Also, Pie Chart, and finally you will get the Scatter Chart.
- Here, we click on the Line Chart command and then select the “Line with Markers” Chart.
So, the chart is inserted into the worksheet. Therefore, you got that how to make or plot a Graph or Chart in Excel.
Moreover, you can also change the color of the chart: you will get the color palette in Two locations: you see there is a Color tab here, and there is a Change Colors dropdown in the Design Context tab. Both have the same color palettes.
Furthermore, there are two categories of colors here: Colorful and Monochromatic. In addition, Monochromatic colors are the same color but in different Accents. On the other hand, Colorful colors are a combination of different colors.
Moreover, you can also change the layout of a chart. Actually, by clicking on the Quick Layout drop-down in the Design tab, you will get a total of 12 layouts in the drop-down list. The number of layouts will vary with the chart type.
Here, by moving your Mouse pointer over the layouts, the chart layout will show you the review immediately. At this existing layout, the Legend is at the bottom of the chart area, when we select the Layout 1 option, you see the Legend is now showing on the right side of the chart area.
So, you can create your own layout and template; we shall discuss later how you can do that.
Now, the next thing you can apply on a chart is: you can switch Row or Column. In this chart, the months are set along with the X-axis, and the age groups are set along with the Y-axis.
So, to identify a data point in the chart, we are moving along the X-axis at first, and then moving along the Y-axis. Furthermore, you can change this row and column setup.
- Here, you may click on the Switch Row or Column command.
Thus, you see now, along the X-axis you get the age groups: below 30, within 30 to 49, and 50+. And along the Y-axis, you get the months in different colors.
Finally, you can easily understand that interpreting this chart is not so easy. Now, we get back to our previous chart.
At some point, you might want to change the Chart type. Here, you can change the chart type in two ways. Firstly, you can click on the Change Chart Type command in the Design contextual tab. Secondly, right-click anywhere on the chart and select the “Change Chart Type” option.
In both cases, the “Change Chart Type” dialog box appears with the All Charts tab activated. Basically, all the Chart types are here. So, you can use any of them that meets your purpose. Or you can work with the “Recommended Charts” tab. As you know how to work with the Recommended Charts. So, you get the details of how to make a graph or chart in Excel.
Now, we shall discuss how you can create a chart using Keyboard Shortcut. Here, to create a chart in the same worksheet, use the keyboard shortcut ALT + F1. And if you want to create a chart in a Chart Sheet, then use only the F11 key.
Actually, using keyboard shortcuts, you can create only the default Excel chart. Here, the default Excel chart is the Clustered Column chart. But you can set any chart type as your default chart.
- Now, we select the data and press the F11 key on the keyboard.
As a result, you see a new Chart sheet has been created and the chart is inserted into the Chart Sheet. So, you know that how to make a graph or chart in Excel.
Here, to view the chart properly in the Chart Sheet, you can zoom in or zoom out the chart sheet size.
- Furthermore, to move the chart in a worksheet, right-click anywhere within the chart area, a menu appears, and select the Move Chart option.
Subsequently, the Move Chart dialog box appears. Here, you may select the preferred one.
Now, we will change the Default Chart Type.
- Again, to change the Default Chart in Excel, click on Change Chart Type.
At this time, the Change Chart Type Dialog box will appear. Also, you see the Clustered Column chart is selected. Right-click on this chart, and you see, an option appears with the text: “Set as Default Chart” and there is a tick mark on the left of the text. So, this chart is selected.
- Now, click on the Line chart and right-click on the Line with Markers chart.
- And select Set as Default Chart.
So, this Line with Markers is set as the Default Chart Type.
- Lastly, click OK.
At this time, you see the chart is turned into a Line with Markers chart type. Consequently, you learn that how to make or plot a Graph or Chart in Excel.
Now, let’s now create another chart using a keyboard shortcut.
- Here, we select any cell within the data range and press ALT + F1 on the keyboard.
As a result, you will see a Clustered Column chart. So, you understand how to make a graph or chart in Excel.
- Here, to change the Chart Type and move this chart to a chart sheet, just right-click anywhere on the chart area, click on the Change Chart Type, then change it to Lines with Markers.
- Then, click on the Move Chart option, and then click on New sheet, you can use a customized name, we name it as “Custom Chart”. Finally, click OK.
You see a new chart sheet is created with the name: “Custom Chart”. And the chart is shown in the chart sheet. So, this is how you can create and customize Excel charts. Finally, you got that how to make a graph or chart in Excel.
You will get more discussion on Chart Customization in our upcoming lectures.
4. Creating a Combination Chart & Introducing with Chart Elements in Excel
In this section,
- You will learn how to create a Combo Chart.
- And then you will be introduced to the different parts of a chart.
A Combo chart is not a unique type of chart. Here, Combo comes from the word “Combination”. So, a Combo chart means the combination of two or more charts.
Here, the worksheet that you are looking for has a small range of data. Additionally, the data has three columns: Month, Sales Calls, and Units Sold. Furthermore, we are going to make a combo chart using this data range.
- At first, we select the whole data range,
- Then, click on the Insert Tab >> click on the Combo Chart drop down, and a list appears.
Here, the first one is the Clustered Column – Line chart, the second one is the combination of Clustered Column chart and Line Chart on the Second Axis and the third one is the combination of the Stacked Area chart and Clustered Column chart.
- Now, to create a customized combo chart, click on the “Create Custom Combo Chart” option.
As a result, the “Insert Chart” dialog box appears with the “All Charts” Tab activated. Also, the Combo window is open and Custom Combination is selected in the window. Here, you can select your preferred chart.
Now, for Units Sold, we select the “Line with Markers” Chart Type. For the Sales Call, Clustered Column chart is already selected. Now, look at the chart preview. Then, you see, the chart is using a single Y-axis.
Now, let’s create another Y-axis on the right side of the chart.
- Thus, we select the Secondary Axis for the Units Sold.
So, you can now easily find out the relationship between Sales Calls and Units Sold. Actually, Sales Calls are going, and Units Sold are also going. So, our Combo Chart is ready.
- Lastly, click OK.
As a result, the chart is inserted into the worksheet. Then, we changed the Chart Style to Style 5. After that, we add some more elements using the Chart Elements icon. Eventually, we add Axis Title, Data Labels, and Data Table to the Chart. Furthermore, you can also add Chart Elements using this “Add Chart Elements” drop-down in the Design contextual Tab.
Here, we change the X-axis title to Month, the left-side Y-axis title to Sales Calls, and the right-side Y-Axis title to Units Sold. Furthermore, you can also change the Chart title: we change it to “Sales Calls vs. Units Sold”. So, you got that how to make a combo graph or chart in Excel.
When the Chart Element is selected, it can be formatted in your own way.
- Now, we select the Series Sales Calls columns.
- Then, we fill these shapes using the Orange color, use the outline as Black and use this Inside Center Shadow.
Lastly, one more thing to discuss: when you move your mouse pointer over a data point, you see three things: at first the Series Name, for this case, the Series name is Units Sold, then the Point, Point is actually Category value, here Point is: June, then the Value. Value is actually the Y-axis value of the Series. Here the value is 143.
So, this is a brief introduction to the chart elements. We shall discuss more about them in our next methods. So, Keep in Touch and Keep watching.
5. Using Chart Elements, Chart Styles and Chart Filters
In this method:
You will work with the three icons that appear in the top-right corner when you select a chart. They are
- Chart Elements
- Chart Styles
- Chart Filters
More or less, you have worked with them in earlier methods, and you will learn their uses in detail in this method.
In this worksheet, you see a combo chart. Actually, we created this Combo chart in our last method. Whenever you select a chart in Excel, these three icons appear in the top right corner next to the chart. The icons are Chart Elements, Chart Styles, and Chart Filters.
Basically, you can use these three icons to format the chart. Now, click on the Chart Elements icon, this is a big plus sign. A list appears. You get all the chart elements related to your Chart Type in this list. Here, you see, Axes, Chart Title, Grid Lines, and Legend are selected. Move your mouse pointer over the Elements, you will get the preview of the Elements in the chart.
Now, to get more options on Axes Element, move your mouse pointer over the Axes Element. Here, you see a little triangle appears on the right side of the Axes text. Now, click on the little triangle. There are four options in the list: three of them are selected. They are
- Primary Horizontal
- Primary Vertical
- Secondary Vertical
- Secondary Horizontal, which is not selected.
Moreover, there is another option in the list, More Options, when you click on More Options, the Format Axis task pane appears.
Then, the Next chart element is: Axes Titles. Which is not selected. Here, to set Axes titles in the chart, tick the option. Furthermore, to get more options on Axes Titles, click on the little triangle on the right side of the text.
After that, the next element is the Chart Title. It is already shown in the chart. Now, click on the little triangle, and you can set Chart Title above the Chart, or you can set the Chart Title as Centered and Overlaid.
Then, the next one is: Data Labels, to set Data Labels, click on it. Now, click on the little triangle, and you can place the Data Labels in the Center position, or in Inside End, Inside the Base, or the Outside End. Or you can use the Data Callout to show the Labels.
Following, the next one is the Data Table. Here, you can show the data table With Legend Keys, or No Legend Keys. This blue rectangle and this red circle between lines are the legend keys.
After that, the next one is Error bars. Here, you see some vertical lines added in the chart. You can display Error Bars in three ways: Standard Error, in Percentage or in Standard Deviation.
Then, the next one is Gridlines. Here, the Gridlines Element is selected. Now, click on the little triangle, you can work with so many grid line options. Here, Primary Major Horizontal and Primary Major Vertical grid lines are shown in the chart.
Following, the next element is Legend. Here, Legend is shown at the bottom of the chart. So in the drop-down list, Bottom is already selected. Additionally, you can place it on the Right, on the Top, or on the Left. Furthermore, to work with more options, click on the More Options command, and the “Format Legend” Task Pane appears.
Now, the last element is the Trendline, click on it.
At this time, the “Add trend line” dialog box appears.
- As we want to show the trend line for the Units Sold series, so select this.
- Lastly, click OK.
As a result, you see a trend line is added in the chart. By default, the trend line added is a linear type of trend line. Here, to add different types of trend lines, click on the little triangle, the list shows four options you can work with.
Then, the next icon is a Painting Brush. Basically, it is the “Chart Styles” option. Here, you can change the chart Style. Apart from this, you can also change the color pattern of the chart. Basically, two types of color patterns are here: Colorful and Monochromatic.
Finally, the last icon is a Funnel. Actually, it is the Chart Filters icon. Furthermore, the Chart Filters have two tabs: Values, and Names. Now, suppose we want to show the values for the month of January and February, and for the Sales Calls series.
- Thus, we deselect Select All in the Series section and only select Sales Calls.
- Then, we deselect Select All in the Categories section and then select Jan and Feb.
- Finally, click on Apply.
As a result, you see Sales Calls data for the Month of January and February is only showing in the chart. Actually, it is very easy to filter a chart in this way.
So, you see that by using these three Icons how easily you can work with the Chart Elements.
6. Resize, Move, Copy, Delete, and Print a Chart in Excel
In this section, you will learn how to:
- Resize a chart
- Move a chart from one location to another
- In the same worksheet
- From one worksheet to another
- From one workbook to another
- And even from a worksheet to a chart sheet.
- You will also learn how to delete a chart
- And how to Print only the chart, not the whole worksheet.
- to delete, at first, you have to select the chart; you see the data that makes the chart is also selected when I select the chart. Just press Delete on the keyboard, and the chart is deleted.
Whatever modification you want to apply to a chart, first you have to activate the chart. If the chart is embedded in the worksheet, you have to click on the chart to activate it. When you activate the chart, these three icons appear:
- Chart Elements,
- Chart Styles, and
- Chart Filters.
Moreover, contextual tabs: Design and Format appear in the tab list.
Let’s see how you can resize the chart. If your chart is an embedded chart, an embedded chart means a chart in the same worksheet, you can easily resize the chart using the Mouse Pointer. At first, activate the chart, move your mouse pointer over the square handles, and you see your mouse pointer turns into a double-headed little arrow. So, click and drag to resize the chart.
But you might want to define an exact size for your chart. Here, to do so, click on the Format contextual tab. In the Size Group of commands, you will find these two spinner buttons: Shape Height and Shape Width. By clicking the Spinner buttons, you can change the Shape Height and the Shape Width. Or you can place value directly into the fields.
Now, let’s see how you can move a chart from one location to another location in the same worksheet, from worksheet to chart sheet, from one worksheet to another worksheet, and even from one workbook to another workbook.
In the same worksheet, the moving chart is simple. Here, just activate the graph, hover your mouse pointer over the border of the chart and then click and drag the chart to a new location.
Also, you can also use the Standard Cut and Paste technique. After the activation of the chart, just click on the Cut command in the Home Ribbon, select a cell where you want to paste the chart, you can right-click and paste, or you can use the Excel keyboard shortcuts CTRL + V on your keyboard. Lastly, you paste the chart. Actually, the standard Copy Cut Paste technique is the only way to copy or cut a chart and then paste the chart into another worksheet or in another workbook.
Eventually, deleting charts is the easiest way. As well as select the chart and press the DELETE key on the keyboard.
Here, if you have more than one chart and want to delete all the Charts, select a chart, then press and hold CTRL key, and select all the charts one by one. Lastly, press the DELETE key on the keyboard. As a result, you see no charts in the sheet.
When you print a worksheet, you can print the Chart only, not the whole worksheet. Just activate the chart, click on the Print Preview, and you see only the chart is ready to print.
Download Practice Workbook
You can download the practice workbook from here:
We hope you found this article helpful. Here, we have explained how to make a graph or chart in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.