Skills© Paradigm Publishing, Inc. 1 Skills© Paradigm Publishing, Inc. 1

Marquee Series Microsoft Excel 2016

Skills© Paradigm Publishing, Inc. 2 Skills© Paradigm Publishing, Inc. 2

SECTION 4 SKILLSWorking with Multiple Worksheets, Tables, and Other File Formats

4.1 Insert, Delete, and RenameWorksheets

4.2 Move and Copy Worksheets

4.2Group and Ungroup Worksheets

4.3Use 3-D References in Formulas

4.4Link Cells Between Worksheets

4.4 Print Multiple Worksheets

CHECKPOINT 1

4.5 Use Page Break Preview

4.6 Format Data as a Table

4.6 Apply Table Design Options

4.7 Sort a Table

4.8 Filter a Table

CHECKPOINT 2

4.9 Use Comments

4.10 Create a Workbook from aTemplate

4.11 Save in a Different File Format

4.11Create A PDF/XPS Copy of a Workbook

CHECKPOINT 3

Skills© Paradigm Publishing, Inc. 3

Insert, Delete, and Rename WorksheetsTo insert a worksheet:1. Click the New worksheet button to the right of the sheet

tab(s).

New worksheet button

Skills© Paradigm Publishing, Inc. 4

Insert, Delete, and Rename Worksheets…continuedTo insert a worksheet using the shortcut menu:1. Right-click a sheet tab.2. Click Insert at the worksheet

shortcut menu.3. Click OK.

worksheet shortcut menu

Skills© Paradigm Publishing, Inc. 5

Insert, Delete, and Rename Worksheets…continuedTo rename a worksheet:1. Right-click the sheet tab.2. Click Rename at the worksheet shortcut menu.3. Type the new sheet tab name.4. Press the Enter key.

renamed sheet tab

Skills© Paradigm Publishing, Inc. 6

Insert, Delete, and Rename Worksheets…continuedTo delete a worksheet:1. Right-click the sheet tab.2. Click Delete at the worksheet

shortcut menu.3. Click Delete if a confirmation

message box appears.

worksheet shortcut menu

Skills© Paradigm Publishing, Inc. 7

Move and Copy WorksheetsTo move a worksheet:1. Drag the worksheet tab to

new location.

To copy a worksheet:2. Press and hold down the

Ctrl key and then drag the worksheet tab to the new location.

copy icon

move icon

Skills© Paradigm Publishing, Inc. 8

Group and Ungroup WorksheetsTo group worksheets:1. Click the first sheet tab.2. Press and hold down Shift and

click the last sheet tab or hold down Ctrl and click the desired sheets.

To ungroup worksheets:3. Right-click any of the grouped

sheet tabs.4. Click Ungroup Sheets at the

worksheet shortcut menu.

worksheet shortcut menu

Skills© Paradigm Publishing, Inc. 9

Use 3-D References in FormulasTo create a formula with a 3-D reference:1. Make the cell active.2. Type =sum(.3. Click the first sheet tab.4. Press and hold down the

Shift key and then click the last sheet tab.

5. Click the cell containing the data to be summed in all sheets.

6. Press the Enter key.

3-D formula

Skills© Paradigm Publishing, Inc. 10

Link Cells Between WorksheetsTo link a worksheet:1. Make the destination cell active.2. Type =.3. Click the sheet tab for the source cell.4. Click the source cell.5. Press Enter.

link formula

Skills© Paradigm Publishing, Inc. 11

Print Multiple WorksheetsTo print multiple worksheets:1. Click the first sheet tab.2. Press and hold down the Shift key and then click the last

sheet tab.3. Click the File tab.4. Click Print.5. Click the Print button.

page navigation

Skills© Paradigm Publishing, Inc. 12

CHECKPOINT 11) A new workbook initially contains

how many sheets?a. 3b. 2c. 1d. 4

3) Use this key to group adjacent worksheets.a. Ctrlb. Shiftc. Altd. F1

2) To copy a worksheet, press and hold down this key when dragging.a. Ctrlb. Shiftc. Altd. F1

4) Do this to the worksheets to print more than one worksheet at once.a. formatb. zoomc. ungroupd. group

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer

Skills© Paradigm Publishing, Inc. 13

Use Page Break PreviewTo use Page Break Preview:1. Click the View tab.2. Click the Page Break

Preview button in the Workbook Views group.

OR3. Click the Page Break

Preview button on the Status bar.

Page Break Preview button

page break locations

Skills© Paradigm Publishing, Inc. 14

Use Page Break Preview…continuedTo adjust the position of the page break:1. Position the mouse pointer on

the blue dashed line between the columns until the pointer displays as a left- and right-pointing arrow.

2. Drag the mouse to the right or left of the desired column.

3. Release the mouse. Notice that the scaling percent automatically adjusts.

manual page break

scaling percent

Skills© Paradigm Publishing, Inc. 15

Use Page Break Preview…continuedTo insert or remove a page break in Normal view:1. Position the active cell in the row

below or the column to the right of where you want the page break to occur.

2. Click the Page Layout tab.3. Click the Breaks button in the

Page Setup group.4. Click Insert Page Break or

Remove Page Break at the drop-down list.

Breaks button

Skills© Paradigm Publishing, Inc. 16

Format Data as a TableTo format data as a table:1. Select the range.2. Click the Format as Table

button in the Styles group on the Home tab.

3. Click table style.4. Click OK at the Format As

Table dialog box.

Format as Table button

Format As Table dialog box

Skills© Paradigm Publishing, Inc. 17

Apply Table Design OptionsTo add a total row:1. Click the Table Tools Design tab.2. Click the Total Row check box in the Table Style Options group.3. If necessary, choose function in the numeric columns.

Total Row check box

Skills© Paradigm Publishing, Inc. 18

Sort a TableTo sort a table by a single column:1. Click in any row within the column

by which to sort.2. Click the Sort & Filter button in the

Editing group on the Home tab.3. Click option at the drop-down list.

Sort & Filter button

Skills© Paradigm Publishing, Inc. 19

Sort a Table…continuedTo sort a table by multiple columns:1. Click the Sort & Filter button

in the Editing group on the Home tab.

2. Click Custom Sort.3. Select the first column to

sort by in the Sort dialog box.4. Select sort order.5. Click the Add Level button.6. Repeat Steps 3-5 for each

sort column.7. Click OK.

Sort dialog box

Skills© Paradigm Publishing, Inc. 20

Sort a Table…continued

sorted worksheet

Skills© Paradigm Publishing, Inc. 21

Filter a TableTo filter a table:1. Click filter arrow button.2. Clear check boxes for items that you

do not want to view.3. Click OK.

filter arrow button

filter selection

Skills© Paradigm Publishing, Inc. 22

Filter a Table…continuedTo remove a filter:1. Click filter arrow

button.2. Click Clear Filter From

(column title).OR3. Click filter arrow

button.4. Click the Select All

option.5. Click OK.

Clear Filter From option

Select All option

Skills© Paradigm Publishing, Inc. 23

CHECKPOINT 21) Use this feature to view the

worksheet with page numbers behind the cells.a. Normalb. Page Break Preview c. Standardd. Page Layout

3) This feature is used to display only certain records within the table that meet specified criteria.a. sortb. filterc. groupd. ungroup

2) Use this feature to arrange cells in a specified order.a. sortb. filterc. groupd. ungroup

4) When you filter a table, the row numbers of the matching items that were found are displayed in this color.a. redb. greenc. blackd. blue

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer

Skills© Paradigm Publishing, Inc. 24

Use CommentsTo insert a comment:1. Make the cell active where you

want to insert the comment.2. Click the Review tab.3. Click the New Comment button

in the Comments group.4. Type the comment text in the

comment box.5. Click in the worksheet outside

the comment box.

New Comment button

comment box

Skills© Paradigm Publishing, Inc. 25

Use Comments…continuedTo open the comment box:1. Press Ctrl + Home to make cell A1 the active cell.2. Click the Review tab.3. Click the Next button in the Comments group.4. Continue clicking the Next button to scroll to the next

comment box.

Next button

Skills© Paradigm Publishing, Inc. 26

Use Comments…continuedTo delete a comment:1. Press Ctrl + Home to make A1 the active cell.2. Click the Review tab.3. Continue clicking the Next button to scroll to the comment box

to be deleted.4. Click the Delete button in the Comments group.

Delete button

Skills© Paradigm Publishing, Inc. 27

Use Comments…continuedTo print comments:1. Click the Review tab.2. Click the Show All Comments button in

the Comments group.3. Click the Page Layout tab.4. Click the Page Setup dialog box launcher.5. Click the Sheet tab at the Page Setup

dialog box.6. Click the down-pointing arrow to the

right of Comments.7. Click As displayed on sheet or At end of

sheet.8. Click OK.9. Print the worksheet.

comment options

Skills© Paradigm Publishing, Inc. 28

Create a Workbook from a TemplateTo create a workbook from a template:1. Click the File tab.2. Click New.3. Type search text in the

search text box.4. Click template.5. Click the Create button.6. Fill in the data or other

information as needed.

Sample templates

Skills© Paradigm Publishing, Inc. 29

Save in a Different File FormatTo save a workbook in Excel 97-2003 file format:1. Click the File tab.2. Click Export.3. Click Change File Type.4. Click Excel 97-2003

Workbook (*.xls).5. Click Save As button.6. Type a file name.7. Click Save.8. Click Continue at the

Compatibility Checker dialog box.

File option

Compatibility Checker dialog box

Skills© Paradigm Publishing, Inc. 30

Create a PDF/XPS CopyTo create a PDF copy of a worksheet:1. Click the File tab.2. Click Export.3. Click the Create PDF/XPS

button.4. Type a file name.5. Click the Publish button.

Publish button

Skills© Paradigm Publishing, Inc. 31

CHECKPOINT 31) Use these to provide instructions,

ask questions, or add other explanatory text to a cell.a. shapesb. clip artc. commentsd. styles

3) Click this option in the backstage area to display options for saving a worksheet in a different format.a. Shareb. Exportc. Optionsd. Info

2) This is the name for worksheets that are formatted and have text and formulas created for specific uses.a. templatesb. stylesc. themesd. comments

4) When a worksheet is saved in this format, the published file opens in an Adobe Reader window by default.a. XPSb. CSVc. XLSd. PDF

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer