Reset the menus

Tools-> Customize-> Customize dialog box-> Options tab
Click " Reset menu and toolbar usage data"

Create or delete a custom menu

To create a custom menu:

Tools-> Customize-> Customize dialog box-> Commands tab
Categories list-> New Menu
Drag New Menu form the Commands list to the desired position on the menu bar



Right-click New Menu and then type name in the Name box






In the Commands list, click the command you want
Drag the command up to the menu bar and over the new menu


To delete a custom menu:

Tools-> Customize-> Customize dialog box
Right-click the custom menu-> Delete

Create a custom toolbar

Open customize dialog box

• Tools-> Customize
• Right click any toolbar-> Customize
• Toolbar Options button-> Add or Remove Buttons-> Customize

Click "New"




Click the "Commands" tab
In the commands list, click the command you want and drag it to the new toolbar

Display, hide, or reset toolbar buttons

At the right end of the toolbar, click the "Toolbar Options" button
Click the " Add or Remove Buttons"
Click "Reset Toolbar" to restore to the default settings

Specifying an error alert style and message

Data-> Validation-> Data Validation Dialog Box-> Error Alert Tab

Creating an input message

Data -> Validation -> Data Validation Dialog Box-> Input Message Tab

Specifying data types and acceptable values

Data -> Validation-> Data Validation Dialog Box -> Settings Tab

Validating data entry

Data validation enables you to define a set of rules that guide data entry for a specific range of cells.

Validation rule specifies

• Data types
• Acceptable values
• Input message
• Error-alert message

Editing a Hyperlink

Right-click the cell with hyperlink-> select Edit Hyperlink-> click the "Screen Tip" button-> Set hyperlink screen tip dialog box

Inserting a Hyperlink to a Workbook

Insert-> Hyperlink

Turning on the AutoRepublish Feature

This feature republishes the Web page automatically whenever the workbook is saved.

Click the " AutoRepublish every time this workbook is saved" check box

Click the " Open published web page in browser" to have Excel launch your Web browser automatically

Selecting Elements to Publish

After clicking publish button on Save As dialog box -> Publish as Web Page dialog box
Item to publish-> Choose

To specify a filename for the HTML file: click "Browse" button-> Publish As dialog box

Saving a Workbook as a Web Page

• File-> Save as Web Page OR File-> Save as-> Save as Type: Web Page
• Click the "Add interactivity " check box to create an interactive Web page. Leave the check box unselected to create a noninteractive page.
• To specify the page title: click the "Change Title" button
  

Previewing a Web Page

File-> Web Page Preview

Converting Excel Workbooks into Web Pages

Excel allows you to create two types of Web pages:

• Noninteractive Web page
• Interactive Web page: any changes users make on the Web page does not affect the original workbook, nor do changes last from one browser session to another
  

Merging Two Workbooks

Requirements must be met:

• The two workbooks must be copies of the same file, which must be a shared workbook.
• The two workbooks must have different filenames.
• The two workbooks must either have the same passwords or not be password-protected.
• The Track Changes feature must be turned on for both workbooks from the time the copy is first made.
• The tracking history must be kept from the time the copy is first made.

• Open the workbook into which you want to merge the files
• Tools-> Compare and Merge Workbooks

Accepting and Rejecting Changes to Cells

Tools-> Track Changes-> Accept or Reject Changes

Tracking Changes to Cells

To highlight the changes:

Tools-> Track Changes-> Highlight Changes


To show the contents of the tracking log:

• Tools-> Track Changes-> Highlight Changes
• Click the " List changes on a new sheet" check box
• A new worksheet " History" appear

Reviewing and Deleting Comments

• View-> Toolbars-> Reviewing-> Reviewing Toolbar
• Next Comment button

• Delete comment button

Resolving Conflcts

A Conflict occurs when users try to save different changes to the same cell.

The last user to save the document sees the Resolve Conflicts dialog box.

Rejected changes are stored in a tracking log to allow users to go back and retrieve wrongly rejected changes.

Creating a Shared Workbook

A shared workbook can be edited simultaneously by more than one user.

Tools-> Share Workbook
Click the "Allow changes by more than one user at the same time" check box

Printing the Comments

Page Setup dialog box-> Sheet tab -> Comments list arrow

Formating the Comment Box

• Double-click the selection border

Or

• Right-click the comment box and click Format Comment

Or

• Format-> Comment

Hiding and Redisplaying the Comment

• Right-click the cell and click Hide Comment
• Hover the pointer over the cell with small red triangle in the upper-right corner
  
• Right-click the cell and click Show/Hide Comments

Inserting a Comment

• Right-click the cell and then click Insert Comment

Or

• Insert-> Comment

Evaluating a Complex Formula

Evaluate part of a formula, which contains nested functions

Tools-> Formula Auditing-> Evaluate Formula
Click the Evaluate button

Using the Watch Window

You want to watch cells and their formulas as you make changes to precedent cells in other parts of a worksheet or on another worksheet.

Tools-> Formula Auditing-> Show Watch Window
Click Add Watch button

Locating Suspect Formulas

A suspect formula returns a value, but the wrong value.

To search for errors:

Click the Error Checking button on the Formula Auditing toolbar
The Error Checking dialog box opens



To correct the error:

Excel update the formula automatically
Click the Update Formula to Include Cells button

Tracing Errors

To locate the source of the error:

Click the Trace Error button on the Formula Auditing toolbar



Double-click the tracer arrow to open the Go To dialog box



Trace the red arrows back to the source of the error

Tracing Dependent Cells

Dependent cells are cells that use the value in the active cell in their formulas

Click the Trace Dependent button on the Formula Auditing toolbar



To determine the dependent cell, double-click the tracer arrow-> Go To dialog box



B14 is dependent on the value in cell B21 in the Balance Sheet worksheet

Tracing Precedent Cells

Precedent cells are the cells are referred to in the formula of the active cell

Click the Trace Precedents button on the Formula Auditing toolbar

Auditing Formulas

To display the formula auditing toolbar:

Tools-> Formula Auditing-> Show Formula Auditing Toolbar

Using Lookup Functions to Create Currency Conversion Woksheets

The minimum administrative fee is $4, which is shown in yellow by using IF function and conditional formatting.

Creating an Excel Workspace

A workspace is an Excel file that saves information about all of the currently opened workbook.

File-> Save Workspace

Using Lookup Function

VLOOKUP function is used for lookup tables in which the compare values are placed in the table's first column.

HOOKUP function is used when the compare values are placed in the table's first row.

Three pieces information needed to carry out the VLOOKUP funcion

• The entry you want it to look up
• The range of the lookup table
• The number of the column in the lookup table containing the value you want to look up

View the List of Linked Workbooks

Edit-> Links

Create the Summary Workbook

To link the LBCSum3 workbook to the LBC2006practice workbook

• E6 on the summary sheet in LBCSum3 workbook
• Switch to LBC2006practice workbook, double-click B8 in the annual worksheet
• Absolute reference-> mixed reference because fill the rest of the values using fill handle

To enter the remaining values

• Copy the formulas from column E and paste to the remaining columns
• Use Find and Replace feature to update workbook reference in each column

Creating the Workbook Reference

To replace the beginning cash balance value in the LBC 2006 workbook with a reference to the ending cash balance in the LBC 2005 workbook