Thank God for copy/pasting, where would we be without it? Some of us like to Ctrl+C/Ctrl+V everything, including Excel formulas. There's just a little problem though – cell referencing changes when you copy cells with formulas, which can set things completely out of whack. This is how to copy cell values and formulas without changing the figures.
Using Paste Special
The easiest way by far to copy cell values only without the parent formula is to use the Paste Special option.
1. Copy the data range you want with Ctrl+C or Ctrl+X as the case might be. You can also highlight the range, right-click, and select Copy.
2. On the destination range, right-click and choose Paste Special.
3. Hover over the different options available to find out the one you need. In this case, the second group's first option is "Paste Values". Other options are Values and Formatting, Values and Source Formatting, etc.
4. Expand the cells to fit Data
Drag with absolute referencing
Dragging is a way to copy the same cell value in the case of absolute cell referencing. For example, if you have 15 in A1 and 20 in B1,
you can add then using =SUM($A$1+$B$1) then drag the formula to repeat the same formula in the cells below. Because of the absolute referencing, the values won't change.
To drag, hover the cursor over the bottom right corner of a cell until it changes into a thick cross, then drag it over the desired range. It also works between different worksheets and with more complex formulas. Double-clicking on the cross fills the column without having to drag, going as far as there is data on the left column.
Note that you can also use mixed referencing to vary column values but not row values, and vice versa. For example, $A1+$B1 will maintain the same column, but the row references will increment serially as you drag down.
Direct copy/paste
There is an easier option if you don't want to meddle with the cumbersome $ used in absolute referencing.
1. Click on the cell which contains the formula you want to copy.
2. To the formula bar and highlight the formula. Ctrl+C or Ctrl+X to copy or cut respectively.
3. Press the Esc button to exit from the formula bar, which prevents a change in reference.
4. Into your destination cell and Ctrl+V to paste the formula.
Copy/Paste from Sticky Notes or Notepad
One of the favorite sneaky ways to make sure you only copy values is to copy the cell formula or values and paste them onto Sticky Notes or Notepad first. Doing this strips the text of any formatting and dynamic referencing.
Copy the text again from Notes/Notepad, and then copy it into the destination cells. Unfortunately, it won't work with very large values. It works great to strip away cell formatting styles though.
Use Find and Replace
For large data ranges, Find and Replace is an ultra-helpful tool. To begin with, select the range of cells that you want to copy. Press Ctrl+H to bring up the Find and Replace dialog box.
On the Replace box, replace the = sign for a unique character, and apply. You can use any unique sign except an asterisk or /. For example, let's say you use the @ sign.
Close the Find and Replace dialog box. The formulas have now changed to text strings which you can Ctrl+C and Ctrl+V like any other text into the destination cells. Once done, select the destination cells and/or the original cells and Ctrl+H to bring up the Find and Replace dialog box. Reverse the process and replace the unique character with an equal sign to reconvert the text to formulas.
Copy/Paste without Formatting
Sometimes, you want to copy the text in a cell but not the formatting that comes with it. In this case, there are a couple of simple tricks you can use.
1. Copy the text/range of text in the cells you wish to get them from.
2. Select Paste Special option. To do this, right-click on the destination cell, go to paste special, and hover over the potential options to see which ones you want. Options include paste formula, paste formatting, paste value, paste pictures, etc.
Those are just some of the (easier) ways you can use to copy cell values while maintaining the values involved.