Intro to Excel - Session 5.2 1

Tutorial 5 - Session 5.2

Working with Excel Lists

Intro to Excel - Session 5.2 2

Session 5.2 Objectives

• Learn how to filter a list• Use Conditional formatting• Add new fields using natural language

formulas• Use Page Break Preview

Intro to Excel - Session 5.2 3

Filtering a List

• Display records of interest

• Temporarily hide the rest

Intro to Excel - Session 5.2 4

Filtering using AutoFilters

• Select any cell in the list• Select Data Filter AutoFilter

• Select the list arrow in the column containing the data you to want filter

• Select the criteria you want to filter

Intro to Excel - Session 5.2 5

Custom AutoFilters

• Enables the specifying of relationships when filtering

• Select Custom in the list arrow

Intro to Excel - Session 5.2 6

Conditional Formatting

• Format that appears when the data meets conditions you specify

• Change:– font– font style and color– border

(continued)

Intro to Excel - Session 5.2 7

Conditional Formatting (continued)

• Select range of cells you want to format• Select Format Conditional Formatting...

Intro to Excel - Session 5.2 8

Natural Language Formulas

• Enables the building of formulas using column headers and row labels

Figure 5-24,page 5.27

Intro to Excel - Session 5.2 9

Accepting Labels in Formulas

• Select Tools Options

• Click theCalculation tab

• Select the AcceptLabels in Formulacheck box

Intro to Excel - Session 5.2 10

Inserting Subtotals

• Summarize data

• Inserts subtotal lines into the list

• A grand total line is added at the bottom

• Usually sort the list first to create groups

Intro to Excel - Session 5.2 11

Calculating Subtotals in a List

• Sort the list

• Select a cell inthe list

• SelectData Subtotals...

Intro to Excel - Session 5.2 12

Subtotals Outline View

Intro to Excel - Session 5.2 13

Outline Levels

• Level 3 (default) - most detail

• Level 2 - some details

• Level 1 - only the grand total

Intro to Excel - Session 5.2 14

Adding Page Breaks

• Force page breaks at logical places:– end of a group– before new data– etc.

Intro to Excel - Session 5.2 15

Inserting Page Breaks

• View the worksheet in Print Preview

• Push the Page Break Preview button

• Click the row number header where the page break should be

• Select Insert Page Break

Intro to Excel - Session 5.2 16

Removing Page Breaks

• Select Select All button• Select Insert Reset All Page Breaks

• SelectView Normal

Select Allbutton