• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Excel Quandaries

Get PDF file

by on
in Excel Training,Office Technology

Q. I keep getting endless pound signs (#) in my cells. What am I doing wrong?

A. This usually occurs when Excel thinks you’re trying to calculate something that results in negative days or negative time. It can be more a format problem than a calculation problem. If you are expecting a value representing the difference between two times or dates in a numeric value, all you have to do is clear the formats (Home, Editing, Clear, Clear Formats). If you want the result to be formatted as a time or a date, then you will need to re­­verse your calculation. In other words, it will need to result in a positive number.

Q. What is a circular cell reference and how can I prevent them?

A. A circular cell reference re­­sults when a formula refers back to the cell it is being typed into. For example, if you are typing the formula A1+B1+C1 into C1, you will get a circular cell reference. Unfortunately, it’s not always that straightforward. Sometimes, your formula is just A1+B1, but B1 contains a formula that refers to another formula that refers to C1. Don’t despair; there are some tools to help you. On the Formu­­las tab in the Formula Auditing group, you’ll find the Evaluate Formula button. You can evaluate each part of the formula. When it arrives at the part that is a circular reference, it will tell you this in the dialog box and italicize the value in the Evalu­­a­­tion box. Now, you can use Trace Precedents to find where the offending cell is located.

Leave a Comment