excel 2007 unit o

Microsoft Excel 2007 -Microsoft Excel 2007 -IllustratedIllustrated

Customizing Excel and Customizing Excel and Advanced Worksheet Advanced Worksheet

ManagementManagement

2Microsoft Office Excel 2007 - Illustrated

• Audit a worksheetAudit a worksheet• Control worksheet calculationsControl worksheet calculations• Group worksheet dataGroup worksheet data• Use cell commentsUse cell comments

ObjectivesObjectives


• Create custom AutoFill listsCreate custom AutoFill lists• Customize ExcelCustomize Excel• Customize Excel optionsCustomize Excel options• Create a templateCreate a template

ObjectivesObjectives


Auditing a WorksheetAuditing a Worksheet

• The Excel auditing feature helps you The Excel auditing feature helps you track errors and check worksheet track errors and check worksheet logiclogic• Because errors can occur at any stage Because errors can occur at any stage

of worksheet development, it’s of worksheet development, it’s important to include auditing as part of important to include auditing as part of your workbook building processyour workbook building process


Auditing a Worksheet (cont.)Auditing a Worksheet (cont.)

Divide-by-zero error message

Tracer arrows


Auditing a Worksheet (cont.)Auditing a Worksheet (cont.)

• Click the Formulas tab, click the Watch Click the Formulas tab, click the Watch Window buttonWindow button

• In the Formula Auditing group, click In the Formula Auditing group, click Add Watch in the Watch Window, then Add Watch in the Watch Window, then click Add. click Add.

• The Watch Window displays the The Watch Window displays the workbook name, worksheet name, the workbook name, worksheet name, the cell address you want to watch, the cell address you want to watch, the current cell value, and its formula.current cell value, and its formula.


Controlling Worksheet Controlling Worksheet CalculationsCalculations

• When you change a value in a cell, When you change a value in a cell, Excel automatically recalculates all Excel automatically recalculates all the formulas in the worksheet based the formulas in the worksheet based on that cellon that cell• This automatic recalculation is not This automatic recalculation is not

efficient in large worksheetsefficient in large worksheets• Choose to selectively determine if and Choose to selectively determine if and

when you want excel to perform when you want excel to perform calculations automaticallycalculations automatically


Controlling Worksheet Controlling Worksheet Calculations (cont.)Calculations (cont.)

Select manual


Grouping Worksheet DataGrouping Worksheet Data

• You can create groups of rows and You can create groups of rows and columns on a worksheet to manage your columns on a worksheet to manage your data and make it easier to work with. data and make it easier to work with.

• The Excel grouping feature provides an The Excel grouping feature provides an outline that allows you to easily expand outline that allows you to easily expand and collapse groups as you need to show and collapse groups as you need to show or hide related worksheet data. or hide related worksheet data.

• You can turn off the outline symbols if you You can turn off the outline symbols if you are using the condensed data in a report.are using the condensed data in a report.

Grouping Worksheet Data Grouping Worksheet Data (cont.)(cont.)


Outline symbols


Adding a Comment to a CellAdding a Comment to a Cell

• If you plan to share a workbook, you If you plan to share a workbook, you should should documentdocument, or make notes , or make notes about, basic assumptions, complex about, basic assumptions, complex formulas, or questionable dataformulas, or questionable data• Use Use cell commentscell comments to document a to document a

workbookworkbook


Adding a Comment to a Cell Adding a Comment to a Cell (cont.)(cont.)

Sizing handle

User name

Type your comment

here


Creating Custom AutoFill ListsCreating Custom AutoFill Lists

• Create a custom AutoFill whenever Create a custom AutoFill whenever you type a list of words regularlyyou type a list of words regularly• Enter the first value in a blank cell and Enter the first value in a blank cell and

drag the AutoFill handle for Excel to drag the AutoFill handle for Excel to enter the rest of the information for youenter the rest of the information for you


Creating Custom AutoFill Lists Creating Custom AutoFill Lists (cont.)(cont.)

Existing AutoFill lists


Customizing Excel WorkbooksCustomizing Excel Workbooks

• Default settings don’t always fit your particular Default settings don’t always fit your particular needsneeds

• Examples: default number of worksheets in a Examples: default number of worksheets in a workbook, the worksheet view, or the default font.workbook, the worksheet view, or the default font.

• Popular category of the Excel Options dialog box Popular category of the Excel Options dialog box contains features that are commonly used by a contains features that are commonly used by a large number of Excel users. large number of Excel users.

• Use the Popular category to create custom listsUse the Popular category to create custom lists• Use the Formulas category to switch to manual Use the Formulas category to switch to manual

calculationcalculation


Customizing Excel Workbooks Customizing Excel Workbooks (cont.) (cont.)

Standard font

defaults

Number of sheets in a workbook

User name for the

computer


Customizing Excel OptionsCustomizing Excel Options

• The Customize category of the Excel Options The Customize category of the Excel Options dialog box allows you to add commands to the dialog box allows you to add commands to the Quick Access toolbar. Quick Access toolbar.

• Useful for commands that you use frequently and Useful for commands that you use frequently and would like to have available with one click.would like to have available with one click.

• Examples of commands that you might add are Examples of commands that you might add are Quick Print, Spelling, Open, and Print Preview. Quick Print, Spelling, Open, and Print Preview.

• You can add the commands to any open You can add the commands to any open workbook or all workbooks.workbook or all workbooks.

Customizing Excel Options Customizing Excel Options (cont.) (cont.)


Click to select

workbook

Default commands


Creating a TemplateCreating a Template

• A A templatetemplate is a workbook that is a workbook that contains text, formulas, macros, and contains text, formulas, macros, and formatting you use repeatedlyformatting you use repeatedly• A workbook saved as a template A workbook saved as a template

provides a model for creating a new provides a model for creating a new workbookworkbook


Creating a Template (cont.)Creating a Template (cont.)

Template


Creating a Template (cont.)Creating a Template (cont.)

Applying templatesApplying templates• Use the New command on the Office Use the New command on the Office

menu. menu. • In the New Workbook dialog box, under In the New Workbook dialog box, under

Templates click My templates. Templates click My templates. • Click the template you want to use, then Click the template you want to use, then

click OK. click OK. • Excel creates a new document named Excel creates a new document named

[Template Name]1.[Template Name]1.


SummarySummary

• Audit worksheets to find errorsAudit worksheets to find errors• Control worksheet calculationsControl worksheet calculations• Group worksheet dataGroup worksheet data• Use cell commentsUse cell comments


Summary (cont.)Summary (cont.)

• Create custom AutoFill lists for data Create custom AutoFill lists for data that are entered regularlythat are entered regularly

• Customize Excel workbooksCustomize Excel workbooks• Customize Excel optionsCustomize Excel options• Use templates for worksheets you Use templates for worksheets you

use repeatedlyuse repeatedly

excel 2007 unit o

Technology