And in the Replace With option, insert ‘=’. (Or press Ctrl + H) This time, in the Find What option, insert ‘&’. Then select all cells of column E, copy them with Ctrl +C and then paste them in column F.You will get all cells in column E having ‘&’ like this. You will get the Find and Replace dialogue box.Go to Home>Find and Select Option from Excel Toolbar.Let us think that we want to copy column E, price with tax to column F, with keeping all the formula intact. This method is very handy when you want to copy formulas from one range of cells to another range of cells without changing the cell reference.
You will get the formula copied to all cells without incrementing cell reference D4.Then go to Home>Fill Option in Excel Toolbar under the Editing section.Select the cell with the formula having the Absolute Cell Reference and the rest of the cells where you want to copy down the formula.Method 2: Using Fill Option from Excel Toolbar Here I drag the Fill Handle from cell E4 to E13.Īnd get the formula copied to all the cells without incrementing D4. You have to copy the formula to the rest of the cells.ĭrag the Fill Handle (The Small Plus(+) Sign in the Bottom Right Corner) from the cell having formula with Absolute Cell Reference up to the cell in which you want to copy the formula. If it is not, you can press Ctrl + End on your keyboard to bring it to the end.Īfter entering the formula of the first cell with Absolute Cell Reference in the Formula Bar, In the Formula Bar, the mouse cursor remains at the end by default.If your formula contains more than one cell reference and you need to make all of them Absolute, press Ctrl + Shift + Home first.If you press F4 again, you will get D4.If you press F4 again, it will turn $D$4 into D$4.Put the cursor after D4 and press F4 on your keyboard.Double click in the Formula Bar or press F2 on your keyboard.For Excel version 2013 or higher, you can use your keyboard shortcut to create an Absolute Cell Reference.