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

Managing Multiple Opened Workbooks

Windows-> Arrange
To undo the arrangement, maximize button
Windows-> Hide
Windows-> Unhide

Entering a Formula That Referencing Data from Multiple Workbooks

• =[Workbook Name]Sheet Reference!Cell Range

• To create a formula in one workbook to reference cell B21 on the Summary worksheet of the Sales.xls workbook

=[Sales.xls]Summary!B21

• If the workbook name or sheet name contains one or more spaces, enclose the entire workbook name and sheet name reference in single quotation marks.

='[US Sales.xls]Summary'!B21

• If one of the workbooks is in different folder,

='C:\My Documents\Domestic Sales\[US Sales.xls]Summary'!B21

Creating and Saving a Custom Workbook Template

• Remove the values and text that will changev each time you create a workbook using your customized template
• Be careful not to delete the formulas
• replace variable data values with zeros
  
• File-> Save As
• Save As Type-> Template

Consolidating Data from Multiple Worksheets Using Consolidate Dialogue Box

Data-> Consolidate

Copying information across worksheets

To copy the values and formats from the January worksheet across the worksheet group

Edit-> Fill-> Across Worksheets

Consolidating data from multiple worksheets using a 3-D reference

• = Worksheet Range!Cell Range

= SUM(Sheet1:Sheet4!B21)

This formula adds the values in cell B21 on the worksheets between Sheet 1 and Sheet 4

• To sum the amount of tuition paid for a year

Create cell references to other worksheets

To insert the cell reference to the January worksheet in the February worksheet

Entering a formula that references another worksheet

• To develop a formula that references cell D10 in the Sales worksheet

=Sales!D10

The exclamation point separates the sheet reference (Sales) from the cell reference (D10).

• If a worksheet name contains one or more spaces, enclose the sheet name in single quotation mark.
  

= 'Sales Data'!D10

To enclose the sheet name in single quotation marks

• To create a formula that adds total sales from two worksheets

=Domestic!B10 + International!B8

Grouping worksheets

To select an adjacent group, press and hold the Shift key





To select a non adjacent group, press and hold the Ctrl key