Lesson 13 organizing and enhancing worksheets
This presentation is the property of its rightful owner.
Sponsored Links
1 / 37

Lesson 13 Organizing and Enhancing Worksheets PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

Lesson 13 Organizing and Enhancing Worksheets. Computer Literacy BASICS: A Comprehensive Guide to IC 3 , 3 rd Edition. Morrison / Wells. Objectives. Hide and unhide columns and rows. Freeze and unfreeze columns and rows. Create, rename, and delete worksheets.

Download Presentation

Lesson 13 Organizing and Enhancing Worksheets

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lesson 13 organizing and enhancing worksheets

Lesson 13Organizing and Enhancing Worksheets

Computer Literacy BASICS: A Comprehensive Guide to IC3, 3rd Edition

Morrison / Wells


Objectives

Objectives

  • Hide and unhide columns and rows.

  • Freeze and unfreeze columns and rows.

  • Create, rename, and delete worksheets.

  • Merge cells and format cell contents.

  • Add borders and shading to worksheet cells and apply built-in cell styles and table Quick Styles.

  • Sort and filter data in a worksheet.

2

2


Lesson 13 organizing and enhancing worksheets

Objectives (continued)

  • Change the page setup of a worksheet and add headers and footers.

  • Customize the print options.

3

3


Vocabulary

cell style

filter

freeze

header row

sheet tab

split

table style

Vocabulary

4

4


Introduction

Introduction

  • A key feature of Microsoft Excel is the ease with which you can organize the data in a worksheet.

5

5


Classroom activities

CLASSROOM ACTIVITIES

  • What makes a printed spreadsheet look professional?

  • What are some examples of ways you can use formatting to emphasize data?


Managing worksheets

Hiding and Unhiding Worksheet Data:

A header row contains column headings or field names in a data source, such as a table or spreadsheet.

Two ways to prevent a header row from scrolling out of view include:

Changing the zoom setting

Hiding rows and columns

Managing Worksheets

7

7


Managing worksheets continued

Freezing Rows and Columns:

Another way to keep rows and columns visible is to freeze them. When you freeze columns and/or rows, you lock them so you can keep an area visible as you scroll through the worksheet.

You can also split the worksheet horizontally or vertically so the panes appear side by side or one above the other.

Managing Worksheets (continued)

8

8


Managing worksheets continued1

Working with Multiple Worksheets:

When a spreadsheet grows to a large size, you can organize related information in multiple worksheets.

When you need to add a new category to an Excel workbook, you click the Insert Worksheet button at the bottom of the screen, and a sheet tab appears.

Managing Worksheets (continued)

9

9


Classroom activities1

CLASSROOM ACTIVITIES

  • True or False? You cannot delete a worksheet from a workbook.

    • Answer: False. When you no longer need a worksheet, you can delete it from the workbook.

  • True or False? Excel automatically assigns a Sheet name to a new worksheet.

    • Answer: True.


Formatting cell contents

Formatting the contents of a cell changes the way it appears.

When you delete the contents of a cell using the Delete key or the Backspace key, the formats for the cell remain in the cell.

You can clear the contents and the formats from the cell, clear only the contents, or clear only the formats.

Formatting Cell Contents

11

11


Formatting cell contents continued

Merging Cells:

You can merge cells and combine several cells into a single cell.

You can use merged cells to create a title or other informational text.

The Merge & Center button automatically centers the text in the merged cells.

Formatting Cell Contents (continued)

12

12


Formatting cell contents continued1

Changing Font Styles and Sizes:

Fonts are available in a variety of styles and sizes, and you can use multiple fonts in one document.

The font size is a measurement in points that determines the height of the font.

Bold, italic, underline and color formats also add emphasis to cell contents.

When you move or copy data in a cell, the formats are also moved or copied.

Formatting Cell Contents (continued)

13

13


Formatting cell contents continued2

Changing Alignment and Wrapping Text in Cells:

By default, Excel aligns text at the left of the cell and numbers at the right side of the cell.

You can also center cell contents.

You can adjust vertical alignment to top, middle, and bottom.

Buttons for these and other settings are available in the Alignment group on the Home tab.

Formatting Cell Contents (continued)

14

14


Formatting cell contents continued3

Formatting Numbers and Dates:

Numbers are displayed with no formatting and are aligned at the right side of a cell.

Dates are automatically formatted in the default styles (such as 20-Jan-12).

Change the format of number data using the options from the Number group on the Home tab.

Formatting Cell Contents (continued)

15

15


Formatting cell contents continued4

Adding Shading and Borders:

Emphasize important information in a cell, a row of cells, or a column by applying color, shading, or border formats.

Use the Format Painter to copy the format of a worksheet cell without copying the contents of the cell.

Formatting Cell Contents (continued)

16

16


Formatting cell contents continued5

Applying Styles:

A cell style is a set of predefined formats you can apply to some of the worksheet data, such as a header row or a cell showing a total.

Formatting Cell Contents (continued)

17

17


Formatting cell contents continued6

Applying Styles (cont):

A table style is a set of predefined formats that you can apply to all worksheet data with a single click.

When you apply a table style, the selected cells are converted to an Excel table.

You can choose from more than 60 table styles in the Quick Styles gallery.

Formatting Cell Contents (continued)

18

18


Classroom activities2

CLASSROOM ACTIVITIES

  • To make text span several rows or columns, select the rows and/or columns and click the ____ button.

  • Answer: Merge and Center

  • True or False? When you move or copy all the data in a cell, the formats are also moved or copied.

  • Answer: True.


Classroom activities continued

CLASSROOM ACTIVITIES (continued)

  • Which button allows you to align text on an angle?

  • Answer: Orientation.

  • Which feature can you use to copy formats to other cells?

  • Answer: Format Painter.


Sorting and filtering data

To sort worksheet data, you must indicate the column on which you want to base the sort.

You can base the sort on data in as many as three different columns.

Sorting and Filtering Data

21

21


Sorting and filtering data continued

In addition to sorting data, you can filter worksheet data.

When you filter data, you screen data that matches specified criteria. The data that does not meet the criteria are hidden, and only the data that meets the criteria will show.

Sorting and Filtering Data (continued)

22

22


Classroom activities3

CLASSROOM ACTIVITIES

  • You can base a sort on how many different columns?

  • Answer: Three.

  • True or False? When you filter data, the data that does not meet the criteria is hidden.

  • Answer: True.


Formatting the page layout

Changing the Page Setup:

Page Break Preview view will show exactly how the worksheet will print.

Excel determines where to break a page and begin a new one.

You can create your own page break by dragging the page break to a new location.

Formatting the Page Layout

24

24


Formatting the page layout continued

Changing the Page Setup (cont):

Sometimes changing the page orientation will fit all the worksheet data on one page.

Another option for fitting the worksheet on one page is to use the Fit to command.

Formatting the Page Layout (continued)

25

25


Formatting the page layout continued1

Adding a Header and a Footer:

Headers are printed in the top margin of every page; footers are printed in the bottom margin.

You can choose from built-in headers or footers which are constructed using fields to insert dates, times, and page numbers automatically, or you can create headers and footers manually by inserting these fields yourself.

Formatting the Page Layout (continued)

26

26


Classroom activities4

CLASSROOM ACTIVITIES

  • 1)Which view shows exactly how the worksheet will print?

  • Answer: Page Break Preview.

  • 2)____ can be used to insert dates, times, filenames, the file path, and page numbers automatically in headers or footers.

  • Answer: Fields


Customizing print options

If you don’t want to print the entire worksheet, you can identify a range that you want to print before you choose the print command.

You can print the row and/or column headings on every page.

Change print settings on the Page Layout tab.

Customizing Print Options

28

28


Classroom activities5

CLASSROOM ACTIVITIES

  • True or False? Gridlines appear on the screen in a worksheet, but they cannot be printed.

    • Answer: False. You select the option to print gridlines in the Sheet Options group on the Page Layout tab.

  • True or False? When you create a print area, the print setting is saved with the workbook.

    • Answer: True.


Summary

Summary

In this lesson, you learned:

  • To keep the header row in view when navigating through a large worksheet, you can change the zoom setting. If that doesn’t work because the worksheet is too large, you can hide some of the rows and columns so you can focus on a particular range of data, or you can freeze some rows and columns.

  • You can delete or add one or several worksheets to a workbook, and you can rename each worksheet.

30

30


Summary continued

Summary (continued)

  • There are many options available for formatting cell contents, including merging cells, changing font styles and sizes, changing the alignment within the cells, and applying number and date formats.

  • To enhance the appearance and highlight data within the worksheet, you can add borders and shading to cells. There are also many predefined styles available that you can quickly apply to give the worksheet a professional look and make reading the data easier.

31

31


Summary continued1

Summary (continued)

  • To organize worksheet data numerically or alphabetically, you can sort the data based on a single column, or you can sort the data based on multiple criteria. To screen data that meets certain criteria, you can filter the data.

  • You can change the page orientation or use the Fit to feature to fit all the data on one page.

  • Headers and footers can be added to worksheets to provide information such as the source and date of the data.

32

32


Summary continued2

Summary (continued)

  • You can control the print output by inserting page breaks manually or creating a print area for the worksheet. With the default settings, the gridlines and row and column headings appear on screen in worksheets but they do not appear when the worksheets are printed. You can change the settings to hide these elements on the screen and/or include them in printed worksheets.

33

33


Summary continued3

Summary (continued)

  • Before you print, you can preview the worksheet to see what it will look like when it is printed. You can choose to print the active worksheet only, or you can choose to print all worksheets in the workbook.

34

34


Key terms

Key Terms

  • Cell style: A set of predefined formats you can apply to some of the worksheet data.

  • Filter: To screen data that matches specified criteria.

  • Freeze: Locking columns and/or rows so you can keep an area visible as you scroll through the worksheet.


Key terms continued

Key Terms (continued)

  • Header row: A row that contains column headings or field names in the data source.

  • Sheet tab: A tab at the bottom of the screen that provides quick and easy access to the worksheet.


Key terms continued1

Key Terms (continued)

  • Split: Divide the worksheet horizontally so the panes appear on the screen one above the other, or to divide the worksheet vertically so the two panes appear side by side on the screen.

  • Table style: A set of predefined formats that you can apply to all the worksheet data with a single click.


  • Login