In Excel, sometimes you may find it useful to use full column or row
references in a function if a range you are calculating changes frequently.
Typically, if you need to total values in the range of B1:B20, you would
enter a Sum function in B21. But what if you need to add to the number of
values in the range?

For example, suppose you need to keep a running total of
the number of hours worked, with hours added weekly. To keep the total current,
you would need to insert a new row for each week. Rather than having to insert
new values above the formula cell each week, you can enter =SUM(B:B),
which references the entire column, in a cell outside column B (such as E1). E1
will reflect the changes as you continue to add values to the end of column B.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.