Creating Complex Formulas

In Excel complex formulas can include several operations. Use complex formulas in Excel to calculate more extensive data.

What is an absolute reference?

In earlier lessons, we saw how cell references in formulas automatically adjust to new locations when the formula is pasted into different cells. This is called a relative reference.

Relative Reference

Sometimes when you copy and paste a formula, you don't want one or more cell references to change. An absolute reference solves this problem. Absolute cell references in a formula always refer to the same cell or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference or the row reference, or both. Examples of absolute referencing include:

Absolute Reference Chart

To create an absolute reference:

Absolute Reference
Absolute Reference

When writing a formula, you can press the F4 key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference.