Microsoft Office Excel 2003

1
Microsoft Office Excel 2003

• Working With Charts and Graphics
• Developing a Professional-Looking Worksheet

2
VLOOKUP examples
3
(No Transcript)
4
Something is missing!
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
Create column and pie charts in Excel

• Charts, or graphs, provide visual representations
  of the workbook data.
• A chart may be embedded in an existing worksheet,
  or can be created on a separate chart sheet, with
  its own tab in the workbook.
• You can use Excels Chart Wizard to quickly and
  easily create charts.
• The Chart Wizard is a series of dialog boxes that
  prompt you for information about the chart you
  want to generate

10
Create a chart usingthe Chart Wizard

• To create a chart with the Chart Wizard
• Select the data you want to chart, which will be
  your data source
• Click the Chart Wizard button on the standard
  toolbar
• In the first step of the chart wizard, select the
  chart type and sub-type
• In the second step of the Chart Wizard, make any
  additions or modifications to the chart's data
  source
• In the third step, make any modifications to the
  chart's appearance
• In the fourth and final step, specify the
  location for the chart, then click the OK button

11
Chart Wizard dialog box 1
12
Choosing a data series

• You can alter the data source during step 2 of
  the Chart Wizard and also choose whether to
  organize the data source by rows or by columns.
• The data source is organized into a collection of
  data series.
• A data series consists of data values, which are
  plotted on the chart's vertical, or Y-axis
• The data series category values, or X values,
  are on the horizontal axis, called the X-axis
• A chart can have several data series all plotted
  against a common set of category values.

13
Chart Wizard dialog box 2
14
Modify the appearance of a chart

• The plot area contains data markers, examples of
  which include the columns of a column chart, pie
  slices in a pie chart, or the points used in an
  XY (scatter) chart.
• An axis covers a range of values, called a scale.
  
• The scale is displayed by placing values
  alongside the axes.
• A chart may also contain gridlines by extending
  the tick marks into the plot area.
• Whenever there are several data series for a
  chart, a legend can be placed next to the plot
  area to uniquely identify each series with a
  different color or pattern.

15
Chart Wizard dialog box 3
16
Chart Wizard dialog box 4
17
Resize and move an embedded chart

• An embedded chart is an object that you can move,
  resize or copy.
• Select the embedded chart to make it active the
  selection handles will appear. To resize the
  chart
• Drag the selection handles to increase or
  decrease the size of the chart
• To keep the chart proportions the same as you
  resize, hold the Shift key as you drag one of the
  selection handles
• To move the chart, make it active and then move
  the pointer over a blank area. Click and drag the
  embedded chart to the new location and release
  the mouse button

18
Moving and resizing tips

• When you select the chart to make it active, be
  sure you have clicked the entire chart, and not
  just one of its elements.
• You will be able to tell by the selection
  handles, which will appear at the outermost edges
  of the chart
• When you move the pointer over a blank area of
  the chart after you have selected it, you should
  see the label Chart Area appear.
• These tips will help you select and move the
  entire chart, and not just one of its elements.

19
A selected embedded chart
20
Create a chart sheet

• Create a chart sheet by using the two options in
  the fourth step of the Chart Wizard
• One option lets you place the new chart as an
  object in any existing sheet, which you can
  select from a drop down list box
• The other option is to place the chart as a new
  sheet, which is called a chart sheet
• When you select this option, the chart will
  appear in a new worksheet with its own tab in the
  workbook.

21
Create a pie chart

• Pie charts are very useful for comparing values
  in a data series to each other, but can only use
  one data series at a time.
• One feature of a pie chart is called exploding,
  in which you can slightly separate a particular
  pie slice from the other slices.
• You can explode any or all of the slices of the
  pie. This is referred to as an exploded pie
  chart.
• Exploding a pie chart adds emphasis to a
  particular area of the chart and makes it easier
  to notice.

22
Explode a pie chart

• You can explode all of the slices by selecting
  the entire pie itself so that all the individual
  pieces have selection handles.
• As you click and drag any portion, all the slices
  of the pie will explode outward from each other.
• When the pie is exploded out to the size you
  desire, release the mouse button.
• A fully exploded pie chart is also one of the
  sub-type options of the pie chart type that you
  will see when you use the Chart Wizard.

23
A pie chart with an exploded slice
24
Modify the properties of your charts

• After you create a chart, you can edit the data
  that is used in the chart by changing it in the
  data source worksheet cells.
• If you wanted to remove a data series from all
  categories, you could delete that particular data
  series from the worksheet in many cases.
• If you want to remove a slice of a pie chart, you
  cannot just delete the data in the data source,
  but rather you must change the cell reference of
  the data series for the chart.

25
Modify a pie chart

• Make the pie chart active and then click Chart on
  the menu bar.
• Click Source Data. Edit the series in this dialog
  box, or click the Collapse Dialog button to
  temporarily collapse the dialog box so you can
  drag the pointer over a new range of cells.
• Whatever you select will replace the existing
  range listed in the current data series you are
  editing
• You can then expand the dialog box again with the
  Expand Dialog button, make other changes as
  desired, and click the OK button.
• To move an embedded chart to a new chart sheet,
  select the chart, click Chart on the menu bar and
  click Location. The same dialog box of Step 4 of
  the Chart Wizard will appear and you can click
  the option to place the chart as a new sheet and
  give it a name.

26
Format chart elements

• To format an individual chart element, select the
  element by clicking it and then format its
  appearance using the same tools on the Formatting
  toolbar you used to format worksheet cells.
• You can also double-click the chart element to
  open a dialog box containing formatting options,
  or right-click the element and then select the
  Format command from the shortcut menu to open the
  dialog box.
• There are three basic types of text in an Excel
  chart
• Label text
• Attached text
• Unattached text

27
Excel chart text types

• Label text includes category names, tick mark
  labels, and legend text, which is linked to or
  derived from cells in the worksheet.
• Attached text is not linked to any cells in the
  worksheet examples include the chart title and
  the axes titles.
• Unattached text is any additional text that you
  want to include in the chart.

28
Format colors and patterns

• To work with colors and fills, double-click an
  element and the Format Data Series dialog box
  opens.
• You can use options provided on the Patterns tab
  to change both the border style and the interior
  of a data marker.
• You can also edit an axis scale by
  double-clicking any value on an axis to open the
  Format Axis dialog box.
• In the Format Data Series dialog box, the Pattern
  tab includes a Fill Effects button that provides
  a full range of options to create sophisticated
  colors and patterns, such as gradient, texture or
  even a picture.

29
The Fill Effects dialog box
30
Add a graphic to a chart

• You can set a graphic image as a background for a
  chart using options on the Picture tab of the
  Fill Effects dialog box.
• This can be done for a data marker, but is often
  more appropriate for a larger portion of the
  chart itself, such as the plot area.
• You could also place graphics within the data
  markers, such as the columns in a Column chart.
• The Fill Effects dialog box options for inserting
  a picture are the same for data markers as they
  are for other areas of the chart.
• You can choose to stretch the graphic over the
  entire size of the column, or choose to stack the
  graphic up to the height of the column.

31
Change the axis scale

• There are four values that comprise the y-axis
  scale the minimum, maximum, major unit, and
  minor unit.
• The minimum and maximum values are the smallest
  and largest tick marks that will appear on the
  axis.
• The major unit is the increment between the
  scale's tick marks.
• The chart has a second set of tick marks, called
  the minor tick marks, which may or may not be
  displayed if shown, their positioning is
  determined by the minor unit setting.
• Major tick marks are displayed alongside an axis
  value, whereas minor tick marks, if present, are
  not alongside an axis value.

32
The Scale tab of the Format Axis dialog box
33
Create 3-D charts

• To create a 3-D chart, you may choose to do so
  during the first step of the Chart Wizard, as
  three-dimensional charts are sub-types of most
  other charts, such as the pie chart.
• To change a chart to a 3-D chart, select the
  chart, click Chart on the menu bar, and then
  click Chart Type.
• Choose the 3-D option sub-type of whichever chart
  type you prefer.
• There are also several 3-D charts on the Custom
  Types tab of the Chart Type dialog box.

34
Modify 3-D chart options

• A 3-D chart has several options for modifying the
  3-D effect.
• Perspective is the illusion that parts of the 3-D
  chart that are farther away from you decrease in
  size
• Elevation is the illusion that you are looking at
  the 3-D chart from some particular heighteither
  above or below the chart
• You may also rotate the 3-D chart to bring
  different parts of the chart to the forefront
• Elevation and rotation are options that you can
  change with the 3-D View dialog box, available
  from the Chart menu.
• Excel creates each 3-D chart with a default
  elevation, rotation and height.
• To change the appearance of a 3-D chart once you
  have created one, make sure it is an active chart
  then click Chart on the menu bar and then click
  3-D View.

35
The 3-D View dialog box
36
Open the Format Cells dialog box

• Formatting is the process of changing the
  appearance of your workbook.
• A properly formatted workbook can be easier to
  read, appear more professional, and help draw
  attention to important points.
• The formatting toolbar is the fastest way to
  format your worksheet.
• With buttons on this toolbar, you can apply a
  comma format, adjust the number of decimal places
  in a number, apply Currency and Percent formats
  and even quickly copy formats.
• If you select a cell or range, click Format on
  the menu bar and then click Cells, the Format
  Cells dialog box opens.

37
The Format Cells dialog box
38
Format data using different fonts, sizes and
font styles

• A font is the design applied to letters,
  characters and punctuation marks. Each font is
  identified by a font name or type face.
• Fonts can be displayed in various sizes and you
  can even change the color of the font or the
  background color in the cell.
• These options are available in the Format Cells
  dialog box and there are also buttons available
  for the formatting toolbar to make formatting
  faster.

39
Align cell contents

• When you enter numbers and formulas into a cell,
  Excel automatically aligns them with the cell's
  right edge and bottom border, while text entries
  are aligned with the left edge and bottom border.
  
• You can control the alignment of data within a
  cell horizontally and vertically.
• Left, Right and Center alignments can be selected
  using their respective alignment buttons on the
  Formatting toolbar.
• To align the cell's contents vertically, open the
  Format Cells dialog box and choose the vertical
  alignment options on the Alignment tab.

40
Align using Merge and Center

• Another option available for alignment in the
  Format Cells dialog box and on the Format toolbar
  is the Merge and Center option, which centers
  text in one cell across a range of cells.
• If you want to fit a lot of text within a cell
  but without having to expand the column width to
  be very large, you can use the text wrapping
  option on the Alignment tab, or even choose to
  indent text.
• You can also have Excel shrink the text to fit
  within the given column width you have chosen or
  even rotate text from -90 to 90 degrees.

41
The Alignment tab of the Format Cells dialog box
42
Examples of text formatting
43
Add cell borders and backgrounds

• Excel provides a range of tools to format not
  only the contents of a cell, but also the cells
  themselves.
• The gridlines you see in Excel in a new worksheet
  are not displayed on printed pages.
• You can add a border to a cell using either the
  Borders button on the Formatting toolbar or the
  options on the Border tab in the Format Cells
  dialog box.

44
The Borders button versus the Border tab

• When you click the list arrow for the Borders
  button, a Borders palette appears showing common
  choices as well as a Draw Borders button at the
  bottom of the Border palette gallery.
• The Borders button allows you to create borders
  very quickly, whereas the Format Cells dialog box
  allows you to refine your choices further.
• The Border Tab in the Format Cells dialog box is
  especially useful for controlling how a block of
  cells or a range appears with borders.
• You have the option to change the outermost top,
  bottom and sides of the range independently, as
  well as determine different borders for the lines
  separating the cells inside the range's grid.

45
The Border tab of the Format Cells dialog box
46
Add patterns or colors to cells

• Patterns and colors can be used to enhance the
  appearance of spreadsheet cells.
• The fastest way to apply background color to
  cells in the worksheet is by clicking the list
  arrow of the Fill color button and choosing a
  color from the palette.
• To apply a fill pattern to cells, use the
  Patterns tab on the Format Cells dialog box.

47
The Patterns tab of the Format Cells dialog box
48
A worksheet with formatting applied
49
Merge a range of cells

• To merge a range of cells into a single cell
• Use the Merge option on the Alignment tab in the
  Format Cells dialog box
• Click the Merge and Center button on the
  Formatting toolbar
• To split a merged cell back into individual
  cells
• Select the merged cell
• Click the Merge and Center button again
• Or uncheck the Merge Cells check box on the
  Alignment tab in the Format Cells dialog box

50
Merge headings across multiple cells