Transcript Practical Literacy 2e Ch. 13.ppt

Chapter 13
Formatting a Worksheet
1
What’s inside and on the CD?
• In this chapter you will learn how to:
– Format worksheets created with Microsoft Excel
– Determine special formatting characteristics for each
type of data
– Make changes to spreadsheet data in order to show
how changes effect results
– Avoid making incorrect modifications to data
Practical Computer Literacy, 2nd edition
Chapter 13
2
How do I add borders and
background colors?
• Borders and background colors define areas and can
draw attention to important information
• To add borders, use the Font group on the Home tab
and Format Cells dialog box
Practical Computer Literacy, 2nd edition
Chapter 13
3
How do I add borders and background
colors?
Practical Computer Literacy, 2nd edition
Chapter 13
4
How do I add borders and
background colors?
• Select border option buttons in the Border section of
dialog box
• The Line area allows selection of decorative line styles
or add color
Practical Computer Literacy, 2nd edition
Chapter 13
5
How do I add borders and background
colors?
• Click the Fill tab to add a colored background to selected
cells
• For a shortcut to Borders, use the
group on the Home tab
button in the Font
Practical Computer Literacy, 2nd edition
Chapter 13
6
How do I format worksheet data?
• Buttons in the Font group on the Home tab allow you
to select different font attributes for data in worksheet
cells
• Values and formula results can use the same font
attributes used in labels
Practical Computer Literacy, 2nd edition
Chapter 13
7
How do I format worksheet data?
Practical Computer Literacy, 2nd edition
Chapter 13
8
How do I format worksheet data?
• To apply format:
– Click in the cell to format
– Click as many font attributes as wanted
– Click outside cell to complete
• To change font for range:
– Click top-left cell
– Drag mouse to selected cells
– Release mouse button
– Apply font formatting option
Practical Computer Literacy, 2nd edition
Chapter 13
9
How do I format worksheet data?
• Separate words or letters in a cell can be formatted
differently. Use the mouse inside the cell to select words
or characters to format
• More formatting options:
– Select cell(s)
– Click Format Cells
– Click Cells to display Cells Format dialog box
– Select formatting option
– Click OK to apply
Practical Computer Literacy, 2nd edition
Chapter 13
10
How do I use the Format Cells dialog
box?
• These number formats can be applied to cells that
contain values:
– Currency
– Percent
– Commas
– Decimals
• Number group on the Home tab can be used to apply
most common number formats
• Format Cells dialog box provides options to improve
readability
Practical Computer Literacy, 2nd edition
Chapter 13
11
How do I use the Format Cells dialog
box?
• The Accounting Number Format button button
displays contents in local currency format
• The Percent Style button displays the cell contents as
a percentage
• The Comma style button adds a comma to values
• The Decrease Decimal button, one less digit is
displayed, the Increase Decimal button, one more digit
is displayed
• Select a range of cells then click any format buttons to
apply formatting to a range
Practical Computer Literacy, 2nd edition
Chapter 13
12
How do I use the Format Cells dialog
box?
Practical Computer Literacy, 2nd edition
Chapter 13
13
How do I adjust column and row size?
• A column too narrow will display as #####. The entire
column width can be changed, but not just one cell
• To adjust the width, position the pointer between two
column headings. When the shape changes to
,
press and hold the left mouse button while you drag
to adjust the width of the column
Practical Computer Literacy, 2nd edition
Chapter 13
14
How do I adjust column and row size?
• If a label is too long to fit into a cell, it will extend into
the cell on the right if that cell is empty. If not, the
label will be cut off
Practical Computer Literacy, 2nd edition
Chapter 13
15
How do I adjust column and row size?
Practical Computer Literacy, 2nd edition
Chapter 13
16
How do I center and align cell
contents?
• By default, labels are left-aligned while values and
formulas are right-aligned.
• Typically, you will want to center or right-align a label
when it is a column heading.
• Select the header cell and click the Align Text Right
button.
Practical Computer Literacy, 2nd edition
Chapter 13
17
How do I center and align cell
contents?
• Select a range of cells and click the desired alignment
button to align a range of cells.
• Click column header at top of a column to select entire
column.
• Click row header on left of row to select all cells in row.
• To center text across columns, select the range, then
click the Merge & Center button.
Practical Computer Literacy, 2nd edition
Chapter 13
18
How do I center and align cell
contents?
• To merge a range of cells in a column:
– Select the range
– Click the Merge & Center button in the Alignment
group on the Home tab
• The down-arrow button next to the Merge & Center
button allows you to
– Unmerge cells
– Merge without centering
Practical Computer Literacy, 2nd edition
Chapter 13
19
How do I center and align cell
contents?
Practical Computer Literacy, 2nd edition
Chapter 13
20
What happens when I copy and move
cells?
• Use the Cut, Copy, and Paste buttons to copy or move
cell contents to a different worksheet location.
• Label data is copied or moved without changing.
• When you copy and paste cells containing a formula, the
copied formula is altered to work in the new location.
Practical Computer Literacy, 2nd edition
Chapter 13
21
What happens when I copy and move
cells?
• A cell reference that changes when a formula is copied
or moved is called a relative reference.
• Unless you specify otherwise, Excel treats all cell
references as relative.
Practical Computer Literacy, 2nd edition
Chapter 13
22
What happens when I copy and move
cells?
• To move data in cells:
– select the cells, then click the Cut button
– click the new cell, then click the Paste button
• When you copy or move data in a range, click the cell in
the top-left corner where you want the data to be pasted
Practical Computer Literacy, 2nd edition
Chapter 13
23
What happens when I copy and move
cells?
• A formula containing a relative reference changes when
it is copied or moved.
– Example:
• Assume cell C4 contains the formula =C2+C3. If
you copy and paste that formula to cell F4, the
formula will be changed to =F2+F3.
– This is because the references C2 and C3 are relative
references.
Practical Computer Literacy, 2nd edition
Chapter 13
24
What happens when I copy and move
cells?
• When the formula was in cell C4, Excel interpreted it as:
=(the contents of the cell two rows up) + (the contents of
the cell one row up)
• When it was copied to cell F4, Excel adjusted the
formula so it retained the same relative references,
making it =F2+F3.
Practical Computer Literacy, 2nd edition
Chapter 13
25
What happens when I copy and move
cells?
Practical Computer Literacy, 2nd edition
Chapter 13
26
When should I use absolute
references?
• Sometimes, you do not want cell references to change
when moved to a new location.
• An absolute reference will not change and always refer
to the same cell.
• To create an absolute reference, insert a dollar sign ($)
before the column reference and another dollar sign
before the row reference.
Practical Computer Literacy, 2nd edition
Chapter 13
27
When should I use absolute
references?
• If you want to use an absolute reference in a formula,
begin typing, then press the F4 key after you click a cell
to add it to the formula.
• Pressing the F4 key changes the current reference to an
absolute reference.
• References can be combined so only one row or column
is an absolute reference.
Practical Computer Literacy, 2nd edition
Chapter 13
28
When should I use absolute
references?
• For example,
– $C1 creates an absolute column and a relative row
reference
– C$1 creates a relative column and an absolute row
reference
• The absolute identifier will not change, but the relative
identifier will.
Practical Computer Literacy, 2nd edition
Chapter 13
29
When should I use absolute
references?
Practical Computer Literacy, 2nd edition
Chapter 13
30
How do I delete and insert rows and
columns?
• When you insert or delete rows or columns, Excel will
adjust your formulas to refer to the correct cells.
• To insert a row, select a row, click the down-arrow button
next to Insert in the Cells group, then click Insert Sheet
Rows.
Practical Computer Literacy, 2nd
edition Chapter 13
31
How do I delete and insert rows and
columns?
• To insert more than one row, drag down over the
number of rows to insert and follow the same steps for
inserting a row
• To delete rows, select rows, drag over the rows you want
to delete, click the down-arrow button next to Delete in
the Cells group, then click Delete Sheet Rows
Practical Computer Literacy, 2nd edition
Chapter 13
32
How do I delete and insert rows and
columns?
• Use the same procedures to insert and delete columns.
• Excel will adjust the relative cell references in formulas
to keep them correct as you insert or delete.
Practical Computer Literacy, 2nd edition
Chapter 13
33
How do I delete and insert rows and
columns?
Practical Computer Literacy, 2nd edition
Chapter 13
34
Can I use styles?
• Use predefined styles or create your own styles
• The ,
, and
toolbar buttons automatically format
a cell or group of cells
• Click Styles in the Styles group, right-click the desired
style from the Number Format section, then click Modify
to redefine the default setting
Practical Computer Literacy, 2nd edition
Chapter 13
35
Can I use styles?
• Styles include:
– Text formatting
• Font
• Size
• Color
– Numeric formatting
• Comma placement
• Number of decimal points
• Currency symbol
Practical Computer Literacy, 2nd edition
Chapter 13
36
Can I use styles?
• Create you own styles:
– Click Cell Styles
– Click New Cell Style
– Type the new style name
– Click the Format button if you want to modify the
characteristics
– Click OK to accept changes in Format Cells dialog
box
– Click OK to create the style
Practical Computer Literacy, 2nd edition
Chapter 13
37
Can I use styles?
• The Format Painter button allows you to copy and
paste formats from one cell to another
– Click cell containing formats to be copied
– Click Format Painter button
– Click cell where you want format applied
• The Styles group includes a variety of predefined
formats designed to format entire worksheets or sections
of worksheets
Practical Computer Literacy, 2nd edition
Chapter 13
38
Can I use styles?
• The Hide function is used to hide rows or columns you
don’t want displayed
• To use function:
– Select rows or columns to be hidden
– Right-click highlighted area
– Select Hide
• To display hidden rows or columns, select rows or
columns bordering hidden section and right-click, then
choose Unhide
Practical Computer Literacy, 2nd edition
Chapter 13
39
Can I use styles?
Practical Computer Literacy, 2nd edition
Chapter 13
40
How do I manage multiple
worksheets?
• A workbook – sometimes called a “3D workbook” – is a
collection of worksheets
• Workbooks allow related worksheets to be grouped
together
• Worksheets in a workbook can access data from other
worksheets
Practical Computer Literacy, 2nd edition
Chapter 13
41
How do I manage multiple
worksheets?
• Default workbook contains three worksheets, titled Sheet
1, Sheet 2, and sheet 3
• Click tabs at bottom of screen to navigate
• Rename worksheets, change color of tab, or change
order by right-clicking worksheet tab and making
selection
Practical Computer Literacy, 2nd edition
Chapter 13
42
How do I manage multiple
worksheets?
• To insert new worksheet:
– Right-click tab for worksheet that should follow new
worksheet
– Select Insert from shortcut menu
– Make selection from Insert dialog box
• To delete existing worksheet:
– Right-click worksheet’s tab
– Click Delete
Practical Computer Literacy, 2nd edition
Chapter 13
43
How do I manage multiple
worksheets?
• Move/copy option allows you to change the order of
worksheets
• For example, to insert new worksheet in front of Sheet 1,
insert new sheet after any tab, then use Move/copy
option to position as first sheet
Practical Computer Literacy, 2nd edition
Chapter 13
44
How do I manage multiple
worksheets?
• To reference data from other worksheets, include tab
name before row letter and column number
• Example:
– Sheet3!A1 indicates Column A, Row 1 on Sheet 3
• Also reference data on other worksheets by:
– Navigating to worksheet
– Click desired cell
– Enter formula or function
Practical Computer Literacy, 2nd edition
Chapter 13
45
How do I manage multiple
worksheets?
Practical Computer Literacy, 2nd edition
Chapter 13
46