Excel can be a very fun beast if you learn how to use it properly. Formulas that use an array of data can very quickly be calculated with using the right type of cell reference, either a relative or absolute reference, which I hope to explain here how it works.

Relative Cell Reference

The relative cell reference is a reference that is relative to the cell you are using to create the formula in. For example, if I had a column of mass, and a column of acceleration and I was attempting to find all the forces for the collected data (F = ma), I would simply create the formula:

So, as I drag this formula down (which is accomplished by hovering over the bottom right corner of the highlighted cell) along all data collected, it show as

=A2*B2

=A3*B3

=A4*B4

…

=An*Bn

Absolute Cell Reference

Sometimes, however, a formula requires a single value for the entire row of calculations. A simple case would be just as above, but if acceleration was constant at gravity 9.8 m/s^2.

So, to reference this cell, we use the $ sign:

This can then be dragged and the D2 absolute reference will stay constant, and the A2 cell reference will be relative as you drag the formula.

=A2*D2

=A3*D2

=A4*D2

…

=An*D2

To this effect, you can also create an absolute column reference using $D2 for example, or an absolute row reference using D$2.

If this doesn’t completely explain this, a great resource is on the Microsoft Office Help site.