How to best drag across and down absolute references

[JollyRoger01]

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

 

[Peter_SSs]

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.

=$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]

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.

=$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]

You're welcome. Good luck with convincing your mother.