Microsoft Office Tutorials and References

In Depth Information

**8. Understanding Formulas**

8. Understanding Formulas

Excel
’
s forté is performing calculations. When you use Excel, you typic-

ally use a combination of cells with numbers and cells with formulas. After

you design a spreadsheet to calculate something, you can change the numbers

used in the assumption cells and then watch Excel instantly calculate new

results.

Getting the Most from This Chapter

Do not skip this entire chapter; one particular trick in this chapter can save

you daily frustration.

I regularly entertain accountants and auditors with my Power Excel program.

Although this program is a fun, laughter-filled tour through the inside tricks

of Microsoft Excel, people always learn new things along the way.

I call them
“
gasp moments.
”

Imagine this setting: I am in front of 200 managerial accountants who have Ex-

cel open for 40 hours each week. You can generally figure that these folks

are super-efficient with Excel. Any trick I show might already be in the ar-

senal of half to three-quarters of the room. A lot of the people nod their

heads, while others look surprised. However, a few tricks I show will get a

gasp from perhaps 90% of these managerial accountants because they didn
’
t

know these tricks before and now realize just how powerful they are. I thrive

on the gasp moments.

Most people reading this topic believe they know Excel formulas. To a certain

extent, this chapter is a primer for the person who is new to Excel. However,

even the most astute person using Excel should check out these sections of the

chapter:

handle to copy a formula. This leads to horrible frustration on long

data sets, as they go flying past the end of the data. This simple but

powerful trick is the one that universally amazes attendees of my

seminar.

•
Honestly answer this question: Do you really understand the differ-

ence between cell H1 and cell $H$1? If you think the latter has any-