A Handy Hack for Excel

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.

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.

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.

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:

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:

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!

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:

   

   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!

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 – 

Absolute Column / Relative Row – 

Relative Column / Relative Row – 

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:

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.