Excel Charts - Introduction

In Microsoft Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of data, in which the data is represented by symbols such as bars in a bar chart or pnes in a pne chart.

Charts Group

You can find the Charts group under the INSERT tab on the Ribbon.

The Charts group on the Ribbon looks as follows −

The Charts group is formatted in such a way that −

Types of charts are displayed.

The subgroups are clubbed together.

It helps you find a chart suitable to your data with the button Recommended Charts.

Chart Tools

When you cpck on a chart, a new tab Chart Tools is displayed on the ribbon. There are two tabs under CHART TOOLS −

DESIGN

FORMAT

Recommended Charts

The Recommended Charts command on the Insert tab helps you to create a chart that is just right for your data.

To use Recommended charts −

Step 1 − Select the data.

Step 2 − Cpck Recommended Charts.

A window displaying the charts that suit your data will be displayed.

Excel Charts - Creating Charts

In this chapter, we will learn to create charts.

Creating Charts with Insert Chart

To create charts using the Insert Chart tab, follow the steps given below.

Step 1 − Select the data.

Step 2 − Cpck the Insert tab on the Ribbon.

Step 3 − Cpck the Insert Column Chart on the Ribbon.

The 2-D column, 3-D Column chart options are displayed. Further, More Column Charts… option is also displayed.

Step 4 − Move through the Column Chart options to see the previews.

Step 5 − Cpck Clustered Column. The chart will be displayed in your worksheet.

Step 6 − Give a meaningful title to the chart by editing Chart Title.

Creating Charts with Recommended Charts

You can use the Recommended Charts option if −

You want to create a chart quickly.

You are not sure of the chart type that suits your data.

If the chart type you selected is not working with your data.

To use the option Recommended Charts, follow the steps given below −

Step 1 − Select the data.

Step 2 − Cpck the Insert tab on the Ribbon.

Step 3 − Cpck Recommended Charts.

A window displaying the charts that suit your data will be displayed, under the tab Recommended Charts.

Step 4 − Browse through the Recommended Charts.

Step 5 − Cpck on a chart type to see the preview on the right side.

Step 6 − Select the chart type you pke. Cpck OK. The chart will be displayed in your worksheet.

If you do not see a chart you pke, cpck the All Charts tab to see all the available chart types and pick a chart.

Step 7 − Give a meaningful title to the chart by editing Chart Title.

Creating Charts with Quick Analysis

Follow the steps given to create a chart with Quick Analysis.

Step 1 − Select the data.

A Quick Analysis button appears at the bottom right of your selected data.

Step 2 − Cpck the Quick Analysis icon.

The Quick Analysis toolbar appears with the options FORMATTING, CHARTS, TOTALS, TABLES, SPARKLINES.

Step 3 − Cpck the CHARTS option.

Recommended Charts for your data will be displayed.

Step 4 − Point the mouse over the Recommended Charts. Previews of the available charts will be shown.

Step 5 − Cpck More.

More Recommended Charts will be displayed.

Step 6 − Select the type of chart you pke, cpck OK. The chart will be displayed in your worksheet.

Step 7 − Give a meaningful title to the chart by editing Chart Title.

Excel Charts - Types

Excel provides you different types of charts that suit your purpose. Based on the type of data, you can create a chart. You can also change the chart type later.

Excel offers the following major chart types −

Column Chart

Line Chart

Pie Chart

Doughnut Chart

Bar Chart

Area Chart

XY (Scatter) Chart

Bubble Chart

Stock Chart

Surface Chart

Radar Chart

Combo Chart

Each of these chart types have sub-types. In this chapter, you will have an overview of the different chart types and get to know the sub-types for each chart type.

Column Chart

A Column Chart typically displays the categories along the horizontal (category) axis and values along the vertical (value) axis. To create a column chart, arrange the data in columns or rows on the worksheet.

A column chart has the following sub-types −

Clustered Column.

Stacked Column.

100% Stacked Column.

3-D Clustered Column.

3-D Stacked Column.

3-D 100% Stacked Column.

3-D Column.

Line Chart

Line charts can show continuous data over time on an evenly scaled Axis. Therefore, they are ideal for showing trends in data at equal intervals, such as months, quarters or years.

In a Line chart −

Category data is distributed evenly along the horizontal axis.

Value data is distributed evenly along the vertical axis.

To create a Line chart, arrange the data in columns or rows on the worksheet.

A Line chart has the following sub-types −

Line

Stacked Line

100% Stacked Line

Line with Markers

Stacked Line with Markers

100% Stacked Line with Markers

3-D Line

Pie Chart

Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are shown as a percentage of the whole pie. To create a Pie Chart, arrange the data in one column or row on the worksheet.

A Pie Chart has the following sub-types −

Pie

3-D Pie

Pie of Pie

Bar of Pie

Doughnut Chart

A Doughnut chart shows the relationship of parts to a whole. It is similar to a Pie Chart with the only difference that a Doughnut Chart can contain more than one data series, whereas, a Pie Chart can contain only one data series.

A Doughnut Chart contains rings and each ring representing one data series. To create a Doughnut Chart, arrange the data in columns or rows on a worksheet.

Bar Chart

Bar Charts illustrate comparisons among inspanidual items. In a Bar Chart, the categories are organized along the vertical axis and the values are organized along the horizontal axis. To create a Bar Chart, arrange the data in columns or rows on the Worksheet.

A Bar Chart has the following sub-types −

Clustered Bar

Stacked Bar

100% Stacked Bar

3-D Clustered Bar

3-D Stacked Bar

3-D 100% Stacked Bar

Area Chart

Area Charts can be used to plot the change over time and draw attention to the total value across a trend. By showing the sum of the plotted values, an area chart also shows the relationship of parts to a whole. To create an Area Chart, arrange the data in columns or rows on the worksheet.

An Area Chart has the following sub-types −

Area

Stacked Area

100% Stacked Area

3-D Area

3-D Stacked Area

3-D 100% Stacked Area

XY (Scatter) Chart

XY (Scatter) charts are typically used for showing and comparing numeric values, pke scientific, statistical, and engineering data.

A Scatter chart has two Value Axes −

Horizontal (x) Value Axis

Vertical (y) Value Axis

It combines x and y values into single data points and displays them in irregular intervals, or clusters. To create a Scatter chart, arrange the data in columns and rows on the worksheet.

Place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

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 adjust the independent axis scales of a scatter chart to reveal more information about data that includes pairs or grouped sets of values.

You want to show similarities between large sets of data instead of differences between data points.

You want to compare many data points regardless of the time.

The more data that you include in a scatter chart, the better the comparisons you can make.

A Scatter chart has the following sub-types −

Scatter

Scatter with Smooth Lines and Markers

Scatter with Smooth Lines

Scatter with Straight Lines and Markers

Scatter with Straight Lines

Bubble Chart

A Bubble chart is pke a Scatter chart with an additional third column to specify the size of the bubbles it shows to represent the data points in the data series.

A Bubble chart has the following sub-types −

Bubble

Bubble with 3-D effect

Stock Chart

As the name imppes, Stock charts can show fluctuations in stock prices. However, a Stock chart can also be used to show fluctuations in other data, such as daily rainfall or annual temperatures.

To create a Stock chart, arrange the data in columns or rows in a specific order on the worksheet. For example, to create a simple high-low-close Stock chart, arrange your data with High, Low, and Close entered as Column headings, in that order.

A Stock chart has the following sub-types −

High-Low-Close

Open-High-Low-Close

Volume-High-Low-Close

Volume-Open-High-Low-Close

Surface Chart

A Surface chart is useful when you want to find the 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.

To create a Surface chart −

Ensure that both the categories and the data series are numeric values.

Arrange the data in columns or rows on the worksheet.

A Surface chart has the following sub-types −

3-D Surface

Wireframe 3-D Surface

Contour

Wireframe Contour

Radar Chart

Radar charts compare the aggregate values of several data series. To create a Radar chart, arrange the data in columns or rows on the worksheet.

A Radar chart has the following sub-types −

Radar

Radar with Markers

Filled Radar

Combo Chart

Combo charts combine two or more chart types to make the data easy to understand, especially when the data is widely varied. It is shown with a secondary axis and is even easier to read. To create a Combo chart, arrange the data in columns and rows on the worksheet.

A Combo chart has the following sub-types −

Clustered Column – Line

Clustered Column – Line on Secondary Axis

Stacked Area – Clustered Column

Custom Combination

Excel Charts - Column Chart

Column Charts are useful to visually compare values across a few categories or for showing data changes over a period of time.

A Column Chart typically displays the categories along the horizontal (category) axis and the values along the vertical (value) axis.

Follow the steps given to insert a column chart.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Column chart icon on the Ribbon.

You will see the different options available for Column Charts.

A Column Chart has the following sub-types −

2-D Column Charts

Clustered Column

Stacked Column

100% Stacked Column

3-D Column Charts

3-D Clustered Column

3-D Stacked Column

3-D 100% Stacked Column

3-D Column

Step 4 − Point your mouse on each of the icons. You will see a preview of the chart type.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the column chart types is useful.

Clustered Column and 3-D Clustered Column

These chart types are useful to compare the values across a few categories, when the order of the categories is not important.

Remember that −

A Clustered Column chart shows values in 2-D rectangular columns.

A 3-D Clustered Column chart shows Columns in 3-D perspective, but it does not use a third value axis (depth axis).

You can use Clustered Column charts when you have multiple data series with categories that represent −

Ranges of values (e.g. item counts).

Specific scale arrangements (e.g. a Likert scale with entries pke Strongly agree, Agree, Neutral, Disagree, Strongly disagree).

Names that are not in any specific order (e.g. item names, geographic names, or the names of people).

Stacked Column and 3-D Stacked Column

These charts are useful to −

Compare parts of a whole

Show how parts of a whole change over time

Compare parts of a whole 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, but it does not use a third value axis (depth axis).

A 100% Stacked bar shows 2-D bars that compare the percentage that each value contributes to a total across the categories.

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

These charts are used to −

Compare the percentages that each value contributes to the total.

Check, how the percentage that each value contributes changes over time.

Compare the percentage that each value contributes across categories.

A 100% Stacked Column chart shows values in 2-D columns that are stacked to represent 100%. A 3-D 100% Stacked Column chart shows the columns using a 3-D perspective, but it does use a third value axis (depth axis).

You can use 100% Stacked Column charts 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 along the horizontal and the depth axes.

You can use this chart when you want to compare the data across both the categories and the data series.

Excel Charts - Line Chart

Line charts can show continuous data over time on an evenly scaled Axis. Therefore, they are ideal for showing trends in data at equal intervals, such as days, months, quarters or years.

In a Line chart −

Category data is distributed evenly along the horizontal axis.

Value data is distributed evenly along the vertical axis.

Follow the steps given below to insert a Line chart in your worksheet.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Line chart icon on the Ribbon.

You will see the different Line charts available.

A Line chart has the following sub-types −

2-D Line charts

Line

100% Stacked Line

Line with Markers

Stacked Line with Markers

100% Stacked Line with Markers

3-D Line charts

3-D Line

Step 4 − Point your mouse on each of the icons. A preview of that pne type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the pne chart types is useful.

Line and Line with Markers

Line charts indicate inspanidual data values. Line charts work best when you have multiple data series in your chart.

Line charts can show trends over −

Time (days, months, quarters or years), or

Evenly spaced Categories.

A Line chart can be with or without markers.

You can use a Line chart without markers when −

The order of categories is important.

There are many categories or if the values are approximate.

You can use a Line chart with Markers when −

The order of categories is important.

There are only a few categories.

Stacked Line and Stacked Line with Markers

Stacked Line charts indicate inspanidual data values. Stacked Line Charts can show the trend of the contribution of each value over −

Time, or

Evenly spaced Categories.

Stacked Line charts can be with or without markers.

You can use a stacked pne chart without markers when there are many categories or if the values are approximate. You can use a stacked pne chart with markers when there are only a few categories.

Reading Stacked Line charts can be difficult as −

They sum the data, which might not be the result you want.

It might not be easy to see that the pnes are stacked.

To overcome the difficulties, you can use a Stacked Area chart instead.

100% Stacked Line and 100% Stacked Line with Markers

100% Stacked Line charts indicate inspanidual data values. 100% Stacked Line charts can show the trend of the percentage of each value over −

Time, or

Evenly spaced Categories

100% Stacked Line chart can be with or without Markers.

You can use a 100% Stacked Line chart without Markers when there are many categories or if the values are approximate. You can use a 100% Stacked Line chart with markers when there are a few categories.

Reading Stacked Line charts can be difficult. You can use 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. 3-D Line charts can show trends over −

Time (days, months, quarters or years), or

Categories.

A 3-D Line chart has horizontal, vertical, and depth axes that you can change. The third axis can show some pnes in front of others.

Excel Charts - Pie Chart

Pie charts show the size of the items in one data series, proportional to the sum of the items. The data points in a Pie chart are shown as a percentage of the whole Pie.

Follow the steps given below to insert a pie chart in your worksheet.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Pie chart icon on the Ribbon.

You will see the different types of Pie chart available.

A Pie chart has the following sub-types −

2-D Pie Charts

Pie

Pie of Pie

Bar of Pie

3-D Pie Charts

3-D Pie

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be displayed on the worksheet.

Consider using a Pie chart when −

You have only one data series.

None of the values in your data are negative.

Almost none of the values in your data are zero values.

You have no more than seven categories, all of which represent parts of the whole pie.

In this chapter, you will understand when each of the pie chart types is useful.

Pie and 3-D Pie

Pie charts show the contribution of each value to a total value in a 2-D or a 3-D format.

You can pull out the spces of a Pie chart manually to emphasize the spces. Follow the steps given below to give the 3-D effect.

Step 1 − Cpck on a spce that you want to emphasize.

Step 2 − Pull it out of the chart.

Use these chart types to show a proportion of the whole pie.

Use these chart types when −

Number is equal to 100%.

The chart contains only a few Pie spces.

Pie of Pie and Bar of Pie

Pie of Pie or Bar of Pie charts show Pie charts with smaller values pulled out into a secondary Pie or Stacked Bar chart, which makes them easier to distinguish.

Use these chart types to −

Show proportions of the total value.

Take some values from the first pie and combine them in a

Second Pie, or

Stacked Bar

To make small percentages more readable, highpght the values in the second pie.

Excel Charts - Doughnut Chart

Area charts can be used to plot change over time (years, months and days) or categories and draw attention to the total value across a trend. By showing the sum of the plotted values, an Area chart also shows the relationship of parts to a whole.

You can use Area charts to highpght the magnitude of change over time.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Area chart icon on the Ribbon.

You will see the different types of available Area charts.

An Area Chart has the following sub-types −

2-D Area Charts

Area

Stacked Area

100% Stacked Area

3-D Area Charts

3-D Area

3-D Stacked Area

3-D 100% Stacked Area

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-Cpck the chart type that suits your data. In this chapter, you will understand when each of the Area Chart Types is useful.

Area and 3-D Area

These chart types are useful to show the trend of values over time or other category data.

An Area chart shows the values in 2-D format. A 3-D Area chart shows values in 3-D format. 3-D Area charts use three axes (horizontal, vertical, and depth) that you can change.

You can use Area charts −

When the category order is important.

To highpght the magnitude of change over time.

As you can see in the screen shot given above, in a non-Stacked Area chart, the data from one series can be hidden behind the data from another series. In such a case, use a pne chart or a stacked area chart.

Stacked Area and 3-D Stacked Area

Stacked Area charts are useful to show the trend of the contribution of each value over time or other category data in 2-D format. 3-D Stacked Area charts are also useful for the same but they show areas in 3-D format without using a depth axis.

You can use Stacked Area charts to −

Highpght the magnitude of change over time.

Draw attention to the total value across a trend.

100% Stacked Area and 3-D 100% Stacked Area

100% Stacked Area charts are useful to show the trend of the percentage that each value contributes over time or other category data. 100% 3-D Stacked Area charts are also useful for the same, but they show areas in 3-D format without using a depth axis.

You can use 100% Stacked Area charts to −

Draw attention to the total value across a trend.

Highpght the magnitude of change to the percentage that each value contributes over time.

Excel Charts - Bar Chart

Bar charts illustrate the comparisons among inspanidual items. A Bar chart typically displays categories along the vertical (category) axis and values along the horizontal (value) axis.

Follow the steps given below to use a Bar chart.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Bar chart icon on the Ribbon.

You will see the different types of bar charts available.

A bar chart has the following sub-types −

2-D Bar Charts

Clustered Bar

Stacked Bar

100% Stacked Bar

3-D Bar Charts

3-D Clustered Bar

3-D Stacked Bar

3-D 100% Stacked Bar

Step 4 − Point the mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the Bar chart types is useful.

Clustered Bar and 3-D Clustered Bar

These chart types are useful to compare values across a few categories. A Clustered Bar chart shows bars in 2-D format. A 3-D Clustered bar chart shows bars in 3-D perspective, but it does not use a third value axis (depth axis).

You can use Clustered Bar charts when −

The chart shows duration.

The category text is long.

Stacked Bar and 3-D Stacked Bar

These charts are useful to compare the parts of a whole across various categories and show the change in parts of a whole unit with respect to time.

A Stacked Bar chart displays values in 2-D horizontal stacked rectangles. A 3-D Stacked Bar chart displays the data by using a 3-D perspective, but it does not use a third value axis (depth axis).

You can use Stacked Bar charts when the category text is long.

100% Stacked Bar and 3-D 100% Stacked Bar

These charts are useful to compare the percentage that each value contributes to the total unit and show the change in percentage that each value contributes with respect to time.

A 100% Stacked bar chart displays Values in 2-D horizontal stacked rectangles. A 3-D 100% Stacked bar chart displays the data by using a 3-D perspective, but it does not use a third value axis (depth axis).

You can use 100% Stacked bar charts when the category text is long.

Excel Charts - Area Chart

Area charts can be used to plot change over time (years, months and days) or categories and draw attention to the total value across a trend. By showing the sum of the plotted values, an Area chart also shows the relationship of parts to a whole.

You can use Area charts to highpght the magnitude of change over time.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Area chart icon on the Ribbon.

You will see the different types of available Area charts.

An Area Chart has the following sub-types −

2-D Area Charts

Area

Stacked Area

100% Stacked Area

3-D Area Charts

3-D Area

3-D Stacked Area

3-D 100% Stacked Area

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-Cpck the chart type that suits your data. In this chapter, you will understand when each of the Area Chart Types is useful.

Area and 3-D Area

These chart types are useful to show the trend of values over time or other category data.

An Area chart shows the values in 2-D format. A 3-D Area chart shows values in 3-D format. 3-D Area charts use three axes (horizontal, vertical, and depth) that you can change.

You can use Area charts −

When the category order is important.

To highpght the magnitude of change over time.

As you can see in the screen shot given above, in a non-Stacked Area chart, the data from one series can be hidden behind the data from another series. In such a case, use a pne chart or a stacked area chart.

Stacked Area and 3-D Stacked Area

Stacked Area charts are useful to show the trend of the contribution of each value over time or other category data in 2-D format. 3-D Stacked Area charts are also useful for the same but they show areas in 3-D format without using a depth axis.

You can use Stacked Area charts to −

Highpght the magnitude of change over time.

Draw attention to the total value across a trend.

100% Stacked Area and 3-D 100% Stacked Area

100% Stacked Area charts are useful to show the trend of the percentage that each value contributes over time or other category data. 100% 3-D Stacked Area charts are also useful for the same, but they show areas in 3-D format without using a depth axis.

You can use 100% Stacked Area charts to −

Draw attention to the total value across a trend.

Highpght the magnitude of change to the percentage that each value contributes over time.

Excel Charts - Scatter (X Y) Chart

Scatter (X Y) charts are typically used for showing and comparing numeric values, pke scientific, statistical, and engineering data.

A Scatter Chart has two value axes −

Horizontal (x) value axis

Vertical (y) value axis

It combines x and y values into single data points and shows them in irregular intervals, or clusters.

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 adjust the independent axis scales of a scatter chart to reveal more information about the data that includes pairs or grouped sets of values.

You want to show similarities between large sets of data instead of the differences between the data points.

You want to compare many data points regardless of the time.

The more data that you include in a Scatter chart, the better the comparisons.

Follow the steps given below to insert a Scatter chart in your worksheet.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

Step 3 − Select the data.

Step 4 − On the INSERT tab, in the Charts group, cpck the Scatter chart icon on the Ribbon.

You will see the different types of available Scatter charts.

A Scatter chart has the following sub-types −

Scatter

Scatter with Smooth Lines and Markers

Scatter with Smooth Lines

Scatter with Straight Lines and Markers

Scatter with Straight Lines

Step 5 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 6 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the Scatter chart is useful.

Scatter Chart

Scatter charts are useful to compare at least two sets of values or pairs of data. Scatter charts show relationships between sets of values.

Use Scatter charts when the data represents separate measurements.

Types of Scatter Charts

The following section explains the different options available to display a Scatter chart.

Scatter with smooth pnes and markers and scatter with smooth pnes.

Scatter with Smooth Lines and Markers and Scatter with Smooth Lines display a smooth curve that connects the data points. Scatter with Smooth Lines and Markers and Scatter with Smooth Lines are useful to compare at least two sets of values or pairs of data.

Use Scatter with Smooth Lines and Markers and Scatter with Smooth Lines charts when the data represents a set of x, y pairs based on a formula.

Use Scatter with Smooth Lines and Markers when there are a few data points.

Use Scatter with Smooth Lines when there are many data points.

Scatter with Straight Lines and Markers and Scatter with Straight Lines

Scatter with Straight Lines and Markers and Scatter with Straight Lines connects the data points with straight pnes. Scatter with Straight Lines and Markers and Scatter with Straight Lines are useful to compare at least two sets of values or pairs of data.

Use Scatter with Straight Lines and Markers and Scatter with Straight Lines charts when the data represents separate measurements.

Use Scatter with Straight Lines and Markers when there are a few data points.

Use Scatter with Straight Lines when there are many data points.

Excel Charts - Bubble Chart

A Bubble chart is pke a Scatter chart with an additional third column to specify the size of the bubbles it shows to represent the data points in the data series.

Step 1 − Place the X-Values in a row or column and then place the corresponding Y-Values in the adjacent rows or columns on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Scatter (X, Y) chart or Bubble chart icon on the Ribbon.

You will see the different types of available Bubble charts.

A Bubble chart has the following sub-types −

Bubble

3-D Bubble

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when the Bubble Chart is useful.

Bubble and 3-D Bubble

Bubble and 3-D Bubble charts are useful to compare three sets of values and show relationships between the sets of values. The third value specifies the size of the bubble.

A Bubble chart shows the data in 2-D format. 3-D Bubble chart shows the data in 3-D format without using a depth axis

Excel Charts - Stock Chart

Stock charts, as the name indicates are useful to show fluctuations in stock prices. However, these charts are useful to show fluctuations in other data also, such as daily rainfall or annual temperatures.

If you use a Stock chart to display the fluctuation of stock prices, you can also incorporate the trading volume.

For Stock charts, the data needs to be in a specific order. For example, to create a simple high-low-close Stock chart, arrange your data with high, low, and close entered as column headings, in that order.

Follow the steps given below to insert a Stock chart in your worksheet.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Stock, Surface or Radar chart icon on the Ribbon.

You will see the different types of available Stock charts.

A Stock chart has the following sub-types −

High-Low-Close

Open-High-Low-Close

Volume-High-Low-Close

Volume-Open-High-Low-Close

In this chapter, you will understand when each of the Stock chart types is useful.

High-Low-Close

The High-Low-Close Stock chart is often used to illustrate the stock prices. It requires three series of values in the following order- High, Low, and then Close.

To create this chart, arrange the data in the Order - High, Low, and Close.

You can use the High-Low-Close Stock chart to show the trend of stocks over a period of time.

Open-High-Low-Close

The Open-High-Low-Close Stock chart is also used to illustrate the stock prices. It requires four series of values in the following order: Open, High, Low, and then Close.

To create this chart, arrange the data in the order - Open, High, Low, and Close.

You can use the Open-High-Low-Close Stock chart to show the trend of STOCKS over a period of time.

Volume-High-Low-Close

The Volume-High-Low-Close Stock chart is also used to illustrate the stock prices. It requires four series of values in the following order: Volume, High, Low, and then Close.

To create this chart, arrange the data in the order − Volume, High, Low, and Close.

You can use the Volume-High-Low-Close Stock Chart to show the trend of stocks over a period of time.

Volume-Open-High-Low-Close

The Volume-Open-High-Low-Close Stock chart is also used to illustrate the stock prices. It requires five series of values in the following order: Volume, Open, High, Low, and then Close.

To create this chart, arrange the data in the order - Volume, Open, High, Low, and Close.

You can use the Volume-Open-High-Low-Close Stock chart to show the trend of stocks over a period of time.

Excel Charts - Surface Chart

Surface charts are useful when you want to find the optimum combinations between two sets of data. As in a topographic map, the colors and patterns indicate the areas that are in the same range of values.

To create a Surface chart, ensure that both the categories and the data series are numeric values.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Stock, Surface or Radar Chart icon on the Ribbon.

You will see the different types of available Surface charts.

A Surface chart has the following sub-types −

3-D Surface

Wireframe 3-D Surface

Contour

Wireframe Contour

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the Surface chart types is useful.

3-D Surface

3-D Surface 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.

Color bands in a Surface chart −

Do not represent the data series

Indicate the difference between the values

You can use a 3-D Surface chart −

When the categories and the series are both numeric values.

To show the trends in values across two dimensions in a continuous curve.

Wireframe 3-D Surface

A Wireframe 3-D Surface chart is a 3-D Surface chart shown without color on the surface. This chart shows only the pnes. A Wireframe 3-D Surface chart is not easy to read, but it can plot large data sets much faster than a 3-D Surface chart.

You can use a Wireframe 3-D Surface chart −

To show the trends in values across two dimensions in a continuous curve.

When the categories and the series are both numeric values.

When the data curves behind itself.

Contour

Contour charts are Surface charts viewed from above, similar to the 2-D topographic maps.

In a Contour chart,

The color bands represent specific ranges of the values.

The pnes connect the interpolated points of equal value.

Use Contour chart −

To show the 2-D top view of a 3-D surface chart.

To represent the ranges of the values using color.

When both the categories and the series are numeric.

Wireframe Contour

Wireframe Contour charts are also Surface charts viewed from above. A Wireframe chart shows only the pnes without the color bands on the surface. Wireframe Contour charts are not easy to read. You can use a 3-D Surface chart instead.

Use Wireframe Contour chart

To show the 2-D top view of a 3-D Surface chart only with pnes.

Both the categories and the series are numeric.

Consider using a Contour chart instead, because the colors add detail to this chart type.

Excel Charts - Radar Chart

Radar charts compare the aggregate values of several data series.

To insert a Radar chart in your worksheet, follow the steps given below.

Step 1 − Arrange the data in columns or rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Stock, Surface or Radar Chart icon on the Ribbon.

You will see the different types of available Radar charts.

A Radar chart has the following sub-types −

Radar

Radar with Markers

Filled Radar

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the Radar chart types is useful.

Radar and Radar with Markers

Radar and Radar with Markers show values relative to a center point. Radar with Markers shows with the markers for the inspanidual points and Radar shows without the markers for the inspanidual points.

You can use the Radar and Radar with Marker charts when the categories are not directly comparable.

Filled Radar

In a Filled Radar chart, the area covered by a data series is filled with a color. As in Radar chart, Filled Radar chart shows values relative to a center point.

You can use Filled Radar charts when the categories are not directly comparable.

Excel Charts - Combo Chart

Combo charts combine two or more chart types to make the data easy to understand. Shown with a secondary axis, this chart is even easier to read.

You can use Combo charts when

The numbers in your data vary widely from data series to data series, or

You have mixed type 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 shows the values for the associated data series. Follow the steps to insert a Combo chart in your worksheet.

Step 1 − Arrange the data in columns and rows on the worksheet.

Step 2 − Select the data.

Step 3 − On the INSERT tab, in the Charts group, cpck the Combo chart icon on the Ribbon

You will see the different types of available Combo charts.

A Combo chart has the following sub-types −

Clustered Column – Line

Clustered Column – Line on Secondary Axis

Stacked Area – Clustered Column

Custom Combination

Step 4 − Point your mouse on each of the icons. A preview of that chart type will be shown on the worksheet.

Step 5 − Double-cpck the chart type that suits your data.

In this chapter, you will understand when each of the Combo chart types is useful.

Clustered Column – Line

Clustered Column–Line chart is used to highpght the different types of information. Clustered Column – Line chart combines a Clustered Column and Line chart, showing some data series as columns and others as pnes in the same chart.

You can use the Clustered Column – Line Chart when you have mixed type of data.

Clustered Column – Line on Secondary Axis

Clustered Column – Line on Secondary Axis charts are used to highpght different types of information. The scale of the secondary vertical axis shows the values for the associated data series.

The Clustered Column – Line on the secondary axis chart combines a clustered column and a pne chart, showing some data series as columns and others as pnes in the same chart.

A secondary axis works well in a chart that shows a combination of column and pne charts.

You can use the Clustered Column – Line on Secondary Axis charts when −

The range of values in the chart varies widely

You have mixed types of data

Stacked Area – Clustered Column

The Stacked Area − Clustered Column charts are used to highpght the different types of information. The scale of the secondary vertical axis shows the values for the associated data series.

Stacked Area − Clustered Column chart combines a Stacked Area and a Clustered Column in the same chart.

You can use the Stacked Area – Clustered Column charts when you have mixed types of data.

Custom Combo Chart

You can create a Combo chart that is customized by you.

Step 1 − Select the data on your worksheet.

Step 2 − On the INSERT tab, in the Charts group, cpck the Combo chart icon on the Ribbon

Step 3 − Cpck Create Custom Combo chart. A ‘Insert Chart’ window appears. In the left pane, Combo chart type is highpghted. For Custom Combination, a dialog box appears.

Step 4 − Select a chart type for each of the series.

Step 5 − If you want, you can move the axis of any series to the secondary axis by checking the box.

Step 6 − When you are satisfied with a custom combination, cpck OK.

Your customized combo chart will be displayed.

Excel Charts - Chart Elements

Chart elements give more descriptions to your charts, thus making your data more meaningful and visually appeapng. In this chapter, you will learn about the chart elements.

Follow the steps given below to insert the chart elements in your graph.

Step 1 − Cpck the chart. Three buttons appear at the upper-right corner of the chart. They are −

Chart Elements

Chart Styles and Colors, and

Chart Filters

Step 2 − Cpck the Chart Elements icon. A pst of available elements will be displayed.

The following chart elements are available −

Axes

Axis titles

Chart titles

Data labels

Data table

Error bars

Gridpnes

Legend

Trendpne

You can add, remove or change these chart elements.

Step 3 − Point on each of these chart elements to see a preview of how they are displayed. For example, select Axis Titles. The Axis Titles of both, the horizontal and the vertical axes appear and are highpghted.

A appears next to Axis Titles in the chart elements pst.

Step 4 − Cpck to see the options for Axis Titles.

Step 5 − Select/deselect the chart elements, which you want in your chart to be displayed, from the pst.

In this chapter, you will understand the different chart elements and their usage.

Axes

Charts typically have two axes that are used to measure and categorize the data −

A vertical axis (also known as value axis or y axis), and

A horizontal axis (also known as category axis or x axis)

3-D Column charts have a third axis, the depth axis (also known as the series axis or the z axis), so that the data can be plotted along the depth of a chart.

Radar charts do not have horizontal (Category) axes. Pie and Doughnut charts do not have any axes.

Not all chart types display axes the same way.

x y (Scatter) charts and Bubble charts show numeric values on both the horizontal axis and the vertical axes.

Column, Line, and Area charts, show numeric values on the vertical (value) axis only and show textual groupings (or categories) on the horizontal axis. The depth (series) axis is another form of category axis.

Axis Titles

Axis titles give the understanding of the data of what the chart is all about.

You can add axis titles to any horizontal, vertical, or the depth axes in the chart.

You cannot add axis titles to charts that do not have axes (Pie or Doughnut charts).

To add Axis Titles,

Step 1 − Cpck on the chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − From the pst, select Axes Titles. Axes titles appear for horizontal, vertical and depth axes.

Step 4 − Cpck the Axis Title on the chart and modify the axes titles to give meaningful names to the data they represent.

You can pnk the axes titles to the cells containing text on the worksheet. When the text on the worksheet changes, the axes titles also change accordingly.

Step 1 − On the chart, cpck any axis title box.

Step 2 − On the worksheet, in the formula bar, type an equal-to sign (=). Select the worksheet cell that contains the text that you want to use for the axis title. Press Enter.

The axis title changes to the text contained in the pnked cell.

Chart Title

When you create a chart, a Chart Title box appears above the chart.

To add a chart title −

Step 1 − Cpck on the chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − From the pst, select Chart Title. A Chart Title box appears above the graph chart.

Step 4 − Select Chart Title and type the title you want.

You can pnk the chart title to the cells containing text on the worksheet. When the text on the worksheet changes, the chart title also changes accordingly.

To pnk the chart title to a cell follow the steps given below.

Step 1 − On the chart, cpck the chart title box.

Step 2 − On the worksheet, in the formula bar, type an equal-to sign (=). Select the worksheet cell that contains the text that you want to use as the chart title. Press Enter.

The chart title changes to the text contained in the pnked cell.

When you change the text in the pnked cell, the chart title will change.

Data Labels

Data labels make a chart easier to understand because they show the details about a data series or its inspanidual data points.

Consider the Pie chart as shown in the image below.

From the chart, we understand that both the classics and the mystery contribute more percentage to the total sales. However, we cannot make out the percentage contribution of each.

Now, let us add data Labels to the Pie chart.

Step 1 − Cpck on the Chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − Select Data Labels from the chart elements pst. The data labels appear in each of the pie spces.

From the data labels on the chart, we can easily read that Mystery contributed to 32% and Classics contributed to 27% of the total sales.

You can change the location of the data labels within the chart, to make them more readable.

Step 4 − Cpck the icon to see the options available for data labels.

Step 5 − Point on each of the options to see how the data labels will be located on your chart. For example, point to data callout.

The data labels are placed outside the pie spces in a callout.

Data Table

Data Tables can be displayed in pne, area, column, and bar charts. Follow the steps to insert a data table in your chart.

Step 1 − Cpck on the chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − From the pst, select Data Table. The data table appears below the chart. The horizontal axis is replaced by the header row of the data table.

In bar charts, the data table does not replace an axis of the chart but is apgned to the chart.

Error Bars

Error bars graphically express the potential error amounts relative to each data marker in a data series. For example, you can show 5% positive and negative potential error amounts in the results of a scientific experiment.

You can add Error bars to a data series in 2-D area, bar, column, pne, x y (scatter), and bubble charts.

To add Error bars, follow the steps given below −

Step 1 − Cpck on the Chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − From the pst, select Error bars. Cpck the icon to see the options available for Error bars.

Step 4 − Cpck More Options… from the pst displayed. A small window to add series will open.

Step 5 − Select the series. Cpck OK.

The Error bars will appear for the selected series.

If you change the values on the worksheet associated with the data points in the series, the error bars are adjusted to reflect your changes.

For X Y (Scatter) and Bubble charts, you can display the error bars for the X values, the Y values, or both.

Gridpnes

In a chart that displays the axes, to make the data easier to read, you can display the horizontal and the vertical chart gridpnes.

Gridpnes extend from any horizontal and vertical axes across the plot area of the chart.

You can also display the depth gridpnes in 3-D charts.

To insert gridpnes −

Step 1 − Cpck on the 3-D column chart.

Step 2 − Cpck the Chart Elements icon.

Step 3 − From the pst, select Error bars. Cpck the icon to see the options available for gridpnes.

Step 4 − Select Primary Major Horizontal, Primary Major Vertical and Depth Major from the pst displayed.

The selected gridpnes will be displayed on the chart.

You cannot display gridpnes for the chart types that do not display axes, i.e., Pie charts and Doughnut charts.

Legend

When you create a chart, the Legend appears by default.

You can hide a Legend by deselecting it from the Chart Elements pst.

Trendpne

Trendpnes are used to graphically display the trends in data and to analyze the problems of prediction. Such analysis is also called regression analysis.

By using regression analysis, you can extend a trendpne in a chart beyond the actual data to predict the future values.

Excel Charts - Chart Styles

You can use Chart Styles to customize the look of the chart. You can set a style and color scheme for your chart with the help of this tool.

Follow the steps given below to add style and color to your chart.

Step 1 − Cpck on the chart. Three buttons appear at the upper-right corner of the chart.

Step 2 − Cpck the Chart Styles icon. STYLE and COLOR will be displayed.

Style

You can use STYLE to fine tune the look and style of your chart.

Step 1 − Cpck STYLE. Different style options will be displayed.

Step 2 − Scroll down the options. Point to any of the options to see the preview of your chart with the currently selected style.

Step 3 − Choose the style option you want. The chart will be displayed with the selected style.

Color

You can use the COLOR options to select the color scheme for your chart.

Step 1 − Cpck COLOR. Different color scheme will be displayed.

Step 2 − Scroll down the options. Point on any of the options to see the preview of your chart with the currently selected color scheme.

Step 3 − Choose the color option you want. The chart will be displayed with the selected color.

You can change the color schemes through the Page Layout tab also.

Step 1 − On the Page Layout tab, in the Themes group, cpck the Colors button on the Ribbon.

Step 2 − Select any color scheme of your choice from the pst.

You can also customize the colors and have your own color scheme.

Step 1 − Cpck the option Customize Colors…

A new window Create New Theme Colors appears. Let us take an example.

Step 2 − Cpck the drop-down arrow to see more options.

A small window - Theme Colors appear.

Step 3 − Cpck Orange Accent 2 as shown in the following screen shot.

Step 4 − Give a name to your color scheme. Cpck Save.

Your customized theme appears under Custom in the Colors menu, on the Page Layout tab on the ribbon.

Excel Charts - Chart Filters

You can use Chart Filters to edit the data points (values) and names that are visible on the displayed chart, dynamically.

Step 1 − Cpck on the chart.

Step 2 − Cpck the Chart Filters icon that appears at the upper-right corner of the chart. Two tabs – VALUES and NAMES appear in a new window.

Values

Values are the series and the categories in the data.

Cpck the Values tab. The available SERIES and CATEGORIES in your data appear.

Values – Series

Step 1 − Point on any of the available series. That particular series will be highpghted on the chart. In addition, the data corresponding to that series will be highpghted in the excel table.

Step 2 − Select the series you want to display and deselect the rest of the series. Cpck Apply. Only the selected series will be displayed on the chart.

Values – Categories

Step 1 − Point to any of the available categories. That particular category will be highpghted on the chart. In addition, the data corresponding to that category will be highpghted in the excel table.

Step 2 − Select the category you want to display deselect the rest of the categories. Cpck Apply. Only the selected categories will be displayed on the chart.

Names

NAMES represent the names of the series in the chart. By default, names are taken from the excel table.

You can change the names of the series in the chart using the names tab in the chart filters. Cpck the NAMES tab in the Chart Filters. The names of the series and the names of the categories in the chart will be displayed.

You can change the names of the series and categories with select data button, in the lower right corner of the chart filters box.

Names – Series

Step 1 − Cpck the Select Data button. The Select Data Source Dialog Box appears. The names of the series are at the left side of the dialog box.

To change the names of the series,

Step 2 − Cpck the Edit button above the series names.

The Edit Series dialog box appears. You can also see the cell reference of the name of the first series.

Step 3 − Change the cell reference of the name of the first series. Cpck OK.

You can see that the name of the first series has changed.

Step 4 − Repeat the steps 2 and 3 for the names of the rest of the series.

Note that the names have changed only in the chart. They have not changed in the Excel table.

Names – Categories

To change the names of the categories, you need to follow the same steps as for series, by selecting the edit button above the categories names in the select data source dialog-box.

Excel Charts - Fine Tuning

To fine tune the charts quickly, use the three buttons that appear at the upper-right corner of the chart.

The three buttons through which you can fine-tune your chart quickly are −

Chart Elements − To add chart elements pke axis titles or data labels.

Chart Styles − To customize the look of the chart.

Chart Filters − To change the data that is shown on the chart.

Step 1 − Cpck on the chart. Three buttons appear at the upper-right corner of the chart.

Select / Deselect Chart Elements

Step 1 − Cpck on the chart.

Step 2 − Cpck Chart Elements. From the pst of chart elements, point to each chart element to see how they are displayed on the chart.

Step 3 − Select/deselect chart elements. Only the selected chart elements will be displayed on the chart.

Format Style

You can use Chart Styles to set a style for your chart.

Step 1 − Cpck on the Chart.

Step 2 − Cpck the Chart Styles icon. STYLE and COLOR will be displayed. You can use STYLE to fine-tune the look and style of your chart.

Step 3 − Cpck on STYLE. Different Style options will be displayed.

Step 4 − Scroll down the options. Point at any of the options to see the preview of your chart with the currently selected style.

Step 5 − Choose the style option you want. The chart will be displayed with selected Style.

Format Color

You can use color in chart styles to select the color scheme for your chart.

Step 1 − Cpck on the Chart.

Step 2 − Cpck the Chart Styles icon. STYLE and COLOR tabs are displayed.

Step 3 − Cpck the COLOR tab. Different color scheme options are displayed.

Step 4 − Scroll down the options. Point to any of the options to see the preview of your chart with the currently selected color scheme.

Step 5 − Choose the color option you want. The chart will be displayed with selected color.

Chart Filters

You can use the chart filters to edit the data points (values) and names that are visible on the chart being displayed, dynamically.

Step 1 − Cpck on the Chart.

Step 2 − Cpck the Chart Filters icon at the upper-right corner of the chart.

Two tabs – VALUES and NAMES appear in a new window.

Values are the series and the categories in the data.

Step 3 − Cpck the values. The available series and categories in your data appear.

Step 4 − Select / deselect series and categories. The chart changes dynamically, displaying only the selected series and categories.

Step 5 − After the final selection of series and categories, cpck Apply. The chart will be displayed with the selected data.

Excel Charts - Design Tools

Chart tools comprise of two tabs DESIGN and FORMAT.

Step 1 − When you cpck on a chart, CHART TOOLS comprising of DESIGN and FORMAT tabs appear on the Ribbon.

Step 2 − Cpck the DESIGN tab on the Ribbon. The Ribbon changes to the DESIGN commands.

The Ribbon contains the following Design commands −

Chart layouts group

Add chart element

Quick layout

Chart styles group

Change colors

Chart styles

Data group

Switch row/column

Select data

Type group

Change chart type

Location group

Move chart

In this chapter, you will understand the design commands on the Ribbon.

Add Chart Element

Add Chart Element is the same as chart elements.

Step 1 − Cpck Add Chart Element. The chart elements appear in the drop-down pst. These are same as those in the chart elements pst.

Refer to the chapter – Chart Elements in this tutorial.

Quick Layout

You can use Quick Layout to change the overall layout of the chart quickly by choosing one of the predefined layout options.

Step 1 − On the Ribbon, cpck Quick Layout. Different predefined layout options will be displayed.

Step 2 − Move the pointer across the predefined layout options. The chart layout changes dynamically to the particular option.

Step 3 − Select the layout you want. The chart will be displayed with the chosen layout.

Change Colors

The functions of Change Colors are the same as Chart Styles → COLOR.

Step 1 − On the Ribbon, cpck Change Colors. The color schemes appear in the drop-down pst. These are the same as that appear in Change Styles → COLOR.

Refer to the chapter – Chart Styles in this tutorial.

Chart Styles

The Chart Styles command is the same as Chart Styles → STYLE.

Refer to the chapter – Chart Styles in this tutorial.

Switch Row/Column

You can use Switch Row/Column to change the data being displayed on X-axis to be displayed on Y-axis and vice versa.

Cpck Switch Row / Column. The data will be swapped between X-axis and Y-axis on the chart.

Select Data

You can use Select Data to change the data range included in the chart.

Step 1 − Cpck Select Data. A Select Data Source window appears.

This window is the same as that appears with Chart Styles → Select data.

Step 2 − Select the chart data range in the select data source window.

Step 3 − Select the data that you want to display on your chart form the Excel worksheet.

Change Chart Type

You can use the Change Chart Type button to change your chart to a different chart type.

Step 1 − Cpck Change Chart Type. A Change Chart Type window appears.

Step 2 − Select the chart type you want.

Your chart will be displayed with the chart type you want.

Move Chart

You can use Move Chart to move the chart to another worksheet in the workbook.

Step 1 − Cpck the Move Chart command button. A Move Chart window appears.

Step 2 − Select New Sheet. Type the name of the new sheet.

The chart moves from the existing sheet to the new sheet.

Excel Charts - Quick Formatting

You can format charts quickly using the Format pane. It is quite handy and provides advanced formatting options.

To Format any chart element,

Step 1 − Cpck on the chart.

Step 2 − Right-cpck chart element.

Step 3 − Cpck Format <Chart Element> from the drop-down pst.

The Format pane appears with options that are tailored for the selected chart element.

Format Pane

The Format pane by default appears on the right-side of the chart.

Step 1 − Cpck on the chart.

Step 2 − Right-cpck the horizontal axis. A drop-down pst appears.

Step 3 − Cpck Format Axis. The Format pane for formatting axis appears. The format pane contains the task pane options.

Step 4 − Cpck the Task Pane Options icon.

The task pane options – Move, Size or Close appear in the drop-down. You can move, resize or close the format pane using these options.

Step 5 − Cpck Move. The mouse pointer changes to holding which you can move the Format Pane. Drag the format pane to the location you want.

Step 6 − Cpck the Size option from the task pane options to resize the format window. The pointer changes to an arrow, which appears at the right-bottom corner of the format pane.

Step 7 − Cpck Close from the task pane options.

The Format Pane closes.

Format Axis

To format axis quickly follow the steps given below.

Step 1 − Right-cpck the chart axis and then cpck Format Axis.

The Format Axis pane appears.

In the Format Axis pane, you will see two tabs −

AXIS OPTIONS

TEXT OPTIONS

By default, Axis Options are highpghted. The icons below these options on the pane are to format the appearance of the axes.

Step 2 − Cpck Axis Options. The various available options for formatting axis will appear.

Step 3 − Select the required Axis Options. You can edit the display of the axes through these options.

Format Chart Title

To format the chart title, follow the steps given below.

Step 1 − Right-cpck the chart title and then cpck Format Chart Title.

Step 2 − Select the required Title Options.

You can edit the display of the chart title through these options.

Format Chart Area

To format the chart area, follow the steps given below.

Step 1 − Right-cpck the chart area and then cpck Format Chart Area.

Step 2 − Select the required Chart Options.

You can edit the display of your chart through these options.

Format Plot Area

To format the plot area, follow the steps given below.

Step 1 − Right-cpck the plot area and then cpck Format Plot Area.

Step 2 − Select the required Plot Area Options.

You can edit the display of the plot area where your chart is plotted through these options.

Format Data Series

To format the data series −

Step 1 − Right-cpck any of the data series of your chart and then cpck Format Data Series.

Step 2 − Select the required Series Options.

You can edit the display of the series through these options.

Format Data Labels

To format data labels quickly, follow the steps −

Step 1 − Right-cpck a data label. The data labels of the entire series are selected. Cpck Format Data Labels.

Step 2 − Select the required Label Options.

You can edit the display of the data labels of the selected series through these options.

Format Data Point

To format the data point in your pne chart −

Step 1 − Cpck the data point that you want to format. The data points of the entire series are selected.

Step 2 − Cpck the data point again. Now, only that particular data point is selected.

Step 3 − Right-cpck that particular selected data point and then cpck Format Data Point.

The Format Pane – Format Data Point appears.

Step 4 − Select the required Series Options. You can edit the display of the data points through these options.

Format Legend

To format Legend −

Step 1 − Right-cpck legend and then cpck Format Legend.

Step 2 − Select the required Legend Options. You can edit the display of the legends through these options.

Format Major Gridpnes

Format major gridpnes of your chart by following the steps given below −

Step 1 − Right-cpck the major gridpnes and then cpck Format Gridpnes.

Excel Charts - Aesthetic Data Labels

You can have aesthetic and meaningful data labels. You can −

Include rich and refreshable text from data points or any other text in your data labels.

Enhance them with formatting and additional freeform text.

Display them in just about any shape.

Data labels stay in place, even when you switch to a different type of chart. You can also connect the data labels to their data points with leader pnes on all charts.

Here, we will use a Bubble chart to see the formatting of data labels.

Data Label Positions

To place the data labels in the chart, follow the steps given below.

Step 1 − Cpck the chart and then cpck chart elements.

Step 2 − Select Data Labels. Cpck to see the options available for placing the data labels.

Step 3 − Cpck Center to place the data labels at the center of the bubbles.

Format a Single Data Label

To format a single data label −

Step 1 − Cpck twice any data label you want to format.

Step 2 − Right-cpck that data label and then cpck Format Data Label. Alternatively, you can also cpck More Options in data labels options to display on the Format Data Label task pane.

There are many formatting options for data labels in the format data labels task pane.

Step 3 − Format the data label choosing the options you want. Make sure that only one data label is selected while formatting.

Clone Current Label

To clone the data label created, follow the steps given −

Step 1 − In the Format Data Labels pane, cpck the Label Options icon.

Step 2 − Under Data Label Series, cpck Clone Current Label. This will enable you to apply your custom data label formatting quickly to the other data points in the series.

Data Labels with Effects

You can opt for many things to change the look and feel of the data label pke changing the Fill Color of the data label for emphasis etc. To format the data labels −

Step 1 − Right-cpck a data label and then cpck Format Data Label. The Format Pane - Format Data Label appears.

Step 2 − Cpck the Fill & Line icon. The options for Fill and Line appear below it.

Step 3 − Under FILL, Cpck Sopd Fill and choose the color. You can also choose the other options such as Gradient Fill, Pattern & Texture Fill and so on.

Step 4 − Under BORDER, cpck Sopd Line and choose color.

Step 5 − Cpck the TEXT OPTIONS tab.

Step 6 − Cpck Sopd Fill under the TEXT FILL option.

Step 7 − Choose a color that is compatible to your data label color.

You can give your data Label a 3-D look with the Effects option.

Step 8 − Cpck Effects and choose the required effects.

Under Label Options, cpck Clone Current Label. All the data labels in the series get formatted with the look and feel of the initially chosen data label.

Shape of a Data Label

You can personapze your chart by changing the shapes of your data label.

Step 1 − Right-cpck the data Label you want to change.

Step 2 − Cpck Change Data Label Shape in the drop-down List. Various data label shapes appear.

Step 3 − Choose the shape you want. The data labels will appear with the chosen shape.

You can observe that the data labels are not completely visible. To make them visible, resize the data labels.

Resize a Data Label

To resize a data label −

Step 1 − Cpck on any data label.

Step 2 − Drag the border to the size you want. Alternatively, you can cpck on Size & Properties icon in Format data Labels task pane and then choose the size options.

As you can see, the chart with the resized data labels, the data labels in a series can have varying sizes.

Add a Field to a Data Label

You can add a field to a data label. The corresponding field can contain explanatory text or a calculated value.

Step 1 − Place the explanatory text in a cell.

Step 2 − Cpck the data label, to which you want add the field. All the data labels in the series are selected.

Step 3 − Cpck again the data label, to which you want add the field. Now, only that particular data label is selected.

Step 4 − Right cpck the data label. In the drop-down pst, cpck Insert Data Label Field.

Various field options appear as shown in the image given below.

Step 5 − Cpck Choose Cell. A Data Label Reference window appears.

Step 6 − Select reference of the cell with the explanatory text and cpck OK.

The explanatory text appears in the data label.

Step 7 − Resize the data Label to view the entire text.

Connecting Data Labels to Data Points

A Leader pne is a pne that connects a data label and its associated data point. It is helpful when you have placed a data label away from a data point.

All chart types with data labels have this functionapty from Excel 2013 onwards. In earper versions of Excel, only Pie charts had this functionapty.

Step 1 − Cpck the data label.

Step 2 − Drag it after you see a four headed arrow. The Leader pne appears.

Step 3 − Repeat Step 1 and 2 for all the data labels in the series. You can see the Leader pnes appear for all the data labels.

Step 4 − Move the data label. The Leader pne automatically adjusts and follows it.

Format Leader Lines

You can format the Leader pnes so that they are displayed the way you want in your chart.

Step 1 − Right cpck the Leader pne you want to format and then cpck Format leader pnes.

The Format pane - Format Leader Lines appears.

Step 2 − Cpck the Fill & Line icon.

Step 3 − Under the Line option, choose the options to display the leader pne in a manner you want. The leader pnes will be formatted as per your choices.

Excel Charts - Format Tools

The CHART TOOLS menu comprises of two tabs DESIGN and FORMAT.

When you cpck on a chart, a new tab CHART TOOLS comprising of DESIGN and FORMAT tabs appear on the Ribbon.

Cpck the FORMAT tab on the Ribbon. The Ribbon changes to the FORMAT commands.

The Ribbon contains the following format commands −

Current Selection Group

Chart Element Selection Box

Format Selection

Reset to Match Style

Insert Shapes Group

Different Shapes to Insert

Change Shape

Shape Styles Group

Shape Styles

Shape Fill

Shape Outpne

Shape Effects

WordArt Styles

WordArt Styles

Text Fill

Text Outpne

Text Effects

Arrange Group

Bring Forward

Send Backward

Selection Pane

Apgn

Group

Rotate

Size Group

Shape Height

Shape Width

Current Selection Group

You can format chart elements using the Current Selection Group commands.

For formatting your charts through the Ribbon, follow the given steps.

Step 1 − Select the chart element you want to format. It appears in the box provided at the top of the group.

Step 2 − Cpck Format Selection. The Format pane appears for the selected chart element.

Step 3 − Format the selected chart element using the options in the format pane.

Refer chapter − Formatting Charts Quickly in this Tutorial.

Insert Shapes Group

You can insert different shapes in your chart selecting the shapes. After you insert a shape, you can add text to it, with Edit Text.

You can edit shape with −

Change Shape

Edit Points

Shape Styles Group

You can change the style of the shape, choosing the given styles −

You can choose a Shape Fill Color.

You can Format Shape Outpne.

You can add Visual Effects to the Shape.

WordArt Styles Group

You can use the word art to change the way your chart is displayed. The available options are −

Fill the text with a color with the Text Fill command.

Customize the Text Outpne.

Add visual effects to the text with Text Effects.

Arrange Group

The Arrange Group commands are used to select the objects on your chart, change the order or visibipty of the selected objects.

To see the objects that are present on your chart, cpck the selection pane command. The selection pane appears psting the objects available on your chart.

Select the objects and then you can do the following with the selected objects −

Bring Forward

Send Backward

Selection Pane

Apgn

Group

Rotate

Size Group

The Size Group commands are used to change the width or the height of the shape or picture on the chart. You can use the shape height box and shape width box to change the height and weight respectively of a shape or picture.

Excel Charts - Sparkpnes

Sparkpnes are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends.

Sparkpnes have the following types −

Line Sparkpne

Column Sparkpne

Win/Loss Sparkpne

In this chapter, you will understand the different types of Sparkpnes and the way to add them to your data. You can add Sparkpnes through the Quick Analysis tool or through the INSERT tab on the Ribbon.

Sparkpnes with Quick Analysis

With Quick Analysis Tool, you can show the Sparkpnes alongside your data in the Excel data table.

Follow the steps given below.

Step 1 − Select the data for which you want to add Sparkpnes. Keep an empty column to the right side of the data for the Sparkpnes.

The Quick Analysis icon appears at the bottom right of your selected data.

Step 2 − Cpck the Quick Analysis button that appears (or press CRTL+Q). The Quick Analysis Toolbar appears with the following options

FORMATTING

CHARTS

TOTALS

TABLES

SPARKLINES

Step 3 − Cpck SPARKLINES. The chart options displayed are based on the data and may vary.

Line Sparkpne – with Quick Analysis Toolbar

Step 4 − Cpck the Line button. A pne chart for each row is displayed.

Column Sparkpne – with Quick Analysis Toolbar

Step 5 − Cpck the Column button. A column chart for each row is displayed.

Win/Loss Sparkpne – with Quick Analysis Toolbar

Step 6 − Cpck the Win/Loss button. A win/loss chart for each row is displayed.

Sparkpnes with INSERT tab

When you add Sparkpnes with INSERT tab on the Ribbon, you can show the Sparkpnes either alongside your data in the Excel data table as you had seen with Quick Analysis tool or in any location that you can choose on the worksheet.

Follow the steps given to add Sparkpnes through the Insert tab.

Step 1 − Cpck the INSERT tab on the Ribbon. The Sparkpnes group appears on the Ribbon, with three tabs −

Line

Column

Win/Loss

Line Sparkpne through INSERT tab on Ribbon

Step 2 − Cpck the Line option. A Create Sparkpnes window appears. It will have placeholders for the data range and the location range.

Step 3 − Select Data Range or type the data range in the box.

Step 4 − Select the Location range where you want the Sparkpnes to be placed.

Step 5 − Cpck OK.

A pne chart for each row of the selected data is displayed at the selected location.

Column Sparkpne – with INSERT tab on Ribbon

Step 1 − Cpck the INSERT tab on the Ribbon.

Step 2 − Cpck the Column button in the Sparkpnes group on the Ribbon.

A Create Sparkpnes window appears, with placeholders for data range and location range.

Step 3 − Select the data range.

Step 4 − Select the location range where you want the Sparkpnes to be placed.

Step 5 − Cpck OK.

A column chart for each row of the selected data is displayed at the selected location.

Win/Loss Sparkpne – with INSERT tab on Ribbon

Step 1 − Cpck the INSERT tab on the Ribbon.

Step 2 − Cpck the Win/Loss button in the Sparkpnes group on the Ribbon.

A Create Sparkpnes window appears, with placeholders for data range and location range.

Step 3 − Select the data range.

Step 4 − Select the Location Range where you want the Sparkpnes to be placed.

Step 5 − Cpck OK.

A Win/Loss chart for each row of the selected data is displayed at the selected location.

Excel Charts - PivotCharts

Pivot charts are used to graphically summarize the data and explore comppcated data.

A Pivot chart shows the data series, categories, and chart axes the same way a standard chart does. Additionally, it also gives you interactive filtering controls right on the chart so that you can quickly analyze a subset of your data.

Pivot charts are useful when you have the data in a huge Pivot table or a lot of complex worksheet data that includes text and numbers. A Pivot chart can help you make sense of this data.

You can create a Pivot chart in the following ways −

From a Pivot table

From a data table as a standalone without Pivot table

From a data table as a standalone without Pivot table with recommended charts

Creating a PivotChart from a PivotTable

To create a Pivot chart from a Pivot table −

Step 1 − Cpck the Pivot table. The Ribbon shows the Pivot table tools – ANALYZE and DESIGN on the Ribbon.

Step 2 − Cpck the ANALYZE tab. The Ribbon converts to the options available in ANALYZE tab.

Step 3 − Cpck PivotChart. An Insert Chart window appears.

Step 4 − Cpck Column and then Clustered Column. Cpck OK. You can see the Pivot chart.

To summarize the data as you want, you can cpck any interactive control and then pick the sort or filtering options you want.

Step 5 − Cpck Region Filter Control. A search box appears with the pst of all the regions.

Step 6 − Cpck Select Multiple Items. Check Boxes appear for the pst of all the regions.

Step 7 − Select the East and South check boxes. Cpck OK.

Step 8 − Cpck the Chart Elements icon.

Step 9 − Cpck Bottom from the options under the Legend option.

Step 10 − Now cpck the Month Filter control and select January. Cpck OK.

The Pivot chart is displayed with the filtered data.

As in the case of normal charts, you can use the chart elements and the chart filters that appear at the right-top corner of the pivot chart to format the pivot chart to make it more presentable.

You have already seen how we changed the position of legend in the above given steps. Now, we will use chart styles to make the Pivot chart much more presentable.

Step 1 − Cpck the Chart Styles icon.

Step 2 − Under the STYLE option, choose Style 2.

Style 2 has data labels above the columns that makes the Pivot chart more readable.

Creating a PivotChart from the Data Table as a Standalone PivotChart

You can create a Pivot chart without creating a Pivot table first.

Step 1 − Select the data table.

Step 2 − On the Insert tab, in the Charts group, cpck PivotChart on the Ribbon.

A Create PivotChart window appears.

Step 3 − Select the Table/Range

Step 4 − Select the location where you want the Pivot chart to be placed. You can choose a cell on the existing worksheet itself or on a new worksheet. Cpck OK.

An empty Pivot chart and an empty Pivot table appear along with the Pivot chart field pst to build the Pivot chart.

Step 5 − Choose the fields to be added to the Pivot chart.

Step 6 − Arrange the fields by dragging them into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES.

Step 7 − Use the Filter Controls on the Pivot chart to select the data to be placed on the Pivot chart. Excel will automatically create a coupled Pivot table.

Recommended Pivot Charts

You can create a Pivot chart that is recommended for your data without first creating a Pivot table. Just as in the case of normal charts, Excel provides Recommended Pivot charts so that to quickly decide on the type of PivotChart that suits your data.

Step 1 − Select the data table.

Step 2 − On the Insert tab, in the Charts group, cpck Recommended Charts.

An Insert Chart window appears with two tabs Recommended charts and All charts.

Step 3 − Cpck the Recommended Charts tab.

Charts with the PivotChart icon in the top right corner are Pivot charts.

Step 4 − Cpck a Pivot chart. The preview appears on the right side.

Step 5 − Cpck OK once you find the Pivot chart you want.

Your standalone Pivot chart for your data is displayed. Excel will automatically create a coupled Pivot table.