How to Copy Values in Excel [Not the Formula]

If you want to copy only the sum of an equation to another cell, using the regular copy and paste option, the pasted value will include the formula.

If you want to copy only the value of a cell, then this is the right article for you. Here, you’ll learn how to copy cell values without formulas, copy cell formatting, and about other handy features.

How to Copy and Paste Values Without Formula in Excel?

Whether you want to copy numbers or letters without a formula, there are two ways to do it. To copy a cell’s value without the formula, do the following:

1. Select the cell with the value you want to copy.
   
2. Right-click on the selected cell and click “Copy.” (You can also use Ctrl + C for this step.)
   
3. Select the cell on your spreadsheet where you want to paste the value.
   
4. Go to the “Home” tab on the Ribbon.
   
5. In the “Clipboard” tab, click on the “Paste” button with a small arrow.
   
6. In the drop-down menu, under the section “Paste values,” click on the first option in the row (“Values”).
   

Note: You can select and copy multiple cells using this method.

Additionally, there is a simpler way to do this:

1. Select the cell(s) with the value you want to copy.
   
2. Right-click on the selected cell or range of cells and click “Copy.”
   
3. Right-click on the cell where you want to paste the value(s).
   
4. Place your cursor on the small arrow right next to the “Paste Special” option.
   
5. In the extended menu, under the section “Paste values”, select the first option in the row (“Values”).
   

How to Copy and Paste Conditional Formatting?

Like in the previous example, you’ll need to use the “Paste Special” option. To copy and paste cells that have conditional formatting, follow these steps:

1. Select the range of cells with the conditional formatting you want to copy.
   
2. Right-click on the selected range and click “Copy” (or use Ctrl + C for this step).
   
3. Select the range of cells where you want to paste the conditional formatting.
4. Click on the “Paste Special” option.
   
5. Under the “Paste” section in the “Paste Special” dialog box, check “Formats.”
   
6. Click “OK.”
   

Another method to copy and paste conditional formatting is to use the “Format Painter” option:

1. Select the range of cells with conditional formatting.
   
2. Go to the “Home” tab on the Ribbon.
   
3. In the “Clipboard” section, click on the “Format Painter” button.
   
4. Drag the cursor over the range of cells where you want to paste the conditional formatting.

Note: The cells where you paste conditional formatting do not have to contain values. You can copy the conditional formatting to blank cells as well.

In addition, you can paste conditional formatting multiple times. In Step 3, double-click the “Format Painter” button. Once you finish pasting conditional formatting, click the button again to turn off the paste function.

Additional FAQs

How to Show Formulas in Excel Instead of the Values?

On occasion, you may want to see the formula behind certain values. To view formulas applied to cells, you simply have to:

1. Go to the “Formulas” tab on the Ribbon.

2. In the “Formula Auditing” section, click on the “Show Formulas” button.

In the cells that contain formulas, you can now see formulas instead of values.

Does Microsoft Excel Make an Exact Copy of a Formula?

Yes, Excel allows you to copy a formula to a different cell without changing cell references.

1. Double-click the cell with the formula you want to copy. The cell is now in edit mode.

2. In the formula bar above the spreadsheet, highlight the formula and press Ctrl + C (copy).

3. Select the cell to which you want to apply the formula and press Ctrl + V (paste).

Note: If you double-click on a cell and the cursor does not appear in the cell, you have to enable Edit mode. Go to File>Options>Advanced and in the “Editing options” section check “Allow editing directly in cells.”

There is a shortcut for copying the formula of one cell to multiple cells. However, this is only applicable when the cells are adjacent to each other:

1. Place the cursor on the bottom-right corner of the cell so that it appears as a black cross.

2. Click and drag the cursor over the adjacent cells to which you want to copy the formula.

3. Release the cursor when you’ve highlighted the cells.

Now the formula is applied to a group of cells.

How Do You Replace Values in Excel?

You can do this by using the “Find and Replace” feature to replace both letters and numbers. This process is very simple.

1. Select the range of cells in which you want to change values.

2. Go to the “Home” tab on the Ribbon.

3. In the “Editing” section, click on the “Find & Select” button.

4. Click “Find” in the drop-down menu.

5. In the “Find and Replace” dialog box, select the “Replace” tab.

6. Enter the value that you want Excel to find in the “Find what” text box.

7. In the “Replace with” text box, enter the replacement value.

Note: You can replace steps 1-3 with the keyboard shortcut Ctrl + H.

Now, there are two things you can do. To replace the value in only one cell, do the following:

1. Click the “Find Next” button. This will select the first cell in the section that contains the value you want to change.

2. Click the “Replace” button to replace the value of that cell with the new value.

Should you want to replace all values within the selected range of cells:

1. Click on the “Find All” button. This will select all the cells that have the value you want to replace.

2. Click “Replace All” to substitute all the old values with the new ones.

Note: You can skip Step 1. if you don’t want to identify the cells that need value replacement.

How Do You Copy Text in Excel With Formulas?

Copying text with formulas requires you to do the basic copy and paste procedure:

1. Select the cell with the text and formula which you want to copy.

2. Press Ctrl + C.

3. Select the cell where you want to paste the text and formula, and then press Ctrl + V.

Why Is Excel Copying the Value but Not the Formula?

For some reason, your Excel is set to Manual recalculation. You have to revert this to Automatic mode:

1. Go to the “Formulas” tab in the Ribbon.

2. In the “Calculations” section, click the “Calculation Options” button.

3. Click on “Automatic.”

How Do You Copy a Value and Format in Excel?

You can use the “Paste Special” feature to achieve this:

1. Select the cell or a range of cells containing the value and format you want to copy.

2. Press Ctrl + C.

3. Right-click on the cell where you want to paste values and formats.

4. Place your cursor on the small arrow next to “Paste Special.”

5. In the extended menu, under the “Paste values” tab, click on the third option in the row (“Values & Source Formatting”).

How Do You Show Value in Excel?

If a cell value is hidden and you can’t see the formula bar, you can unhide that value in the following way:

1. Select the cell with the value you want to reveal.

2. Go to the “View” tab on the Ribbon.

3. In the “Show” section, check “Formula bar.”

You should now be able to see the value of the selected cell in the formula bar. Alternatively, if you want to show values directly in cells, then you should:

1. Select the desired range of cells.

2. Go to the “Home” tab on the Ribbon.

3. In the “Number” section, click on the small arrow button in the bottom-right corner.

4. Select “Custom” in the “Category” section.

5. Scroll down the slider to the bottom.

6. You should see an entry with one or more semicolons (“;”). Select this entry and click “Delete.”

All the hidden values within the selected range of cells should now appear.

Copying the Value in Excel Without the Formula

There are some features in Excel that you just can’t figure out intuitively. Copying the value of a cell is one of them. Hopefully, this article has enabled you to surmount this obstacle.

More importantly, you’ve learned how to copy other elements of a cell, like its formatting and formulas. “Paste Special” is the feature that you will use the most for these purposes.

Also, if you view Excel documents created by someone else, you now know how to show values and formulas that the author hid. This option helps you to find all the important information in the document.

Have you ever had a problem with copying values in Excel? If so, how did you approach the issue? Let us know in the comments section below.

Let's block ads! (Why?)

Original page link

Best Cool Tech Gadgets

Top favorite technology gadgets