To copy only the values of cells in Excel without retaining formatting or formulas, select the source range, press
F9 to convert formulas to their results, and then use
Ctrl+C followed by
Ctrl+V on the destination. Alternatively, use the Paste Special dialog with the Values option. This method is essential for cleaning up data before sharing or archiving.
The F9 Function Trick
The most efficient way to paste values directly into a formula bar or cell is using the
F9 key. This technique works specifically when you are editing a cell or have selected a range within the formula bar. It converts any active references into static numbers or text strings instantly.
First, highlight the cells containing the formulas you wish to convert. You can do this by selecting the range in the worksheet or by clicking inside the formula bar after highlighting multiple cells. Once the selection is active, press the
F9 key. The formulas will immediately disappear from view, replaced by their calculated results. For example, if you had
=A1+B1, it will now show just the number resulting from that addition.
After pressing
F9, press
Enter to confirm the change in the current cell, or
Esc to cancel if you made a mistake. If you selected a range and pressed
F9 in the formula bar, you must then click away or press Enter to apply the changes to all selected cells. This method is particularly useful when you need to break links between workbooks or remove complex calculations while keeping the final output.
Paste Special Method
For a more visual approach that does not require editing the formula bar, use the Paste Special feature. This is the standard method for copying values from one location to another across different sheets or workbooks. It ensures that your clipboard content is interpreted strictly as data rather than formatted objects.
Select the cells you want to copy and press
Ctrl+C. Navigate to the destination cell where you want the values to appear. Right-click the target cell and look for the Paste Options menu. Click the icon that displays a clipboard with numbers, often labeled "Values Only" or represented by an icon with
123. This action pastes only the raw data, ignoring font styles, colors, borders, and underlying formulas.
You can also use the keyboard shortcut for this process. After copying with
Ctrl+C, press
Alt+E+S in older versions of Excel, or navigate to the Home tab ribbon. In the Clipboard group, click the dropdown arrow under Paste and select "Values". This opens the Paste Special dialog box where you can explicitly choose the Values radio button before clicking OK.
Handling Formulas and References
When dealing with formulas that reference other cells, converting them to values breaks the dynamic link. This means future changes in the source cells will not update the destination. This behavior is often desired when preparing reports for external stakeholders who should see static historical data rather than live calculations.
If you are working with large datasets, consider using the
Adjust Excel Column Width to Fit Content guide to ensure your pasted values are readable. Sometimes, pasting values can result in truncated text if column widths are not optimized. Adjusting columns after pasting helps maintain clarity in your spreadsheets.
Be cautious when using
F9 on ranges that contain errors like
#N/A or
#VALUE!. These errors will remain visible as text strings after conversion. They will not be converted to numbers, so you may need to clean the data separately if those errors are undesirable in the final output.
Common Pitfalls and Tips
One common mistake is trying to paste values into a locked cell or a protected sheet. Ensure that the destination sheet is unprotected before attempting to paste. Additionally, remember that
F9 only works within the formula bar context. If you try to press
F9 while a cell is selected in the main grid without entering edit mode, nothing will happen.
Another tip is to use Paste Special with "Skip Blanks" if you are updating existing data. This prevents overwriting non-empty cells with empty values from your source range. This feature is available in the Paste Special dialog box under the Options section. It is highly effective for incremental updates where only new data needs to replace old entries.
Finally, always verify your data after pasting values. Check for any unexpected rounding issues, especially with currency or scientific notation. Excel stores precision internally but displays rounded values based on format settings. Pasting values captures the displayed number, not necessarily the full internal precision, which can lead to minor discrepancies in subsequent calculations.
Frequently Asked Questions
Can I paste values using a keyboard shortcut?
Yes, after copying cells with
Ctrl+C, you can press
Ctrl+Alt+V to open the Paste Special dialog. From there, select "Values" and press Enter. This provides a quick keyboard-only workflow without needing to use the mouse or ribbon menus.
Does pasting values keep the original formatting?
No, pasting values removes all formatting such as bold text, background colors, and borders. It inserts only the raw data content. If you need both values and formatting, use the standard
Ctrl+V paste command instead of the Values option.
What happens if I press F9 on a cell with text?
Pressing
F9 on a cell containing plain text has no effect because there is no formula to evaluate. The key is designed to calculate formulas and convert them to results. Text remains unchanged unless it is part of a larger formula expression.
How do I break links between workbooks permanently?
Use the Edit Links feature found in the Data tab. Select "Break Link" to convert all external references to static values. This is safer than manually copying and pasting values because it handles all linked cells at once and updates the status of the workbook accordingly.
Why are my pasted values showing as dates incorrectly?
Excel may interpret numeric sequences as dates if the cell format is set to Date. To fix this, change the cell format to Number or Text before pasting. Use the
Excel 2023 on Mac: Date Format Mismatch insights if you are encountering platform-specific date interpretation issues that affect how values are displayed.