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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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
178
Replies
2
Views
308
Replies
10
Views
237
Replies
3
Views
144
Replies
2
Views
532

1,147,632
Messages
5,742,228
Members
423,714
Latest member
ftp2jz

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.

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