How To Make Charts In Excel

Charting is one of the unique features of excel. It allows user to create, edit and print various chart or graph types as desired. You can modify data, switch between chart types and control various properties including formating titles and background.

As usual, chart is drawn to reflect a relation between two or more items of either points or ranges from any given numeric data. The varities of chart types are useful when deciding "what would you like to point out from data and how to present it?". The chart could be inserted either within a worksheet or as a separate page.

Some important definitions:

• Data points: set of values or x,y,z coordinates which are used to plot a chart and could be represented by bars, columns, lines, pie or doughnut slices, dots, and data markers of other various shapes.
  
• Data series: set of data points of one item. Each data series in a chart have data markers of the same color or pattern and is indicated in the chart legend. All chart types could be plotted for one or more data series in a chart, except Pie charts show only one data series for each chart.
  
• Chart area: the entire chart and all its elements.
  
• Plot area: in a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.

By the end of this tutorial you will be aware of:

• Types of Charts In Excel and Their Uses
• How To Make Charts or Graphs In Excel
• How To Change the Chart Type
• How To Resize a Chart
• How To Move a Chart

Types of Charts In Excel and Their Uses

To plot any of chart types, data shall be arranged in columns or rows of a worksheet.

Column charts

Column charts are used to show data variations over a period of time or comparisons between items. In column charts, the horizontal axis represents categories and the vertical axis represents values. To present data in a 3-D format that uses three axes (a horizontal axis, a vertical axis, and a depth axis) that you can modify, use a 3-D chart subtypes. The third value axis (depth axis) is automatically set to display the 3-D perspective only, except for 3-D column charts.

Column charts comprise the next subtypes:

• Clustered Column and Clustered Column in 3-D

  Clustered column charts are used to compare values across any of the next categories that represent:
  • Ranges of values (for example, item counts).
  • Specific scale arrangements (for example, a Likert scale with entries, such as strongly agree, agree, neutral, disagree, strongly disagree).
  • Names that does not have any specific order (for example, item names, geographic names, or the names of people).
  
  A clustered column chart displays values in 2-D rectangles. A clustered column in 3-D chart displays values in 3-D cuboids.

• Stacked Column and Stacked Column in 3-D

  Stacked column charts are used to show the relationship of individual items to the whole, comparing the contribution of each value to a total across categories, and when you want to emphasize the total between multipile series.
  
  A stacked column chart displays values in 2-D vertical stacked rectangles. A 3-D stacked column chart displays values in 3-D cuboids.

• 100% Stacked Column and 100% Stacked Column in 3-D

  100% stacked column charts and 100% stacked column in 3-D charts are used to compare the percentage that each value contributes to a total across categories and when you want to emphasize the contributions to the whole, especially if the total is the same for each category.
  
  A 100% stacked column chart displays values in 2-D vertical 100% stacked rectangles. A 3-D 100% stacked column chart displays values in 3-D 100% stacked cuboids.

• 3-D Column

  3-D column charts are used to compare data points along the horizontal and the depth axes and when you want to compare data across the categories and across the series equally.
  
  3-D column charts have three axes that you can modify (a horizontal axis, a vertical axis, and a depth axis). Where, the vertical axis displays the values, while both the horizontal axis and the depth axis display categories.

• Cylinder, Cone, and Pyramid Columns in 3-D

  Cylinder, cone, and pyramid columns in 3-D charts are available in the above clustered, stacked, 100% stacked, and 3-D chart types that are represented in rectangular shapes, and used to show and compare data the same way. Where, the only difference is that these chart types display cylinder, cone, and pyramid shapes instead of rectangles.
  
  

Line charts

Line charts are used to display continuous data over time of common scale - representing categories of equally spaced numeric labels such as months, quarters, or fiscal years - showing trends in data at equal intervals. Also, they are used for multiple series and less than or equal ten numeric labels. Otherwise, scatter chart is used in case of one series or more than ten numeric labels. The category data is distributed equally along the horizontal axis, and all value data is distributed equally along the vertical axis.


Line chart can be displayed with or without markers to indicate individual data values. Mainly, line chart without markers are used for approximate categories or values. When it is not easy to see that the lines are stacked, a stacked area chart to be used instead.

Line charts comprise the next subtypes:

• Line and line with markers

  Line charts are used in case of many data points to show trends over time or categories of important order.
  
  
  

• Stacked line and stacked line with markers

  Stacked line charts are used to show the trend of the contribution of each value over time or categories of important order.
  
  
  

• 100% stacked line and 100% stacked line with markers

  100% stacked line charts are used to show the trend of the percentage of the contribution of each value over time or categories of important order.
  
  
  

• 3-D line

  3-D line charts show each row or column of data as a 3-D ribbon in horizontal, vertical, and depth axes that you can modify.
  
  
  

Pie charts

Pie chart used to show less than eight data points and for only one data series that is arranged in one column or row of a worksheet. Each data point in a pie chart represent fraction of item size over sum of items as a percentage of the whole pie.


Where, none of the values that you want to plot can be negative or zero.

Pie charts comprise the next subtypes:

• Pie and pie in 3-D

  Pie charts display the percentage of each value to total values in a 2-D or 3-D format. Slices of a pie chart can pulled out manually as to emphasize the corresponding values.
  
  
  

• Pie of pie and bar of pie

  Pie of pie or bar of pie charts display the main pie chart combined with a secondary pie chart or a stacked bar chart related to one of the main slices. These chart types are used to provide details of a slice in the main pie chart.
  
  
  

• Exploded pie and exploded pie in 3-D

  Exploded pie charts display the percentage of each value to a total while emphasizing individual values in a 2-D or 3-D format. Slices of a pie chart can pulled out by changing the pie explosion setting as to emphasize the corresponding values.
  
  
  

Bar charts

Bar charts are used to show comparisons between items with long durations.

Bar charts comprise the next subtypes:

• Clustered Bar and Clustered Bar in 3-D

  Clustered bar charts are used to compare values across categories. In these chart types, the vertical axis represents the categories, and the horizontal axis represents the values. A clustered bar in 3-D chart displays the horizontal rectangles in 3-D.
  
  
  

• Stacked bar and stacked bar in 3-D

  Stacked bar charts are used to show the relationship of individual items to the whole. A stacked bar in 3-D chart displays the horizontal rectangles in 3-D.
  

• 100% stacked bar and 100% stacked bar in 3-D

  These chart types are used to compare the percentage of each value to total across categories. A 100% stacked bar in 3-D chart displays the horizontal rectangles in 3-D.
  
  
  

• Horizontal cylinder, cone, and pyramid

  These charts are available in the above clustered, stacked, and 100% stacked chart types that are provided are represented in rectangular bar shapes, and used to show and compare data the same way. Where, the only difference is that these chart types display cylinder, cone, and pyramid shapes instead of rectangles.
  
  
  

Area charts

Area charts are used to emphasize the magnitude of change over time, and to draw attention to the total value across a trend. For example, profit over time can be plotted in an area chart to emphasize the total profit. Also, by displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.

Area charts comprise the next subtypes:

• 2-D area and 3-D area

  These chart types are used to display the trend of values over time or other category data. 3-D area charts use three axes (horizontal, vertical, and depth) that you can modify. As a rule, you should consider using a line chart instead of a nonstacked area chart, because data from one series can be obscured by data from another series.
  
  
  

• Stacked area and stacked area in 3-D

  Stacked area charts are used to display the trend of the contribution of each value over time or other category data. A stacked area chart in 3-D display the same in 3-D.
  
  
  

• 100% stacked area and 100% stacked area in 3-D

  100% stacked area charts are used to display the trend of the percentage that each value contributes over time or other category data. A 100% stacked area chart in 3-D display same in 3-D perspective.
  
  
  

XY (scatter) charts

Scatter charts show the relationships between numeric values for one or more data series of xy coordinates, such as scientific, statistical, and engineering data. x and y values are set in two adjacent rows or columns. Each of the horizontal axis (x-axis) and the vertical axis (y-axis) show a set of numeric data. It combines these values into single data points and displays them in irregular intervals, or clusters. A scatter chart can be used when:

• You want to change the scale of the horizontal axis.
• You want to make that axis a logarithmic scale.
• Values for horizontal axis are not evenly spaced.
• There are many data points on the horizontal axis.
• You want to effectively display worksheet data that includes pairs or grouped sets of values and adjust the independent scales of a scatter chart to reveal more information about the grouped values.
• You want to show similarities between large sets of data instead of differences between data points.
• You want to compare many data points without regard to time — the more data that you include in a scatter chart, the better the comparisons that you can make.

Scatter charts comprise the next subtypes:

• Scatter with only markers - without lines

  This type of chart are used with data markers but without lines to compare pairs of values when you do not want to show connectivity of the data points. Where, there are many data points and connecting lines would make the data more difficult to read.
  
  
  

• Scatter with smooth lines and scatter with smooth lines and markers

  This type of chart are used to display a smooth curve that can connect many data points, using mooth lines with or without markers.
  
  
  

• Scatter with straight lines and scatter with straight lines and markers

  This type of chart are used to display straight connecting lines between data points, with or without markers.
  
  
  

Stock charts

A stock charts are used to display the fluctuation of stock prices or for scientific data like the fluctuation of daily or annual temperatures. It is very important to organize your data in the correct order to create stock charts. For example, to create a simple high-low-close stock chart, you should arrange your data with High, Low, and Close entered as column headings, in that order.

Stock charts comprise the next subtypes:

• High-low-close

  The high-low-close stock chart are used to display stock prices. It requires three series of values in the following order: high, low, and then close.
  
  
  

• Open-high-low-close

  This type of stock chart requires four series of values in the correct order (open, high, low, and then close).
  
  
  

• Volume-high-low-close

  This type of stock chart requires four series of values in the correct order (volume, high, low, and then close). It measures volume by using two value axes: one for the columns that measure volume, and the other for the stock prices.
  
  
  

• Volume-open-high-low-close

  This type of stock chart requires five series of values in the correct order (volume, open, high, low, and then close).
  
  
  

Surface charts

A surface chart are used to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values. Both categories and data series shall be numeric values.

Surface charts comprise the next subtypes:

• 3-D surface

  3-D surface charts are used to show trends in values across two dimensions in a continuous curve for large amounts of data that may otherwise be difficult to imagine. Color bands in a surface chart represent the difference between the values of the data series in a 3-D view, in form a rubber sheet stretched over a 3-D column chart.
  
  
  

• Wireframe 3-D surface

  The wireframe 3-D surface charts are used for faster plotting of large data sets with only lines without colors on a surface in 3-D.
  
  
  

• Contour

  Contour charts are used as surface charts viewed in plan, similar to 2-D topographic maps. They show color bands that represent specific ranges of values and lines connect interpolated points of equal value.
  
  
  

• Wireframe contour

  Wireframe contour charts are also used as surface charts viewed in plan, for faster plotting of large data sets with only lines - without color bands on the surface.
  
  
  

Doughnut charts

Doughnut charts are similar to pie or stacked column or stacked bar charts, used to show the relationship of parts to a whole, but it can display more than one data series.

Doughnut charts comprise the next subtypes:

• Doughnut

  Doughnut charts are used to display data in rings, where each ring represents a data series. Percentages can be displayed in data labels for each ring with total 100%.
  
  
  

• Exploded Doughnut

  Exploded doughnut charts are similar to exploded pie charts, used to display the contribution of each value to a total while emphasizing individual values, but they can display more than one data series.
  
  
  

Bubble charts

To plot a bubble chart, data shall be arranged in columns on a worksheet so that x values are listed in the first column and corresponding y values and bubble size values are listed in adjacent columns, as shown in the following example.

Bubble charts comprise the next subtypes:

• Bubble or bubble with 3-D effect

  Both bubble chart types are used to compare sets of three values instead of two in 2-D or 3-D. The third value displays the size of the bubble marker.
  
  
  

Radar charts

Radar charts are used to compare the aggregate values of several data series.

Radar charts comprise the next subtypes:

• Radar and radar with markers

  These radar charts are used with or without markers to display changes in values relative to a center point.
  
  
  

• Filled radar

  These radar charts are used to display the same, but the area covered by a data series is filled with a color.
  
  

How To Create a Chart In Excel

• Select the data that you want to chart. The data should be arranged in rows or columns, with row labels to the left and column labels above the data. Where, Excel automatically determines the best way to plot the data in the chart.
  
  
  
• Click on the Insert Ribbon, go to the Charts group, click the chart type that you want to use, and then click a chart subtype.
  
  Or, explore all the available chart types, click to launch the Insert Chart dialog box, and then click the arrows to scroll through the chart types. When you move the mouse pointer over any chart type, a ScreenTip displays its name.
  
  
  
• In order to add titles and data labels, and change the design, layout, or format of your chart, you can use the Chart Tools - Click anywhere inside the chart to activate it as below.
  
  

How to Change the Chart Type

• Do one of the following:
  > To change the chart type of the whole chart, click the chart area or plot area of the chart to display the chart tools.
  > You can also right-click the chart area or plot area, click Change Chart Type.
• To change the chart type of a data series, click that data series. Note that you can change the chart type of only one data series at a time. To change the chart type of more than one data series in the chart, repeat the steps of this procedure for each data series that you want to change. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
• On the Design tab, in the Type group, click Change Chart Type.
  
  
• In the Change Chart Type dialog box, click a chart type that you want to use. The first box shows a list of chart type categories, and the second box shows the available chart types for each chart type category.

How to Resize a Chart

• Move your mouse over the dotted handles on the Chart frame border and notice when the mouse cursor change to a double arrow.
  
• Click and drag as desired - up, down, left or right. Hold down the [ALT] key if you wish to snap to the cell gridlines.

How to Move a Chart

• Move mouse over the chart frame border and notice when mouse cursor change to a four pointed black arrows.
  
• Click on the chart frame border and hold the mouse button down as you drag.
• Release the mouse when the chart is in the desired position.

References:

• Microsoft Manuals
• Other Manuals