Microsoft Excel 2010 Charts and Graphs. Class Workbook

(1)

Microsoft Excel 2010

Charts and Graphs

Class Workbook

A UCSD Biomedical Library Workshop

Information Commons Educational Services

2008 0 2000 4000 6000 8000 10000 July _Aug Sept _Oct Nov _Dec 2008 2009 2008 0 2000 4000 6000 8000 10000 July _Aug Sept _Oct Nov _Dec 2008 2009

(2)

Introduc on

Charts

and

graphs

are

a

great

way

of

represen ng

your

data

visually.

Microso

Excel

2010

o

ﬀ

ers

a

varie

‐

ty

of

chart

types

and

makes

it

easy

to

draw

them

so

that

your

data

can

be

quickly

understood

in

a

graphical

format.

In

this

workshop

you

will

learn

about

each

chart

type,

what

the

chart

represents,

and

addi onal

func onality

you

might

not

have

heard

about.

The

goal

of

this

workshop

is

to

make

you

more

e

ﬃ

cient

and

e

ﬀ

ec ve

in

crea ng

visual

displays

of

infor

‐

(3)

(4)

July Aug Sept Oct Nov Dec 2008 2422 1717 2807 4801 6035 5418 2009 2698 4231 4687 9417 5953 4628 2 4 2 2 1 7 1 7 2 8 0 7 4 8 0 1 6 0 3 5 5 4 1 8 2 6 9 8 4 2 3 1 4 6 8 7 9 4 1 7 5 9 5 3 4 6 2 8 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 In q u ir ie s Months

Chart Title

2008 2009

Iden fying

Chart

Elements

Ver cal Axis, also called the Y axis Horizontal Axis Also called the X axis Ver cal Axis Title Horizontal Axis Title Data Table Data Label Legend Data Series Chart Area Plot Area Grid Lines

(5)

How to Create an Excel Chart

Charts make data visual. Instead of having to analyze columns of numbers, you can see at a plethora of informa on at glance.

To

create

a

chart:

1. Select the table in which you have your data for the chart 2. Click the Insert Tab

3. Click the down arrow of the chart category you want to create (column, line, pie, bar or etc.) 4. Select the type of chart within the category

5. Select and drag the chart to where you want it situated

6. Fine tune it with options in the Chart Tools menu (Design, Layout, and Format)

1 2 3 4 6 5

(6)

Design

Tab

The Design tab provides tools that give you quick and easy choices for how your chart is laid out, how the elements are labeled, and some color combina ons and 3‐D eﬀects that can add impact to the overall presenta on. Use this tab when you are not concerned about details.

Change

Chart

Type

This command will change the type of chart that you have currently selected in your spreadsheet, to a diﬀerent type that you select from a drop‐down menu (See Page 3). The old chart disappears and the newly selected chart replaces it.

Save

As

Template

Saves a chart with the .crtx extension so it can be used as a basis for future charts.

Switch

columns

and

rows

If Excel has misinterpreted your data and mixed up your X and Y axis, you can rec fy the problem by pressing this bu on. This will auto‐ ma cally switch the Y axis data to the X axis and the X axis data to the Y axis,

Select

Data

This command will start the Select Data Source dialog box which will allow you to edit the range of data included in the chart.

Chart

Layout

Gallery

Depending on the chart type you have chosen, the Chart Layout Gallery oﬀers 4‐12 built‐in combina on of chart elements. When you choose a new chart layout from the gallery, you get a predefined combina on of tles, legend, gridlines, data labels, and etc. These layouts may not al‐ ways be what you need, but they will give you the basic elements, which you can then modify.

(7)

Chart

Styles

The Design tab is also home to the Chart Styles gallery that oﬀers 48 varia ons of color and eﬀects. The gallery has columns for each of the six accent colors, monochromes and mixed colors.

Moving

Charts

If you want to move your chart to another worksheet, the dialog box associated with this func on will make it pos‐ sible. You can choose to have it transferred to a new sheet or to one of the exis ng sheets using the drop down menu provided in the dialog box.

Layout

Tab

The Layout tab contains a few popular choices for forma ng 15 chart elements. In 60% of cases, you can use op ons from the drop‐down menus on the Layout tab to create a perfect chart.

Current Selec on Command Group

Select

Chart

Elements

This feature provides a drop‐down menu that lists all the elements in the selected chart, such that when you select one from the list, the corresponding element in the chart will be selected

Format

Selec on

Once the chart element has been selected, you can press this bu on, which will bring up the format dialog box which can be used to format the element at a very high degree of detail.

(8)

Reset

to

Match

Style

If you don’t like the changes you’ve made to the chart element, you can press this bu on to get back to where you started.

Insert Command Group

Picture

Opens files for selec on and inser on of an image into whatever cell is selected

Shapes

Allows for the selec on and drawing of a wide range of shapes

Text

Box

Provides for the drawing of a text box anywhere in the chart

Labels

Command

Group

Chart

Title

 By default, a chart with more than one series is created without any tle.

 When the tle is added above the chart, Excel shrinks the plot area to make more room for the tle.

 The tle is selected by default so you can immediately type the actual tle, which will appear in the formula bar.  Press the Enter key to finalize your chart tle.

 You can also select the text in the box and type your tle over it.

 To edit the tle text, select the text and use the text edi ng tools in the Font and Paragraph sec ons of the Home tab.  To edit the tle box you can either use the Chart Tools Format ribbon on the tool bar, or right click the Title box and select

“Format Chart Title” which provides an extensive range of op ons.

 To help the reader interpret the chart, include the message in the tle. Instead of using an Excel generated tle such as “Sales,” you can actually use a two– or three‐lined tle such as “Sales have grown every quarter except for Q3, when a strike impacted produc on.”

(9)

Axis

Titles

The Axis Title bu on on the Layout tab displays four op ons when clicked: No Title, Rotated Title, Horizontal Title, and Ver cal Title.

 The Rotated Title faces the plot area of the chart,  the Horizontal tle is parallel to the horizontal axis,  the Ver cal tle reads from top to bo om.

For the Horizontal Axis tle the only op on is to turn it on or oﬀ.

All the forma ng op ons available for the chart tle also apply to the axis tles.

Legend

 The built in choices for the legend include having the legend outside the le , right, bo om, or top of the plot area.  If you move the legend to the top or the bo om, Excel rearranges the legend in a horizontal format.  Floa ng a Legend in the Plot Area To float the legend in the Plot Area:  From the Layout tab, select the Legend drop‐down  Overlay Legend at Right. This keeps the legend in a ver cal arrangement and stretches the plot area out to the edge of the chart.  Carefully click inside the legend. When the mouse pointer is a four‐headed arrow, drag the mouse and drop the legend in a free spot on the chart.  While the legend is s ll selected, click the Format tab. Select Shape Fill, White, to convert the trans‐ parent fill to a solid fill. The fill prevents gridlines from overwri ng your legend tles.

 Select Format  Shape Outline  Black to add a border around the legend.

(10)

 Changing the Arrangement of a Legend To change the arrangement of a legend:

 Click the legend to ac vate the resizing handles  Drag the bo om –right corner of the legend up and out to produce a leg‐ end with the labels arranged in a horizontal format

Data

Labels

 Data labels can make things clearer and help you communicate the significance of what is being presented.  Adding Data Labels to a chart You can insert data labels by  selec ng the series for which you want the labels,  selec ng the Data Labels bu on on the Layout tab, or

 selec ng one of the following loca ons from the drop‐down list.  Outside End, Center, Inside End, Inside Base, and None.  The three “inside” choices are useful when you have a stacked column chart.  Nudging a Label If you want to adjust the posi on of your data labels to either separate them from each other or get them closer to the series to which they relate, you can do so by  clicking once on the one you want to move (this se‐ lects all the labels in the series)  then click again (which selects only the one you are selec ng), holding down the mouse key, and nudge the label with the mouse to the desired loca on  Forma ng Labels

 The Forma ng Labels dialog box contains a wide range of forma ng op ons that can enhance labels and make them stand out. However, it does not contain font commands

 To change the font, font color, font size and alignment, you will need to use the font sec on of the Home tab, or right click and select Data Labels.

(11)

Data

Table

Data

Table

 A Data Table is a mini worksheet that appears below a chart.  An advantage of a data table is that you can see the values of each data point without having to insert labels.  There are two built‐in op ons in the Data Table drop‐down on the Layout tab: You can show the data table with or without legend keys  Choosing to show the legend keys can save space because it obviates the need for a legend.

 If you select the More Data Table op ons in the Data Table drop‐down, you will see the usual op ons for fill, border styles, shadow, and 3‐D, plus special data op ons

(12)

Axes

Command

Group

 The Horizontal Axis  The Horizontal Axis, also called the Category or X‐Axis, appears along the bo om of the chart in column, line, area, and stock charts  Your most important choice for this axis is whether it should be me based or text based.  In a text based axis, the points along the axis are equally spaced.

 In a chart that uses a me‐based axis, the points are spaced on the rela ve me distance between points.  The Ver cal Axis  Contains the scale for the numbers plo ed in the chart.

 The primary ver cal axis appears along the le side of the chart in column, line, area, and stock charts.

 Choices for this axis include scaling of the axis, the minimum and maximum value for the axis, and the distance between ck marks on the axis. 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%

July Aug Sept Oct Nov Dec

2009 2008

Ver cal

(Y)

Axis

(13)

 More choices are available in the Format Axis Dialog.  If your data has numbers of diﬀerent scales you should specify a logarithmic axis  Say that you have a series of data with both large and small data values such as Bookstore sales by products. The Bookstore has high flying products that account for 80% of its revenue and then some older product lines that are s ll hanging around. When you try to plot these items on a chart, Excel must make the axis scale large enough to show the sales for the best‐selling products. This causes the detail for the smaller product lines to become lost because the values are a rela vely small percentage of the en re scale.  When this occurs the solu on is to convert the axis to a logarithmic axis. In a logarithmic axis, the distance form 1 to 10 is the same as the distance from one to 100, and so on. This allows you to see detail of the product selling a few hundreds units as well as the products selling 100,000 units.  To convert to a logarithmic scale, select Layout, Axes, Primary Ver cal Axis, Show Axis with Log Scale. The result is a chart which shows detailed data for both small and large amounts.  Overriding Scale Op ons

 When you create a chart, Excel automa cally decides whether the ver cal axis should reach to zero

 To over‐ride the Maximum/Minimum se ngs for the ver cal axis, and thus create the illusion of greater variances in series da‐ ta:

 Double‐click on the axis  Go to Axis Op ons Override the minimum value on the axis by selec ng the Fixed op‐ on bu on for Minimum Type a value for the minimum

(14)

Gridlines

 Gridlines help the reader locate data on the chart

 Without gridlines, it is diﬃcult to follow the plo ed points over the ver cal axis to figure out the value of a point.  Gridlines work in conjunc on with the Major Unit and Minor Unit se ngs in the Format Axis dialog box.

 Built in op ons on the Layout tab for both horizontal and ver cal lines allow you to turn on major or minor, major and minor, or no gridlines

 Crea ng unobtrusive Gridlines by using format Gridlines

 Each Format Gridlines dialog box has three categories in the le naviga on bar, which can be used to dim the impact of gridlines

 Line Color: Use pale, light colors

 Line Style: Use do ed or dashed lines at .25 points Shadow: Use dim colored, blurred shadow

(15)

Background

Command

Group

Plot

Area

 Using a Gradient

To apply a gradient to the plot area, do the following steps:

 Select Plot Area More Plot Area Op ons from the Background sec on of the Layout Tab

 Change the Fill se ng from Automa c to Gradient Fill  Choose Linear Gradient  Change the angle to 90 degrees  Each chevron shape on the Gradient Stops bar indicates a gradient stop.  If there are more than 2 stops, select the last stop and click the mi‐ nus bu on to remove that stop.  Con nue to remove any stop higher than Stop 2  Click the first stop chevron to work with Stop 1  Choose a green color.

 Set the stop posi on at 5% by either using the spin bu on or drag‐ ging the chevron  Set the transparency to 25% to make a lighter green  Click the Stop 2 chevron  Choose white as the color  Set the stop posi on to 95% The result is a 2‐color gradient from dark green at the top to white at the bo om

(16)

 Using a Picture or Texture

 When you select the Picture or Texture op on, texture is the default se ng, and you can select a texture of your choice using the Texture bu on.  To use a picture, click the File or Clip Art bu on and browse to locate a picture to insert  Use your Picture as Texture check box to create a repea ng image across the background  You can adjust the size of the image using the oﬀsets set out below the Stretch Op ons heading  You can lighten the background image, and thus make it less intrusive, by using the Transparency slider at the bo om of the box.

(17)

Controlling 3‐D Rota on in a 3‐D Chart

 A number of op ons are available for rota ng a 3‐D chart. Of the available op ons, you are most likely to change the X rota on and leave the other angels alone:

 X‐Rota on: Turns the floor of the chart clockwise, then counter clockwise 359.9 degrees

 Y‐Rota on: Tilts the chart up and down from +90 to ‐90 degrees. For best results use a value from 0‐20 degrees.  Perspec ve: Adds distor on to a chart causing close items to be unusually large and items further away to be smaller.

You need to uncheck Right Angle Axis box in order for this feature to work.  Depth: Increasing the depth causes columns to become wide rectangles.  Height: Clear the Auto Scale check box to enable the Height se ng . You can choose a value from 0‐500 percent of the base X = 20° Y = 30° Z = 0° Perspec ve = 75° X = 10° Y = ‐20° Z = 0° Perspec ve = 60° X = 0° Y = 0° Z = 0° Perspec ve = 100° X = 0° Y = ‐90° Z = 0° Perspec ve = 0°

(18)

Analysis

Command

Group

Trendlines

 A trendline a empts to fit exis ng data points to a formula and extend that formula for exis ng points or into the future.  To add a trendline, select:

 Layout Trendline Linear Trendline

 Excel asks which series it should add the trendline toSelect the series and press OK  To format a trendline:

 right‐click the trendline select Format Trendline Op ons in the Format Trendline dialog box allow you to change the line color and the line style.

 Choosing a forecast method

 In business a linear trendline is used most o en, which assumes a constant rate of progress throughout the life of the chart.

 Several other forecast methods are available for trendlines:

 Exponen al Trendline: Used most o en in science. It describes a popula on that is rapidly increasing over suc‐ cessive genera ons such as the number of fungi in a petri dish over a period of me.

 Logarithm Trendline: Results when there is an ini al period of rapid growth that levels oﬀ over me.

 Polynomial Trendline: Can describe a line that undulates due to two to six external factors. When you specify a polynomial trendline, you have to specify which order of polynomial. For example, in a third order polynomial, the line is fit to the equa on Y=b+c1x+c2x2+c3x3x.

 Power Trendline: Fits the points to a line, where y=cxb. This describes a line that increases at a specific exponen‐ al rate over me.

 Moving Average Trendline: Used to smooth out data that fluctuates over me. A typical trendline would use a three‐month moving average.

(19)

(20)

 Adding Drop Lines to a Line or Area Chart

 A drop line is a ver cal line that extends from a point on a line or area chart down to the corre‐ sponding value on the horizontal axis.

 It’s purpose is to make it easier for the reader to discern the exact value of data points.  To add a drop line select: Layout Lines Drop Lines

 Adding High/Low and Up/Down Bars to a Line Chart

 These lines and Bars enable you to compare two series at each point along the horizontal axis.

 There are two diﬀerent op ons for this comparison that are available in the two loca ons along the Layout tab  High‐Low lines extend from one line to the other:

 Select Layout Lines High‐Low Lines  Up/Down Bars extend from one line to the other.

 These bars appear in contras ng colors, depending on which line is higher at that par cular point  Select: Layout UP/Down Bars Up/Down Bars

(21)

 Showing Acceptable Tolerances by Using Error Bars  Error bars tell you the acceptable range

of tolerance for a given range of sta s‐ cs, and when that tolerance has been exceeded.

 Select one series in the chart and then select Layout Error Bars More Error Bar Op ons  In the Format Error Bars dialog, select whether you want the error bars to ex‐ tend up, down or both from the line.  You can add a cap (op onal).  In the lower sec on specify one of the five methods for calcula ng the size of the error bar.

 Based on the standard devia on, specify that the error bars should extend a fixed number of units.

Forma ng

a

Series

 Ini ate the Format Series Dialog by double‐ clicking or right clicking one of the series in a chart, or using the Current Selec on drop‐ down box in the le side of the Layout ribbon and selec ng Series and clicking the Format Selec on bu on.

 The Series Op ons category allows you to plot a series on a secondary axis, which is helpful if one data series contains data that is of a diﬀerent order of magnitude than the other series.

(22)

markers are drawn on top of each other  Using the remaining categories, you can edit the Marker style for a series, including the marker color, line color, line style, shadow and 3‐D forma ng.

Forma ng a Single Data Point

 When you click a single data point once, all the data points in a series are selected.  When you double‐click, only that data point is selected.  Once you have clicked a data point twice, you can proceed to modify it using the Format dialog box

 Selec ng a single data label is helpful if you have two labels on top of each other and you need to move one to another posi on.

The

Format

Tab

The Format tab contains icons that enable you to micromanage the color, fill, outline, and eﬀects for any individual chart element. For example, if you want to apply a so glow, a metal finish, and a reflec on to the January data point, you can do so using the Format tab.

 Conver ng Text to WordArt

 The WordArt Styles on the Format tab is used to apply WordArt styles to any text on a chart.

(23)

 Shape Styles The Shape Styles gallery contains 6 built‐in styles for six accent colors in the current theme. The six styles progress from simple on the first row to extreme on the last row.  To format a par cular element, click that element and choose a new style from the gallery  The styles and effects change if you change the theme  Using Shape Fill and Shape Effects In addi on to the shape styles, you can add custom‐ ized effects using the Shape Fill, Shape Outline and Shape Effects drop‐downs on the Format tab

 Replacing Data Markers with Clip Art or Shapes

Using Clip Art as a Data Marker Follow these steps to create a pictograph:  In a 2‐D column chart, select a data series. Right‐click the data series and select Format Data Series  Select the Fill category in the Le panel  In the right panel choose Picture or Texture Fill  Click the ClipArt bu on. Excel displays a shortened version of the ClipArt panel.  Select Picture  Select the Include Content from Oﬃce Online check box.  In the Search Text Box, type a keyword to describe the clip art and then click Go  Browse through the returned images. You are looking for something that is cartoonish and narrow. Rather than clip art with ta de‐ tailed background, look for clip art where only the character appears. When you find an acceptable image, click OK Click the border Color category and select No Line. Click Close to close the Format Data Series dialog

(24)

 Using a Shape in Place of a Data Marker

The fill se ngs on the Format Data Series dialog do not allow you to specify a shape. However they allow you to import a shape from the clipboard.

 Create a 2‐D column chart  Select a cell in the worksheet.  From the Insert menu, choose an upward arrow from the Shapes drop‐down.  Click and drag to draw an arrow on the worksheet  Right‐click the shape and then select Copy.  Click the data series in the chart  Right‐click and select Format Data Series.  In the Format Data Series dialog that appears, choose the Fill category in the le panel.  In the right panel, choose Picture or Texture Fill

 In the Insert From sec on, click the Clipboard bu on. Excel replaces the columns with the arrows. Click Close to close the Format Data

(25)

(26)

Types of Charts

Column Charts

A Column Chart shows data changes over a period of me or illustrates comparisons among items. Categories are organized horizontally, values ver cally, to emphasize varia on over me. Stacked column charts show the rela‐

onship of individual items to the whole. The 3‐D perspec ve column chart compares data points along two axes.

Clustered

column

and

clustered

column

in

3 ‐

D

Clustered column charts compare values across categories. A clustered column chart displays values in 2‐D ver cal rectangles. A clustered col‐ umn in 3‐D chart displays the data by using a 3‐D perspec ve 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). 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000

2008 2009 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000

2008 2009

(27)

Stacked

column

and

stacked

column

in

3 ‐

D

Stacked column charts show the rela onship of individual items to the whole, comparing the contribu on of each value to a total across catego‐ ries. A stacked column chart displays values in 2‐D ver cal stacked rectangles.

A 3‐D stacked column chart displays the data by using a 3‐D perspec ve only. A third value axis (depth axis) is not used. You can use a stacked column chart when you have mul ple 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 cate‐ gories. A 100% stacked column chart displays values in 2‐D ver cal 100% stacked rectangles.

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

(28)

3 ‐

D

Column

3‐D column charts use three axes that you can modify (a horizontal axis, a ver cal axis, and a depth axis), and they compare data points 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 ver cal 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 rectangu‐ lar column charts, and they show and compare data the same way. The only diﬀerence is that these chart types display cylinder, cone, and pyra‐ mid shapes instead of rectangles. 2008 0 2000 4000 6000 8000 10000

July Aug Sept _{Oct Nov Dec}

2008 2009

(29)

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 iscal years. This is especially true if there are multiple series — for one series, you should consider using a scatter chart.

You should also use a line chart if you have several evenly spaced numeric labels, espe-cially years. If you have more than ten numeric labels, use a scatter chart instead.

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 val-ues are approximate, use a line chart without markers.

(30)

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. 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, ver cal, and depth axes that you can modify 2008 2009 0 2000 4000 6000 8000 10000 July _Aug Sept _Oct Nov Dec 2008 2009

(31)

Pie Charts

Pie charts show the size of items in one data series, proportional to the sum of the items. They are good for comparing two to five different components. You typically select a range that contains category labels in Column A and values in Column B. Categories are generally sorted so that the largest value is at the top and the other values are sorted in descending order

Excel gives you choices about what you can display in data labels in the For-mat Data Labels dialog, as illustrated on the right. You can also edit each indi-vidual label by clicking inside it. The leader lines category is helpful if you have to move a label outside, as in the 3-D table below, if the slice is too nar-row to adequately fit the label. The leader line appears automatically to link

Pie

and

pie

in

3 ‐

D

Pie charts display the contribu on of each value to a total in a 2‐D or 3‐D format. Showing both the value and the category is par cularly helpful if you have to display the chart in black and white

(32)

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

Exploded

pie

and

exploded

pie

in

3 ‐

D

Exploded pie charts display the contribu on 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 se ng for all slices and individual slices, but you cannot move the slices of an exploded pie manually.

(33)

Bar

charts

 Data that is arranged in columns or rows on a worksheet can be plo ed in a bar chart.  Bar charts illustrate comparisons among individual items, as opposed to column charts which are be er for displaying trends.  Bar charts can be used to show how something changes over me or to compare diﬀerent mes.

 The default for ver cal axis in bar charts is star ng from the bo om and reading up. You can change this by going into Axis Op ons and checking the box marked Categories in Reverse

(34)

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 ver cal 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 . Note that Axis labels cannot be shown within bars in clustered 3D; they can only be shown outside the bars, as illustrated below.

(35)

Stacked

bar

and

stacked

bar

in

3 ‐

D

Stacked bar charts show the rela onship of individual items to the whole. It is mainly used to show the significance of one par cular item in rela‐

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. This type of chart is similar to a pie chart in func on. Below is an example depic ng the number of inquires received by a computer lab help desk by type and by month. You could also use this type of chart to show the breakdown of the amount of me spent on various aspects of a project, the cost of the sub‐

(36)

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 diﬀerence is that these chart types display cylinder, cone, and pyramid shapes instead of horizontal rectangles. 0% 50% 100% July Aug Sept Oct Nov Dec 2008 2009

(37)

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. They are a type of presentation graphic that emphasizes a change in values by filling in the portion of the graph be-neath the line connecting various data points. 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

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

(38)

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 me or other category data. A 100% stacked area chart in 3‐D is displayed in the same way but uses a 3‐D perspec ve. A 3‐D perspec ve is not a true 3‐D chart — a third value axis (depth axis) is

(39)

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 plot 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 sin-gle 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 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.

 Values for horizontal axis are not evenly spaced.

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 corre-sponding y values in the adjacent rows or columns.

Use a sca er chart with data markers but without lines when you use many data points and connec ng lines would make the data more diﬃcult to read. You can also use this chart type when you do not have to

(40)

Sca er

Charts

with

Lines—Sorted

and

Unsorted

You can use lines to connect the points of a sca er chart, as illustrated below. However, lines can be confusing and decep ve because when you add lines, Excel treats the sca er chart as a line chart a emp ng to show a trend rather than a rela onship.

In the first example, we used the data in the order it appears on the previous page, and Excel connected the points in that order going back and forth along the x axis and crea ng total confusion.

In the second example, we sorted the data form lowest to highest in the Educa on column and Excel plo ed the line like it would on a line chart. The last chart shows what happens if we format the data, unsorted, as a line chart; the result is confusing.

(41)

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 tem-peratures. 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 or

High

‐

low

‐

close

The high‐low‐close stock chart is o en used to illustrate stock prices. It requires three series of values in the following order: high, low, and then close. 0.00 5.00 10.00 15.00 20.00 25.00 30.00 35.00 40.00 45.00 7/1/2011 7/2/2011 7/3/2011 7/4/2011 7/5/2011 7/6/2011 7/7/2011

(42)

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

‐

open

‐

high

‐

low

‐

close

0.00 5.00 10.00 15.00 20.00 25.00 30.00 35.00 40.00 45.00 1 2 3 4 5 6 7 Open Hi Low Close 5.00 10.00 15.00 20.00 25.00 30.00 35.00 40.00 45.00 50,000 100,000 150,000 200,000 250,000 300,000 350,000 400,000 450,000 Volume Open Hi Low Close

(43)

Surface charts

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

3 ‐

D

surface

3‐D surface charts show trends in values across two dimensions in a con nuous curve. Color bands in a surface chart do not represent the data series; they represent the diﬀerence 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 rela onships between large amounts of data that may otherwise be diﬃcult to see. 10 20 30 40 50 0 50 100 150 200 250 300 350 400 450 500 0.2 0.3 _0.4 0.5 0.6 0.7 0.8 0.9 1.0 450-500 400-450 350-400 300-350 250-300 200-250 150-200 100-150 50-100 0-50

(44)

Wireframe

3 ‐

D

surface

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. 10 20 30 40 50 0 50 100 150 200 250 300 350 400 450 500 0.2 _0.3 0.4 _0.5 0.6 _0.7 0.8 0.9 1.0 Axi s Ti tl e Axis Title Chart Title 450-500 400-450 350-400 300-350 250-300 200-250 150-200 100-150 50-100 0-50 10 20 30 40 50 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0 400-500 300-400 200-300 100-200 0-100

(45)

Doughnut charts

Data that is arranged in columns or rows 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. Doughnut charts are not easy to read. You may want to use a stacked column or

stacked bar chart instead

Doughnut

2 ‐

D

and

3 ‐

D

Charts

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

Charts

Much like exploded pie charts, exploded doughnut charts display the contribution of each value to a total while emphasizing indi-vidual values, but they can contain more than

2005

(46)

Bubble

charts

A type of chart in which each plo ed en ty is defined in terms of three dis nct numeric parameters. The en es displayed on a bubble chart can be compared in terms of their size as well as their rela ve posi‐

ons with respect to each numeric axis. Bubble charts can facilitate the understanding of the social, eco‐ nomical, medical, and other scien fic rela onships.

Bubble

with

2D

or

3 ‐

D

e

ﬀ

ect

Both bubble chart types compare sets of three values instead of two. The third value deter‐ mines the size of the bubble marker. You can choose to display bubbles in 2‐D format or with a 3‐D eﬀect. $10,000 $20,000 $30,000 $40,000 $50,000 $60,000 $70,000 0 5 10 15 20 25 30 Sa le s No. of Products Product Performance Series1 $10,000 $20,000 $30,000 $40,000 $50,000 $60,000 $70,000 0 5 10 15 20 25 30

(47)

Radar

charts

Data that is arranged in columns or rows on a worksheet can be plo ed in a radar chart. Radar charts com‐ pare the aggregate values of several data series. They allow you to see the rela onship between four to six variables and see the big picture more quickly.

Applica ons

Radar charts are used for such things as:  Control of quality improvement to display the performance metrics of any ongoing program.  Sports applica ons to chart players' strengths and weaknesses, where they are usually called spider charts

Radar,

Radar

with

Markers

and

Radars

Filled

In

With or without markers for individual data points, radar charts display changes in values rela‐ ve to a center point, in this case average temperatures in Bermuda, Sydney and Memphis, at various mes of the year.

(48)

Thermometer

Charts

Crea ng

a

chart

that

acts

like

a

thermometer

1. Start with a table that lists Days from Day 1— Day 15 (see example below using Auto fill. 2. At the bo om enter labels: Goal, Current and % (see example below)

3. Insert numbers from 90 to 150 in the first 7 days 4. In the Goal cell enter 1000

5. In the Current field enter the formula =SUM(U6:U20)(or whatever range applies to your worksheet)

6. In the % field =U23/U22 and format the field as a percentage(in class worksheet) 7. With the percentage field selected, insert a Column Cylinder Chart next to your table 8. Select the Design tab under the Chart Tools menu and click the design circled be‐

low

9. Right click the blue cylinder and select Format Data Series

10. Select Series Op ons and move the sliders for the width and depth gaps as neces‐ sary

11. Under Chart Tools, click on Layout and select Data Labels>Show

12. When the data label appears in the

plot area, move it to the center of the

cylinder and en‐ large as appropriate

(49)

Combina on

Charts

Change one of the series in a bar chart to a line chart to enhance the comparison of the two data series.

1. Select the series (bars) that you want to change 2. Right click and select Change Series Chart Type 3. Select Line and choose a line chart type

4. Press OK, and the line appears over the top of the other series bar

(50)

Sparklines

Sparkline charts are small, high resolu on line graphs embedded in a context of words, numbers, or images. Sparklines are data‐intense, design‐simple, word‐sized graphics. that illustrate a single trend.

They do not include axes or labels; context comes from the related content.

Usually, when a graph is included in a report, the reader has to break concentra on and take me to study the graph. A sparkline minimizes the me it takes to understand what is being displayed by the graph. Sparklines commonly display trends over me, but they can be used to show any trend that could be displayed on a line graph. They are o en used to illustrate stock trends, weather trends or produc on rates over me.

Steps for crea ng a Sparkline 1. Select a data range 2. On the Insert Tab, in the Sparkline sec on, select either Line, Column or Win‐Loss. 3. When the Sparkline dialog appears, click in a cell to specify the loca on in which you want the Sparkline to appear. 4. Click OK. The Sparkline appears in the selected cell To Insert it into a Word document, copy it and paste it as a picture.

Op ons on the Sparkline Tools Design ribbon include:  Changing the type of chart  Showing various points along the chart  Changing styles and marker colors  Several axis op‐ ons