Excel Copy with Formula Formatting? Solved

Updated 7 November 2026 · Standard formula, shown below

This article explains how copying and pasting in Excel handles formulas and formatting, and how to control which parts—formula, formatting, or both—get transferred. You’ll learn the exact paste options, keyboard shortcuts, and common pitfalls so you can copy with confidence every time.

Why Copying in Excel Is Not Always Simple

When you copy a cell in Excel, you copy more than just the visible value. The clipboard carries the formula, the cell’s formatting (font, fill, borders, number format), and sometimes data validation or conditional formatting. The default paste (Ctrl+V) pastes everything, which is often not what you want. For example, you might want to copy a formula to another column but keep the destination’s formatting, or copy only the calculated value without the formula. Excel provides several paste options to handle each case.

The Default Copy and Paste Behavior

By default, pressing Ctrl+C (or right-click → Copy) and then Ctrl+V pastes the formula, formatting, and data validation from the source cell. If the formula uses relative references (like A1), Excel adjusts the references relative to the new location. For instance, copying =A1+B1 from row 1 to row 2 changes it to =A2+B2. This is usually what you want when copying down a column, but it can cause errors if you intended to keep the same cell references. To prevent reference adjustment, you can use absolute references (e.g., $A$1) or use the copy-paste special options.

Paste Special: The Key to Control

To access Paste Special, copy a cell or range, then right-click the destination and choose “Paste Special…” (or press Ctrl+Alt+V). This opens a dialog with several options:

All – pastes everything (same as Ctrl+V).
Formulas – pastes only the formula, without formatting or borders.
Values – pastes only the calculated value, removing the formula entirely.
Formats – pastes only the formatting (font, fill, number format, borders).
Column widths – pastes only the width of the copied column.
Validation – pastes only data validation rules.
Transpose – flips rows to columns and vice versa.

You can also combine options, like “Formulas and number formats” or “Values and number formats,” by selecting the desired checkboxes in the Paste Special dialog.

How to Copy Formulas Without Formatting

To copy a formula but not the formatting, use Paste Special → Formulas. Steps:

Select the cell(s) with the formula.
Press Ctrl+C to copy.
Select the destination cell(s).
Press Ctrl+Alt+V to open Paste Special.
Choose “Formulas” and click OK.

This pastes the formula (with adjusted references) but leaves the destination’s formatting intact. If the destination already has formatting you want to keep, this is the correct option.

How to Copy Formatting Without the Formula

If you want to copy only the formatting (e.g., to apply the same fill and borders to another range), use Paste Special → Formats. Steps:

Copy the cell(s) with the desired formatting.
Right-click the destination and choose Paste Special → Formats (or use the Format Painter icon in the Home tab).
Click OK.

This copies only visual formatting, not values or formulas. The Format Painter is a faster alternative for single or multiple cell formatting.

Common Mistakes and How to Avoid Them

Mistake: Copying a formula and pasting it over a range that contains merged cells. This can cause errors or unexpected results. Solution: Unmerge the destination cells first, or paste into a single cell and then drag the fill handle.
Mistake: Forgetting that relative references change. If you copy a formula with A1 and paste it one column to the right, it becomes B1. To keep the same reference, use $A$1 in the original formula.
Mistake: Pasting values when you meant to paste formulas. This often happens when using Ctrl+V after copying from a formula cell but the source was already a value. Always check the formula bar after pasting.
Mistake: Using Paste Special on a merged cell range. Paste Special works on the top-left cell of a merged range; other cells in the range are ignored.

Keyboard Shortcuts for Faster Pasting

Excel offers quick keyboard shortcuts for common paste options:

Ctrl+V – paste all.
Ctrl+Alt+V – open Paste Special dialog.
Ctrl+Alt+V, then F – paste formulas only.
Ctrl+Alt+V, then V – paste values only.
Ctrl+Alt+V, then T – paste formats only.

These shortcuts work in most modern versions of Excel (2013, 2016, 2019, 365). If you use a Mac, the shortcut for Paste Special is Ctrl+Command+V.

Frequently Asked Questions

Why does my copied formula show an error when pasted?

This usually happens because the formula references cells that are not available in the new location, or because relative references changed and now point to empty or incorrect cells. Check the formula bar to see the references and adjust them, or use absolute references if you want the formula to always point to the same cells.

How do I copy a formula down a column without changing the reference?

Use absolute references in the formula (e.g., $A$1) before copying, or edit the formula after pasting to add the dollar signs. Alternatively, use Ctrl+Alt+V → Formulas and then manually fix the references if needed.

Can I copy only the number formatting (like currency or percent) without the formula?

Yes, use Paste Special → Formats. This copies all formatting, including number format, font, and borders. If you want only the number format, you can use the Format Painter, but it also copies all formatting. There is no built-in option to copy only number format, but you can use the Format Cells dialog to manually apply the same format.

What is the difference between “Formulas” and “Values” in Paste Special?

“Formulas” pastes the underlying formula (e.g., =A1+B1) so that the cell recalculates if the source data changes. “Values” pastes only the current result (e.g., 10) as a static number, with no link to the original formula. Use Values when you want to preserve the result and remove the dependency.

Why does my pasted formatting look different from the source?

This can happen if the destination cells have conditional formatting or table styles that override the pasted formatting. Also, if the source uses a custom number format that is not available in the destination, Excel may approximate it. Check the destination’s cell styles and conditional formatting rules.

How do I copy a formula to another worksheet without changing references?

Copy the cell, go to the other sheet, right-click and choose Paste Special → Formulas. The references will adjust to the new sheet, but if you want them to point to the original sheet, use absolute references with the sheet name, like =Sheet1!$A$1.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.