Excel Lesson 11 Improving Data Accuracy

1. Excel Lesson 11Improving Data Accuracy Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Use advanced sorting. • Add subtotals to worksheet data. • Create a validation rule. • Enter data in a validation range. • Circle invalid data.

3. Objectives (continued) • Improve data entry accuracy using drop-down lists. • Convert text to columns. • Combine text. • Use a Watch Window.

4. Vocabulary • concatenation • data validation • delimiter • destination range • grand total • subtotal • validation range • validation rule • Watch Window 4 4

5. Introduction • Excel has several features that enable you to work efficiently and accurately with data. • Subtotal feature instantly adds a formula that totals the worksheet data to a cell. • Data Validation feature is used to set rules about how data can be entered. • Circle Invalid Data feature places circles around any invalid data. 5 5

6. Introduction (continued) • Concatenation: a process in which Excel combines data. • Convert Text to Columns feature separates data. • Watch Window feature enables you to view data from one worksheet while you enter or view data in another worksheet. 6 6

7. Advanced Sorting • Excel offers many advanced sorting options. • Can create custom sorting options • Can sort multiple columns of data at one time Sort dialog box

8. Advanced Sorting (continued) • Completed sort dialog box

9. Adding Subtotals • A subtotal is the total of only specific parts of the data. • The grand total is the total for all the data. • The Subtotal feature quickly calculates subtotals and grand totals. • These totals can be removed when you’re finished viewing or printing the data.

10. Adding Subtotals (continued) • Data after subtotals applied

11. Adding Subtotals (continued) • Options in the Subtotal dialog box

12. Creating a Data Validation Rule • The Data Validation feature ensures that data is entered as accurately as possible. • A validation ruleis used to set parameters for cells where values will be entered. • The validation range is the range of cells that you select to apply the validation rule.

13. Creating a Data Validation Rule (continued) • Completed Settings tab in Data Validation dialog box

14. Entering Data into a Validation Range • After you select a range and enter a validation rule for the validation range, the data entered into this range is monitored. • If the data entered is invalid, a message will appear to let you know what is wrong.

15. Entering Data into a Validation Range (continued) • Error message appears when incorrect data is entered

16. Circling Invalid Data • The Circle Invalid Data feature is used to have circles drawn around data that does not meet the data validation requirements. • The circles act as a visual reminder of which cells contain invalid data.

17. Circling Invalid Data (continued) • Invalid data appears with a circle

18. Adding a Drop-Down List for Selections • Drop-down lists display specific selections from which you can choose. • Can be used to further increase data accuracy • Very useful when only specific data can be entered into a cell • To create a drop-down list, it is easier if you first create a range of cells that has the selections that should appear in the list.

19. Adding a Drop-Down List for Selections (continued) • Store Aisle Location data validation list

20. Converting Text to Columns • The Convert Text to Columns is used to separate data in a worksheet. • Convert Text to Columns Wizard provides a step-by-step process to do this. • A delimiteris a character, such as a comma, space, or a tab, used to separate data. • The destination rangeis the location where the separated data will be placed.

21. Converting Text to Columns (continued) • Convert Text to Columns Wizard – Step 1 of 3

22. Combining Text • To join the values of cells together, you use the Concatenate function. • The Concatenate function combines the text from two or more cells into one cell. • You can also add other text, such as spaces, into the Concatenate function. • To add a space, enter “ “ into Concatenate function.

23. Combining Text (continued) • Completed Function Arguments dialog box

24. Adding a Watch Window • The Watch Window is used so that you are able to view specific data, no matter which worksheet you are using. Worksheet with Watch Window

25. Summary In this lesson, you learned: • You can use advanced sorting features to organize worksheet data. • Adding subtotals to worksheet data lets you instantly view totals in the worksheet. • Adding a validation rule to a range improves accuracy when data is entered.

26. Summary (continued) • When you enter data in a validation range, you can create input and error messages for the range. • You can choose to have the Data Validation feature place circles around invalid data. • You can improve data entry accuracy with drop-down lists.

27. Summary (continued) • Rather than retyping data, you can use the Convert Text to Columns feature to separate data from one cell into multiple cells. • Text from multiple cells can be combined into one cell using the Concatenate feature. • The Watch Window lets you view data from one worksheet while you work in other worksheets.