Transcript Practical Literacy 2e Ch. 14.ppt

Chapter 14
Finalizing a Worksheet
1
What’s Inside and on the CD?
• In this chapter, you will learn how to:
– Finalize your worksheets
– Prepare your worksheets for printing
– Turn your worksheets into Web pages
Practical Computer Literacy, 2nd edition
Chapter 14
2
Can I sort data in a worksheet?
• Data in Excel can be sorted in:
– Ascending order – alphabetically, A to Z
– Descending order – reverse alphabetically, Z to A
• It’s good practice to save worksheet before sorting data
• Select all columns of related data before sorting data
Practical Computer Literacy, 2nd edition
Chapter 14
3
Can I sort data in a worksheet?
• Click Undo button if you forget to select all columns
before sorting
• Use Sort Ascending or Sort Descending buttons on
the ribbon if you want to sort data in first column
• Use procedure shown in the figure on the next slide to
sort by column other than the first, or by several columns
Practical Computer Literacy, 2nd edition
Chapter 14
4
Can I sort data in a worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
5
How do I create a chart?
• Pick chart type that suits data:
– Line charts used to show data that changes over
time
– Pie charts illustrate proportion of parts to whole
– Bar charts (“column charts”) used to show
comparisons
Practical Computer Literacy, 2nd edition
Chapter 14
6
How do I create a chart?
• To create a chart, use the Chart group on the Insert tab
• When selecting cells for a chart, include the cells that
contain labels
• If you are not certain which chart type to use, hover the
pointer over the chart buttons to see a description
• If the chart doesn’t seem to make sense, try clicking the
Switch Row/Column button
Practical Computer Literacy, 2nd edition
Chapter 14
7
How do I create a chart?
• By default, the chart is inserted into the current
worksheet
• When a chart is selected, you can move it or resize it by
dragging the sizing handles
Practical Computer Literacy, 2nd edition
Chapter 14
8
How do I create a chart?
Practical Computer Literacy, 2nd edition
Chapter 14
9
How do I modify a chart?
• Excel creates a chart based on data and labels you
select from a worksheet
• When making changes to chart, make sure the chart is
selected so that you see all of the charting tabs
• To select a different chart type, select the Design tab,
and then click the Change Chart Type button
Practical Computer Literacy, 2nd edition
Chapter 14
10
How do I modify a chart?
• To select a different range of cells to be charted, click the
Select Data button from the Design tab, and then select
the cells or type a range into the Chart data range box
• If you change the data in a worksheet cell, Excel updates
the chart immediately after you press the Enter key
Practical Computer Literacy, 2nd edition
Chapter 14
11
How do I modify a chart?
Practical Computer Literacy, 2nd edition
Chapter 14
12
How do I modify a chart?
• The next step is to improve the readability and
appearance of the chart
• Most modifications begin by right-clicking the chart
element you want to change
• Label formats on charts can be different from those in
worksheet cells
Practical Computer Literacy, 2nd edition
Chapter 14
13
How do I modify a chart?
Practical Computer Literacy, 2nd edition
Chapter 14
14
How do I modify a chart?
• For readability, consider changing the units used for axis
labels
• Excel includes a variety of colors, patterns, and
gradients that can be applied to:
– Backgrounds
– Plot areas
– Gridlines
– Data series
Practical Computer Literacy, 2nd edition
Chapter 14
15
Can I add graphics to a worksheet?
• Worksheet graphics used to highlight important sections,
add interest, or graphically illustrate spreadsheet data.
To insert clip art:
– Click cell where graphic will be placed
– Click Insert tab
– Click Clip Art
– Choose image from available pictures
Practical Computer Literacy, 2nd edition
Chapter 14
16
Can I add graphics to a worksheet?
• Resize graphics using round “handles” on edges of
selected graphic
• Move graphic by holding mouse button down while
dragging it to new location
• Rotate graphic by dragging green rotate handle right or
left
Practical Computer Literacy, 2nd edition
Chapter 14
17
Can I add graphics to a worksheet?
• Shapes tools allow you to draw simple lines and shapes
• To draw an arrow
– Click Insert tab
– Click Shapes
– Select Arrow from the Lines group
– Click the worksheet cell where you want the arrow to
start, then drag to “draw” the arrow
Practical Computer Literacy, 2nd edition
Chapter 14
18
Can I add graphics to a worksheet?
• The SmartArt button on the Insert tab allows you to
insert visual aids
• To insert SmartArt
– Click the SmartArt button on the Insert tab
– Select the shape you want
– Click the OK button
– Drag the shape to your desired location
Practical Computer Literacy, 2nd edition
Chapter 14
19
Can I add graphics to a worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
20
How do I check spelling in a
worksheet?
• Excel can check spelling of all labels in worksheet
– Doesn’t show misspelled words with wavy underlines
– Doesn’t provide a grammar checker
– Important that you proofread
• Begin checking with any cell selected
– If you make A1 active cell, avoid the question in Step
5 in the figure on the following slide
Practical Computer Literacy, 2nd edition
Chapter 14
21
How do I check spelling in a
worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
22
How do I check spelling in a
worksheet?
• If correct spelling appears in Suggestions list, click to
select, then click Change button
• If no suggested spellings are displayed, click Not in
Dictionary text box, then type correct word. Click Change
button to replace misspelled word.
• Click Ignore Once button if the word is correct
Practical Computer Literacy, 2nd edition
Chapter 14
23
How do I check spelling in a
worksheet?
• Click Ignore All to ignore all other occurrences of word
throughout worksheet
• If word is one you use frequently, click Add to Dictionary
to stop the spelling tool from identifying a word as
misspelled
Practical Computer Literacy, 2nd edition
Chapter 14
24
How do I test my worksheet?
• Always test worksheet before relying on results
• Possible to enter wrong value in cell, use wrong cell
reference in formula, or other error
• Good idea to use the Save As option to rename and
save extra copy of worksheet before testing
Practical Computer Literacy, 2nd edition
Chapter 14
25
How do I test my worksheet?
• One test is to enter series of consistent, easily verified
values, such as 1 or 10, into data cells
• Another test is to enter real-world values for which you
already know results
• Compare calculated results with real-world results
– This testing also helps identify formatting problems
Practical Computer Literacy, 2nd edition
Chapter 14
26
How do I test my worksheet?
• Good idea to enter largest and smallest values that
would reasonably be expected in normal use of
worksheet
– Small values, including zero, can lead to errors such
as division by zero
– Large values can lead to results not fitting in cell
where answer to be displayed
• In this case, make those columns wider
Practical Computer Literacy, 2nd edition
Chapter 14
27
How do I test my worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
28
How do I use Print Preview and Page
Setup?
• Print Preview shows how worksheet will look when
printed
• Can open Print Preview by clicking Preview button on
Print Dialog box
Practical Computer Literacy, 2nd edition
Chapter 14
29
How do I use Print Preview and Page
Setup?
• Zoom button toggles between close-up and normal
view
– Click to switch between them
– In close-up mode, look for ##### symbol
indicating columns needing to be wider
• Adjust margins by:
– clicking Show Margins check box
– positioning pointer over margin line to change to
or
– dragging line to new position
– releasing mouse button
Practical Computer Literacy, 2nd edition
Chapter 14
30
How do I use Print Preview and Page
Setup?
Practical Computer Literacy, 2nd edition
Chapter 14
31
How do I use Print Preview and Page
Setup?
• Also set margins using Page Setup dialog box
• Outside top and bottom margin lines control location of
header and footer
• Inside lines control placement of worksheet data
• More options for controlling printed format in Page Setup
dialog box
Practical Computer Literacy, 2nd edition
Chapter 14
32
How do I use Print Preview and Page
Setup?
• Page Setup dialog box allows control of orientation and
structure
• Click Page Setup button in the Print Preview window
• Or click Page Layout tab, then click Page Setup Dialog
Box Launcher
• Settings saved when you save worksheet
Practical Computer Literacy, 2nd edition
Chapter 14
33
How do I use Print Preview and Page
Setup?
• Page tab contains settings for orientation and scaling
– Portrait orientation – vertically oriented
– Landscape orientation – horizontally oriented
– Scaling options
• Adjust worksheet’s overall size
• Force to fit within width of one page
• Printing large worksheet on one page makes it
easier to read, as long as font legible
– Cancel scaling – click Adjust to option button and
change to 100% normal size
Practical Computer Literacy, 2nd edition
Chapter 14
34
How do I use Print Preview and Page
Setup?
• Margins tab allows you to specify margin settings
• Header/Footer tab allows you to work with headers and
footers
• Sheet tab allows you to specify section of worksheet to
print
– Useful for printing selected section of large worksheet
– Specify whether to print gridlines or row and column
headings
• Printouts with both are useful when you want to show
structure of worksheet
Practical Computer Literacy, 2nd edition
Chapter 14
35
How do I use Print Preview and Page
Setup?
Practical Computer Literacy, 2nd edition
Chapter 14
36
How do I add headers and footers to a
worksheet?
• Excel worksheets can contain headers and footers
– Header is text that appears at top of every page
– Footer is text that appears at bottom of every page
• Excel includes predefined headers and footers:
– Worksheet Title
– Date
– Page number
Practical Computer Literacy, 2nd edition
Chapter 14
37
How do I add headers and footers to a
worksheet?
• Create your own headers and footers
• Footers work just like headers –select appropriate footer
option
• Custom dialog boxes have options to insert commonly
used elements
Practical Computer Literacy, 2nd edition
Chapter 14
38
How do I add headers and footers to a
worksheet?
• Click buttons to:
Practical Computer Literacy, 2nd edition
Chapter 14
39
How do I add headers and footers to a
worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
40
How do I set up a multipage
worksheet?
• Large worksheets may need additional setup to print
correctly on multiple pages
• Use Page Break Preview to examine, and modify, page
breaks for logical data arrangement
• It’s good practice to include row and column labels on
every printed page to assist with data identification
Practical Computer Literacy, 2nd edition
Chapter 14
41
How do I set up a multipage
worksheet?
• Use Page Setup dialog box to specify order in which
multipage worksheets print
• To view page breaks, click View tab, then Page Break
Preview
– Drag blue page break lines to change locations
• To insert new page break, click location for new page
break, then Click Page Layout tab, then click Breaks in
the Page Setup group, then click Insert Page Break
Practical Computer Literacy, 2nd edition
Chapter 14
42
How do I set up a multipage
worksheet?
• To include column or row labels on every page, use the
Page Setup dialog box
– On Sheet tab, use Print titles text boxes to specify row
and column that contain headings
• To specify order in which pages of multipage worksheet
are printed, use Sheet tab of Page Setup dialog box
– In Page Order section choose Down, then over or
Over, then down
Practical Computer Literacy, 2nd edition
Chapter 14
43
How do I set up a multipage
worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
44
How do I print a worksheet?
• Use the Print dialog box to
– Print a single copy of the current worksheet
– Print multiple copies
– Designate selected pages
– Use advanced print options
• Default setting only prints the current worksheet
Practical Computer Literacy, 2nd edition
Chapter 14
45
How do I print a worksheet?
• Determine what you want to print before opening Print
dialog box
• By default, Excel prints entire active worksheet
• To print only a section of worksheet, select range of cells
before you click the Microsoft Office button and click
Print
– Click Selection option in Print what section of dialog
box
Practical Computer Literacy, 2nd edition
Chapter 14
46
How do I print a worksheet?
• Click Active sheet(s) option in Print what section to print
only current worksheet
• Click Entire workbook option to print all worksheets in
current workbook
• Click Preview button to see how worksheet or workbook
will look when printed
• If worksheet doesn’t print, check that printer is online,
and you have specified correct printer in Print dialog box
Practical Computer Literacy, 2nd edition
Chapter 14
47
How do I print a worksheet?
Practical Computer Literacy, 2nd edition
Chapter 14
48
How do I save a worksheet as a Web
page?
• You can save worksheet as Web page to post on
Internet
• Easy way to make data accessible to large number of
people
• Before saving as a Web page, it is a good idea to save in
Excel (.xlsx) format
Practical Computer Literacy, 2nd edition
Chapter 14
49
How do I save a worksheet as a Web
page?
• Tables are a valuable formatting tool for creating Web
pages
• To save as a Web page:
– In Save As dialog box, click the Selection option
– Choose the HTML file type
– Name your file
– Click Save
Practical Computer Literacy, 2nd edition
Chapter 14
50
How do I save a worksheet as a Web
page?
• Not all Excel formatting options can be duplicated in
Web page
– Notification of any problem areas
– Option of canceling or continuing with save
• Not all worksheets successfully convert to Web pages
• Preview worksheet in Web browser to make sure
conversion is acceptable before posting on Internet
Practical Computer Literacy, 2nd edition
Chapter 14
51
How do I save a worksheet as a Web
page?
Practical Computer Literacy, 2nd edition
Chapter 14
52
What makes a good worksheet?
• Longest data sets should go down the screen
• Information should read from left to right and top to
bottom
• Provide meaningful labels
• Enter data accurately
• Enter formulas and functions carefully
• Avoid including labels in mathematical formulas
Practical Computer Literacy, 2nd edition
Chapter 14
53
What makes a good worksheet?
•
•
•
•
•
Avoid circular references
Understand mathematical precedence
Use absolute and relative references appropriately
Avoid using too many fonts, font sizes, and colors
Format numbers for easy reading
Practical Computer Literacy, 2nd edition
Chapter 14
54
What makes a good worksheet?
• Use consistent formats for similar data
• Format cells so that data fits in them
• Add documentation as necessary
Practical Computer Literacy, 2nd edition
Chapter 14
55