Microsoft Office Tutorials and References
In Depth Information
that the source cells (that is, the cells being referred to) use, and applies that to the
cell with the formula. If you add two numbers and you’ve formatted both with the
Currency number format, then your result also has the Currency number format.
Of course, you’re always free to change the formatting of the cell after you’ve entered
Usually, Excel’s automatic formatting is quite handy. Like all automatic features,
however, it’s a little annoying if you don’t understand how it works when it springs
into action. Here are a few points to consider:
• Excel copies only the number format to the formula cell. It ignores other details,
like fonts, fill colors, alignment, and so on.
• If your formula uses more than one cell reference, and the different cells use
different number formats, Excel uses its own rules of precedence to decide which
number format to use. For example, if you add a cell that uses the Currency
number format with one that uses the Scientific number format, then the
destination cell has the Scientific number format. Sadly, these rules aren’t spelled out
anywhere, so if you don’t see the result you want, it’s best to just set your own
• If you change the formatting of the source cells after you enter the formula, it
won’t have any effect on the formula cell.
• Excel copies source cell formatting only if the cell that contains the formula
uses the General number format (which is the format that all cells begin with).
If you apply another number format to the cell before you enter the formula,
then Excel doesn’t copy any formatting from the source cells. Similarly, if you
change a formula to refer to new source cells, then Excel doesn’t copy the format
information from the new source cells.
A good deal of Excel’s popularity is due to the collection of functions it provides.
Functions are built-in, specialized algorithms that you can incorporate into your
own formulas to perform powerful calculations. Functions work like miniature
computer programs—you supply the data, and the function performs a calculation
and gives you the result.
In some cases, functions just simplify calculations that you could probably perform
on your own. For example, most people know how to calculate the average of several
values, but when you’re feeling a bit lazy, Excel’s built-in AVERAGE() function
automatically gives you the average of any cell range. Even more usefully, Excel functions
perform feats that you probably wouldn’t have a hope of coding on your own,
including complex mathematical and statistical calculations that predict trends —hid-
den relationships in your data that you can use to make guesses or predict the future.
Every function provides a slightly different service. For example, one of Excel’s
statistical functions is named COMBIN(). It’s a specialized tool used by probability
mathematicians to calculate the number of ways a set of items can be combined.