Microsoft Excel Questions & Answers

QUESTION: When I enter 123 in a cell, it appears in the cell as 1.23. What's going on?

 

ANSWER: You've got the "Fixed Decimal" option turned on. Go into Tools->Options->Edit and uncheck the Fixed Decimal option. This feature is intended to allow you to enter dollar-and-cents amounts without having to enter the decimal point.

QUESTION: How can I enter multiple lines of text in a cell?

 

ANSWER: Type the first line of text, press ALT+ENTER, and enter the next line.

QUESTION: How can I determine the number of hours and minutes between two times?

 

ANSWER: You can determine the number of hours and minutes between two times by subtracting the two times. However, since Excel cannot handle negative times, you must use an =IF statement to adjust the time accordingly. If your times were entered without a date (e.g. 22:30), the following statement will compute the interval between two times in A1 and B1;

=IF(A1>B1,B1+1-A1,B1-A1)

The "+1" in the formula causes Excel to treat B1 as if it were in the next day, so 02:30-22:00 will result in 4:30, four hours and thirty minutes, which is what we would expect. To covert this to a decimal number, for example, 4.5, indicating how many hours, multiply the result by 24 and format the cell as General or Decimal, as in …

=24*(IF(A1>B1,B1+1-A1,B1-A1))

QUESTION: How can I hide error values and error indicators in cells? For example, the formula =1/0 returns #DIV/0. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

 

ANSWER: You can format text in cells that contain errors so they don't show. First, select the cells that contain the error value, then …

  1. On the Format menu, click Conditional Formatting.
  2. In the box on the left, click Formula Is.
  3. In the box on the right, type =ISERROR(reference), where reference is a reference to the cell that contains the error value.
  4. Click Format, and then click the Font tab.
  5. Click Format.

  6. In the Color box, select white.

OR you can display NA or a dash in place of the error value. Use the IF, ISERROR, and NA functions to do this task.

  1. Select the cell that contain the error value.
  2. Wrap the following formula around the formula in the cell, where old_formula is the formula that was previously in the cell.

=IF(ISERROR(old_formula),"",old_formula)

EXAMPLES…
=IF(ISERROR(A2/A3),"NA",A2/A3) — Returns NA when the value is an error

=IF(ISERROR(A2/A3),"-",A2/A3) — Returns a dash when the value is an error

 

Back to Staff Development Page

Last Updated 10/25/2005