Transcripts for the above video clip:

Dragging Boxes

In Excel we can click into a cell that has a formula in it. You can either click  beside the formula or push F2, and a little box will  appear around the cells that are relevant. What a lot of people don’t realize you can actually change the way a formula is looking by  working  with these coloured boxes. For example, here you can see the sum is for two cells only (G34 and 35) but  it should have summed all three(G34-36).  What you can do is hover over the box and then click and drag it down, you’ll see the formula changes. Click “Enter” and now correctly it includes all the cells in the sum. If you look at this cell (H35), you’ll see it what its doing is multiplying the opening balance, it was meant to multiply by cell C3 (the percentage) but accidently its looking at D3. You’ll see if I hover over the block,(just watch the D3 in the function bar) copy it across and that changes to C3 in the function bar  and it keeps the absolute or relative referencing. So now when I press “Enter” , you’ll see its now correct and go back and you’ll see its now looking at the right areas. The last example, you’ll see this cell here, you’ll see it looks at G37, so its looking at the closing balance two years ago instead of this one (H37), just hover, click on it, drag it across, you’ll see the reference has changed . Press “Enter”  and its now looking at the correct area.