Copy an Excel 2016 formula without changing the cell references

How do you copy an Excel 2016 formula without changing the cell references?- Have you copied a range of cells in Excel and wanted them to reference the same cells as before? Have you happily copied them, pasted them and realized that Excel dynamically updates the cell references? There are two ways around this. Either you lock the cell references with the dollar ($) sign or use a small Notepad hack which I will show you below.

Suppose you have a 4x2 table starting from cell A1 in a spreadsheet. You have another table starting from cell B8 that has the formula "=A1" and so on. Now you want to copy this range so that you can do some other calculations etc on the same values. You copy range B8:E9 and paste it on cell B11. You will now see that your old references are not preserved.

To solve this problem, just go through each cell's formula and press F4. Suppose you press F4 in cell B8's formula. The formula will change to "=$A$1". This means that the cell formulas reference will not change, even when you are copying, cutting, dragging or pasting the formula reference. Problem solved. But, what do you do when you have created a table that could be 100x100 cells? It is very time-consuming to change all these cell references.

You can then do another thing. First, go to the Formula tab and click on the "Show formulas" button. Your spreadsheet will now look like below. Now select your range, and copy it. Open Word, Notepad or any text editor and paste your content. You will see that you have pasted the formulas. Select all the text (Control + A) and go back to your spreadsheet. Paste the formulas anywhere on your spreadsheet and deactivate the "Show Formulas" button. Voila! You have just copied the formulas without having to add any dollar sign. Happy copypasting!