Excel Week 2

 

Home
Windows Essentials
Word Week 1
Word Week 2
Word Week 3
Web with Word
Excel Week 1
Excel Week 2
Excel Week 3
Excel Week 4
Excel Week 5
Access Database files
Access Week 1
Access Week 2
Access Week 3
Access Week 4
Access Week 1 Class Exercise

FORMAT Þ CELLS

¨        Alignment Tab - you may align cells horizontally or vertically either left, center, right, justify or centered. You may use merge cells check box to merge the selected cells. You may use wrap text check box to wrap text to next line in the same cell.

¨        Fonts Tab – refer to both the size and the different types available in Excel. You may also underline text from this tab in one of 4 ways: single, double, single accounting, double accounting.

¨        Border tab - enables you to create a border around a cell or range 

¨        Patterns and Shading tabs - lets you choose a different color to shade the cell or range

Numeric Formats

General is the default format for numeric entries and displays the way it was entered.

Number displays a number without the thousands separator comma and with any number of decimal places

Currency displays a number with the 1000 separator comma and an optional dollar sign and negative values (in red or minus sign)

Accounting displays a number with the 1000 separator, optional dollar sign (leftmost aligned) negative values in () and zero values as hyphens

Date displays a date in various date formats (1 / 4)

Time displays the time in various time formats

Percentage is the format where the number is multiplied by 100 before is displayed with a % sign

Fraction displays a number as a fraction such as ¼ (=1/4)

Scientific displays a number as a decimal followed by the exponent of base 10

Text left aligns the entry; useful for numbers that are not used in calculations such as zip codes

Special displays a number with extra characters such as () around a phone number area code

Custom  allows you to develop your own formats

 Note that changing the format of a number does not change its value.

IF function

Returns one value if a condition you specify evaluates to TRUE and another if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

IF(logical_test,value_if_true,value_if_false)

¨       Logical_test is any value or expression that can be evaluated to TRUE or FALSE.

¨       Value_if_true is the value that is returned if logical_test is TRUE. If logical_test is TRUE and value_if_true is omitted, TRUE is returned. Value_if_true can be another formula.

¨       Value_if_false is the value that is returned if logical_test is FALSE. If logical_test is FALSE and Value_if_false is omitted, FALSE is returned. Value_if_false can be another formula.

Remarks

bullet

Up to seven IF functions can be nested as value_if_true and Value_if_false arguments to construct more elaborate tests. See the following last example.

bullet

When the value_if_true and Value_if_false arguments are evaluated, IF returns the value returned by those statements

bullet

 If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. If some of the value_if_true and Value_if_false arguments are action-taking functions, all of the actions are taken.

More functions

The NOW() function displays the current date and time and is continually updated throughout a session.

The TODAY() function is similar in concept except it displays only the date.

You can change the display format through Format Cells command, the Number Tab: the Date category.

Inserting a comment for a cell

I.                   Select the cell to be commented.

II.                Use the Insert Menu Þ Comment (or right click and select the Insert Comment command alternatively.

III.             Type your comment and click elsewhere in the worksheet.

Note:

Tools Menu Þ Options command Þ View Tab defines the way comments are displayed. You have 3 options:

¡      None

¡      Comment indicator only

¡      Comment & indicator

The comment indicator is the tiny red triangle at the top right corner of a cell that is commented.

Inserting a hyperlink into a cell

You can insert a hyperlink to a cell by using the Insert Menu, Hyperlink command. Just type in the web address and the displayed text in the text boxes provided. You can edit a hyperlink by right clicking the cell containing the hyperlink, and then selecting the Hyperlink command, Edit Hyperlink option.

Hiding and unhiding rows and columns

Excel enables you to hide a column from view while retaining the data in the workbook. Select the column(s) you wish to hide, click the right mouse button, and then select the Hide command from the shortcut menu. Reverse the process to unhide a column: i.e., right-click the header column to the left of the hidden column, then click the Unhide command. To unhide column A, however, click in the Name box and enter A1. Pull down the Format Menu, click column, and then click the Unhide command. Follow a similar process to hide and unhide a row.

Troubleshoot formulas and error values

If a formula cannot properly evaluate a result, Microsoft Excel will display an error value. For example, error values can be the result of using text where a formula expects a numeric value, deleting a cell that is referenced by a formula, or using a cell that is not wide enough to display the result.

Error values might not be caused by the formula itself. For example, if a formula shows #N/A or #VALUE! , a cell referenced by the formula may contain the error.

What does the error ##### mean?

§         The numeric value entered into a cell is too wide to display within the cell. You can resize the column by dragging the boundary between the column headings.

§         The formula in the cell produces a result that is too long to fit in the cell. You can increase the width of the column by dragging the boundary between the column headings or by changing the number format for the cell. To change the number format, click Cell on the Format menu, click the Number tab, and then select another format.

§         When you subtract dates and times, make sure you build the formula correctly. Dates and times in Microsoft Excel must be positive values. If the date or time formula produces a negative result, Microsoft Excel will display #### across the width of the cell. To display the value, click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.

What does the error #VALUE! mean?

The #VALUE! error value occurs when the wrong type of argument or operand is used, or if the Formula AutoCorrect feature cannot correct the formula.

What does the error #DIV/0! mean?

The #DIV/0! error value occurs when a formula divides by 0 (zero).

What does the error #NAME? mean?

The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a formula.

What does the error #N/A mean?

The #N/A error value occurs when a value is not available to a function or a formula. If certain cells on your worksheet will contain data that is not yet available, enter #N/A in those cells. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.

What does the error #REF! mean?

The #REF! error value occurs when a cell reference is not valid.

What does the error #NUM! mean?

The #NUM! error value occurs when a problem occurs with a number in a formula or function.

What does the error #NULL! mean?

The #NULL! error value occurs when you specify an intersection of two areas that do not intersect.