CA202 Spreadsheet Application

1. CA202Spreadsheet Application Focusing on Specific Data using Filters Lecture # 5

2. 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

3. 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

4. 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,

5. 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.

6. Activity on Page 85

7. 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

8. 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

9. 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

10. 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

11. 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”

12. 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

13. Activity on Page 93

14. Chapter 5 Key Points • 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