How to use the coloured cells assistant to change formula in Excel by dragging them around.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 01:51
- - Skill Level: 4
- - What's my skill level
Coloured Cells Assistant
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. This is the coloured cells assistant.
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.