Excel and Range References

As an example, let's look at how we might summarise monthly sales data into quarters.

In the image below we've got a listing of monthly sales. We want to summarise that into quarters.

On the right I will create a formula to calculate the first quarter sales. See image below.

If I copy the formula in cell E2 down to E3 it calculates the next three month sales, which is not what we need. See image below.

The reference to cell B5 however is correct. That is the starting cell we need for the next quarter. What many people don't realise is you can edit a range and change the starting cell to the ending cell and when you press Enter Excel will correct the range reference automatically. See image below.

This means you can copy the formula down, change one number and fix the formula.

Excel is flexible, so keep an eye out to see if this technique can save you some time.