Chapter 2: Formula Tricks and Techniques

In This Chapter

• Getting an overview of Excel formulas

• Differentiating between absolute and relative references in formulas

• Understanding and using names

• Introducing array formulas

• Counting and summing cells

• Working with dates and times

• Creating megaformulas

About Formulas

Virtually every successful spreadsheet application uses formulas. In fact, constructing formulas can certainly be construed as a type of programming. This chapter covers some of the common (and not so common) types of Excel formulas.


For a much more comprehensive treatment of Excel formulas and functions, refer to my Excel 2013 Formulas (Wiley).

Formulas, of course, are what make a spreadsheet a spreadsheet. If it weren't for formulas, your worksheet would be just a static document — something that a word processor that has great support for tables could produce.

Excel has a huge assortment of built-in functions, has excellent support for names, and even supports array formulas (a special type of formula that can perform otherwise impossible calculations).

A formula entered into a cell can consist of any of the following elements:

• Operators such as + (for addition) and * (for multiplication)

• Cell references (including named cells and ranges)

• Numbers or text strings

• Worksheet functions (such as SUM or AVERAGE)

A formula can consist of up to 8,192 ...

Get Excel 2013 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.