In addition to data management, Microsoft Excel performs various computations and mathematical operations to ease business operations.
Copying and pasting data from one cell to another is expected while using Microsoft Excel. Most individuals find it confusing and highly challenging to copy a value from Excel rather than formula and paste it into a cell.
When you copy data from a cell, the formatting of that cell, including the background color, formula, and font color, is also copied. However, you want to duplicate the values and not the formula.
Let’s examine it with a simple example:
As shown in below Excel sheet, there are four columns – Products in the C5 column, Quantity in D5, Price in E5, and Total Sale amount in F5—along with a formula for multiplying the D and E columns
The assignment is to replicate only the values from one table to the adjacent table, not its formulas.
Remember that if you copy an entire table or only the Total Sale column (F column) using the keyboard shortcut CTRL + C or the mouse (right-click and copy), both values and formulas will be pasted as shown in the Excel sheet below.
You will obtain the same outcomes in Excel if you copy and paste values using the same approach. Using this method, it is not possible to achieve the goal of solely taking values from an Excel column or sheet.
For the user who merely wants to replicate values, replicating formulas is annoying.
In the below section, we’ll look at a couple of techniques for copying values from a Microsoft Excel spreadsheet rather than formulas.
Using a Mouse to paste only values
We shall comprehend how we can only copy values with a mouse, not formulas.
Let’s utilize the above Excel to understand this operation:
In the example below (Picture 1), column F contains formulas. Choose the table as a whole or the column. When you wish to paste the data, right-click at the margin of the column while still holding the mouse right button and drag the mouse to the required spot.
Excel will prompt you with alternatives once you release the right mouse button, as seen in Picture 1 below. If you choose “Copy Here as Values Only,” only values will be copied, as shown in Picture 2.
It is one of the simplest ways to copy only values—not formulas—in a Microsoft Excel spreadsheet.
To use this technique, copy the column or table using your preferred method and place the cursor where you want to paste it. Then, as shown in Picture 1, pick the paste option from the paste special menu in the main menu bar.
Users can also access the paste special menu by right-clicking the mouse where they wish to paste it and selecting it, as seen in Picture 2.
You can see that only values have been inserted in Picture 3 below.
Using Paste Special keyboard shortcut – Ctrl+Alt+V
In the previous technique, we saw how to use the mouse and the special menu for pasting, but in this method, we’ll see how to utilize the keyboard’s keys to paste values, not formulas.
Let’s utilize the identical Excel sheet as in the procedures described previously.
When you paste a column, copy it, select where you want to put it, and hit the Ctrl+Alt+V keyboard shortcut.
You’ll see the paste special box displayed in the Excel sheet below. Only values will be pasted when you choose the “values” option from the list.
You have now successfully used keyboard shortcuts to copy only values and not formulas in a Microsoft Excel sheet.
Using Alt+e+s+v Keystrokes
This method also uses the keyboard keys, but it is a little more challenging than the method described above because it calls for the simultaneous usage of four keys. The combination of Alt+e+s+v keys.
After pressing all these buttons, you will see the paste special menu, as seen in the Excel spreadsheet below.
After selecting the Only Values option from the menu, only values will be inserted in the cell.
This method would be time-consuming and inconvenient if you wish to duplicate several columns in a massive Excel spreadsheet.
Final Words
We all regularly need to copy data from one section or column to another using Microsoft Excel spreadsheets. It will be challenging to complete this step if a cell has a formula. You can complete these tasks more rapidly if you use the preceding techniques.
Next, you can check out life-saver Excel tips and tricks.