Ultimate Guide to Cell References

Ultimate Guide to Cell References

Cell References

In storytelling, a static character maintains the same persona from the beginning of the novel until the end. A good example would be the ultra-intelligent, pipe-smoking crime detective Sherlock Holmes.

Conversely, a dynamic character is more malleable and transitions throughout the trials of the narration; think Edmund Dantes of the Count of Monte Cristo.

Dantes begins his journey as a poor aspiring ship captain who morphs into the Wealthy Count with a lust for justice after his imprisonment and betrayal of a trusted companion. If you hate books think Batman for dynamic (orphan to Bruce Wayne) and Homer Simpson for Static.

Excel Workbooks can be similar to Novels except the stories are told with data and formulas instead of character narration. Rather than static or dynamic like characters in a Novel, cell references of a workbook are described as absolute or relative.

Absolute and Relative Cell references behave similarly until their formulas are copied/dragged into adjacent cells. Let’s examine the syntax and how they behave.

Relative Cell Reference

In cell D2, Let’s enter the following formula

=B2 * C2

This is a relative cell reference – B2 * C2 calculates the factors and the value is shown in D2.

If the formula is dragged to cell D3 and further notice how D3 Calculates A3 * B3 and so on.

However, the Absolute Reference Works a little differently. Let’s try it out now with the same data set.

Remove the prior cell data from D2 to D4 and Replace D2 with this:

= $B$2 * C2

What you have just entered is an absolute reference for B2. The dollar signs are sometimes referred to as anchors and precede the indicator that they are locking in place. The $ before the A locks the reference onto column A while the $ before the two makes this cell always refer to row two. Together both dollar symbols create a locking reference and make the cell as absolute (static) as Homer Simpson.

Observe the absolute behavior of the cells as you drag the formula to D3 and further.

The above example is not an appropriate use of an absolute reference so let’s find a better fit.

When using absolute references?

One of the most common reasons to use absolute cell references is to keep a constant variable for our Formulas. Some examples would be a tax rate, conversion factor, or an overhead rate that you want to calculate.

Let’s assume some of the books get damaged, lost, or stolen from the warehouse and we want to calculate the amount of loss from these types of misfortunate events. We’ll assume the rate of loss is 3 % for this exercise.

Its poor practice to a hard key or retype your tax rate into every cell like this:

E2 * .03

A better way to include the tax rate would be to create a referencing cell. Let’s enter .03 into cell H2 and make it our referenced cell.

Now we can use an absolute reference in our formula and drag it down so it looks like this:

E2 * $H$2

E3 * $H$2

E4 * $H$2

Doesn’t that look better? I’m not sure what is a realistic amount of loss in a book warehouse but three percent seems reasonable for our example.  A restaurant manager would benefit from calculating a food waste rate similar to our example above. I’m sure that many times they will use a rate that’s much higher than our three percent (or they should). Just ask Jon Taffer from Bar Rescue!

Mixed Cell References

In terms of cell references, there isn’t a definitive line between using absolute and relative references! Sometimes you can use a mixed cell reference. In this case, you decide to lock the column only ($A1) or row only (A$1).

The best example of Mixed Cell References that I can provide is if you want to combine or multiply values in a table.

For example, there are two sets of values and you want to layout all of the possible combinations.

Data Set 1: Square, Triangle, Rectangle, Circle

Data Set 2: 1,2, 3, 4

Since we are trying to combine two values, we will use the CONCATENATE feature (or CONCAT for short).  The dollar sign precedes the letter A because you want to lock the column as you drag down vertically. As you drag horizontally to anchor the row the $ sign will be placed before the number.

**Remember the Rows in excel are numbers so to lock them place the $ in front of a number. To Anchor Columns use the $ in front of the corresponding column letter.

Here’s a table to help you write a formula to reference cell A1 using relative absolute and mixed cells.

Relative Reference A1
Absolute Reference $A$1
Mixed Reference- Lock Column Only $A1
Mixed Reference- Lock Row Only A$1

I hope this article provides you with a better understanding of Excel Cell References.

Here’s a key to help you remember Absolute vs Relative Cell References:

Absolute Relative
Static Dynamic
Stays the Same Regardless Ready for Change
$A$1 A1

***Bonus Tip***

I know the excitement is building as it’s time to run off and begin referencing cells with the tips learned today but it wouldn’t be an Excel shortcut without a bonus shortcut to take with you. The F4 Key can be used to cycle through different reference types. Just type a reference cell to get started.

Type A1

Click F4 once –  =A$1$

Click F4 twice- =A$1, Row locked only

Click F 3rd time- =$A1, Lock Column only

Click again to return to the Relative cell

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

Updated for Superbowl 57 This Version is unlocked, so be careful with the formulas. You have to press F9 to Regenerate the squares because calculations are set to manual. This spreadsheet is for educational purposes only. Please check your local gambling restrictions...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Planning Software

Fiverr Freelancers

Share This