Achieve Innovative Process Improvement+Standardization by IT System with MS 365.

Excel Absolute and Relative Cell References and Autofill Function【Data Analysis Excel Skill】

Excel Absolute and Relative Cell References and Autofill Function【Data Analysis Excel Skill】
\ この記事を共有 /
Excel Absolute and Relative Cell Ref...

You can learn the differences in Absolute and Relative Cell References with case study examples. Mastering Cell References increases your work speed greatly.

(Duration: 6:49)

<< Related Posts >>

The ‘Double-Click’ Method of Excel Autofill Function

Hi, this is Mike Negami, Lean Sigma Black Belt.

Today’s agenda is the AutoFill function and how to refer to other cells. These two features are essential when you do data analyses with Excel. Let’s start with the first one, Autofill.

For example, in this worksheet, there is a purchase date column in Column A and formulas are contained in all the cells in Column D. If I had to type all of these manually, I would stop using Excel. Of course, this is not the case.  When you change or type the formula in the top cell, you would like to apply that downwards. If you select a cell, the lower right corner of the cell becomes a small square. Hover your cursor over it, then the cursor turns into a black cross.

Lower Right Corner Cell Square
Black Cross Cursor

As you drag it down, the first formula will be copied. However, if there are many rows, it takes a lot of time. Instead, you can double-click when you see the cross, then you can copy it to the bottom in a flash. I was impressed when I first did it!

When you select an entire column and hover your cursor over the top right corner, your cursor turns into a black cross. Dragging it to the right will copy everything including the column width.

Autofill with Entire Column

What’s Absolute and Relative Cell References?

But there is a problem. If you double-click a formula in Column D, you can see a blue color and that the formula is referring to a purchase date and calculates its fiscal quarter. However, in the copied Column E, the reference destination is shifted to the ‘Sales Channel’ in Column B and all the cells will be in error (See the image above).  Here, you need to understand absolute and relative cell references. The words themselves are not important, but please understand the differences in the two aspects of that concept.

If we look at the formulas we copied downward earlier, the referencing cells are shifted downward too. Since you want to show each quarter next to each date, this is just fine. This reference method is ‘relatively shifted’, so it’s called the ‘Relative Reference’.  However, there are times when you don’t want it to shift like the one we copied horizontally. In that case, we use ‘Absolute Reference’. It’s easy to do that, just enter a ‘$ (dollar)’ symbol.

Ony Column Absolute Cell Reference

In this example, since you want the reference to be fixed to Column A, meaning to make it absolute, put a ‘$ (dollar)’ symbol in front of A (See the image above). Then auto-fill the cell downward again and copy the column horizontally again. We don’t get any errors this time. Please be aware that the references are fixed horizontally, but they remain relative, vertically.

To summarize, when copying horizontally, the column letters would change. Instead, if you want them to be ‘absolute’, place the $ symbol in front of the letters. On the other hand, when copying vertically, the row numbers will change. If you want them to be ‘absolute’ instead, place the $ symbol in front of the numbers.

You can understand Absolute and Relative Cell References with this!

Make Dollar Yen Conversion Table

Let’s see the next example. I recently did a similar task at work. Let’s say this table is all displayed in dollar. Today, 1 Yen equals $0.0089 (Cell N32). What would you do if you were asked to make a table in Yen below? Let’s do it here.

Start working on the cell on the upper left. By dividing 700 by 0.0089, you’ll make it display the Yen amount (Cell N35).  If you just copy it down, the referenced cells will be shifted. We want the numerators in blue shifted, but the denominator in red kept in Row 32, so I’ll put a dollar symbol in front of the ’32’ (See the image below) and copy the cell down. It worked this time.

Only Row Absolute Cell Reference

Next, copy all the formulas on the left, over to the right, but that doesn’t display well. These are ‘Divided by 0’ errors because the denominators are referring to blank cells, which are zero and caused the errors.

Divided by 0 Error

Instead, when copying these columns to the right, we want the denominators to be kept in Column N, so put a dollar symbol in front of the N, too.  Overall, put two dollar symbols. By the way, instead of manually entering the dollar symbols here, you can easily change the position of the dollar symbol by pressing the F4 key for Windows and ‘command + T’ for Mac.

Cell-Reference Switch Shortcuts

I’m using Windows, so I’ll press my F4 key (See the image below). Pressing it once moves the $ symbol to the front of the N. Pressing it again makes it a relative reference, then I press it once again, then two $ symbols are set.

Execute Shortcut Example

Copy the cell and paste it to all the other cells, then the all cells’ denominators refer to the same cell. This is ‘absolute reference’. Finally, we completed the table in Japanese Yen.

Dollar Yen Conversion Table Complete

It took me a long time to fully understand the concept of these cell references. As I always say, please practice this method over and over again so that you will master it.

Copyright©Process Improvement & IT Consulting |,2023All Rights Reserved.