How to copy formula as absolute

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,


When I copy a formula to another location, Excel automatically adjusts the cell references in that formula to the new location. Is there a way to copy the formula as absolute and copy it for an enitre column or for about 1000 rows without having to do it one by one?

Any help will be great. Thanks!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
It is not clear why you want that. it will give the same value in all the 100 cells.

anyhow you can name the formula
suppose some numbers are entered in A1 and B1

for example the formula is
=$B$1+$A$1


now click insert menu and click name-define
in define window
under "names in workbook" give a name e.g
formula1
now in reference window at the bottom in the same window type or copy

=$B$1+$A$1
click ok

suppose you want to copy this from J1 to J30 highlight these cells
type
formula1
hit control+shift+enter

is this what you want.