A Handy Hack for Excel

formula hacks, excel, numbers, calculations, easy, simple, know these tips
Make Excel formulas child’s play!

Using Microsoft Excel is something that people either LOVE or HATE.  And the only difference between those two kinds of people is whether you understand it or not.

Let’s pretend you’re the owner of SellingCoolStuff Pty Ltd and you have four salespeople who work for you: Bob, Jane, Mary and Fred, and you want to use Excel to track the total sales that each of them has made in the previous week.

excel hacks, formula, formula tricks, formula tips
Calculate sales for the week.

In the above image, I simply clicked the AutoSum function to total the figures in Column A. To get the totals for Jane, Mary and Fred, you can then copy this formula to Column B and C.

This is called Relative Cell Referencing (ooooooohhh, fancy!). It’s Excel’s default cell referencing, which it does without you having to modify the formula.

Let’s see what happens when you click and drag the sum from Column A over to Column E.

excel hacks, excel calculations, copy, paste, fill handle, easy
Complete the total sales formula by copying to the other columns.

 

excel hacks, excel calculations, excel formulas, excel made easy, relative referencing
See how Excel change the cell references in the formula to match the location of the formula.

Sweet! See how when the formula was copied from Column A to Column B, the formula now shows the total for Jane’s week, instead of Bob’s?  It also shows the correct cell references for the position of the formula. i.e. “B” changes to “C”, then “D” and finally “E”.

Now let’s consider another scenario. Let’s assume that we want to calculate the commission for each sales rep in the example below.

excel hacks, excel formulas, excel calculations, excel relative referencing, absolute referencing
Get ready to calculate the commission for each sales rep.

Each sales rep will receive 25% of their sales figure for the week as a commission (let’s keep it simple). So, the formula for calculating Bob’s commission will be B8*E1:

excel hacks, formula, formula tricks, formula tips
Create the commission formula for the first sales rep.

Straight forward so far. But is it?

Do the same thing as with the sums and copy the formula across to Fred’s column. Notice what happens:

excel hacks, formula, formula tricks, formula tips, relative cell referencing
Oops, not what we were expecting – what happened?

Huh?  What happen there? Excel has applied Relative Cell Referencing to the formula as it has been copied.  Such that Jane’s sales figure is being multiplied by an empty cell (F1) which is the same outcome for Mary (G1) and Fred (H1).  No wonder, their commission was zero.  So how do we fix that WITHOUT typing the 25% figure into the formula?  Easy!

excel hacks, formula, formula tricks, formula tips
The commission rate has moved one cell to the right each time the formula was copied.

The Fix?

  1. To fix this we need apply Absolute Cell Referencing:
  2. Return to the original formula and examine the cells referenced in the formula.
  3. In Bob’s example, we have B8 and E1.
  4. Ask yourself – which cell or cells needs to remain the same after the formula has been copied?
  5. Your answer should be E1.
  6. Click on the formula bar and place your cursor between the E and the 1.
  7. Press the F4 key on the keyboard and $ signs will appear in front of the E and 1, as shown:
    excel hacks, formula, formula tricks, formula tips, relative cell referencing, absolute cell referencing
    Apply Absolute Cell Referencing to stop the commission rate changing as the formula is copied.

    In this case, the $ signs do not mean currency. Instead, they mean, keep this cell reference no matter what.

  8. Lock in the formula by pressing the Enter key.
  9. Then copy the formula again and notice the difference!
excel hacks, formula, formula tricks, formula tips, relative cell referencing, absolute cell referencing, f4
Copy the new formula to calculate the correct commission for all sales reps.

 

excel hacks, formula, formula tricks, formula tips, absolute cell referencing, relative cell referencing, f4
Each time the formula is copied the Absolute Cell Reference does not change.

Other fun facts

There are other variations of Absolute Cell Referencing, which can be found by continuing to press the F4 key.

Relative Column / Absolute Row  Handy Hack for Excel Image 9

Absolute Column / Relative Row – excel hacks, formula, formula tricks, formula tips

Relative Column / Relative Row – excel hacks, formula, formula tricks, formula tips

Which can all be used for various purposes that we can cover another time. Great job for today!!

Quiz

Before I go, I will leave you with a brain teaser! See if you can work out how to complete this puzzle:

excel hacks, formula, formula tricks, formula tips, absolute cell referencing, relative cell referencing, f4
12-times table anyone?

See if you can use your new Cell Referencing knowledge to work out the required formula.

Rules:

  • Finish the 12-Times Table.
  • Using only one formula placed into cell B3.
  • Copy that formula down and then across or across and then down to complete the table.
  • Remember – ONE FORMULA ONLY!

I will share the answer in the next Excel article.

Leave a Reply