Excel 2007, working with charts and graphs
1. Create an embedded chart
2. Work with chart titles and legends.
3. Create and format a pie chart.
4. Work with 3D charts.
5. Create and format a column chart.
6. Create and format a line chart.
7. Use custom formatting with chart axes.
8. Work with tick marks and scale values.
9. Create and format a combined chart.
10.Insert and format a graphic shape.
11. Create a chart sheet
Important Points:
1. Chart Wizard - select data, chick chart wizard, select chart type,
next, make any modification to chart data source, next, make modifications to
chart appearance, next, specify location for chart, finish.
| Dialog box |
Task Options |
| Chart Type |
Select from list of available chart types and corresponding
sub-types, or choose to customize a chart type |
| Chart Source Data |
Specify the cells that contain the data on which the chart will be
based and the cells that contain the labels that will appear in the
chart |
| Chart Options |
Change the appearance of the chart by selecting the options that
affect titles, axes, gridlines, legends, data labels, and data tables |
| Chart Location |
specify where the chart will be placed: embedded as on object in the
worksheet containing the data or on a separate worksheet, also called a
chart sheet. |
2. Chart Type:
| Chart Type |
Description |
| Column |
compares values from different categories. Values are indicated by
height of column |
| Bar |
compares values from different categories. Values are indicated by
the length of the bars. |
| Line |
compares values from different categories. Values are indicated by
the height of the line. Often used to show trends. |
| Pie |
Compares relative values of different categories to the whole. value
are indicated by the size of the pie slice. |
| XY(scatter) |
shows the patterns or relationship between two or more sets of
numeric values. Often used in scientific studies and statistical
analyses. |
| Area |
Similar to the line chart, except that areas under the lines are
filled with colors indicating the different categories. |
| Doughnut |
similar to the pie chart, except the it can display multiple sets of
data |
| Radar |
compares a collection of values from several different data sets. |
| Surface |
compares threes sets of values in a three-dimensional chart. |
| Bubble |
similar to the XY (scatter) chart, except the size of the data
marker is determined by a third numeric value. |
| Stock |
Displays stock market data, including the high, low, opening and
closing prices of a stock |
3. Data source is the range that contain the data you want to display in the
char.
4. data series consist of data values - plotted on vertical y-axis
5. Series name identifies the data series.
6. Series values are the actual data displayed in he chart.
7 category values are plotted on horizontal x-axis.
8. Plot area is rectangular area contain graphical representation of
values.
9. Data marker represent data
10. Scale is the range of values that spans an axis.
11. Tic marks are like division lines on a ruler.
12. Gridlines extend tick marks Major and minor.
13. legend identifies the format of the data marker used for each
series.
14. chart title is used to describe the content of the plot area and
data series.
15. chart area is the entire chart.
16. Insert Titles into chart- click titles tag in Chart Wizard, Click
Chart title, type in title, tab, type in x axis title, tab, type in Y axis
title.
17. Insert Gridlines - click gridlines tab in chart wizard, click
Major gridlines and/or Minor Gridlines.
18. Chart Location - embedded chart is a chart that is displayed
within the worksheet- chart sheet is a new sheet that is automatically
inserted into the workbook.
19. The embedded chart is placed in the worksheet next to its data
source.
20. Designing effective charts
| Keep it simple |
| Limit the number of data series used in the chart |
| Use gridlines in moderation |
| Choose colors carefully |
| Limit the use of different text styles to no more than two. |
| Analyze whether you need a chart in the first place |
| Pie charts should be used only when the number of categories is
small and the relative sizes of the different slices can be easily
determined. |
| Use a column or bar chart when you have several categories |
| Line charts are best for categories that follow a sequential
order. note: time intervals must be a constant length if used in a
line chart. |
| XY scatter charts show the patterns or relationships between two
or more sets of values. Good for data recorded at irregular time
intervals |
| Custom charts are created when none of the default chart will
work. Also can buy third party vendors chart software that allow
excel to create charts not built into the software. |
|
Include data labels with all 3D charts
|
21. Pie Chart - select range of cells, click the insert tab on the
Ribbon, in the charts group, click Pie in the chart type list box,
| Name |
Pie chart with |
| Layout 1 |
Chart title, percentages, and legend |
| Layout 2 |
Chart title, percentages, and legend above the pie |
| Layout 3 |
Legend below the pie |
| Layout 4 |
Labels in pie slices |
| Layout 5 |
Chart title and labels in pie slices |
| Layout 6 |
Chart title, percentages, and legend to the right of the pie |
| Layout 7 |
Legend to the right of the pie |
22. Format chart title: click the chart title, type in the
new title, enter. Click home tab on ribbon, in front group click font size,
select size.
23. Format Chart legend click chart tools layout tab on
ribbon, in labels group, click the legend button, click show legend at left
or right or bottom or top. In labels group click the legend button, click
more legend options, select the option you want.
24. Format pie data labels- click chart, in labels group on
chart tools layout tab, click data labels button, click, more data label
options, select the option you want. click numbers in the list on the left
side of box, click the option you want.
25. Leader lines might be added to the labels to connect
them to their corresponding slices.
26. An exploded pie chart moves one slice away from the pie
as if someone were taking here piece out of the pie.
27. Formatting chart elements.
28. Data labels: In labels group on chart tools layout tab, click
data labels button, click more data label option. select the option you
want.
29. Pie Slice Colors - click the pie, click one of the
slices, chick home tab on ribbon, in font group click fill color button
arrow, select the color you want.
30. Rotating a pie chart - double click the pie chart to open format
data series box, double click the first slice of the pie chart, click in
angle of first slice and put in the degrees desired, OK.
31.Explode a slice of a pie chart. - click the pie chart, click the
pie slice you want to explode, drag the selected pie slice away from the
rest, release mouse.
32. Explode the entire pie chart - click the pie chart, drag any pie
slice to explode all the slices, release mouse.
33. Editing data source of a chart- go to the cell(s) that contain
the data that the chart uses and change the data where appropriate.
34. Changing chart type- click the chart, click chart tools design
tab on the ribbon., in the type group click change chart type, select the
chart type you want.
35. A column chart displays values in different categories
as columns with the height of the column being the value of the data. A
Bar chart is a column chart turned on it side.
36. Creating a column chart - select data, click insert tab
on the ribbon, in charts group click column, select the type of column chart
you want, if you want the chart placed in another sheet, in the location
group on the chart tools design tab, click move chart button, click object
in arrow and select the sheet you want the chart to be place in.
37. Elements of a column chart
| Vertical, or value axis displays the values associated with the
heights of each column |
| Primary value axis |
| Secondary value axis |
| Scale is a range of values that spans the vertical axis. |
| Major tic mark act line the lines on a ruler making it easier to
read the scale |
| Minor tick marks to further divide the space between the major
tick marks. |
| Gridlines are use to extend the tick marks across the plot area |
| Horizontal, or category axis displays the categories associated
with each data value |
38. Format legend - click chart tools layout tab on the
ribbon, in the labels group click the legend button and click the option of
your choice.
39. Format chart title - click the chart title, click home
tab on the ribbon, in the font group, click the font size arrow, click the
font size you desire, enter.
40. Format chart axes - click the axis you want, in the
font group on the home tab, click font size arrow, select the font size,
click the chart tools layout tab on the ribbon, in the axes group click axes
button, point to primary vertical axis , click more primary vertical axis
option, click major unit fixed button, press tab, key in the sixw of major
tick mark, click number, type in the number of decimal places, click close.
41. Format chart columns: click any column, in current
selection group on the chart tools layout tab, click format selection,
select the options you want to apply to the columns from the format data
series dialog box , click close.
42. Creating a line chart which compares values from
several categories with a sequential order. select data, click insert tab on
the ribbon, in charts group click line button, select the line chart you
want,
43. Shapes -excel has a gallery of 160 different shapes -
Select cell, click insert tab on ribbon, in illustrations group click shapes
button , select the shape you want from the gallery, drag the pointer over
the area you want to insert the shape.
44. When a shape is selected the format tab appears on the
ribbon with a label identifying it as drawing tools.
45. Resize the shape- in the size group on the drawing
tools format tab, click shape height down arrow and select the desired
height., in the size group click shape width down arrow and select the width
you want.
46. To copy and paste shape - select the shape, click the
home tab on the ribbon, in clipboard group click copy button, click paste.
47. Aligning and grouping shapes - click the first shape,
hold down the shift key and click each of the remaining shapes, click the
drawing tools format tab on the ribbon, in the arrange group click align
button, click align middle, in the arrange group click align button then
click distribute horizontally.
48. Group several shapes into single unit - with shapes
selected, in the arrange group on the drawing tools format tab, click group
button, then click group.
49. Create chart sheet - once chart has been built - in the
locations group on the chart tools design tab, click move chart button,
click new sheet option button type in name of sheet, click OK.
50. Format chart sheet. click the chart sheet, in chart
styles group on the chart tools design tab click more select a style from
the gallery,