How to copy cell as text value not formula in Excel?

When working in Excel, it is common to use formulas to calculate data dynamically. However, there are situations where instead of pasting formulas into a new location, you need the actual displayed result—the cell's value—as plain static text. Direct copying and pasting typically transfers the formula itself, leading to unwanted recalculations or reference errors in the new location. This can be especially troublesome when sharing data with others, archiving results, or preparing fixed reports where cell values should not change. To address this need, this article outlines several practical methods to copy cells as text values only, not formulas, in Excel. These techniques enable you to confidently preserve the content as-is, avoid formula errors, and improve data integrity when moving your results between sheets or workbooks.

Copy cell as text value not formula with Excel built-in function
Copy cell as text value not formula with Kutools for Excel
VBA Code - Use a macro to copy and paste only the values of cells as text

Copy cell as text value not formula with Excel built-in function

Excel provides a straightforward way to paste only the values of cells, without formulas, using its built-in paste options. This method is appropriate when you need to make static copies of calculated or formula-based results. It's helpful for finalizing data, preparing reports, or preventing further recalculation and reference issues.

1. Select the range you want to copy as text values only and press Ctrl + C to copy the selection.

2. Click to select a blank cell or destination area where you want to paste the results.

3. Navigate to the Home tab, then click Paste and choose Values from the drop-down menu. This option is usually displayed as a clipboard with "123" icon. See screenshot:

This pastes only the displayed values into the selected location, ensuring the results are static and no formulas are transferred. This method is suitable for most scenarios and preserves only what you visually see in the cells. Note that formatting, data validation, and comments are not included—only the value appears in the paste destination.

Copy cell as text value not formula with Kutools for Excel

In addition to the built-in paste values method, Kutools for Excel offers a more flexible way to copy cells as text only. The Copy Ranges feature allows not only copying just displayed values, but also including or excluding formats, comments, and even row heights and column widths. This utility is especially useful when you need fine control or if you need to copy multiple non-contiguous ranges simultaneously—something Excel's default tools don't always handle efficiently.

1. Select the range containing the cells you want to copy as text. Then go to Kutools > Range > Copy Ranges.

2. In the Copy Multiple Ranges dialog box, check the Displayed value option. You can also choose additional options if needed—for example, to retain formatting or comments. Once ready, click the OK button.

3. In the prompt, select a blank area for placing the copied cells and then confirm by clicking OK. See screenshot for reference:

Only the visible (displayed) values from your original selection are pasted into the new location, leaving out any underlying formulas. You can use this method when summarizing reports, preparing records for external sharing, or combining data from different sheets.

With Copy Multiple Ranges, you can also maintain original row heights and column widths, or even copy several separate ranges at once. This helps when moving complex tables or consolidating data from different regions of your worksheet. See example screenshot:

Be mindful to select "Displayed value" to avoid copying any formulas. If your selection is too large or covers non-adjacent ranges that don't line up in the destination, ensure your destination range is large enough or you may encounter a prompt to adjust your selection.

VBA Code - Use a macro to copy and paste only the values of cells as text

In situations where you need to automate the process—for example, when working on repetitive tasks or handling multiple sheets—a VBA macro can efficiently copy and paste only the cell values, stripping out all formulas and ensuring the result is plain text. This method is helpful for batch operations, cleaning datasets, or for those who want to streamline their workflow.

1. Open the VBA editor by clicking Developer tab > Visual Basic. In the VBA window, click Insert > Module to create a new module, then input the following code:

Sub CopyPasteValuesAsText()
    Dim rngSource As Range
    Dim rngTarget As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngSource = Application.Selection
    Set rngSource = Application.InputBox("Select the range to copy:", xTitleId, rngSource.Address, Type:=8)
    
    Set rngTarget = Application.InputBox("Select the destination cell for pasting values as text:", xTitleId, "", Type:=8)
    
    If rngSource Is Nothing Or rngTarget Is Nothing Then Exit Sub
    
    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
End Sub

2. To run the code, click the button. A dialog box will prompt you to select the range you want to copy, and then a second prompt will let you pick the destination cell where you want the plain text to appear.

This macro offers greater flexibility for copying and pasting cell values in bulk and is especially valuable if you need to repeat the operation frequently. The macro pastes only the visible value, not the underlying formula, ensuring your data remains stable in the destination area. Be sure to save your workbook as a macro-enabled file (*.xlsm) to retain the VBA functionality. If you receive any errors, ensure macros are enabled in your Excel settings.

Related article:

• How to copy cells data with row height and column width in Excel?