Microsoft Excel 2003 Illustrated Complete

1. Microsoft Excel 2003 Illustrated Complete Customizing Excel and Advanced WorksheetManagement

2. Objectives • Find files • Audit a worksheet • Outline a worksheet • Control worksheet calculations Customizing Excel and Advanced Worksheet Management Unit O

3. Objectives • Create custom AutoFill lists • Customize Excel • Add a comment to a cell • Create a template Customizing Excel and Advanced Worksheet Management Unit O

4. Finding Files • Use the Search task pane in Excel to find files • Search for a file by name or by specific text located in the file • Specify one or more criteria, or conditions that must be met, to find your file Customizing Excel and Advanced Worksheet Management Unit O

5. Finding Files (cont.) Advanced Search pane Customizing Excel and Advanced Worksheet Management Unit O

6. Finding Files (cont.) • Using file properties • Excel automatically tracks specific file properties, such as author name, file size, and file type • Enter additional file properties, such as descriptive title or subject • Enter file properties in the Properties dialog box Customizing Excel and Advanced Worksheet Management Unit O

7. Auditing a Worksheet • The Excel auditing feature helps you track errors and check worksheet logic • Because errors can occur at any stage of worksheet development, it’s important to include auditing as part of your workbook building process • Tracers point from cells that might have caused an error to the active cell containing the error Customizing Excel and Advanced Worksheet Management Unit O

8. Auditing a Worksheet (cont.) Divide-by-zero error message Cell causing the error Tracer arrows Customizing Excel and Advanced Worksheet Management Unit O

9. Auditing a Worksheet (cont.) • Watching and Evaluating Formulas • Show Watch Window button allows you to view changes to a cell’s value as its formula is calculated • Evaluate Formula button allows you to view the replacement of cell references in a formula with values as the formula is calculated Customizing Excel and Advanced Worksheet Management Unit O

10. Outlining a Worksheet • The Outline command displays a worksheet with buttons that allow you to adjust the worksheet display to show only critical rows and columns • For outlining to function with the default, worksheet formulas must be point consistently in the same direction • Summary rows must be located below related data and summary columns must be located to the right of related data Customizing Excel and Advanced Worksheet Management Unit O

11. Outlining a Worksheet (cont.) Column outline symbols Row outline symbols Customizing Excel and Advanced Worksheet Management Unit O

12. Controlling Worksheet Calculations • When you change a value in a cell, Excel automatically recalculates all the formulas in the worksheet based on that cell • This automatic recalculation is not efficient in large worksheets • Choose to selectively determine if and when you want excel to perform calculations automatically Customizing Excel and Advanced Worksheet Management Unit O

13. Controlling Worksheet Calculations (cont.) Changed value Indicates that the worksheet need to be recalculated Customizing Excel and Advanced Worksheet Management Unit O

14. Creating Custom AutoFill Lists • Create a custom AutoFill whenever you type a list of words regularly • Enter the first value in a blank cell and drag the AutoFill handle for Excel to enter the rest of the information for you Customizing Excel and Advanced Worksheet Management Unit O

15. Creating Custom AutoFill Lists (cont.) Existing AutoFill lists Customizing Excel and Advanced Worksheet Management Unit O

16. Customizing Excel • The thirteen tabs of the Options dialog box, allow you to customize Excel to suit your work habits Selected Options dialog box tabs Customizing Excel and Advanced Worksheet Management Unit O

17. Customizing Excel (cont.) General tab in the Options dialog box Customizing Excel and Advanced Worksheet Management Unit O

18. Adding a Comment to a Cell • If you plan to share a workbook, you should document, or make notes about, basic assumptions, complex formulas, or questionable data • Use cell comments to document a workbook • Cell comments move with the cells they are attached to Customizing Excel and Advanced Worksheet Management Unit O

19. Adding a Comment to a Cell (cont.) User name Sizing handle Type your comment here Customizing Excel and Advanced Worksheet Management Unit O

20. Adding a Comment to a Cell (cont.) • Editing, copying, and deleting comments • To edit an existing comment, select the cell to which the comment is attached, click Insert on the menu bar, then click Edit Comment • Right-click a cell with a comment and select Edit comment from the shortcut menu Customizing Excel and Advanced Worksheet Management Unit O

21. Creating a Template • A template is a workbook that contains text, formulas, macros, and formatting you use repeatedly • A workbook saved as a template provides a model for creating a new workbook • Excel provides several templates in the Templates section of the New Workbook task pane Customizing Excel and Advanced Worksheet Management Unit O

22. Creating a Template (cont.) Default file location for templates Extension of xlt is added Customizing Excel and Advanced Worksheet Management Unit O

23. Creating a Template (cont.) • Applying and editing templates Open a document based on a template: • Open the New Workbook task pane • Click “On my computer” under Templates • Click the General tab • Choose the template that you want to use • Click OK Customizing Excel and Advanced Worksheet Management Unit O

24. Summary • Use the Advanced and Basic File Search panes to find files • Use Excel’s Formula Auditing toolbar to find worksheet errors • Outline a worksheet to summarize data • Apply manual calculation in worksheets with many formulas Customizing Excel and Advanced Worksheet Management Unit O

25. Summary (cont.) • Create custom AutoFill lists for lists that are entered regularly • Use the Options dialog box to customize Excel • Document your worksheet using comments • Use templates for worksheets you use repeatedly Customizing Excel and Advanced Worksheet Management Unit O