Coloured Cells Assistant

How to use the coloured cells assistant to change formula in Excel by dragging them around

• 01:51
• Skill Level: 4
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

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.