OR JOIN FREE!

How To Make Charts In Excel

WBxs
May 21, 2017

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

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.
Clustered Column Chart in 3-D

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).
    Clustered column chart and clustered column chart in 3-D
    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.
    Stacked column chart and stacked column chart in 3-D
    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.
    100% stacked column chart and 100% column chart in 3-D
    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 chart
    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.
    Cylinder, cone, and pyramide chart

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 with Markers

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.
    Line charts with and without markers

  • 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.
    Stacked line charts with and without markers

  • 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.
    100% stacked line charts with and without markers

  • 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.
    3-D line chart

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.
Pie Chart in 3-D

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.
    Example of a 2-D and 3-D pie chart

  • 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.
    Example of a pie of pie and bar of 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.
    Example of a 2-D and 3-D exploded pie chart

Bar charts

Bar charts are used to show comparisons between items with long durations.
Bar Chart in 3-D

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.
    Clustered bar and clustered bar in 3-D charts

  • 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.
    Stacked bar and stacked bar in 3-D charts
  • 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.
    100% stacked bar and 100% stacked bar in 3-D charts

  • 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.
    Cylinder, cone, and pyramid charts

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 Chart

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.
    Area and 3-D area chart types

  • 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.
    Stacked area and stacked area in 3-D chart types

  • 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.
    100% stacked area and 100% stacked area in 3-D chart types

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.
XY (scatter) chart

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 only markers chart

  • 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 charts with smooth lines with and 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.
    Scatter charts with straight lines with and 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 chart

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.
    High-low-close stock chart type

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

  • 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-high-low-close stock chart type

  • 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).
    Volume-open-high-low-close stock chart type

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 chart

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.
    3-D surface chart type

  • 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.
    Wireframe 3-D surface chart type

  • 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.
    Contour chart type

  • 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.
    Wireframe contour chart type

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 chart

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%.
    Doughnut chart type

  • 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.
    Exploded doughnut chart type

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 chart

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.
    Bubble and bubble with 3-D effect chart

Radar charts

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

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.
    Radar and radar with markers chart types

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

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.
    Worksheet data

  • 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.
    Excel  Ribbon Image
    Or, explore all the available chart types, click Button Image 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.
    Insert Chart dialog box

  • 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.
    Chart Tools

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.
    Ribbon Image
  • 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.
    Resize a Chart in Excel
  • 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.
    Move a Chart in Excel
  • 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:

Views: 558

× Close
Results:
Article Categories
Ask us anything
Ask anonymously or
Share
Tweet
Share
Share
Pin
Print