Transcript Class 05 Excel CA202.ppt

CA202
Spreadsheet Application
Focusing on Specific
Data using Filters
Lecture # 5
1
Objectives
✔ Limit the data that appears on the screen
✔ Perform calculations on filtered data
✔ Define a valid set of values for a range of
cells
2
Limit the Data That Appears on
Screen
• You can limit the data shown in a worksheet by
creating a filter
• To create a filter, you click the cell in the group
you want to filter and use the Data menu to turn
on AutoFilter
3
Limit the Data That Appears on
Screen
• Choosing the Top 10, it opens the Top 10
AutoFilter dialog box. From within this dialog
box, you can choose whether to show values
from the top or bottom of the list,
4
Limit the Data That Appears on
Screen
• When you choose Custom from the AutoFilter
list, you can define a rule that Excel uses to
decide which rows to show after the filter is
applied.
5
Activity on Page 85
6
Data validation
• To ensure that the data
entered into your
worksheets is as accurate
as possible
• You can set up a
validation rule to make
sure the data entered into
a cell meets certain
standards.
• You can use the Data
Validation dialog box to
define the type of data
that Excel should allow in
the cell
7
Data Validation
• You can set the constraint
for data of a particular type
– Choose Data  Validation
 Setting Tab
– Set Constraints for whole
number, decimal, date or
time specify length
– Use Data field to further limit
the data fall in a particular
range
– Restrict the valid entries for a
cell to come from a List of
your own
– Choose List, and type in the
entries or select a range
8
Displaying an Input Message
• An input message can be
displayed whenever a
user selects or Click the
cell
• Use the message to help
the user in entering Data
9
Displaying an Input Message
• Choose Data  Validation 
Input Message Tab
– Message up to 255 Char
– Title appear in BOLD
– Message still appear when a
user select a restricted cell
– Turn off all input messages,
clear “Show Input message
When cell is Selected”
– Message remain displayed until
user move to the next cell
10
Display an Error Message
• Choose the Data 
Validation  Error Alert
Tab
• Turn off all error
messages, clear the box
“Show Error Alert After
Invalid Data is Entered”
11
Can’t remember which cells are
restricted
Edit  GOTO or F5
Choose Special
Turn on Data Validation down at the bottom
Choose All to find all cells on the worksheet that have
Data Validation applied
• Data Validation attributes can be copied and pasted
just like other cell formatting
• After Copying a cell choose Paste Special and Choose
Validation
•
•
•
•
12
Activity on Page 93
13
Chapter 5 Key Points
1.
2.
3.
4.
5.
6.
A number of filters are defined in Excel
Filtering an Excel worksheet based on values in a
single column
you can create a custom filter to limit your data
based on the values in more than one column as
well
you can get a running total just select the cells and
look on the status bar
You can use the controls in the Function box to edit
your functions
Use data validation techniques to improve the
accuracy of data entered into your worksheets
14