Excel Formulas, Cell References, and Core Functions

1. Introduction

A formula turns cells into a repeatable calculation. Learning how references move when a formula is copied is essential: otherwise a workbook may look correct in the first row and silently calculate the wrong range below it.

2. Understand the concept

A relative reference such as B2 changes when copied. An absolute reference such as $B$2 stays fixed; mixed references lock only a row or column. Functions such as SUM, AVERAGE, COUNTIF, IF, and SUMIFS express common calculations without manually editing each record.

3. Syntax and interpretation

=B2*$F$1\n=IF(C2=\"Paid\",D2,0)\n=SUMIFS(Orders[Amount],Orders[Region],H2)

The first formula can be filled down while the tax-rate cell stays fixed. The structured SUMIFS formula adds amounts for rows meeting a region criterion.

4. Apply it to an analysis

Write formulas that calculate correctly when copied and use common functions for everyday analysis. Start with a small, known example and state what each row or value represents. After the transformation, check row counts, types, keys, and units against the original data. If an assumption fails, stop and investigate instead of presenting the output as a valid result.

5. Common mistakes

• Forgetting dollar signs on a fixed assumptions cell before filling a formula.
• Using a broad range that accidentally includes a subtotal or blank header.

6. Practice task

Quantity is in D2, unit price in E2, and tax rate in cell B1. Write a formula for tax-inclusive line total that can be filled down.

7. One possible solution

=D2*E2*(1+$B$1). The row references move; the tax-rate cell remains fixed.

8. Summary and next step

Write formulas that calculate correctly when copied and use common functions for everyday analysis. Use the next lesson in this module to extend the idea with another technique and a more realistic analysis case.