Microsoft Excel 2003 Illustrated Complete Customizing Excel and

Microsoft Excel 2003 Illustrated Complete Customizing Excel and Advanced Worksheet Management

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

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

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 4

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

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 6

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 7

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

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 9

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 10

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

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 12

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

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

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

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 16

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

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 18

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

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 20

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 21

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

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

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 24

Summary (cont. ) Create custom Auto. Fill 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 25