How to best drag across and down absolute references

JollyRoger01

New Member
Joined
Jun 1, 2021
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Excel Can Read to You
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
Joined
May 28, 2005
Messages
51,723
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 1, 2021
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
51,723
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Good luck with convincing your mother. :biggrin:
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top