Transcript Excel Tutorial 3 PowerPoint

XP
Microsoft Excel 2002
Tutorial 3 – Developing a Professional
Looking Worksheet
New Perspectives on Microsoft
Excel 2002 Tutorial 3
1
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
2
XP
The Format Cells dialog box
The Format Cells dialog
box has six tabs, each
dedicated to a different set
of format properties.
The Font tab will be used to
format the font, size and style
of text in your worksheets.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
3
Format data using different
fonts, sizes and font styles
XP
• 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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
4
XP
Examples of various formats
Excel, by default, will format all your entries using a style called the General format.
This figure shows examples
of some items formatted
with the General format.
You can also see the
Percent Style and
Currency Style
applied to various
cells.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
5
XP
Copy existing formats to other cells
• One way to copy a format is to use the Format Painter
button. To use the Format Painter:
– Select a cell, then click the Format Painter button, which has a
picture of a paintbrush on it
– Select the cell or range you wish to format and the operation is
complete
• Another way to copy a format is to drag the fill handle and
click the Auto Fill Options button, then click the Fill
Formatting Only option.
• Another way to format cells is through the Format Cells
dialog box.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
6
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
7
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
8
The Alignment tab of the
Format Cells dialog box
XP
The Alignment tab provides many
options for aligning data.
Click the check boxes to
select these options.
Rotate text by moving the arrow
with the mouse, or specify the
number of degrees in the text box.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
9
XP
Examples of text formatting
This column header shows an
example of text wrapping.
The text in column
A is indented.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
10
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
11
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
12
XP
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.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
13
XP
The Border tab of the Format Cells
dialog box
The Border tab of the Format Cells dialog box gives you complete control over
the border you want to create for a cell, range of cells, or the entire worksheet.
Click a button to
turn on or turn off
the border for the
indicated area.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
14
XP
The Patterns tab of the Format Cells
dialog box
Using the Patterns tab of the Format
Cells dialog box, not only can you
change the background color of the
worksheet, but also you can select
from a palette of patterns, as shown
in the figure to the right.
The color palette on the Patterns tab is
the same one that is also available from
the list arrow of the Fill Color button.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
15
XP
A worksheet with formatting applied
This figure shows how you might use
borders, background colors and patterns
to improve the appearance of a worksheet.
Certain background patterns can
overwhelm the text in some cells, so
you can improve the appearance by
changing the color of the pattern
itself to a lighter color if you are using
standard black text.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
16
XP
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
New Perspectives on Microsoft
Excel 2002 Tutorial 3
17
XP
Hide rows and/or columns
• You can hide rows or columns, which does not affect the
data stored there, nor does it affect any cell that might have
a formula reference to a cell within the hidden row or
column.
• To hide a row or column:
– Select the row or column and then choose Hide from either the
Row or Column option of the Format menu, or, from the shortcut
menu that pops up when you right click the row or column heading
• To unhide a row or column:
– Select the headings of the rows or columns that border the hidden
area, then choose Unhide from either the Row or Column option of
the Format menu, or, from the shortcut menu that pops up when
you right click the row or column heading
New Perspectives on Microsoft
Excel 2002 Tutorial 3
18
XP
Merge headings across multiple cells
This figure shows a sample
worksheet with a boldfaced
title in the first row merged
and centered across the
columns used for data.
The Merge and Center button is the fastest way to merge several
cells into one or to split one merged cell back into several cells
New Perspectives on Microsoft
Excel 2002 Tutorial 3
19
XP
Worksheet with hidden cells
This figure shows the same worksheet that was shown in the previous
slide, but it has now had several cells hidden. Hiding extraneous cells
can frequently improve the overall appearance of the worksheet.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
20
XP
Format the worksheet background
and sheet tabs
• You can use an image file as a background for a worksheet.
• Images can be used to give the background a textured
appearance, like that of granite, wood, or fibered paper.
• The background image does not affect the format or
content of any cell in the worksheet, and if you have
already defined a background color for a cell, Excel
displays the color on top, hiding that portion of the image.
• You cannot apply a background image to all the sheets of
the workbook at the same time.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
21
Insert a background image and XP
change a worksheet tab color
• To add a background image to a worksheet:
– Click Format on the menu bar, point to Sheet and click
Background
– Locate and select an image from your hard drive, floppy drive,
network, etc., and click the Insert button
• You can also format the background color of the worksheet
tabs, but this color is only visible when the worksheet is
not the active sheet in the workbook.
– Right click the tab you want to change and choose Tab color from
the shortcut menu
– Select a color from the color palette, click the OK button, and then
click on a different tab in order to see the color displayed on the
changed tab
New Perspectives on Microsoft
Excel 2002 Tutorial 3
22
XP
A worksheet with a background image
This figure shows a
worksheet with a
background image
added, creating a
textured
background.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
23
XP
Find and replace formats
within a worksheet
• The Undo button on the Standard toolbar is very
useful for removing formatting choices you have
decided you do not want to use.
• You can also clear the formatting of selected cells,
returning them to their initial, unformatted
appearance.
– To clear formatting, select a cell or range, click Edit on
the menu bar, point to Clear and then click Formats
New Perspectives on Microsoft
Excel 2002 Tutorial 3
24
Use Find and Replace
to change formats
XP
• Click Edit on the menu bar and then click Replace.
• When the Find and Replace dialog box opens, click the
Options >> button to expand the box and display additional
find and replace options.
• Click on the Replace tab and then click the topmost Format
button to open a Find Format dialog box, select the format
combinations you want to search for, then click the OK
button.
• Click the lower Format button and when the dialog box
opens, select the options you want to use for replacing the
formatting.
• Click the OK button and then the Replace All button to
quickly change all the cells that meet your Find Format
criteria.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
25
Dialog boxes used for Find and
Replace operations
XP
The Find Format
dialog box.
The Find and Replace dialog box.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
26
XP
Create and apply styles
• If you have several cells that use the same format, you can
create a style for those cells.
• A style is a saved collection of formatting options: number
formats, text alignment, font sizes and colors, borders, and
background fills.
• If you modify the specifications for a style, the appearance of
any cell associated with that style would be automatically
changed to reflect the new style.
• To create a style, click on a cell that has formatting applied to
it and this formatting becomes the basis of the new style you
want to create.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
27
XP
Create a style using the
Style dialog box
• Click Format on the menu bar, and then click Style. The Style dialog
box opens and all the formatting options associated with the active cell
are listed.
• Give the style a name, and then modify the formatting options by
removing or adding to the existing ones listed in the dialog box. Click
the OK button to create a style with a specific name.
• To apply a style within a worksheet, first select the cells you want
associated with the style, then open the Style dialog box, choose the
style name from the list arrow and then click the OK button.
• When you create a style, you can also click the Merge button in the
Style dialog box to merge a style with those from other open
workbooks.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
28
XP
The Style dialog box
The Style dialog box allows you to create, name and customize styles.
You can also copy styles from one workbook to another. Copying styles allows
you to create a collection of workbooks that share a common look and feel.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
29
XP
Apply an AutoFormat to a table
• You can apply a professionally designed format to your
worksheet by choosing one of 17 predefined formats from
the AutoFormat gallery.
• To apply an AutoFormat to a table:
– Select a range that has a table of information in it
– Click Format on the menu bar, click AutoFormat and the
AutoFormat dialog box opens. Scroll through the gallery to view
different table formats, click on one you want to try, and then click
the OK button.
– Click on a cell outside of your selected range to remove the
highlighting from your table so you can see what it looks like with
the AutoFormat design applied.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
30
XP
The AutoFormat style gallery
This figure shows some of the
AutoFormats available for use.
The designs in the AutoFormat
gallery are very useful. You can
either employ the professional
design that Excel provides you,
or simply use it as a starting
point to apply a design that is
close you what you want, which
you can then modify to fit your
own needs.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
31
XP
Format a printout using Print Preview
• Open a Print Preview window by clicking the Print Preview button on
the Standard toolbar.
• Excel will display the preview as a full page, which may be difficult to
read.
• Click the Zoom button on the Print Preview toolbar, or pass your
mouse over the page, and the pointer changes to the shape of a
magnifying glass. When you click any portion of the page Excel will
zoom in. Zoom out using the same methods.
• By clicking the Setup button on the Print Preview toolbar, you can
change margins, orientation, center the page or set several other
formatting and printing features.
• You can also open the Page Setup dialog box by selecting that option
from the File menu.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
32
The Margins tab of the
Page Setup dialog box
XP
The Page Setup dialog box controls how a worksheet is placed on a page for printing.
You can adjust the size of the
margins, which are the spaces
between the page content and the
edge of the page. Most printers
require a minimum margin.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
33
Create a header and footer for a XP
printed worksheet
• A header is text printed within the top margin of every
worksheet page and a footer is printed within the bottom
margin of every page.
• Headers and footers can add important information to your
printouts.
• Excel tries to anticipate headers and footers and provides
several preformatted options in list boxes on the
Header/Footer tab of the Page Setup dialog box.
• Click the list arrow for these header and footer options and
select one of Excel's suggestions or create your own by
choosing the Custom Header or Custom Footer buttons on
the Header/Footer tab.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
34
XP
The Header dialog box
This figure shows
the Header dialog
box. This dialog
box presents the
same options as
the Footer dialog
box.
You can type in any text you like and use the Font button to format the text just
as you would in a worksheet cell. The other Header/Footer formatting buttons
provide some common actions using built-in Excel formatting codes.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
35
Define a print area and add a pageXP
break to a printed worksheet
• By default, Excel prints all parts of the active worksheet
that contain text, formulas, or values.
• You can define a print area that contains only the content
that you want to print.
• To define a print area, select the range you want to print,
click File on the menu bar, point to Print Area, and then
click Set Print Area.
• You can also specify different sections of your worksheet
to print on separate pages.
– Insert a page break by clicking on a cell, clicking Insert on the
menu bar, and then clicking Page Break
New Perspectives on Microsoft
Excel 2002 Tutorial 3
36
The Sheet tab of the
Page Setup dialog box
XP
The Page Setup dialog box can specify cells that will repeat on each page,
print gridlines, and whether to print or not to print headings on each page.
New Perspectives on Microsoft
Excel 2002 Tutorial 3
37