# How to best drag across and down absolute references

#### JollyRoger01

##### New Member
I am trying to help my mum with some Excel formulas for a course she is doing and am trying to show her how to use absolute references to drag formulas properly. The problem is I have some references that need to stay the same horizontally and some vertically. If I make the vertical ones absolute, I can drag down. But then I need to manually change each of those references to relative to be able to drag each row across. I am not doing a good job of convincing her that this is more efficient than entering them manually. I know there is a better way of doing this and was wondering if someone can show me.

As an example, in the attached file I have used an absolute reference to D4 inside cell F4, and multiplied that by a percentage in F2. I can drag that formula horizontally all the way to Q4 and the formulas will fill fine. Now if I want to do the same thing on the rows below, I have to remove the absolute reference to D4 and apply an absolute reference to F2, then drag F4 down. Then I have to reverse these references to be able to drag them across, which is very time consuming.

Budget

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Peter_SSs

##### MrExcel MVP, Moderator
Absolute and relative addresses to not have to be "all or nothing". You can make the row absolute and the column relative or vice-versa.

Try this in F4 and you should be able to drag across and down.

Excel Formula:
``=\$D4*F\$2``
The \$D in the first reference keeps column D no matter which way you drag but allows the row to increase when you drag down.
The \$2 in the second reference keeps row 2 no matter which way you drag but allows the column to increment when you drag across.

#### JollyRoger01

##### New Member
Absolute and relative addresses to not have to be "all or nothing". You can make the row absolute and the column relative or vice-versa.

Try this in F4 and you should be able to drag across and down.

Excel Formula:
``=\$D4*F\$2``
The \$D in the first reference keeps column D no matter which way you drag but allows the row to increase when you drag down.
The \$2 in the second reference keeps row 2 no matter which way you drag but allows the column to increment when you drag across.
Perfect, thank you!

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Good luck with convincing your mother.

Replies
2
Views
250
Replies
3
Views
29
Replies
2
Views
532
Replies
10
Views
300
Replies
3
Views
165

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back