Overview of charting

Microsoft Office Excel 2007 no longer provides the chart wizard. Instead, you can create a basic chart by clicking the chart type that you want on the Microsoft Office Fluent user interface Ribbon. To create a professional-looking chart that displays the details that you want, you can modify the chart, apply predefined styles and layouts, and add eye-catching formatting. You can also reuse a favorite chart by saving it as a chart template.

If you have Office Excel 2007 installed, you can also take advantage of the powerful Excel charting functionality in other 2007 Microsoft Office system programs, such as Microsoft Office PowerPoint 2007 and Microsoft Office Word 2007.

In this article

Charts and their elements

Creating charts in Excel

Creating charts in PowerPoint and Word

Modifying charts

Using predefined chart styles and chart layouts for a professional look

Adding eye-catching formatting to charts

Reusing charts by creating chart templates

Charts and their elements

Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data.

A chart has many elements. Some of these elements are displayed by default, others can be added as needed. You can change the display of the chart elements by moving them to other locations in the chart, resizing them, or by changing the format. You can also remove chart elements that you do not want to display.

The chart area (chart area: The entire chart and all its elements.) of the chart.

The plot area (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.) of the chart.

The data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) of the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that are plotted in the chart.

The horizontal (category) and vertical (value) axis (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) along which the data is plotted in the chart.

The legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) of the chart.

A chart and axis title (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.) that you can use in the chart.

A data label (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.) that you can use to identify the details of a data point in a data series.

When you click anywhere in the chart area, and then hover the mouse pointer over one of these elements, Excel displays information about that element in a ScreenTip.

Clicking anywhere in a chart also makes the Chart Tools available, adding the Design, Layout, and Format tabs.

 Top of Page

Creating charts in Excel

To create a chart in Excel, you start by entering the data for the chart on a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.). The data can be arranged in rows or columns — Excel automatically determines the best way to plot the data in the chart. Some chart types (such as pie and bubble charts) require a specific data arrangement as described in the following table.

Once you have entered the data for your chart, you can select the chart type that you want to use on the Office Fluent Ribbon (Insert tab, Charts group).

Worksheet data

Chart created from worksheet data

Excel supports many types of charts to help you display data in ways that are meaningful to your audience. When you create a chart or change an existing chart, you can select from a variety of chart types (such as a column chart or a pie chart) and their subtypes (such as a stacked column chart or a pie in 3-D chart).

For more information about the chart types that are supported and how to create a chart in Excel, see Available chart types and Create a chart.

 Top of Page

Creating charts in PowerPoint and Word

Charts are fully integrated with other 2007 Office release programs, such as Office PowerPoint 2007 and Office Word 2007. Both programs provide the same chart tools that are available in Excel. When you have Excel installed, you can create Excel charts in PowerPoint and Word by clicking the Chart button on the Ribbon (Insert tab, Illustrations group), and then by using the chart tools to modify or format the chart. Charts that you create will be embedded in Office PowerPoint 2007 and Office Word 2007, and the chart data is stored in an Excel worksheet that is incorporated in the PowerPoint or Word file.

 Note    If you work in Compatibility Mode in Word, you can insert a chart by using Microsoft Graph instead of Excel. In PowerPoint, you can always use Excel to create a chart.

You can also copy a chart from Excel to PowerPoint 2007 and Word 2007. When you copy a chart, it can be embedded as static data or linked to the workbook. For a chart that is linked to a workbook that you have access to, you can specify that it automatically checks for changes in the linked workbook whenever the chart is opened.

For more information about how to create a chart in PowerPoint 2007 or Office Word 2007, see Use charts and graphs in your presentation or Present data in a chart or graph.

 Top of Page

Modifying charts

After you create a chart, you can modify it. For example, you may want to change the way that axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) are displayed, add a chart title, move or hide the legend, or display additional chart elements.

To modify a chart, you can:

• Change the display of chart axes  You can specify the scale of axes and adjust the interval between the values or categories that are displayed. To make your chart easier to read, you can also add tick marks (tick marks and tick-mark labels: Tick marks are small lines of measurement, similar to divisions on a ruler, that intersect an axis. Tick-mark labels identify the categories, values, or series in the chart.) to an axis, and specify the interval at which they will appear.
• Add titles and data labels to a chart  To help clarify the information that appears in your chart, you can add a chart title, axis titles, and data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.).
• Add a legend or data table   You can show or hide a legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) or change its location. In some charts, you can also show a data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) that displays the legend keys (legend keys: Symbols in legends that show the patterns and colors assigned to the data series (or categories) in a chart. Legend keys appear to the left of legend entries. Formatting a legend key also formats the data marker that's associated with it.) and the values that are presented in the chart.
• Apply special options for each chart type  Special lines (such as high-low lines and trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.)), bars (such as up-down bars and error bars), data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), and other options are available for different chart types.

 Top of Page

Using predefined chart layouts and chart styles for a professional look

Instead of manually adding or changing chart elements or formatting the chart, you can quickly apply a predefined chart layout and chart style to your chart. Excel provides a variety of useful predefined layouts and styles that you can select from. As needed, you can fine-tune a layout or style by making manual changes to the layout and format of individual chart elements, such as the chart area (chart area: The entire chart and all its elements.), plot area (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.), data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), or legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) of the chart.

When you apply a predefined chart layout, a specific set of chart elements (such as titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), a legend, a data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.), or data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.)) are displayed in a specific arrangement in your chart. You can select from a variety of layouts that are provided for each chart type.

When you apply a predefined chart style, the chart is formatted based on the document theme (theme: A combination of theme colors, theme fonts, and theme effects. A theme may be applied to a file as a single selection.) that you have applied, so that your chart matches your organization's or your own theme colors (theme colors: A set of colors that is used in a file. Theme colors, theme fonts, and theme effects compose a theme.) (a set of colors), theme fonts (theme fonts: A set of major and minor fonts that is applied to a file. Theme fonts, theme colors, and theme effects compose a theme.) (a set of heading and body text fonts), and theme effects (theme effects: A set of visual attributes that is applied to elements in a file. Theme effects, theme colors, and theme fonts compose a theme.) (a set of lines and fill effects).

 Note    You cannot create your own chart layouts or styles, but you can create chart templates that include the chart layout and formatting that you want. For more information about chart templates, see Reusing charts by creating chart templates.

For more information about how to change the look of a chart, see Change the layout or style of a chart.

 Top of Page

Adding eye-catching formatting to charts

In addition to applying a predefined chart style, you can easily apply formatting to individual chart elements such as data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), the chart area, the plot area, and the numbers and text in titles and labels to give your chart a custom, eye-catching look. You can apply specific shape styles and WordArt styles, but you can also format the shapes and text of chart elements manually.

To add formatting, you can:

• Fill chart elements  You can use colors, textures, pictures, and gradient fills to help draw attention to specific chart elements.
• Change the outline of chart elements  You can use colors, line styles, and line weights to emphasize chart elements.
• Add special effects to chart elements  You can apply special effects, such as shadow, reflection, glow, soft edges, bevel, and 3-D rotation to chart element shapes, which gives your chart a finished look.
• Format text and numbers  You can format text and numbers in titles, labels, and text boxes on a chart as you would text and numbers on a worksheet. To make text and numbers stand out, you can even apply WordArt styles.

For more information about how to format chart elements, see Format chart elements.

 Top of Page

Reusing charts by creating chart templates

If you want to reuse a chart that you customized to meet your needs, you can save that chart as a chart template (*.crtx) in the chart templates folder. When you create a chart, you can then apply the chart template just as you would any other built-in chart type. In fact, chart templates are true chart types, and you can also use them to change the chart type of an existing chart. If you use a chart template frequently, you can save it as the default chart type.

 Note    Chart templates are not based on document themes (theme: A combination of theme colors, theme fonts, and theme effects. A theme may be applied to a file as a single selection.). To use the current theme colors (theme colors: A set of colors that is used in a file. Theme colors, theme fonts, and theme effects compose a theme.), theme fonts (theme fonts: A set of major and minor fonts that is applied to a file. Theme fonts, theme colors, and theme effects compose a theme.), and theme effects (theme effects: A set of visual attributes that is applied to elements in a file. Theme effects, theme colors, and theme fonts compose a theme.) in a chart that you create by using a chart template, you can apply a style to the chart. A chart style resets the theme of a chart to the document theme. For more information, see Using predefined chart styles and chart layouts for a professional look.

For more information about how to use chart templates, see Create, apply, or remove a chart template.

 Top of Page

See Also

• Create a chart

 

 

Excel > Charts > Creating charts

Available chart types

Excel 2007

Microsoft Office Excel 2007 supports many types of charts to help you display data in ways that are meaningful to your audience. When you create a chart or change the type of an existing chart, you can select one of the following chart types.

Important  This article describes the chart types that you can use. For information about how to create a chart, see Create a chart.

In this article

Column charts

Line charts

Pie charts

Bar charts

Area charts

XY (scatter) charts

Stock charts

Surface charts

Doughnut charts

Bubble charts

Radar charts

Other types of charts that you can create in Excel

Column charts

Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items.

In column charts, categories are typically organized along the horizontal axis and values along the vertical axis.

Column charts have the following chart subtypes:

• Clustered column and clustered column in 3-D  Clustered column charts compare values across categories. A clustered column chart displays values in 2-D vertical rectangles. A clustered column in 3-D chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a clustered column chart type when you have 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 are not in any specific order (for example, item names, geographic names, or the names of people).

 Note    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 column chart subtype instead.

• Stacked column and stacked column in 3-D  Stacked column charts show the relationship of individual items to the whole, comparing the contribution of each value to a total across categories. A stacked column chart displays values in 2-D vertical stacked rectangles. A 3-D stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a stacked column chart when you have multiple data series and when you want to emphasize the total.

• 100% stacked column and 100% stacked column in 3-D  100% stacked column charts and 100% stacked column in 3-D charts compare the percentage that each value contributes to a total across categories. A 100% stacked column chart displays values in 2-D vertical 100% stacked rectangles. A 3-D 100% stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a 100% stacked column chart when you have three or more data series and you want to emphasize the contributions to the whole, especially if the total is the same for each category.

• 3-D column  3-D column charts use three axes that you can modify (a horizontal axis, a vertical axis, and a depth axis), and they compare data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) along the horizontal and the depth axes.

You can use a 3-D column chart when you want to compare data across the categories and across the series equally, because this chart type shows categories along both the horizontal axis and the depth axis, whereas the vertical axis displays the values.

• Cylinder, cone, and pyramid  Cylinder, cone, and pyramid charts are available in the same clustered, stacked, 100% stacked, and 3-D chart types that are provided for rectangular column charts, and they show and compare data the same way. The only difference is that these chart types display cylinder, cone, and pyramid shapes instead of rectangles.

Tip  To create a column chart, see Present your data in a column chart.

 Top of Page

Line charts

Data that is arranged in columns or rows on a worksheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis.

You should use a line chart if your category labels are text, and are representing evenly spaced values such as months, quarters, or fiscal years. This is especially true if there are multiple series—for one series, you should consider using a category chart. You should also use a line chart if you have several evenly spaced numeric labels, especially years. If you have more than ten numeric labels, use a scatter chart instead.

Line charts have the following chart subtypes:

• Line and line with markers  Displayed with markers to indicate individual data values, or without, line charts are useful to show trends over time or ordered categories, especially when there are many data points and the order in which they are presented is important. If there are many categories or the values are approximate, use a line chart without markers.

• Stacked line and stacked line with markers  Displayed with markers to indicate individual data values, or without, stacked line charts can be used to show the trend of the contribution of each value over time or ordered categories, but because it is not easy to see that the lines are stacked, consider using a different line chart type or a stacked area chart instead.

• 100% stacked line and 100% stacked line with markers  Displayed with markers to indicate individual data values, or without, 100% stacked line charts are useful to show the trend of the percentage each value contributes over time or ordered categories. If there are many categories or the values are approximate, use a 100% stacked line chart without markers.

Tip  For a better presentation of this type of data, consider using a 100% stacked area chart instead.

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

Tip  To create a line chart, see Present your data in a scatter chart or a line chart.

 Top of Page

Pie charts

Data that is arranged in one column or row only on a worksheet can be plotted in a pie chart. Pie charts show the size of items in one data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), proportional to the sum of the items. The data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in a pie chart are displayed as a percentage of the whole pie.

Consider using a pie chart when:

• You only have one data series that you want to plot.
• None of the values that you want to plot are negative.
• Almost none of the values that you want to plot are zero values.
• You do not have more than seven categories.
• The categories represent parts of the whole pie.

Pie charts have the following chart subtypes:

• Pie and pie in 3-D  Pie charts display the contribution of each value to a total in a 2-D or 3-D format. You can pull out slices of a pie chart manually to emphasize the slices.

• Pie of pie and bar of pie  Pie of pie or bar of pie charts display pie charts with user-defined values that are extracted from the main pie chart and combined into a secondary pie chart or into a stacked bar chart. These chart types are useful when you want to make small slices in the main pie chart easier to distinguish.

• Exploded pie and exploded pie in 3-D  Exploded pie charts display the contribution of each value to a total while emphasizing individual values. Exploded pie charts can be displayed in 3-D format. You can change the pie explosion setting for all slices and individual slices, but you cannot move the slices of an exploded pie manually. If you want to pull out the slices manually, consider using a pie or pie in 3-D chart instead.

Tip  To create a pie chart, see Present your data in a pie chart.

 Top of Page

Bar charts

Data that is arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar charts illustrate comparisons among individual items.

Consider using a bar chart when:

• The axis labels are long.
• The values that are shown are durations.

Bar charts have the following chart subtypes:

• Clustered bar and clustered bar in 3-D  Clustered bar charts compare values across categories. In a clustered bar chart, the categories are typically organized along the vertical axis, and the values along the horizontal axis. A clustered bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• Stacked bar and stacked bar in 3-D  Stacked bar charts show the relationship of individual items to the whole. A stacked bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• 100% stacked bar and 100% stacked bar in 3-D  This type of chart compares the percentage that each value contributes to a total across categories. A 100% stacked bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• Horizontal cylinder, cone, and pyramid  These charts are available in the same clustered, stacked, and 100% stacked chart types that are provided for rectangular bar charts. They show and compare data the same way. The only difference is that these chart types display cylinder, cone, and pyramid shapes instead of horizontal rectangles.

Tip  To create a bar chart, see Present your data in a bar chart.

 Top of Page

Area charts

Data that is arranged in columns or rows on a worksheet can be plotted in an area chart. Area charts emphasize the magnitude of change over time, and can be used to draw attention to the total value across a trend. For example, data that represents profit over time can be plotted in an area chart to emphasize the total profit.

By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.

Area charts have the following chart subtypes:

• 2-D area and 3-D area  Whether they are shown in 2-D or in 3-D, area charts 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 display the trend of the contribution of each value over time or other category data. A stacked area chart in 3-D is displayed in the same way but uses a 3-D perspective. A 3-D perspective is not a true 3-D chart — a third value axis (depth axis) is not used.

• 100% stacked area and 100% stacked area in 3-D  100% stacked area charts display the trend of the percentage that each value contributes over time or other category data. A 100% stacked area chart in 3-D is displayed in the same way but uses a 3-D perspective. A 3-D perspective is not a true 3-D chart — a third value axis (depth axis) is not used.

Tip  To create an area chart, see Present your data in an area chart.

 Top of Page

XY (scatter) charts

Data that is arranged in columns and rows on a worksheet can be plotted in an xy (scatter) chart. Scatter charts show the relationships among the numeric values in several data series, or plots two groups of numbers as one series of xy coordinates.

A scatter chart has two value axes, showing one set of numeric data along the horizontal axis (x-axis) and another along the vertical axis (y-axis). It combines these values into single data points and displays them in irregular intervals, or clusters. Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data.

Consider using a scatter chart 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.

To arrange data on a worksheet for a scatter chart, you should place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

Scatter charts have the following chart subtypes:

• Scatter with only markers  This type of chart compares pairs of values. Use a scatter chart with data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) but without lines when you use many data points and connecting lines would make the data more difficult to read. You can also use this chart type when you do not have to show connectivity of the data points.

• Scatter with smooth lines and scatter with smooth lines and markers  This type of chart displays a smooth curve that connects the data points. Smooth lines can be displayed with or without markers. Use a smooth line without markers if there are many data points.

• Scatter with straight lines and scatter with straight lines and markers  This type of chart displays straight connecting lines between data points. Straight lines can be displayed with or without markers.

Tip  To create a scatter chart, see Present your data in a scatter chart or a line chart.

 Top of Page

Stock charts

Data that is arranged in columns or rows in a specific order on a worksheet can be plotted in a stock chart. As its name implies, a stock chart is most often used to illustrate the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you could use a stock chart to indicate the fluctuation of daily or annual temperatures. You must organize your data in the correct order to create stock charts.

The way stock chart data is organized in the worksheet is very important. 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 have the following chart sub-types:

• High-low-close  The high-low-close stock chart is often used to illustrate 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).

Tip  To create a stock chart, see Present your data in a stock chart.

 Top of Page

Surface charts

Data that is arranged in columns or rows on a worksheet can be plotted in a surface chart. A surface chart is useful when you want 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.

You can use a surface chart when both categories and data series are numeric values.

Surface charts have the following chart subtypes:

• 3-D surface  3-D surface charts show trends in values across two dimensions in a continuous curve. Color bands in a surface chart do not represent the data series; they represent the distinction between the values. This chart shows a 3-D view of the data, which can be imagined as a rubber sheet stretched over a 3-D column chart. It is typically used to show relationships between large amounts of data that may otherwise be difficult to see.

• Wireframe 3-D surface  When displayed without color on the surface, a 3-D surface chart is called a wireframe 3-D surface chart. This chart shows only the lines. A 3-D surface chart that is displayed without color bands on any surface is called a wireframe 3-D surface chart. This chart shows only the lines.

 Note    A wireframe 3-D surface chart is not easy to read, but this chart type is useful for faster plotting of large data sets.

• Contour  Contour charts are surface charts viewed from above, similar to 2-D topographic maps. In a contour chart, color bands represent specific ranges of values. The lines in a contour chart connect interpolated points of equal value.

• Wireframe contour  Wireframe contour charts are also surface charts viewed from above. Without color bands on the surface, a wireframe chart shows only the lines.

 Note    Wireframe contour charts are not easy to read. You may want to use a 3-D surface chart instead.

Tip  To create a surface chart, see Present your data in a surface chart.

 Top of Page

Doughnut charts

Data that is arranged in columns or rows only on a worksheet can be plotted in a doughnut chart. Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but it can contain more than one data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

 Note    Doughnut charts are not easy to read. You may want to use a stacked column or stacked bar chart instead.

Doughnut charts have the following chart subtypes:

• Doughnut  Doughnut charts display data in rings, where each ring represents a data series. If percentages are displayed in data labels, each ring will total 100%.

• Exploded Doughnut  Much like exploded pie charts, exploded doughnut charts display the contribution of each value to a total while emphasizing individual values, but they can contain more than one data series.

Tip  To create a doughnut chart, see Present your data in a doughnut chart.

 Top of Page

Bubble charts

Data that is 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, can be plotted in a bubble chart.

For example, you would organize your data as shown in the following example.

Bubble charts have the following chart subtypes:

• Bubble or bubble with 3-D effect  Both bubble chart types compare sets of three values instead of two. The third value determines the size of the bubble marker. You can choose to display bubbles in 2-D format or with a 3-D effect.

Tip  To create a bubble chart, see Present your data in a bubble chart.

 Top of Page

Radar charts

Data that is arranged in columns or rows on a worksheet can be plotted in a radar chart. Radar charts compare the aggregate values of several data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

Radar charts have the following chart subtypes:

• Radar and radar with markers  With or without markers for individual data points, radar charts display changes in values relative to a center point.

• Filled radar  In a filled radar chart, the area covered by a data series is filled with a color.

Tip  To create a radar chart, see Present your data in a radar chart.

 Top of Page

Other types of charts that you can create in Excel

If you do not see the chart type that you want to create on the list of available chart types, there may be another way to create it in Excel.

For example, you can create the following charts:

• Gantt charts and floating column charts  You can use a chart type to simulate these types of charts. For example, you can use a bar chart to simulate a Gantt chart, or you can use a column chart to simulate a floating column chart that depicts minimum and maximum values. For more information, see Present your data in a Gantt chart in Excel and Present your data in a column chart.
• Combination charts  To emphasize different types of information in a chart, you can combine two or more chart types in that chart. For example, you can combine a column chart with a line chart for an instant visual effect that might make the chart easier to understand. For more information, see Present your data in a combination chart.
• Organization charts  You can insert an SmartArt graphic to create an organization chart, flow chart, or hierarchy chart. For more information, see Create an organization chart.
• Histogram and pareto charts  To create a histogram or pareto (sorted histogram) chart in Excel, you can use the Data Analysis Tools that become available after you load the Analysis ToolPak, an Excel add-in program that is available when you install Microsoft Office or Excel. For more information, see Present your data in a histogram.

 Top of Page

See Also

• Change the chart type of an existing chart
• Create a chart
• Create an organization chart
• Present your data in a combination chart
• Present your data in a Gantt chart in Excel
• Present your data in a histogram
• Reuse a favorite chart by using a chart template

 

 

Excel > Charts > Creating charts

Create a chart

Excel 2007

Tags  bar chart; chart; chart data; chart template; chart type; chart wizard; create a chart; flow charts; graph; insert chart; pivot

What are tags?

Microsoft Office Excel 2007 no longer provides the chart wizard. Instead, you can create a basic chart by clicking the chart type that you want on the Microsoft Office Fluent user interface Ribbon. To create a chart that displays the details that you want, you can then continue with the next steps of the following step-by-step process.

What do you want to do?

Learn about charts

Step 1: Create a basic chart

Step 2: Change the layout or style of a chart

Apply a predefined chart layout

Apply a predefined chart style

Change the layout of chart elements manually

Change the format of chart elements manually

Step 3: Add or remove titles or data labels

Add a chart title

Add axis titles

Link a title to a worksheet cell

Add data labels

Remove titles or data labels from a chart

Step 4: Show or hide a legend

Step 5: Display or hide chart axes or gridlines

Display or hide primary axes

Display or hide secondary axes

Display or hide gridlines

Step 6: Move or resize a chart

Move a chart

Resize a chart

Step 7: Save a chart as a template

Learn about charts

Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data.

To create a chart in Excel, you start by entering the numeric data for the chart on a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.). Then you can plot that data into a chart by selecting the chart type that you want to use on the Office Fluent Ribbon (Insert tab, Charts group).

Worksheet data

Chart created from worksheet data

Excel supports many types of charts to help you display data in ways that are meaningful to your audience. When you create a chart or change an existing chart, you can select from a variety of chart types (such as a column chart or a pie chart) and their subtypes (such as a stacked column chart or a pie in 3-D chart). You can also create a combination chart by using more than one chart type in your chart.

Example of a combination chart that uses a column and line chart type.

For more information about the chart types that you can select in Excel, see Available chart types.

Getting to know the elements of a chart

A chart has many elements. Some of these elements are displayed by default, others can be added as needed. You can change the display of the chart elements by moving them to other locations in the chart, resizing them, or by changing the format. You can also remove chart elements that you do not want to display.

The chart area (chart area: The entire chart and all its elements.) of the chart.

The plot area (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.) of the chart.

The data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) of the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that are plotted in the chart.

The horizontal (category) and vertical (value) axis (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) along which the data is plotted in the chart.

The legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) of the chart.

A chart and axis title (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.) that you can use in the chart.

A data label (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.) that you can use to identify the details of a data point in a data series.

Modifying a basic chart to meet your needs

After you create a chart, you can modify any one of its elements. For example, you might want to change the way that axes are displayed, add a chart title, move or hide the legend, or display additional chart elements.

To modify a chart, you can:

• Change the display of chart axes  You can specify the scale of axes and adjust the interval between the values or categories that are displayed. To make your chart easier to read, you can also add tick marks (tick marks and tick-mark labels: Tick marks are small lines of measurement, similar to divisions on a ruler, that intersect an axis. Tick-mark labels identify the categories, values, or series in the chart.) to an axis, and specify the interval at which they will appear.
• Add titles and data labels to a chart  To help clarify the information that appears in your chart, you can add a chart title, axis titles, and data labels.
• Add a legend or data table   You can show or hide a legend, change its location, or modify the legend entries. In some charts, you can also show a data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) that displays the legend keys (legend keys: Symbols in legends that show the patterns and colors assigned to the data series (or categories) in a chart. Legend keys appear to the left of legend entries. Formatting a legend key also formats the data marker that's associated with it.) and the values that are presented in the chart.
• Apply special options for each chart type  Special lines (such as high-low lines and trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.)), bars (such as up-down bars and error bars), data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), and other options are available for different chart types.

Applying a predefined chart layout and chart style for a professional look

Instead of manually adding or changing chart elements or formatting the chart, you can quickly apply a predefined chart layout and chart style to your chart. Excel provides a variety of useful predefined layouts and styles that you can select, but you can fine-tune a layout or style if it is needed by making manual changes to the layout and format of individual chart elements, such as the chart area, plot area, data series, or legend of the chart.

When you apply a predefined chart layout, a specific set of chart elements (such as titles, a legend, a data table, or data labels) are displayed in a specific arrangement in your chart. You can select from a variety of layouts that are provided for each chart type.

When you apply a predefined chart style, the chart is formatted based on the document theme (theme: A combination of theme colors, theme fonts, and theme effects. A theme may be applied to a file as a single selection.) that you have applied, so that your chart matches your organization's or your own theme colors (theme colors: A set of colors that is used in a file. Theme colors, theme fonts, and theme effects compose a theme.) (a set of colors), theme fonts (theme fonts: A set of major and minor fonts that is applied to a file. Theme fonts, theme colors, and theme effects compose a theme.) (a set of heading and body text fonts), and theme effects (theme effects: A set of visual attributes that is applied to elements in a file. Theme effects, theme colors, and theme fonts compose a theme.) (a set of lines and fill effects).

You cannot create your own chart layouts or styles, but you can create chart templates that include the chart layout and formatting that you want.

Adding eye-catching formatting to a chart

In addition to applying a predefined chart style, you can easily apply formatting to individual chart elements such as data markers, the chart area, the plot area, and the numbers and text in titles and labels to give your chart a custom, eye-catching look. You can apply specific shape styles and WordArt styles, and you can also format the shapes and text of chart elements manually.

To add formatting, you can:

• Fill chart elements  You can use colors, textures, pictures, and gradient fills to help draw attention to specific chart elements.
• Change the outline of chart elements  You can use colors, line styles, and line weights to emphasize chart elements.
• Add special effects to chart elements  You can apply special effects, such as shadow, reflection, glow, soft edges, bevel, and 3-D rotation to chart element shapes, which gives your chart a finished look.
• Format text and numbers  You can format text and numbers in titles, labels, and text boxes on a chart as you would text and numbers on a worksheet. To make text and numbers stand out, you can even apply WordArt styles.

Reusing charts by creating chart templates

If you want to reuse a chart that you customized to meet your needs, you can save that chart as a chart template (*.crtx) in the chart templates folder. When you create a chart, you can then apply the chart template just as you would any other built-in chart type. In fact, chart templates are custom chart types — you can also use them to change the chart type of an existing chart. If you use a specific chart template frequently, you can save it as the default chart type.

 Top of Page

Step 1: Create a basic chart

For most charts, such as column and bar charts, you can plot the data that you arrange in rows or columns on a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) into a chart. However, some chart types (such as pie and bubble charts) require a specific data arrangement.

1. On the worksheet, arrange the data that you want to plot in a chart.

The data can be arranged in rows or columns — Excel automatically determines the best way to plot the data in the chart. Some chart types (such as pie and bubble charts) require a specific data arrangement as described in the following table.

2. Select the cells that contain the data that you want to use for the chart.

Tip  If you select only one cell, Excel automatically plots all cells that contain data that is adjacent to that cell into a chart. If the cells that you want to plot in a chart are not in a continuous range, you can select nonadjacent cells or ranges as long as the selection forms a rectangle. You can also hide the rows or columns that you do not want to plot in the chart.

How to select cells, ranges, rows, or columns

3. On the Insert tab, in the Charts group, do one of the following:
   • Click the chart type, and then click a chart subtype that you want to use.
   • To see all available chart types, click a chart type, and then click All Chart Types to display the Insert Chart dialog box, click the arrows to scroll through all available chart types and chart subtypes, and then click the ones that you want to use.

Tip  A ScreenTip displays the chart type name when you rest the mouse pointer over any chart type or chart subtype. For more information about the chart types that you can use, see Available chart types.

4. By default, the chart is placed on the worksheet as an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.). If you want to place the chart in a separate chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.), you can change its location by doing the following:
   • Click the embedded chart to select it.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

• On the Design tab, in the Location group, click Move Chart.

• Under Choose where you want the chart to be placed, do one of the following:
  • To display the chart in a chart sheet, click New sheet.

Tip  If you want to replace the suggested name for the chart, you can type a new name in the New sheet box.

• To display the chart as an embedded chart in a worksheet, click Object in, and then click a worksheet in the Object in box.
5. Excel automatically assigns a name to the chart, such as Chart1 if it is the first chart that you create on a worksheet. To change the name of the chart, do the following:
   • Click the chart.
   • On the Layout tab, in the Properties group, click the Chart Name text box.

Tip  If necessary, click the Properties icon in the Properties group to expand the group.

• Type a new name.
• Press ENTER.

 Notes 

• To quickly create a chart that is based on the default chart type, select the data that you want to use for the chart, and then press ALT+F1 or F11. When you press ALT+F1, the chart is displayed as an embedded chart; when you press F11, the chart is displayed on a separate chart sheet.
• When you create a chart, Excel determines the orientation of the data series based on the number of worksheet rows and columns that are included in the chart. After you create a chart, you can change the way that worksheet rows and columns are plotted in the chart by switching rows to columns or vice versa. For more information, see Plot data series from worksheet rows or columns.
• After you create a chart, you can quickly change the chart type of the whole chart to give the chart a different look, or you can select a different chart type for any single data series, which turns the chart into a combination chart. For more information, see Change the chart type of an existing chart.
• If you no longer need a chart, you can delete it. Click the chart to select it, and then press DELETE.

 Top of Page

Step 2: Change the layout or style of a chart

After you create a chart, you can instantly change its look. Instead of manually adding or changing chart elements or formatting the chart, you can quickly apply a predefined layout and style to your chart. Excel provides a variety of useful predefined layouts and styles (or quick layouts and quick styles) that you can select from, but you can customize a layout or style as needed by manually changing the layout and format of individual chart elements.

Apply a predefined chart layout

1. Click the chart that you want to format by using a predefined chart layout.

Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Chart Layouts group, click the chart layout that you want to use.

 Note    When the size of the Excel window is reduced, chart layouts will be available in the Quick Layout gallery in the Chart Layouts group.

Tip  To see all available layouts, click More .

 Top of Page

Apply a predefined chart style

1. Click the chart that you want to format by using a predefined chart style.

Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

 Note    When the size of the Excel window is reduced, chart styles will be available in the Chart Quick Styles gallery in the Chart Styles group.

Tip  To see all predefined chart styles, click More .

 Top of Page

Change the layout of chart elements manually

1. Click the chart or the chart element for which you want to change the layout, or do the following to select a chart element from a list of chart elements.
   1. Click anywhere in the chart to display the Chart Tools.
   2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

2. On the Layout tab, in the Labels, Axes, or Background group, click the chart element that you want to change, and then click the layout option that you want.

 Note    The layout options that you select are applied to the chart element that you have selected. For example, if you have the entire chart selected, data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.) will be applied to all data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.). If you have a single data point selected, data labels will only be applied to the selected data series or data point.

 Top of Page

Change the format of chart elements manually

1. Click the chart or the chart element for which you want to change the style, or do the following to select a chart element from a list of chart elements.
   1. Click a chart to display the Chart Tools.
   2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.

2. Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.
3. On the Format tab, do any of the following:
   1. To format any selected chart element, in the Current Selection group, click Format Selection, and then select the formatting options that you want.
   2. To format the shape of a selected chart element, in the Shape Styles group, click the style that you want, or click Shape Fill, Shape Outline, or Shape Effects, and then select the formatting options that you want.
   3. To format the text in a selected chart element by using WordArt, in the WordArt Styles group, click the style that you want, or click Text Fill, Text Outline, or Text Effects, and then select the formatting options that you want.

 Note    After you apply a WordArt style, you cannot remove the WordArt format. If you do not want the WordArt style that you applied, you can select another WordArt style, or you can click Undo on the Quick Access Toolbar to return to the previous text format.

Tip  To use regular text formatting to format the text in chart elements, you can right-click or select the text, and then click the formatting options that you want on the Mini toolbar. You can also use the formatting buttons on the Ribbon (Home tab, Font group).

 Top of Page

Step 3: Add or remove titles or data labels

To make a chart easier to understand, you can add titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), such as a chart title and axis titles. Axis titles are typically available for all axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) that can be displayed in a chart, including depth (series) axes in 3-D charts. Some chart types (such as radar charts) have axes, but they cannot display axis titles. Chart types that do not have axes (such as pie and doughnut charts) cannot display axis titles either.

You can also link chart and axis titles to corresponding text in worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) cells by creating a reference to those cells. Linked titles are automatically updated in the chart when you change the corresponding text on the worksheet.

To quickly identify a data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in a chart, you can add data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.) to the data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) of the chart. By default, the data labels are linked to values on the worksheet, and they update automatically when changes are made to these values.

Add a chart title

1. Click the chart to which you want to add a title.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, click Chart Title.

3. Click Centered Overlay Title or Above Chart.
4. In the Chart Title text box that appears in the chart, type the text that you want.

Tip  To insert a line break, click to place the pointer where you want to break the line, and then press ENTER.

5. To format the text, select it, and then click the formatting options that you want on the Mini toolbar.

Tip  You can also use the formatting buttons on the Ribbon (Home tab, Font group). To format the whole title, you can right-click it, click Format Chart Title, and then select the formatting options that you want.

 Top of Page

Add axis titles

1. Click the chart to which you want to add axis titles.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, click Axis Titles.

3. Do any of the following:
   • To add a title to a primary horizontal (category) axis, click Primary Horizontal Axis Title, and then click the option that you want.

Tip  If the chart has a secondary horizontal axis, you can also click Secondary Horizontal Axis Title.

• To add a title to primary vertical (value) axis, click Primary Vertical Axis Title, and then click the option that you want.

Tip  If the chart has a secondary vertical axis, you can also click Secondary Vertical Axis Title.

• To add a title to a depth (series) axis, click Depth Axis Title, and then click the option that you want.

 Note    This option is only available when the selected chart is a true 3-D chart, such as a 3-D column chart.

4. In the Axis Title text box that appears in the chart, type the text that you want.

Tip  To insert a line break, click to place the pointer where you want to break the line, and then press ENTER.

5. To format the text, select it, and then click the formatting options that you want on the Mini toolbar.

Tip  You can also use the formatting buttons on the Ribbon (Home tab, Font group). To format the whole title, you can right-click it, click Format Axis Title , and then select the formatting options that you want.

 Notes 

• If you switch to another chart type that does not support axis titles (such as a pie chart), the axis titles will no longer be displayed. The titles will be displayed again when you switch back to a chart type that does support axis titles.
• Axis titles that are displayed for secondary axes will be lost when you switch to a chart type that does not display secondary axes.

 Top of Page

Link a title to a worksheet cell

1. On a chart, click the chart or axis title that you want to link to a worksheet cell.
2. On the worksheet, click in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), and then type an equal sign (=).
3. Select the worksheet cell that contains the data or text that you want to display in your chart.

Tip  You can also type the reference to the worksheet cell in the formula bar. Include an equal sign, the sheet name, followed by an exclamation point; for example, =Sheet1!F2

4. Press ENTER.

 Top of Page

Add data labels

1. On a chart, do one of the following:
   • To add a data label to all data points of all data series, click the chart area (chart area: The entire chart and all its elements.).
   • To add a data label to all data points of a data series, click anywhere in the data series that you want to label.
   • To add a data label to a single data point in a data series, click the data series that contains the data point that you want to label, and then click the data point that you want to label.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, click Data Labels, and then click the display option that you want.

 Note    Depending on the chart type that you used, different data label options will be available.

Tip  For more information about how to change data label entries or how to reposition data labels, see Add or remove data labels in a chart.

 Top of Page

Remove titles or data labels from a chart

1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, do one of the following:
   • To remove a chart title, click Chart Title, and then click None.
   • To remove an axis title, click Axis Title, click the type of axis title that you want to remove, and then click None.
   • To remove data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.), click Data Labels, and then click None.

Tip  To quickly remove a title or data label, click it, and then press DELETE.

 Top of Page

Step 4: Show or hide a legend

When you create a chart, the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) appears, but you can hide the legend or change its location after you create the chart.

1. Click the chart in which you want to show or hide a legend.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, click Legend.

3. Do one of the following:
   • To hide the legend, click None.

Tip  To quickly remove a legend or a legend entry from a chart, you can select it, and then press DELETE. You can also right-click the legend or a legend entry, and then click Delete.

• To display a legend, click the display option that you want.

 Note    When you click one of the display options, the legend moves, and the plot area (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.) automatically adjusts to make room for it. If you move and size the legend by using the mouse, the plot area does not automatically adjust.

• For additional options, click More Legend Options, and then select the display option that you want.

Tip  By default, a legend does not overlap the chart. If you have space constraints, you might be able to reduce the size of the chart by clearing the Show the legend without overlapping the chart check box.

Tip  When a chart has a legend displayed, you can modify the individual legend entries. For more information, see Modify chart legend entries.

 Top of Page

Step 5: Display or hide chart axes or gridlines

When you create a chart, primary axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.) are displayed for most chart types. You can turn them on or off as needed. When you add axes, you can specify the level of detail that you want the axes to display. A depth axis is displayed when you create a 3-D chart.

When the values in a chart vary widely from data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) to data series, or when you have mixed types of data (for example, price and volume), you can plot one or more data series on a secondary vertical (value) axis. The scale of the secondary vertical axis reflects the values for the associated data series. After you add a secondary vertical axis to a chart, you can also add a secondary horizontal (category) axis, which might be useful in an xy (scatter) chart or bubble chart.

To make a chart easier to read, you can display or hide the horizontal and vertical chart gridlines that extend from any horizontal and vertical axes across the plot area (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.) of the chart.

Display or hide primary axes

1. Click the chart for which you want to display or hide axes.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Axes group, click Axes, and then do one of the following:
   • To display an axis, click Primary Horizontal Axis, Primary Vertical Axis, or Depth Axis (on a 3-D chart), and then click the axis display option that you want.
   • To hide an axis, click Primary Horizontal Axis, Primary Vertical Axis, or Depth Axis (on a 3-D chart), and then click None.
   • To specify detailed axis display and scaling options, click Primary Horizontal Axis, Primary Vertical Axis, or Depth Axis (on a 3-D chart), and then click More Primary Horizontal Axis Options, More Primary Vertical Axis Options, or More Depth Axis Options.

For more information about how to change axis display options and scaling, see the following articles.

• Change the display of chart axes
• Change the scale of the horizontal (category) axis in a chart
• Change the scale of the vertical (value) axis in a chart
• Change the scale of the depth (series) axis in a chart

 Top of Page

Display or hide secondary axes

1. In a chart, click the data series that you want to plot along a secondary vertical axis, or do the following to select the data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series that you want to plot along a secondary vertical axis.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Click Series Options if it is not selected, and then under Plot Series On, click Secondary Axis and then click Close.
4. On the Layout tab, in the Axes group, click Axes.

5. Do one of the following:
   1. To display a secondary vertical axis, click Secondary Vertical Axis, and then click the display option that you want.

Tip  To help distinguish the secondary vertical axis, you can change the chart type for just one data series. For example, you can change one data series to a line chart.

6. To display a secondary horizontal axis, click Secondary Horizontal Axis, and then click the display option that you want.

 Note    This option is available only after you display a secondary vertical axis.

7. To hide a secondary axis, click Secondary Vertical Axis or Secondary Horizontal Axis, and then click None.

Tip  You can also click the secondary axis that you want to delete, and then press DELETE.

 Top of Page

Display or hide gridlines

1. Click the chart to which you want to add chart gridlines.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Axes group, click Gridlines.

3. Do the following:
   • To add horizontal gridlines to the chart, point to Primary Horizontal Gridlines, and then click the option that you want. If the chart has a secondary horizontal axis, you can also click Secondary Horizontal Gridlines.
   • To add vertical gridlines to the chart, point to Primary Vertical Gridlines, and then click the option that you want. If the chart has a secondary vertical axis, you can also click Secondary Vertical Gridlines.
   • To add depth gridlines to a 3-D chart, point to Depth Gridlines, and then click the option that you want. This option is only available when the selected chart is a true 3-D chart, such as a 3-D column chart.
   • To hide chart gridlines, point to Primary Horizontal Gridlines, Primary Vertical Gridlines, or Depth Gridlines (on a 3-D chart), and then click None. If the chart has a secondary axes, you can also click Secondary Horizontal Gridlines or Secondary Vertical Gridlines, and then click None.
   • To quickly remove chart gridlines, select them, and then press DELETE.

 Top of Page

Step 6: Move or resize a chart

You can move a chart to any location on a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) or to a new or existing worksheet. You can also change the size of the chart for a better fit.

Move a chart

• To move a chart, drag it to the location that you want.

 Top of Page

Resize a chart

To resize a chart, do one of the following:

• Click the chart, and then drag the sizing handles to the size that you want.
• On the Format tab, in the Size group, enter the size in the Shape Height and Shape Width box.

Tip  For more sizing options, on the Format tab, in the Size group, click the Dialog Box Launcher . In the Size and Properties dialog box, on the Size tab, you can select options to size, rotate, or scale the chart. On the Properties tab, you can specify how you want the chart to move or size with the cells on the worksheet.

 Top of Page

Step 7: Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I create a pictograph in Excel?
• Need to create an excel chart 21 depts jan-feb 04-06 how?
• How do i create a chart with 4 sets of data?
• How do I set up a line - bar graph bar first and line all others?
• How do i build a stacked bar graph with three variables?
• How do I set up a clustered stacked column graph? Is it possible?
• How to change color scheme in pie chart?
• How do I insert a marker on an Excel bar chart?
• How to insert a symbol in a chart legend?

See Also

• Add or remove series lines, drop lines, high-low lines, or up-down bars in a chart
• Add, change, or remove a trendline in a chart
• Add, change, or remove error bars in a chart
• Display hidden data and empty cells in a chart
• Present your data in a combination chart
• Print a chart

 

 

Excel > Charts > Creating charts

Change the chart type of an existing chart

Excel 2007

For most 2-D charts, you can change the chart type of the whole chart to give the chart a different look, or you can select a different chart type for any single data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), which turns the chart into a combination chart.

For bubble charts and most 3-D charts, you can only change the chart type of the whole chart.

Important  To complete this procedure, you must have an existing chart. For more information about how to create a chart, see Create a chart.

1. Do one of the following:
   • To change the chart type of the whole chart, click the chart area (chart area: The entire chart and all its elements.) or plot area (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.) of the chart to display the chart tools.
   • To change the chart type of a data series, click that data series.

 Note    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.

2. Tip  This displays the chart tools, adding the Design, Layout, and Format tabs.
3. On the Design tab, in the Type group, click Change Chart Type.

4. In the Change Chart Type dialog box, do one of the following:
   • Click a chart type in the first box, and then click the chart subtype that you want to use in the second box.

For more information about the chart types that you can use, see Available chart types.

• If you saved a chart type as a template, click Templates, and then click the chart template that you want to use in the second box.

Tip  If you use a specific chart type frequently when you create a chart, you may want to set that chart type as the default chart type. After you select the chart type and the chart subtype in the Change Chart Type dialog box, click Set as Default Chart.

See Also

• Add or remove a secondary axis in a chart
• Plot data series on a different axis
• Present your data in a combination chart
• Rename a chart

 

 

Excel > Charts > Creating charts

Copy a chart

Excel 2007

In Microsoft Office Excel, you can copy an existing chart to another worksheet or chart sheet, a different workbook, or to another location on the same worksheet. To copy a chart in the same worksheet, you can drag the chart by using the mouse.

You can also copy an Excel chart to other Microsoft Office programs, such as PowerPoint and Word. If needed, you can copy a static picture of a chart to another workbook location or to a document that you created by using another program.

In this article

Copy a chart to another workbook location

Copy a chart by using the mouse

Copy a chart to a PowerPoint presentation

Copy a chart to a Word document

Copy a picture of a chart to another workbook location or document

Copy a chart to another workbook location

1. In Excel, select the chart that you want to copy.
2. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

3. Click the worksheet where you want to paste the copied chart.

Tip  To paste the copied chart in another workbook, open that workbook, and then click the worksheet or chart sheet where you want to paste the copied chart.

4. On the Home tab, in the Clipboard group, click Paste.

Keyboard shortcut  You can also press CTRL+V.

5. If you paste a chart on a worksheet, click Paste Options next to the chart, and then do one of the following:
   • To paste the chart in its original format, click Keep Source Formatting.
   • To paste the chart and format it by using the document theme that is applied to the presentation, click Use Destination Theme.

 Note    If you copied a chart from another program, different paste options will be available on the worksheet, depending on the program that you copied from.

Tip  You can also paste a copied chart as a static picture in PNG format (.png). On the Home tab, in the Clipboard group, click the arrow below Paste, click As Picture, and then click Paste as Picture. A chart that is pasted as a picture does not update when the source data changes. Also, a chart that is pasted as a picture might have a reduced print quality.

 Top of Page

Copy a chart by using the mouse

1. Right-click the chart that you want to copy, and then drag it to the location that you want.
2. After you release the mouse, click Copy Here.

 Top of Page

Copy a chart to a PowerPoint presentation

1. In Excel, select the chart that you want to copy to a PowerPoint presentation.
2. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

3. Click in the PowerPoint presentation where you want to paste the copied chart.
4. On the Home tab, in the Clipboard group, click Paste.

Keyboard shortcut  You can also press CTRL+V.

5. Click Paste Options next to the chart, and then do one of the following:
   • To paste the chart with a link to its source data, click Chart (linked to Excel data).
   • To paste the chart and to include access to the whole workbook in the presentation, click Excel Chart (entire workbook).
   • To paste the chart as a static picture, click Paste as Picture.
   • To paste the chart in its original format, click Keep Source Formatting.
   • To paste the chart and format it by using the document theme that is applied to the presentation, click Use Destination Theme.

Tips

• To paste a chart in another format, click the arrow on the Paste button, and then click Paste Special. In the As list, select the format that you want to use.
  • To paste a chart as a static picture, click any Picture or Bitmap option. Picture format generally provides better display quality than Bitmap format.
  • Click Microsoft Office Graphic Object to paste a chart as a graphic object that can be edited by using the chart tools in PowerPoint. This option is not available when you are using Compatibility Mode in PowerPoint.
  • Click Microsoft Office Excel Chart Object, and then click Paste link to paste a chart as an embedded object (embedded object: Information (object) contained in a source file and inserted into a destination file. Once embedded, the object becomes part of the destination file. Changes you make to the embedded object are reflected in the destination file.) that is linked to the source data of the original Excel chart so that it is updated when the chart data changes. Microsoft Office Excel Chart Object is not available when you are using Compatibility Mode in PowerPoint.
  • To paste the chart as an Object Linking and Embedding (OLE) (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) PowerPoint program icon (instead of pasting the actual chart) into a presentation, click Microsoft Office Excel Chart Object, and then select the Display as icon check box. You can click the icon to open the application, and then view the chart. Microsoft Office Excel Chart Object is not available when you are using Compatibility Mode in PowerPoint.
• You can also create a Microsoft Office Excel 2007 chart directly in Microsoft Office PowerPoint 2007 by clicking the Chart button on the Ribbon (Insert tab, Illustrations group), and then by using the chart tools to modify or format the chart. Charts that you create in PowerPoint will be embedded in the PowerPoint presentation, and their source data is stored in an Excel worksheet that is incorporated in the PowerPoint file.

 Note    The Ribbon is a component of the Microsoft Office Fluent user interface.

 Top of Page

Copy a chart to a Word document

1. In Excel, select the chart that you want to copy to a Word document.
2. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

3. In the Word document, click where you want to paste the copied chart.
4. On the Home tab, in the Clipboard group, click Paste.

Keyboard shortcut  You can also press CTRL+V.

5. Click Paste Options next to the chart, and then do one of the following:
   • To paste the chart with a link to its source data, click Chart (linked to Excel data).
   • To paste the chart and to include access to the whole workbook in the document, click Excel Chart (entire workbook).
   • To paste the chart as a static picture, click Paste as Picture.
   • To paste the chart in its original format, click Keep Source Formatting.
   • To paste the chart and format it by using the document theme that is applied to the document, click Use Destination Theme.

 Note    The Paste Options button is not available when you paste a chart in a document that is opened in Compatibility Mode in Word. In Compatibility Mode, the chart is pasted as a static picture.

 Top of Page

Copy a picture of a chart to another workbook location or document

1. In Excel, select the chart that you want to copy as a picture.
2. On the Home tab, in the Clipboard group, click the arrow below Paste, click As Picture, and then click Copy as Picture.

3. In the Copy Picture dialog box, do the following:
   • Under Appearance, click the option that you want to use.
   • Under Format, click the option that you want to use.

Tip  For the best picture quality, make sure that the As shown on screen and Picture options are selected. However, to preserve picture quality, items that you copy by using the As shown on screen and Picture options are always pasted at 100% of their original size. For example, if you scale a worksheet at 75%, a copied item from that worksheet may appear larger because it is pasted at actual size.

4. Click OK.
5. On a worksheet or chart sheet, click where you want to paste the picture.

Tip  To paste the picture in a document of another program, open that document, and then use the Paste commands that are available in that program.

6. On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut  You can also press CTRL+V.

7. If you want to format the picture, do the following:
   • Select the picture.

Tip  This displays the Picture Tools, adding the Format tab.

• On the Format tab, in the Adjust group, Picture Style group, Arrange group, or Size group, and then click the formatting options that you want.

 Top of Page

See Also

• Create a chart

 

 

Excel > Charts > Creating charts

Reuse a favorite chart by using a chart template

Excel 2007

To reuse a favorite chart that you customized to meet your needs, you can save that chart as a chart template (*.crtx) in the charts template folder. A chart template that you create is in reality a custom chart type that you can apply like any other chart type.

Instead of re-creating the chart, you can apply the chart template to the worksheet data when you create a new chart. You can also change the chart type of an existing chart by applying a chart template.

If you no longer need a specific chart template, you can remove it from the charts template folder or you can delete it from your computer.

What do you want to do?

Save a chart as a chart template

Apply a chart template

Remove or delete a chart template

Save a chart as a chart template

1. Click the chart that you want to save as a template.

Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Type group, click Save As Template.

3. In the Save in box, make sure that the Charts folder is selected.

 Note    The Charts folder is typically selected by default. Chart templates that you save in this folder will automatically be added to the Templates folder in the Insert Chart and Change Chart Type dialog box so that you can select it when you create or update a chart.

4. In the File name box, type an appropriate name for the chart template.

 Top of Page

Apply a chart template

You can apply a chart template when you create a new chart, or you can change the chart type of an existing chart to chart template.

Apply a chart template to a new chart

1. On a worksheet, select the data that you want to plot in the chart.
2. On the Insert tab, in the Charts group, click any chart type, and then click All Chart Types.

Tip  You can also click the Dialog Box Launcher next to Charts in the Charts group on the Insert tab.

3. In the Insert Chart dialog box, click Templates in the first box, and then click the template that you want to use in the second box under My Templates.

 Note    If a chart template is located in a folder other than the Charts folder, click Manage Templates, locate the chart template, and then copy or move it to the Charts folder under Templates.

Apply a chart template to an existing chart

1. Click the chart area (chart area: The entire chart and all its elements.) of the chart to which you want to apply the chart template.
2. On the Design tab, in the Type group, click Change Chart Type.

3. In the Change Chart Type dialog box, click Templates in the first box, and then click the template that you want to use in the second box under My Templates.

 Note    If a chart template is located in a folder other than the Charts folder, click Manage Templates, locate the chart template, and then copy or move it to the Charts folder under Templates.

 Top of Page

Remove or delete a chart template

1. On the Insert tab, in the Charts group, click any chart type, and then click All Chart Types.

Tip  You can also click the Dialog Box Launcher next to Charts in the Charts group on the Insert tab.

2. Click Manage Templates.
3. Do one of the following:
   • To remove the chart template from the Charts folder, drag it to the folder where you want to store it.
   • To delete the chart template from your computer, right-click it, and then click Delete.

 Top of Page

See Also

• Create a chart
• Create and use an Excel template
• Rename a chart

 

 

Excel > Charts > Creating charts

Display hidden data and empty cells in a chart

By default, data that is hidden in rows and columns in the worksheet is not displayed in a chart, and empty cells are displayed as gaps. You can, however, display the hidden data and change the way that the empty cells are displayed. Rather then displaying gaps, you can display empty cells as zero values, or you can span the gaps with a line.

1. Click a chart in which you want to display hidden data and empty cells.

Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

3. Click Hidden and Empty Cells, and then do one of the following:
   • To define how empty cells are displayed in the chart, click Gaps, Zero, or Connect data points with line.
   • To display hidden cells in the chart, select the Show data in hidden rows and columns check box.

See Also

• Change the plotting order of categories, values, or data series
• Create a chart
• Plot data series on a different axis

 

 

Excel > Charts > Creating charts

Plot data series on a different axis

Excel 2007

When you create a chart, Microsoft Office Excel determines the axis on which the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) are plotted, based on the number of worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) rows and columns that are included in the chart. However, after you create a chart, you can change the way that worksheet rows and columns are plotted in the chart by switching rows to columns or vice versa.

For example, when you create a chart from two rows and two columns of worksheet data, Excel plots the rows of data on the vertical (value) axis), and the columns of data on the horizontal (category) axis. When you reverse this by switching rows to columns, the columns of data will be plotted on the vertical axis, and the rows of data will be plotted on the horizontal axis instead.

Important  To complete this procedure, you must have an existing chart. For more information about how to create a chart, see Create a chart.

1. Click the chart that contains the data that you want to plot differently.

Tip  This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Switch Row/Column.

See Also

• Add or remove a secondary axis in a chart
• Change the display of chart axes
• Change the plotting order of categories, values, or data series
• Display hidden data and empty cells in a chart
• Display or hide chart gridlines
• Rearrange (transpose) data from columns to rows or vice versa
• Show or hide a chart legend or data table
• Update the data in an existing chart
• Use titles on a chart

 

 

Excel > Charts > Creating charts

Present your data in an area chart

Excel 2007

Area charts are much like line charts, but they display different colors in the areas below the lines. This colorful and visual display distinguishes the data more clearly. Unfortunately, data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) with smaller values that are plotted in the back of an area chart may be completely or partially hidden behind data series with larger values that are plotted in front of them. However, in Microsoft Office Excel 2007, you can use transparency to show the entire outline of smaller data series through any larger data series in front.

In this article

Learn more about plotting data in an area chart

Create an area chart that uses transparency

Save a chart as a template

Learn more about plotting data in an area chart

Data that is arranged in columns or rows on a worksheet can be plotted in an area chart. Area charts emphasize the magnitude of change over time and can be used to draw attention to the total value across a trend. For example, data that represents profit over time can be plotted in an area chart to emphasize the total profit.

A stacked area chart also shows the relationship of parts to a whole.

Area charts include the following chart subtypes:

• 2-D area and 3-D area  Whether they are shown in 2-D or in 3-D, area charts 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 general 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 display the trend of the contribution of each value over time or other category data. A stacked area chart in 3-D is displayed in the same way but uses a 3-D perspective. A 3-D perspective is not a true 3-D chart — a third value axis (depth axis) is not used.

• 100% stacked area and 100% stacked area in 3-D  100% stacked area charts display the trend of the percentage that each value contributes over time or other category data. A 100% stacked area chart in 3-D is displayed in the same way but uses a 3-D perspective. A 3-D perspective is not a true 3-D chart — a third value axis (depth axis) is not used.

 Top of Page

Create an area chart that uses transparency

So, how did we create this area chart? The following procedure will help you create an area chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in an area chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the area chart.
3. On the Insert tab, in the Charts group, click Area.

4. Under 3-D Area, click 3-D Area.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our area chart, we used Style 2.

7. In the chart, click the legend, and then press DELETE.
8. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

Tip  For our area chart, we used 3.5" for the shape height and 4" for the shape width.

9. To add, format, and position a chart title in the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
   1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

2. In the chart, click the chart title, and then type the text that you want.

Tip  For our area chart, we typed Regional Sales.

3. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Font size box.

Tip  For our area chart, we used 14.

10. Click the vertical axis, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
11. On the Format tab, in the Current Selection group, click Format Selection.
12. Under Axis Options, in the Display Units box, click Thousands.

Tip  You do not need to click Close. You can leave the dialog box open and continue with the next step.

13. In the chart, click the first data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
14. In the Format Data Series dialog box, click Fill.
15. Under Fill, click Solid Fill, and then do the following:
    1. In the Color palette, click the color that you want to use for the selected data series.
    2. Drag the Transparency slider to the percentage of transparency that you want to use, or type the percentage in the Transparency box.

Tip  For our area chart, we used 33%.

16. In the chart, click the second data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), or select it from a list of chart elements, and then repeat steps 14 and 15.
17. Click Close.
18. To use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our area chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a bar chart

Excel 2007

Tags  bar; bar chart; chart; chart template; display data; graph; present data

What are tags?

Using Microsoft Office Excel 2007, you can create a bar chart and give it a brand-new, appealing look. Much like a column chart, a bar chart is useful for comparing data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in one or more data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

After you create a bar chart, you can change the spacing between the bars. You can also use a bar chart type to simulate a Gantt chart, a horizontal bar chart that is often used in project management programs.

In this article

Learn more about plotting data in a bar chart

Create an elaborate bar chart

Change the spacing between the bars

Simulate a Gantt chart

Save a chart as a template

Learn more about plotting data in a bar chart

Bar charts illustrate comparisons among individual items.

In bar charts, categories are organized along the vertical axis and values along the horizontal axis.

Consider using a bar chart when:

• You have one or more data series that you want to plot.
• Your data contains positive, negative, and zero (0) values.
• You want to compare the data for numerous categories.
• The axis labels are long.
• The values that are shown are durations.

When you create a bar chart, you can choose one of the following bar chart subtypes:

• Clustered bar and clustered bar in 3-D  Clustered bar charts compare values across categories. In a clustered bar chart, the categories are typically organized along the vertical axis, and the values along the horizontal axis. A clustered bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• Stacked bar and stacked bar in 3-D  Stacked bar charts show the relationship of individual items to the whole. A stacked bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• 100% stacked bar and 100% stacked bar in 3-D  This type of chart compares the percentage that each value contributes to a total across categories. A 100% stacked bar in 3-D chart displays the horizontal rectangles in 3-D format; it does not display the data on three axes.

• Horizontal cylinder, cone, and pyramid  These charts are available in the same clustered, stacked, and 100% stacked chart types that are provided for rectangular bar charts. They show and compare data exactly the same way. The only difference is that these chart types display cylinder, cone, and pyramid shapes instead of horizontal rectangles.

 Top of Page

Create an elaborate bar chart

So, how did we create this bar chart? The following procedure will help you create a bar chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a bar chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the bar chart.
3. On the Insert tab, in the Charts group, click Bar.

4. Under 2-D Bar, select Clustered Bar.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our bar chart, we used Style 4.

7. In the chart, click the legend, and then press DELETE.
8. To reduce the size of the chart title, right-click the title, and then select the size that you want in the Size box.

Tip  For our bar chart, we used 12.

9. To reduce the size of the axis labels, right-click the vertical (category) axis, and then select the size that you want in the Size box.

Tip  For our bar chart, we used 8.

10. Click the plot area (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.) of the chart, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
11. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our bar chart, we used the Subtle Effect - Accent 1.

12. Click a data point (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) to select all of the bars, or select the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
13. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our bar chart, we used the Subtle Effect - Accent 2.

14. On the Format tab, in the Shape Styles group, click Shape Effects.
15. Click Glow, and then under Glow Variations, click the glow effect that you want.

Tip  For our bar chart, we used the Accent color 2, 8 pt glow.

16. Click the chart area of the chart.
17. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our bar chart, we used the Colored Outline - Accent 1.

18. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height box, and then press ENTER.

Tip  For our bar chart, we used 4".

19. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our bar chart, we used the Solstice theme.

 Top of Page

Change the spacing between the bars

You can enlarge or reduce the space between the bars in a bar chart. In a 2-D bar chart, the bars can even overlap each other.

1. In a chart, click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to change, or do the following to select the data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series.

2. Tip  You need to select only a single data series to change the spacing of all data series of the same chart type.
3. On the Format tab, in the Current Selection group, click Format Selection.
4. Under Series Options, do one of the following:
   1. To change the overlap of bars in a 2-D bar chart, drag the slider to the percentage of series overlap that you want, or enter a percentage between -100 and 100 in the Series Overlap box.

Tip  The higher the value, the greater the overlap within the category.

2. To change the spacing between categories of data markers in a 2-D or 3-D bar chart, drag the slider to the percentage of gap width that you want, or enter a value between 0 (zero) and 500 in the Gap Width box.

Tip  The higher the value, the greater the spacing between the bars.

3. To change the spacing between the data series in a 3-D bar chart, enter a value between 0 and 500 in the Gap Depth box.

Tip  The higher the value, the greater the distance between the bars.

 Top of Page

Simulate a Gantt chart

Even though Excel does not provide a Gantt chart type, you can simulate a Gantt chart by customizing the stacked bar chart type so that it depicts tasks, task duration, and hierarchy.

The following procedure will help you create a Gantt chart with results that are similar to those shown in the preceding graphic. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data, as long as you use the same column headers and worksheet structure.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a Gantt chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

 Note    The values in columns B and C (Start and Duration) represent the number of days from the start date and the number of days required to complete the task.

2. Select the data that you want to plot in the Gantt chart (A1:C6 in our example worksheet data).
3. On the Insert tab, in the Charts group, click Bar.
4. Under 2-D Bar, click Stacked Bar.
5. In the chart, click the first data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
6. On the Format tab, in the Current Selection group, click Format Selection.

7. Click Fill, and then click No fill.
8. Click Close.
9. On the chart, click the legend, and then press DELETE.
10. Select the vertical (value) axis, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
11. On the Format tab, in the Current Selection group, click Format Selection.
12. Under Axis Options, select the Categories in reverse order check box.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a bubble chart

Excel 2007

A bubble chart is a variation of a scatter chart in which the data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) are replaced with bubbles, and an additional dimension of the data is represented in the size of the bubbles. Just like a scatter chart, a bubble chart does not use a category axis — both horizontal and vertical axes are value axes. In addition to the x values and y values that are plotted in a scatter chart, a bubble chart plots x values, y values, and z (size) values.

In this article

Learn more about plotting data in a bubble chart

Create an elaborate bubble chart

Save a chart as a template

Learn more about plotting data in a bubble chart

You can use a bubble chart instead of a scatter chart if your data has three data series that each contain a set of values. The sizes of the bubbles are determined by the values in the third data series.

Bubble charts are often used to present financial data. Different bubble sizes are useful to visually emphasize specific values.

To create a bubble chart, arrange your data in rows or columns on a worksheet so that x values are listed in the first row or column and corresponding y values and bubble size (z) values are listed in adjacent rows or columns. For example, organize your worksheet data as shown in the following picture.

In this bubble chart, the number of products is displayed along the horizontal axis, the sales amounts are displayed along the vertical axis, and the market share percentages are represented by the size of the bubbles.

Consider using a bubble chart when your data includes the following:

• Three values per data point  Three values are required for each bubble. These values can be in rows or columns on the worksheet, but they must be in the following order: x value, y value, and then z value.
• Multiple data series  Plotting multiple data series in a bubble chart (multiple bubble series) is similar to plotting multiple data series in a scatter chart (multiple scatter series). Scatter charts use sets of x values and y values, but bubble charts use sets of x values, y values, and z values.

When you create a bubble chart, you can choose one of the following bubble chart subtypes.

• Bubble or bubble with 3-D effect  Both bubble chart types compare sets of three values instead of two. The third value determines the size of the bubble marker. You can choose to display bubbles in 2-D format or with a 3-D effect.

 Top of Page

Create an elaborate bubble chart

So, how did we create this bubble chart? The following procedure will help you create a bubble chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a bubble chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

 Note    Make sure that your worksheet has at least four rows or columns of data. When you create a bubble chart from three or fewer rows or columns of data, the chart does not plot the bubbles correctly.

2. Select the data that you want to plot in the bubble chart.

 Note    It is best not to include row or column headings in the selection. If you select the headings with your data, the chart may produce incorrect results.

3. On the Insert tab, in the Charts group, click Other Charts.

4. Under Bubble, click Bubble with a 3-D Effect.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our bubble chart, we used Style 29.

7. On the chart, click the legend, and then press DELETE.
8. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width box, and then press ENTER.

Tip  For our bubble chart, we used 3.5" for both shape height and shape width.

9. To add, format, and position a chart title on the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
   1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

2. In the chart, click the chart title, and then type the text that you want.

Tip  For our bubble chart, we typed Industry Market Share Study.

3. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our bubble chart, we used 12.

4. To align the chart title with the plot area (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.), click the chart title, and then drag it to the position that you want.
10. To add a horizontal axis title, click the chart area of the chart, and then do the following:
    1. On the Layout tab, in the Labels group, click Axis Titles, and then click Primary Horizontal Axis Title, and then click Title Below Axis.
    2. Click the horizontal axis title, type the text that you want, and then press ENTER.

Tip  For our bubble chart, we typed Number of products.

11. Click the vertical axis, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
12. On the Format tab, in the Current Selection group, click Format Selection.
13. Under Axis Options, do the following:
    1. For Minimum, select the Fixed option, and then type 0 (zero) in the Fixed box.
    2. For Maximum, select the Fixed option, and then type the number that you want in the Fixed box.

Tip  For our bubble chart, we typed 80000.

14. In the Format Axis dialog box, click Number.
15. Under Number, in the Decimal places box, type 0 (zero), and then click Close.
16. To apply a special formatting effect to the plot area, chart area, chart title, or vertical axis of the chart, click that chart element, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box), and then do the following:
    1. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our bubble chart, we used the Subtle Effect - Accent 4 for the plot area, Intense Effect - Accent 4 for the chart area, Subtle Effect - Accent 4 for the chart title, and Intense Line - Accent 6 for the vertical axis.

17. If you want to use theme colors different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our bubble chart, we used the Foundry theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Present your data in a scatter chart or a line chart
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a column chart

Excel 2007

Using Microsoft Office Excel 2007, you can create a column chart and give it a brand new, appealing look. As one of the most common chart types, a column chart is useful to compare data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in one or more data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

After you create a column chart, you can change the spacing between the columns. You can also create a floating column chart by hiding a data series in the chart.

In this article

Learn more about plotting data in a column chart

Create an elaborate column chart

Change the spacing between the columns

Simulate a floating column chart

Save a chart as a template

Learn more about plotting data in a column chart

Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items.

In column charts, categories are typically organized along the horizontal axis and values along the vertical axis.

Consider using a column chart when:

• You have one or more data series that you want to plot.
• Your data contains positive, negative, and zero (0) values.
• You want to compare the data for numerous categories side by side.

When you create a column chart, you can choose one of the following column chart subtypes:

• Clustered column and clustered column in 3-D  Clustered column charts compare values across categories. A clustered column chart displays values in 2-D vertical rectangles. A clustered column in 3-D chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a clustered column chart type when you have 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 are not in any specific order (for example, item names, geographic names, or the names of people).

 Note    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 column chart subtype instead.

• Stacked column and stacked column in 3-D  Stacked column charts show the relationship of individual items to the whole, comparing the contribution of each value to a total across categories. A stacked column chart displays values in 2-D vertical stacked rectangles. A 3-D stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a stacked column chart when you have multiple data series and when you want to emphasize the total.

• 100% stacked column and 100% stacked column in 3-D  100% stacked column charts and 100% stacked column in 3-D charts compare the percentage that each value contributes to a total across categories. A 100% stacked column chart displays values in 2-D vertical 100% stacked rectangles. A 3-D 100% stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

You can use a 100% stacked column chart when you have three or more data series and you want to emphasize the contributions to the whole, especially if the total is the same for each category.

• 3-D column  3-D column charts use three axes that you can modify (a horizontal axis, a vertical axis, and a depth axis), and they compare data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) along the horizontal and the depth axes.

You can use a 3-D column chart when you want to compare data across the categories and across the series equally, because this chart type shows categories along both the horizontal axis and the depth axis, while the vertical axis displays the values.

• Cylinder, cone, and pyramid  Cylinder, cone, and pyramid charts are available in the same clustered, stacked, 100% stacked, and 3-D chart types that are provided for rectangular column charts, and they show and compare data exactly the same way. The only difference is that these chart types display cylinder, cone, and pyramid shapes instead of rectangles.

 Top of Page

Create an elaborate column chart

So, how did we create this column chart? The following procedure will help you create a column chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a column chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

 

2. Select the data that you want to plot in the column chart.
3. On the Insert tab, in the Charts group, click Column.

4. Under 3-D Column, select Stacked Column in 3-D.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Layouts group, select the layout that you want to use.

Tip  For our column chart, we used Layout 3.

7. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our column chart, we used Style 34.

8. In the chart, click the title, and then type the title text that you want.

Tip  For our column chart, we used the title Regional Sales in North America.

9. In the chart, click the vertical (value) axis, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
10. On the Format tab, in the Current Selection group, click Format Selection.
11. Under Axis Options, in the Display units box, click Millions, and then click Close.
12. In the chart, click a data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
13. On the Format tab, in the Shape Styles group, click Shape Effects, and then click Bevel.

14. Click 3-D Options, and then under Bevel, click the Top and Bottom bevel options that you want to use.

Tip  For our column chart, we used Soft Round.

15. In the Width and Height boxes for Top and Bottom bevel options, type the point size that you want.

Tip  For our column chart, we used 12 pt for top width, 4 pt for top height, 6 pt for bottom width, and 6 pt for bottom height.

16. Under Surface, click Material, and then click the material option that you want to use.

Tip  For our column chart, we used Plastic.

17. In the chart, click another data series, and then repeat steps 16 through 18.
18. Keep the last data series selected and the dialog box open after you have formatted all data series, and then click Series Options in the Format Data Series dialog box.
19. Under Gap Width, drag the slider to the gap width that you want.

Tip  For our column chart, we dragged the slider to No Gap (0%).

20. Keep the dialog box open, and then click the chart area of the chart.
21. In the dialog box, click Border Styles, and then select the Rounded corners check box.
22. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our column chart, we used the Median theme.

 Top of Page

Change the spacing between the columns

You can enlarge or reduce the space between the data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) or columns in a column chart. In a 2-D column chart, the columns can even overlap each other.

1. In a chart, click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to change, or do the following to select the data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series.

2. Tip  You need to select only a single data series to change the spacing of all data series of the same chart type.
3. On the Format tab, in the Current Selection group, click Format Selection.
4. Under Series Options, do one of the following:
   1. To change the overlap of columns in a 2-D column chart, drag the slider to the percentage of series overlap that you want, or enter a percentage between -100 and 100 in the Series Overlap box.

Tip  The higher the value, the greater the overlap within the category.

2. To change the spacing between categories of data markers in a 2-D or 3-D column chart, drag the slider to the percentage of gap width that you want, or enter a value between 0 (zero) and 500 in the Gap Width box.

Tip  The higher the value, the greater the spacing between the columns.

3. To change the spacing between the data series in a 3-D column chart, enter a value between 0 (zero) and 500 in the Gap Depth box.

Tip  The higher the value, the greater the distance between the columns.

 Top of Page

Simulate a floating column chart

Floating charts are useful when you want the columns in the column chart to depict minimum and maximum values. Excel does not provide a floating column chart type. However, when you create a stacked column chart that contains two (or more) data series, you can make the first data series invisible to give the second data series a floating column chart effect.

1. On the worksheet, select the data that you want to plot into a floating column chart.
2. On the Insert tab, in the Charts group, click Column.

3. Under 2-D Column, click Stacked Column.

Tip   For a 3-D effect, under 3-D Column, click Stacked Column in 3-D.

4. In the chart, click the first data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
5. On the Format tab, in the Current Selection group, click Format Selection.

6. Click Fill, and then click No fill.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Add or remove a secondary axis in a chart
• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Present your data in a combination chart
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a combination chart

Excel 2007

To emphasize different types of information in a chart, you can combine two or more chart types in that chart. For example, you can combine a column chart with a line chart for an instant visual effect that might make the chart easier to understand.

When the range of values for different data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in your chart varies widely, or when you have mixed types of data, you can plot one or more data series from a different chart type on a secondary vertical (value) axis.

In this article

Create a combination chart

Save a chart as a template

Create a combination chart

The following procedure will help you create a combination chart with results that are similar to those shown in our example combination chart graphic. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data, as long as you use the same column headers and worksheet structure.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a combination chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the combination chart.
3. On the Insert tab, in the Charts group, click Column.

4. Under 2-D Column, click Clustered Column.
5. In the chart, click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to display in a different chart type, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).

Tip  For our combination chart, we selected the data series for Average Price.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Type group, click Change Chart Type.

 Note    If the whole chart changes to a line chart, you need to make sure that only one data series is selected before you change the chart type.

7. Under Line, click Line with Markers, and then click OK.
8. To plot the line on a secondary axis, do the following:
   1. In the chart, click the line that represents Average Price once to select the data series, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
   2. On the Layout tab, in the Current Selection group, click Format Selection.

3. In the Series Options category, under Plot Series On, click Secondary Axis, and then click Close.
9. Click the chart area (chart area: The entire chart and all its elements.) of the chart.
10. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our combination chart, we used Style 42.

11. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

Tip  For our combination chart, we used 5 for the shape height and 5 for the shape width.

Tip  You can also resize a chart by dragging one of the corners of the chart until the chart reaches the size that you want.

12. To add, format, and position a chart title on the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
    1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

2. In the chart, click the chart title, and then type the text that you want.

Tip  For our combination chart, we typed Recent Home Sales.

3. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our combination chart, we used 18.

13. To move the legend, do the following:
    1. Click the legend to select it.
    2. On the Layout tab, in the Labels group, click Legend, and then click the position that you want.

Tip  For our combination chart, we clicked Show Legend at Bottom.

14. To add vertical axis titles, do the following:
    1. On the Layout tab, in the Labels group, click Axis Titles, and then do the following:
       • Click Primary Vertical Axis Title, and then click the title option that you want.
       • Click Secondary Vertical Axis Title, and then click the title option that you want.

Tip  For our combination chart, we clicked Rotated Title for both axes.

2. Click each axis title, and then type the text that you want for that title.

Tip  For our combination chart, we typed Number of homes for the primary vertical axis title and Average price per home in thousands for the secondary axis title.

3. To change the font size of the axis titles, click each axis title, and then click the size that you want in the Font Size box.

Tip  For our combination chart, we used 14 for the font size.

15. To change the appearance of the markers that are displayed on the Average Price line, do the following:
    1. Right-click a marker, and then click Format Data Series on the shortcut menu.
    2. Click Marker Options, and then under Marker Type, click Built-in.
    3. In the Type box, click the marker type that you want to use.

Tip  For our combination chart, we used a round marker type.

16. Click the chart area of the chart.
17. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our combination chart, we used the Subtle Effect - Dark 1 for the chart area.

18. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our combination chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Add or remove a secondary axis in a chart
• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a doughnut chart

Excel 2007

Using Microsoft Office Excel 2007, you can quickly turn your data into a doughnut chart, and then use the new formatting features to make that doughnut chart easier to read. For example, by adding a legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.), data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.), and text boxes (text box: A rectangular object on a worksheet or chart, in which you can type text.) that point out what each ring of a doughnut chart represents, you can quickly understand the data that is plotted in the chart.

After you create a doughnut chart, you can rotate the slices for different perspectives, focus on specific slices by pulling out slices of the doughnut chart, or change the hole size of the doughnut chart to enlarge or reduce the size of the slices.

What do you want to do?

Learn more about plotting data in a doughnut chart

Create an elaborate doughnut chart

Rotate the slices in a doughnut chart

Pull out slices of a doughnut chart

Change the hole size in a doughnut chart

Save a chart as a template

Learn more about plotting data in a doughnut chart

Data that is arranged in columns or rows only on a worksheet can be plotted in a doughnut chart. Just like a pie chart, a doughnut chart shows the relationship of parts to a whole, but a doughnut chart can contain more than one data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.). Each data series that you plot in a doughnut chart adds a ring to the chart. The first data series is displayed in the center of the chart.

Because of their circular nature, doughnut charts are not easy to read, especially when they display multiple data series. The proportions of outer rings and inner rings do not represent the size of the data accurately — data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) on outer rings may appear larger than data points on inner rings while their actual values may be smaller. Displaying values or percentages in data labels is very useful in a doughnut chart, but if you want to compare the data points side by side, you should use a stacked column or stacked bar chart instead.

Consider using a doughnut chart when:

• You have one or more data series that you want to plot.
• None of the values that you want to plot is negative.
• None of the values that you want to plot is a zero (0) value.
• You don't have more than seven categories per data series.
• The categories represent parts of whole in each ring of the doughnut chart.

When you create a doughnut chart, you can choose one of the following doughnut chart subtypes:

• Doughnut  Doughnut charts display data in rings, where each ring represents a data series. If percentages are displayed in data labels, each ring will total 100%.

• Exploded Doughnut  Much like exploded pie charts, exploded doughnut charts display the contribution of each value to a total while emphasizing individual values, but they can contain more than one data series.

Doughnut charts and exploded doughnut charts are not available in 3-D, but you can use 3-D formatting to give these charts a 3-D-like appearance.

 Top of Page

Create an elaborate doughnut chart

So, how did we create this doughnut chart? The following procedure will help you create a doughnut chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Open the worksheet that contains the data that you want to plot into a doughnut chart, or copy the example worksheet data into a blank worksheet.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the doughnut chart.
3. On the Insert tab, in the Charts group, click Other Charts.

4. Under Doughnut, click Doughnut.
5. Click the plot area (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.) of the doughnut chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Layouts group, select the layout that you want to use.

Tip  For our doughnut chart, we used Layout 6.

 Note    Layout 6 displays a legend. If your chart has too many legend entries or if the legend entries are not easy to distinguish, you may want to add data labels to the data points of the doughnut chart instead of displaying a legend (Layout tab, Labels group, Data Labels button).

7. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our doughnut chart, we used Style 26.

8. To change the size of the chart, do the following:
   1. Click the chart.
   2. On the Format tab, in the Size group, enter the size that you want in the Shape Height and Shape Width box.

Tip  For our doughnut chart, we set the shape height to 4" and the shape width to 5.5".

9. To change the size of the doughnut hole, do the following:
   1. Click a data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Click Series Options, and then under Doughnut Hole Size, drag the slider to the size that you want, or type a percentage value between 10 and 90 in the Percentage box.

Tip  For our doughnut chart, we used 20%.

10. To make the data labels stand out better, do the following:
    1. Click a data label once to select the data labels for an entire data series, or select them from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
    2. On the Format tab, in the Shape Styles group, click More , and then click a shape style.

Tip  For our doughnut chart, we used Subtle Effect - Dark 1.

3. Repeat these steps to format the data labels of all data series in your doughnut chart.
11. To change and format the chart title, do the following:
    1. Click the chart title, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
    2. Type the title that you want to use, and then press ENTER.
    3. On the Format tab, in the Shape Styles group, click More , and then click a shape style.

Tip  For our doughnut chart, we used Moderate Effect - Accent 1.

4. If you want to change the placement of the title, drag it to the location that you want.
12. To change the format of the legend, click the legend, and then select the style that you want in the Shape Styles box (Format tab, in the Shape Styles group, More button).
13. To add text labels with arrows that point to the doughnut rings, do the following:
    1. On the Layout tab, in the Insert group, click Text Box.
    2. Click on the chart where you want to place the text box, type the text that you want, and then press ENTER.
    3. Select the text box, and then on the Format tab, in the Shape Styles group, click the Dialog Box Launcher .
    4. Click Text Box, and then under Autofit, select the Resize shape to fit text check box, and click OK.
    5. In the Shape Styles group, select the style that you want to use.
    6. On the Layout tab, in the Insert group, click Shapes.
    7. Under Lines, click Arrow.
    8. On the chart, draw the arrow from the corner of the text box to the doughnut ring that you want it to point to.
    9. To change the format of text boxes, click a text box, and then select the style that you want in the Shape Styles group (Format tab, Shape Styles group).

Repeat these steps for all doughnut rings in your chart.

14. To change the background of the chart, do the following:
    1. Click the chart area, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
    2. On the Format tab, in the Shape Styles group, click More , and then click a shape style.

Tip  For our doughnut chart, we used Subtle Effect - Accent 3.

15. To round the corners of the chart background, do the following:
    1. On the Format tab, in the Shape Styles group, click the Dialog Box Launcher .
    2. Click Border Styles, and then select the Rounded corners check box.
16. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our doughnut chart, we used the Apex theme.

 Top of Page

Rotate the slices in a doughnut chart

The order in which data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in doughnut charts are plotted in Office Excel 2007 is determined by the order of the data on the worksheet. For a different perspective, you can rotate the doughnut chart slices within the 360 degrees of the circle of the doughnut chart.

1. In a doughnut chart, click the data series or a data point, or do the following to select it from a list of chart elements.
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series or data point that you want.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Under Angle of first slice box, drag the slider to the degree of rotation that you want, or type a value between 0 (zero) and 360 to specify the angle at which you want the first slice to appear.

 Top of Page

Pull out slices of a doughnut chart

To emphasize the individual slices of a doughnut chart, you can use the exploded doughnut chart type when you create the chart. Exploded doughnut charts display the contribution of each value to a total while emphasizing individual values. You can change the doughnut explosion setting for all slices or individual slices.

You can also pull out the slices manually.

Change the settings of slices in an exploded doughnut chart

1. In the exploded doughnut chart, click a data series or a data point, or do the following to select a data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click a data series.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Under Doughnut Explosion, drag the slider to increase or decrease the percentage of separation, or type the percentage of separation that you want in the Percentage box.

Pull out slices of a doughnut chart manually

Click the doughnut chart, and then do one of the following:

• To pull out all of the slices of the doughnut chart, drag away from the center of the chart.
• To pull out individual slices of the doughnut chart, click the slice that you want to pull out, and then drag that slice away from the center of the chart.

 Top of Page

Change the hole size in a doughnut chart

By enlarging or reducing the hole size in a doughnut chart, you reduce or enlarge the width of the slices. For example, you may want to display wider slices to better accommodate data labels that contain long series or category names, or a combination of names, values and percentages.

1. In a doughnut chart, click a data series, or do the following to select it from a list of chart elements.
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click a data series.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Under Doughnut Hole Size, drag the slider to the size that you want, or type a percentage value between 10 and 90 in the Percentage box.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a Gantt chart in Excel

Excel 2007

Even though Microsoft Office Excel 2007 does not provide a Gantt chart type, you can simulate a Gantt chart by customizing a stacked bar chart type so that it depicts tasks, task duration, and hierarchy.

An Excel Gantt chart typically uses days as the unit of time along the horizontal axis. If you want to use hours instead of days, see the link in the See Also section about how to create a Gantt chart that uses hours as the scale in Excel 2007.

What do you want to do?

Simulate a Gantt chart

Save a chart as a template

Simulate a Gantt chart

The following procedure will help you create a Gantt chart with results that are similar to those shown in our example Gantt chart graphic. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data, as long as you use the same column headers and worksheet structure.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a Gantt chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

 Note    The values in columns B and C (Start and Duration) represent the number of days from the start date and the number of days required to complete the task.

2. Select the data that you want to plot in the Gantt chart (A1:C6 in our example worksheet data).
3. On the Insert tab, in the Charts group, click Bar.
4. Under 2-D Bar, click Stacked Bar.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our Gantt chart, we used Style 27.

7. In the chart, click the first data series (Start), or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
8. On the Format tab, in the Current Selection group, click Format Selection.

9. Click Fill, and then click No fill.
10. Click Close.
11. In the chart, click the legend, and then press DELETE.
12. Select the vertical (category) axis, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
13. On the Format tab, in the Current Selection group, click Format Selection.
14. Under Axis Options, select the Categories in reverse order check box, and then click Close.
15. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our Gantt chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Change the layout or style of a chart
• Create a chart
• Format chart elements
• Present your data in a bar chart
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a histogram

Excel 2007

You can analyze your data and display it in a histogram (a column chart that displays frequency data) by using the Histogram tool of the Analysis ToolPak. This data analysis add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) is available when you install Microsoft Office Excel 2007, but it might not be loaded automatically.

Important  If you don't see the Data Analysis button in the Analysis group on the Data tab, you must load the Analysis ToolPak add-in.

What do you want to do?

Learn more about plotting data in a histogram

Load the Analysis ToolPak

Create a histogram

Learn more about plotting data in a histogram

To create a histogram, you must organize the data in two columns on the worksheet. These columns must contain the following data:

• Input data  This is the data that you want to analyze by using the Histogram tool.
• Bin numbers  These numbers represent the intervals that you want the Histogram tool to use for measuring the input data in the data analysis.

When you use the Histogram tool, Excel counts the number of data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in each data bin. A data point is included in a particular bin if the number is greater than the lowest bound and equal to or less than the greatest bound for the data bin. If you omit the bin range, Excel creates a set of evenly distributed bins between the minimum and maximum values of the input data.

The output of the histogram analysis is displayed on a new worksheet (or in a new workbook) and shows a histogram table and a column chart that reflects the data in the histogram table.

 Top of Page

Load the Analysis ToolPak

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Add-Ins.
3. In the Manage box, click Excel Add-ins, and then click Go.
4. In the Add-Ins available box, do one of the following:
   • To load the Analysis ToolPak, select the Analysis ToolPak check box, and then click OK.
   • To include Visual Basic for Applications (VBA) functions for the Analysis ToolPak, select the Analysis ToolPak - VBA check box, and then click OK.

Tip  If Analysis ToolPak or Analysis ToolPak - VBA is not listed in the Add-Ins available box, click Browse to locate it.

5. If you see a message that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.

Tip  After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.

 Top of Page

Create a histogram

1. To enter the data that you want to analyze in a histogram, do one of the following:
   • Copy the example worksheet data to your worksheet.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

• On a worksheet, enter your own data as follows:
  1. In one column, type the input data.

 Note    You must enter quantitative numeric data (such as item amounts or test scores) in each cell of the input column — the Histogram tool does not work with qualitative numeric data (such as identification numbers).

2. In another column, type the bin numbers that you want to use for the analysis. The bin numbers must be entered in ascending order.

 Note    If you don't enter the bin numbers on the worksheet, the Histogram tool automatically creates evenly distributed bin intervals by using the minimum and maximum values in the input range as start and end points. However, these bins may not be useful — we recommend that you use your own bin numbers.

Tip  If you want, you can add a label in the first cell of these columns.

2. On the Data tab, in the Analysis group, click Data Analysis.
3. In the Analysis Tools box, click Histogram, and then click OK.
4. Under Input, in the Input Range box, enter the cell reference for the range of data you want to analyze.

Tip  If you are using the sample worksheet data, type A1:A11.

You can also click the Collapse Dialog button , select the range on the worksheet, and then click the Collapse Dialog button again to return to the dialog box.

5. Under Input, in the Bin Range box, enter the cell reference to a range that contains an optional set of boundary values that define bin ranges.

Tip  If you are using the sample worksheet data, type B1:B5.

You can also click the Collapse Dialog button , select the range on the worksheet, and then click the Collapse Dialog button again to return to the dialog box.

 Note    If you do not enter a range in the Bin Range box, the Histogram tool creates a set of evenly distributed bins between the data's minimum and maximum values. However, we recommend that you enter or select the bin range that you used on the worksheet.

6. If you included column labels when you selected the input and bin range data, select the Labels check box.
7. Under Output options, do one of the following:
   • To paste the output table on the same sheet, click Output Range, and then enter the cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) of the upper-left cell of the output table.

 Note    The Histogram tool automatically determines the size of the output area and displays a message if the output table will replace existing data.

• To insert a new worksheet in the current workbook and paste the output table starting at cell A1 of the new worksheet, click New Worksheet Ply.

Tip  You can type a name in the New Worksheet Ply box.

• To create a new workbook and paste the output table on a new worksheet in the new workbook, click New Workbook.
8. Under Output options, do any or all of the following:
   • To present data in the output table in descending order of frequency, select the Pareto (sorted histogram) check box.
   • To generate an output table column for cumulative percentages and to include a cumulative percentage line in the histogram chart, select the Cumulative Percentage check box.
   • To generate an embedded histogram chart with the output table, select the Chart Output check box.
9. Click OK.

Tip  After the bin and frequency table is generated, you can select any of the text and change the default labels. When you click the histogram, you can use the design, layout, and format options of the Chart Tools to change the display of the chart. For more information about changing the design and format of a chart, see the links in the See Also section.

 Top of Page

See Also

• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Format chart elements
• Perform statistical and engineering analysis with the Analysis ToolPak
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a pie chart

Excel 2007

Using Microsoft Office Excel 2007, you can quickly turn your data into a pie chart, and then give that pie chart a spiffy, professional look.

After you create a pie chart, you can rotate the slices for different perspectives. You can also focus on specific slices by pulling them out of the pie chart, or by changing the chart type to a pie of pie or bar of pie chart to draw attention to very small slices.

In this article

Learn more about plotting data in a pie chart

Create an elaborate pie chart

Rotate the slices in a pie chart

Pull out slices of a pie chart

Draw attention to small slices in a pie of pie or bar of pie chart

Save a chart as a template

Learn more about plotting data in a pie chart

Pie charts show the size of items in one data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), proportional to the sum of the items. The data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in a pie chart are displayed as a percentage of the whole pie. Because you can plot only one data series in a pie chart, the worksheet data should be arranged in one column or one row. You can also include a column or row of category names, as long as that column or row is the first column or row in the selection. Categories are then presented in the legend of the pie chart.

Consider using a pie chart when:

• You have only one data series that you want to plot.
• None of the values that you want to plot is negative.
• None of the values that you want to plot is a zero (0) value.
• You don't have more than seven categories.
• The categories represent parts of the whole pie.

When you create a pie chart, you can choose one of the following pie chart subtypes:

• Pie and pie in 3-D  Pie charts display the contribution of each value to a total in a 2-D or 3-D format. You can pull out slices of a pie chart manually to emphasize the slices.

• Pie of pie and bar of pie  Pie of pie or bar of pie charts display pie charts with user-defined values that are extracted from the main pie chart and combined into a secondary pie chart or into a stacked bar chart. These chart types are useful when you want to make small slices in the main pie chart easier to distinguish.

• Exploded pie and exploded pie in 3-D  Exploded pie charts display the contribution of each value to a total while emphasizing individual values. Exploded pie charts can be displayed in 3-D format. You can change the pie explosion setting for all slices and individual slices, but you cannot move the slices of an exploded pie manually. If you want to pull out the slices manually, consider using a pie or pie in 3-D chart instead.

 Top of Page

Create an elaborate pie chart

So, how did we create this pie chart? The following procedure will help you create a pie chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a pie chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the pie chart.
3. On the Insert tab, in the Charts group, click Pie.

4. Under 3-D Pie, select Pie in 3-D.
5. Click the plot area (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.) of the pie chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Layouts group, select the layout that you want to use.

Tip  For our pie chart, we used Layout 2.

 Note    Layout 2 displays a legend. If your chart has too many legend entries or if the legend entries are not easy to distinguish, you may want to add data labels to the slices of the pie chart instead of displaying a legend (Layout tab, Labels group, Data Labels button).

7. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our pie chart, we used Style 2.

8. On the Format tab, in the Shape Styles group, click Shape Effects, and then click Bevel.

9. Click 3-D Options, and then under Bevel, click the Top and Bottom bevel options that you want to use.

Tip  For our pie chart, we used Circle.

10. In the Width and Height boxes for Top and Bottom bevel options, type the point size that you want.

Tip  For our pie chart, we used 512 pt.

11. Under Surface, click Material, and then click the material option that you want to use.

Tip  For our pie chart, we used Plastic.

12. Click Close.
13. On the Format tab, in the Shape Styles group, click Shape Effects, and then click Shadow.
14. Under Outer, Inner, or Perspective, click the shadow option that you want to use.

Tip  For our pie chart, we used Below under Perspective.

15. To rotate the chart for a better perspective, select the plot area, and then on the Format tab in the Current Selection group, click Format Selection.
16. Under Angle of first slice, drag the slider to the degree of rotation that you want, or type a value between 0 (zero) and 360 to specify the angle at which you want the first slice to appear, and then click Close.

Tip  For our pie chart, we used 350.

17. Click the chart area of the chart.
18. On the Format tab, in the Shape Styles group, click Shape Effects, and then click Bevel.
19. Under Bevel, select the bevel option that you want to use.

Tip  For our pie chart, we used Circle.

20. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our pie chart, we used the Origin theme.

 Top of Page

Rotate the slices in a pie chart

The order in which data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in pie charts are plotted in Office Excel 2007 is determined by the order of the data on the worksheet. For a different perspective, you can rotate the pie chart slices within the 360 degrees of the circle of the pie chart.

1. In a pie chart, click the data series or a data point, or do the following to select it from a list of chart elements.
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series or data point that you want.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Under Angle of first slice box, drag the slider to the degree of rotation that you want, or type a value between 0 (zero) and 360 to specify the angle at which you want the first slice to appear.

 Top of Page

Pull out slices of a pie chart

To emphasize the individual slices of a pie chart, you can use the exploded pie or exploded pie in 3-D chart type when you create the chart. Exploded pie charts display the contribution of each value to a total while emphasizing individual values. You can change the pie explosion setting for all slices or individual slices, but you cannot move the slices of an exploded pie manually.

If you want to pull out the slices manually, consider using a pie or pie in 3-D chart instead.

Change the settings of slices in an exploded pie chart

1. In the exploded pie chart, click the data series or a data point, or do the following to select the data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. Under Pie Explosion, drag the slider to increase or decrease the percentage of separation, or type the percentage of separation that you want in the percentage box.

Pull out slices of a pie chart manually

Click the pie chart, and then do one of the following:

• To pull all of the slices out of the pie chart, drag away from the center of the chart.
• To pull individual slices out of the pie chart, click the slice that you want to pull out, and then drag that slice away from the center of the chart.

 Top of Page

Draw attention to small slices in a pie of pie or bar of pie chart

When several data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in a pie chart each amount to less than 5% of the pie, it becomes hard to distinguish the slices.

To make smaller slices more visible in a pie chart, you can use a pie of pie or bar of pie chart type. Each of these chart types separates the smaller slices from the main pie chart and displays them in a secondary pie or stacked bar chart.

The data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.) in the secondary pie chart display the same percentage values as in the regular pie chart. The percentages in the secondary pie chart represent the slices that were separated from the main pie chart, and they do not total 100%. All slices are part of the same data series.

When you select the pie of pie or bar of pie chart type, the last third of the data points is moved into the secondary chart by default. For example, if there are seven to nine data points in the chart, the last three points are plotted in the secondary chart. However, you can change how data points are distributed across the main and secondary pie chart, and you can change how they are displayed.

Create a pie of pie or bar of pie chart

1. In the worksheet, select the data that you want to plot in a pie of pie or bar of pie chart.
2. On the Insert tab, in the Charts group, click Pie, and then click Pie of Pie or Bar of Pie.

 Notes 

• The main and secondary charts are always positioned horizontally and adjacent to each other. They cannot be moved independently. The left chart is always the main chart.
• You cannot select the individual charts or format them separately. Both the main and secondary charts are part of one data series. You can only select the entire data series or its individual data points, and then apply formatting to the selection.
• Connector lines are automatically added to indicate the association between the main and secondary charts. You can format the connector lines by applying different line styles. You can also remove connector lines.
• Depending on how many decimal places are specified for percentages on the Number tab of the Format Cells dialog box (Home tab, Number group, Dialog Box Launcher ), percentages that are displayed in data labels may be rounded so that they don't add up correctly.

Change the distribution and display of data points

1. In the pie of pie or bar of pie chart, select the data series or a data point, or do the following to select the data series from a list of chart elements:
   1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series.

2. On the Format tab, in the Current Selection group, click Format Selection.
3. In the Split Series By box, click the type of data that you want to display in the secondary chart.
4. To adjust the number of data points that appear in the secondary chart, do the following:
   1. If you chose to split the series by position, in the Second plot contains the last values box, enter a different number.
   2. If you chose to split the series by value or percent value, in the Second plot contains all values less than box, type a different number.
   3. To change the placement of a selected data point, in the Point belongs to box, click First Plot or Second Plot.
5. To change the space between the slices, under Pie Explosion or Point Explosion, drag the slider to increase or decrease the percentage of separation, or type the percentage of separation that you want in the percentage box.
6. To change the space between the charts, under Gap Width, drag the slider to increase or decrease the number, or type the number that you want in the percentage box.

 Note    This number represents the gap width as a percentage of the secondary chart width.

7. To change the size of the secondary chart, under Second Plot Size box, drag the slider to increase or decrease the number, or type the number that you want to use in the percentage box.

 Note    This number represents the size as a percentage of the main chart size.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a radar chart

Excel 2007

A radar chart, also known as a spider chart or a star chart because of its appearance, plots the values of each category along a separate axis that starts in the center of the chart and ends on the outer ring.

In this article

Learn more about plotting data in a radar chart

Create a radar chart

Save a chart as a template

Learn more about plotting data in a radar chart

Data that is arranged in columns or rows on a worksheet can be plotted in a radar chart. Radar charts compare the aggregate values of multiple data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

Radar charts include the following chart subtypes:

• Radar and radar with markers  With or without markers for individual data points, radar charts display changes in values relative to a center point.

• Filled radar  In a filled radar chart, the area covered by a data series is filled with a color.

 Top of Page

Create a radar chart

So, how did we create this radar chart? The following procedure will help you create a radar chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a radar chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the radar chart.
3. On the Insert tab, in the Charts group, click Other Charts.

4. Under Radar, click Filled Radar.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our radar chart, we used Style 26.

7. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

Tip  For our radar chart, we used 3 for the shape height and 4 for the shape width.

8. To add, format, and position a chart title in the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
   1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

2. In the chart, click the chart title, and then type the text that you want.

Tip  For our radar chart, we typed Garden Center Sales.

3. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our radar chart, we used 12.

9. To remove the axis labels, click the vertical axis, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
10. On the Format tab, in the Current Selection group, click Format Selection.
11. Under Axis Options, in the Axis labels box, click None.
12. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our radar chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a scatter chart or a line chart

Excel 2007

Scatter charts and line charts look very similar, especially when a scatter chart is displayed with connecting lines. However, there is a big difference in the way each of these chart types plots data along the horizontal axis (which is also known as the x-axis) and the vertical axis (which is also known as the y-axis).

This formatted scatter chart displays numerical values along the horizontal and the vertical axis, combining these values into single data points that are displayed in uneven intervals.

This formatted line chart distributes category data (in this case, a time interval) evenly along a horizontal axis and all numerical value data evenly along a vertical axis.

Before you choose either of these chart types, you may want to learn more about the differences and find out when it is better to use a scatter chart instead of a line chart, or the other way around.

In this article

Learn about the differences between scatter and line charts

Know when to use a scatter or line chart

Explore scatter and line chart types

Create a scatter chart

Create a line chart

Save a chart as a template

Learn about the differences between scatter and line charts

The main difference between scatter and line charts is the way that they plot data on the horizontal axis. For example, when you use the following worksheet data to create a scatter chart and a line chart, you can see that the data is distributed differently.

In a scatter chart, the daily rainfall values from column A are displayed as x values on the horizontal (x) axis, and the particulate values from column B are displayed as values on the vertical (y) axis. Often referred to as an xy chart, a scatter chart never displays categories on the horizontal axis.

A scatter chart always has two value axes to show one set of numerical data along a horizontal (value) axis and another set of numerical values along a vertical (value) axis. The chart displays points at the intersection of an x and y numerical value, combining these values into single data points. These data points may be distributed evenly or unevenly across the horizontal axis, depending on the data.

The first data point to appear in the scatter chart represents both a y value of 137 (particulate) and an x value of 1.9 (daily rainfall). These numbers represent the values in cell A9 and B9 on the worksheet.

In a line chart, however, the same daily rainfall and particulate values are displayed as two separate data points, which are evenly distributed along the horizontal axis. This is because a line chart only has one value axis (the vertical axis). The horizontal axis of a line chart only shows evenly spaced groupings (categories) of data. Because categories were not provided in the data, they were automatically generated, for example, 1, 2, 3, and so on.

This is a good example of when not to use a line chart.

A line chart distributes category data evenly along a horizontal (category) axis , and distributes all numerical value data along a vertical (value) axis.

The particulate y value of 137 (cell B9) and the daily rainfall x value of 1.9 (cell A9) are displayed as separate data points in the line chart. Neither of these data points is the first data point displayed in the chart — instead, the first data point for each of the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) refers to the values in the first data row on the worksheet (cell A2 and B2).

Axis type and scaling differences

Because the horizontal axis of a scatter chart is always a value axis, it can display numeric values or date values (such as days or hours) that are represented as numerical values. To display the numeric values along the horizontal axis with greater flexibility, you can change the scaling options on this axis the same way that you can change the scaling options of a vertical axis.

Because the horizontal axis of a line chart is a category axis, it can be only a text axis or a date axis. A text axis displays text only (non-numerical data or numerical categories that are not values) at evenly spaced intervals. A date axis displays dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in order or in the same base units.

The scaling options of a category axis are limited compared with the scaling options of a value axis. The available scaling options also depend on the type of axis that you use.

 Top of Page

Know when to use a scatter or line chart

Scatter charts are commonly used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. These charts are useful to show the relationships among the numeric values in several data series, and they can plot two groups of numbers as one series of xy coordinates.

Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals or over time. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis. As a general rule, use a line chart if your data has non-numeric x values — for numeric x values, it is usually better to use a scatter chart.

Consider using a scatter chart instead of a line chart if you want to:

• Change the scale of the horizontal axis  Because the horizontal axis of a scatter chart is a value axis, more scaling options are available.
• Use a logarithmic scale on the horizontal axis  You can turn the horizontal axis into a logarithmic scale.
• Display worksheet data that includes pairs or grouped sets of values  In a scatter chart, you can adjust the independent scales of the axes to reveal more information about the grouped values.
• Show patterns in large sets of data  Scatter charts are useful for illustrating the patterns in the data, for example by showing linear or non-linear trends, clusters, and outliers.
• Compare large numbers of data points without regard to time  The more data that you include in a scatter chart, the better the comparisons that you can make.

Consider using a line chart instead of a scatter chart if you want to:

• Use text labels along the horizontal axis  These text labels can represent evenly spaced values such as months, quarters, or fiscal years.
• Use a small number of numerical labels along the horizontal axis  If you use a few, evenly spaced numerical labels that represent a time interval, such as years, you can use a line chart.
• Use a time scale along the horizontal axis  If you want to display dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in order or in the same base units, use a line chart.

 Top of Page

Explore scatter and line chart types

Scatter charts can use the following chart subtypes.

• Scatter with only markers  This type of chart compares pairs of values. Use a scatter chart with data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) but without lines when you use many data points and connecting lines would make the data harder to read. You can also use this chart type when there is no need to show connectivity of the data points.

• Scatter with smooth lines and scatter with smooth lines and markers  This type of chart displays a smooth curve that connects the data points. Smooth lines can be displayed with or without markers. Use a smooth line without markers if there are many data points.

• Scatter with straight lines and scatter with straight lines and markers  This type of chart displays straight connecting lines between data points. Straight lines can be displayed with or without markers.

Line charts can use the following chart subtypes.

• Line and line with markers  Displayed with markers to indicate individual data values, or without, line charts are useful to show trends over time or ordered categories, especially when there are many data points and the order in which they are presented is important. If there are many categories or the values are approximate, use a line chart without markers.

• Stacked line and stacked line with markers  Displayed with markers to indicate individual data values, or without, stacked line charts can be used to show the trend of the contribution of each value over time or ordered categories, but because it is not easy to see that the lines are stacked, consider using a different line chart type or a stacked area chart instead.

• 100% stacked line and 100% stacked line with markers  Displayed with markers to indicate individual data values, or without, 100% stacked line charts are useful to show the trend of the percentage each value contributes over time or ordered categories. If there are many categories or the values are approximate, use a 100% stacked line chart without markers.

Tip  For a better presentation of this type of data, consider using a 100% stacked area chart instead.

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

 Top of Page

Create a scatter chart

So, how did we create this scatter chart? The following procedure will help you create a scatter chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a scatter chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the scatter chart.
3. On the Insert tab, in the Charts group, click Scatter.

4. Click Scatter with only Markers.

Tip  You can rest the mouse on any chart type to see its name.

5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our scatter chart, we used Style 26.

7. Click the chart title, and then type the text that you want.

Tip  For our scatter chart, we typed Particulate Levels in Rainfall.

8. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Font Size box on the shortcut menu.

Tip  For our scatter chart, we used 14.

9. Click the chart area of the chart.
10. On the Layout tab, in the Labels group, click Axis Titles, and then do the following:
    1. To add a horizontal axis title, click Primary Horizontal Axis Title, and then click Title Below Axis.
    2. To add a vertical axis title, click Primary Vertical Axis Title, and then click the type of vertical axis title that you want.

Tip  For our scatter chart, we used Rotated Title.

3. Click each title, type the text that you want, and then press ENTER.

Tip  For our scatter chart, we typed Daily Rainfall in the horizontal axis title, and Particulate level in the vertical axis title.

11. Click the plot area (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.) of the chart, or select Plot Area from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
12. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our scatter chart, we used the Subtle Effect - Accent 3.

13. Click the chart area of the chart.
14. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our scatter chart, we used the Subtle Effect - Accent 1.

15. If you want to use theme colors different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our line chart, we used the Office theme.

 Top of Page

Create a line chart

So, how did we create this line chart? The following procedure will help you create a line chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a line chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the line chart.
3. On the Insert tab, in the Charts group, click Line.

4. Click Line with Markers.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our line chart, we used Style 2.

7. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

8. Click the chart title, and then type the text that you want.

Tip  For our line chart, we typed Particulate Levels in Rainfall.

9. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our line chart, we used 14.

10. On the chart, click the legend, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
11. On the Layout tab, in the Labels group, click Legend, and then click the position that you want.

Tip  For our line chart, we used Show Legend at Top.

12. To plot one of the data series along a secondary vertical axis, click the data series for Rainfall, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
13. On the Layout tab, in the Current Selection group, click Format Selection.

14. Under Series Options, select Secondary Axis, and then click Close.
15. On the Layout tab, in the Labels group, click Axis Titles, and then do the following:
    1. To add a primary vertical axis title, click Primary Vertical Axis Title, and then click the type of vertical axis title that you want.

Tip  For our line chart, we used Rotated Title.

2. To add a secondary vertical axis title, click Secondary Vertical Axis Title, and then click the type of vertical axis title that you want.

Tip  For our line chart, we used Rotated Title.

3. Click each title, type the text that you want, and then press ENTER.

Tip  For our line chart, we typed Particulate level in the primary vertical axis title, and Daily Rainfall in the secondary vertical axis title.

16. Click the plot area (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.) of the chart, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
17. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our line chart, we used the Subtle Effect - Dark 1.

18. Click the chart area of the chart.
19. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our line chart, we used the Subtle Effect - Accent 3.

20. If you want to use theme colors different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our line chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Display hidden data and empty cells in a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a stock chart

Excel 2007

As its name implies, a stock chart is most often used to illustrate the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you can use a stock chart to indicate the fluctuation of daily or annual average temperatures.

If you use a stock chart to display the fluctuation of stock prices, you can also incorporate the trading volume.

In this article

Learn more about plotting data in a stock chart

Create a stock chart

Save a chart as a template

Learn more about plotting data in a stock chart

The way stock chart data is organized on your worksheet is very important. 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.

A stock chart that also measures volume displays two vertical (value) axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.). The vertical axis on the left shows the volume of stocks traded, and the vertical axis on the right shows the stock prices.

When you create a stock chart, you can choose one of the following stock chart subtypes:

• High-low-close  The high-low-close stock chart is often used to illustrate 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).

 Top of Page

Create a stock chart

So, how did we create this stock chart? The following procedure will help you create a stock chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a stock chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the stock chart.
3. On the Insert tab, in the Charts group, click Other Charts.

4. Under Stock, click Volume-High-Low-Close.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our stock chart, we used Style 26.

7. On the Design tab, in the Chart Layouts group, click the chart layout that you want to use.

Tip  For our stock chart, we used Layout 4.

8. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

Tip  For our stock chart, we used 5" for both shape height and shape width.

9. In the chart, click the chart title, and then type the text that you want.

Tip  For our stock chart, we typed Mutual Fund.

10. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our stock chart, we used 14.

11. To apply a special formatting effect to the plot area and chart area, click that chart element, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box), and then do the following:
    1. On the Format tab, in the Shape Styles group, click the More button , and then click the effect that you want to use.

Tip  For our stock chart, we used the Subtle Effect - Accent 3 for the plot area, and Subtle Effect - Accent 6 for the chart area.

12. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our stock chart, we used the Office theme.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Present your data in a surface chart

Excel 2007

A surface chart shows a three-dimensional surface that connects a set of data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.). A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, the colors and patterns in a surface chart indicate areas that contain the same range of values. Unlike other chart types, a surface chart does not use colors to distinguish the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) — colors are used to distinguish the values instead. To enhance a surface chart, you can change the colors and use transparency to display color bands that are obscured in the back of the chart.

In this article

Learn more about plotting data in a surface chart

Create a surface chart

Change the colors in an existing surface chart

Save a chart as a template

Learn more about plotting data in a surface chart

Data that is arranged in columns or rows on a worksheet can be plotted in a surface chart. A surface chart is useful when you want to find optimum combinations between two sets of data. To create a surface chart, both categories and data series should contain numeric values.

Surface charts include the following chart subtypes:

• 3-D surface  3-D surface charts show trends in values across two dimensions in a continuous curve. Color bands in a surface chart do not represent the data series; they represent the distinction between the values. This chart shows a 3-D view of the data, which can be imagined as a rubber sheet stretched over a 3-D column chart. It is typically used to show relationships between large amounts of data that may otherwise be difficult to see.

• Wireframe 3-D surface  When displayed without color on the surface, a 3-D surface chart is called a wireframe 3-D surface chart. This chart shows only the lines. A 3-D surface chart that is displayed without color bands on any surface is called a wireframe 3-D surface chart. This chart shows only the lines.

 Note    A wireframe 3-D surface chart is not easy to read, but this chart type is useful for faster plotting of large data sets.

• Contour  Contour charts are surface charts viewed from above, similar to 2-D topographic maps. In a contour chart, color bands represent specific ranges of values. The lines in a contour chart connect interpolated points of equal value.

• Wireframe contour  Wireframe contour charts are also surface charts viewed from above. Without color bands on the surface, a wireframe chart shows only the lines.

 Note    Wireframe contour charts are not easy to read. You may want to use a 3-D surface chart instead.

 Top of Page

Create a surface chart

So, how did we create this surface chart? The following procedure will help you create a surface chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot in a surface chart.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.

2. Select the data that you want to plot in the surface chart.

 Note    It is best not to include row or column headings in the selection. If you select the headings with your data, the chart may produce different results.

3. On the Insert tab, in the Charts group, click Other Charts.

4. Under Surface, click 3-D Surface.
5. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

6. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Tip  For our surface chart, we used Style 34.

7. To move the legend, do the following:
   1. On the chart, right-click the legend, and then click Format Legend on the shortcut menu.
   2. Under Legend Position, click the position that you want.

Tip  For our surface chart, we used Top.

8. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

Tip  For our surface chart, we used 6.5 for the shape height and 6.0 for the shape width.

9. To add, format, and position a chart title in the chart, click the chart area (chart area: The entire chart and all its elements.), and then do the following:
   1. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

2. In the chart, click the chart title, and then type the text that you want.

Tip  For our surface chart, we typed Tensile Strength Measurements.

3. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

Tip  For our surface chart, we used 14.

10. To add axis titles, first click the chart area of the chart. Then, on the Layout tab, in the Labels group, click Axis Titles, and then do the following:
    1. To add a title to the horizontal axis, click Primary Horizontal Axis Title, and then click the option that you want.

Tip  For our surface chart, we used Title Below Axis.

2. To add a title to the vertical axis, click Primary Vertical Axis Title, and then click the option that you want.

Tip  For our surface chart, we used Rotated Title.

3. To add a title to the depth axis, click Depth Axis Title, and then click the option that you want.

Tip  For our surface chart, we used Horizontal Title.

4. Click each axis title, type the text that you want, and then press ENTER.

Tip  For our surface chart, we typed Seconds for the horizontal axis, Tensile strength for the vertical axis, and Temperature for the depth axis.

11. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
    1. On the Page Layout tab, in the Themes group, click Themes.

2. Under Built-in, click the theme that you want to use.

Tip  For our surface chart, we used the Office theme.

 Top of Page

Change the colors in an existing surface chart

Because the colors in a surface chart are based on the values instead of the data series, you cannot select the colors in the chart itself. However, you can select the corresponding color keys in the legend of a surface chart to make the formatting changes that you want. In Microsoft Office Excel 2007, you can even use transparency to show data that is obscured in the back of a surface chart.

1. If a legend is not displayed, do the following:
   1. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Labels group, click Legend, and then click the legend position that you want.
2. In the legend, click the legend key for which you want to change the format.
3. On the Layout tab, in the Current Selection group, click Format Selection.
4. In the Format Band dialog box, click any formatting category, and then select the formatting options that you want.

Tip  If you want to use transparency, click Solid Fill, Gradient Fill, or Picture or texture fill, select any color, picture, or texture options, and then move the Transparency slider to the percentage of transparency that you want. You can also enter a percentage in the Transparency box.

5. Repeat steps 2-4 for each level you want to format.

 Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

1. Click the chart that you want to save as a template.
2. On the Design tab, in the Type group, click Save as Template.

3. In the File name box, type a name for the template.

Tip  Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note    A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

 Top of Page

See Also

• Available chart types
• Change the layout or style of a chart
• Change the shape fill, outline, or effects of chart elements
• Create a chart
• Format chart elements
• Print a chart

 

 

Excel > Charts > Creating charts

Use a picture in a chart

Excel 2007

You can enhance a chart by inserting a picture (such as a business logo) in the chart area (chart area: The entire chart and all its elements.). Or you can use a picture fill to draw attention to specific chart elements such as the chart area, the plot area (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.), the data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) (in column, bar, area, bubble, 3-D line, and filled radar charts), the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.) (in 2-D and 3-D charts), or the 3-D walls and floor (3-D walls and floor: The areas surrounding many 3-D chart types that give dimension and boundaries to the chart. Two walls and one floor are displayed within the plot area.) of a chart.

You can also copy a picture to a chart or to chart elements such as data markers in 2-D line charts, scatter charts, or an unfilled radar chart. If a picture is no longer needed, you can remove it.

What do you want to do?

Insert a picture in a chart

Fill a chart element with a picture

Copy a picture to a chart or chart element

Remove a picture from a chart

Insert a picture in a chart

1. Click the chart area of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Insert group, click Picture.

3. Locate the picture that you want to insert, and then double-click it.

Tip  To add multiple pictures, hold down CTRL while you click the pictures that you want to insert, and then click Insert.

4. To change the size of the picture, drag a sizing handle (sizing handle: One of the small circles or squares that appears at the corners and sides of a selected object. You drag these handles to change the size of the object.) away from or toward the center of the picture while doing one of the following:
   • To keep the center of the picture in the same place, hold down CTRL while you drag the sizing handle.
   • To maintain the proportions of the picture, hold down SHIFT while you drag the sizing handle.
   • To both keep the center of a picture in the same place and maintain its proportions, hold down CTRL+SHIFT while you drag the sizing handle.

Tip  You can also resize a picture to an exact height and width by selecting the picture and then entering the size that you want in the Shape Height and Shape Width boxes (Format tab, Size group).

5. To move the picture, drag it to the location that you want.

 Note    Pictures that you insert in your chart are embedded in your chart and therefore increase the file size. To reduce the file size, you can link to a picture instead of inserting it. In the Insert Picture dialog box, click the picture that you want to insert, click the arrow next to Insert, and then click Link to File.

 Top of Page

Fill a chart element with a picture

1. On a chart, click the chart area, the plot area, the data marker, the legend, the 3-D wall, or the 3-D floor that you want to fill with a picture.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Shape Styles group, click Shape Fill.

3. Click Picture.
4. Locate the picture that you want to insert, and then double-click it.

 Top of Page

Copy a picture to a chart or chart element

1. On a worksheet or chart, select the picture that you want to copy.
2. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  To copy a selection, press CTRL+C.

3. Click the chart area or the chart element into which you want to paste the picture.
4. On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut  To paste a selection, press CTRL+V.

 Top of Page

Remove a picture from a chart

Do one of the following:

• To remove a picture that was inserted in the chart, click that picture, and then press DELETE.
• To remove a picture that was filled into a chart element, select that chart element, and then do the following:
  1. On the Layout tab, in the Current Selection group, click Format Selection.

2. Click Fill, and then under Fill, click Automatic.

Tip  To remove the picture and any other fill effects that are applied to the selected chart element, click No Fill.

 Top of Page

See Also

• Change the size of a picture, shape, text box, or WordArt
• Create a picture from cells, a chart, or an object
• Insert a picture or clip art
• Reduce the file size of a picture

 

 

Excel > Charts > Creating charts

Print a chart

Before you print a chart, you can adjust where the chart will print on the page by moving and sizing the chart in Page Layout or Page Break Preview view. You can also quickly print a chart without the worksheet data.

For faster printing, you can change the print quality of the chart to draft quality or black and white printing.

What do you want to do?

Adjust and print a chart on a worksheet

Set page options for a chart

Set page margins for a chart

Print a chart without worksheet data

Change the print quality of a chart

Adjust and print a chart on a worksheet

1. Click the worksheet that contains the chart that you want to print.
2. On the View tab, in the Workbook Views group, click Page Layout or Page Break Preview.

3. To move the chart, drag it to a preferred location on the page that you want to print.
4. To resize the chart, do one of the following:
   • Click the chart, and then drag the sizing handles to the size that you want.
   • On the Format tab, in the Size group, enter the size in the Shape Height and Shape Width box.

5. Tip  If a worksheet contains more than one chart, you may be able to print the charts on one page by reducing the size of the charts.
6. Click the worksheet.
7. Click the Microsoft Office Button , and then click Print.

 Top of Page

Set page options for a chart

1. On the Page Layout tab, in the Page Setup group, click the Dialog Box Launcher .

2. Do one or more of the following:
   • On the Page tab, specify the orientation, paper size, print quality, and the page number of the first page.

 Note    You cannot adjust the scaling of a chart.

Tip  To quickly specify the orientation of the printed pages, on the Page Layout tab, in the Page Setup group, click Orientation, and then click Portrait or Landscape.

 Top of Page

Set page margins for a chart

1. On the Page Layout tab, in the Page Setup group, click Margins.

2. Do one of the following:
   • To use predefined margins, click the margin option that you want.
   • To specify custom page margins, click Custom Margins, and then in the Top, Bottom, Left, and Right boxes, enter the margin sizes that you want.
   • To set header or footer margins, click Custom Margins, and then enter a new margin size in the in the Header or Footer box. Setting the header or footer margins changes the distance from the top edge of the paper to the header or from the bottom edge of the paper to the footer.

 Note    The header and footer settings should be smaller than your top and bottom margin settings, and larger than or equal to the minimum printer margins.

• To see how the margins will affect the printed worksheet, click Print Preview. To adjust the margins in print preview, click Show Margins, and then drag the black margin handles on either side and at the top of the page.

 Note    You cannot center the page horizontally or vertically for charts.

 Top of Page

Print a chart without worksheet data

You can print one chart without worksheet data per page.

1. Click the chart that you want to print.
2. Click the Microsoft Office Button , and then click Print.

Under Print what, Selected Chart is selected.

Tip  You can move and size the chart area of the chart as needed before you print the chart.

 Top of Page

Change the print quality of a chart

1. Click the chart that you want to print.
2. On the Page Layout tab, in the Page Setup group, click the Dialog Box Launcher .

3. On the Chart tab, under Printed quality, select the Draft quality check box, the Print in black and white check box, or both.

Tip  To see the result of the print quality settings you select, click Print Preview.

4. Click Print.

 Top of Page

See Also

• Create a chart
• Overview of charting

 

 

Excel > Charts > Creating charts

Rename a chart

When you create charts, Microsoft Office Excel assigns a default name to each chart by using the following naming convention: Chart1, Chart2, and so on. However, you can change the name of each chart to make it more meaningful to you.

1. Click the chart that you want to rename.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Layout tab, in the Properties group, click the Chart Name text box.

Tip  You may have to click the Properties icon in the Properties group to expand the group.

3. Type a new name.
4. Press ENTER.

See Also

• Create a chart