How to Copy Format in Excel Without Copying Cell Data

To copy only the appearance of cells in Excel, select the formatted cell, choose Home > Format Painter, then select the destination. For a more controlled paste, copy the source and choose Home > Paste > Paste Special > Formats. Both methods transfer formatting without replacing the destination’s values or formulas.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows.
A source cell or range with the formatting you want to reuse, and the destination cells you want to format.
If the source and destination are far apart, it helps to know their sheet names so you can move between them without changing your selection accidentally.

You do not need an add-in, a formula, or a special file type. Save the workbook first if it contains important work, especially if you plan to apply formatting across a large range.

Step-by-Step

Find the source formatting. Click a cell that already looks the way you want. Check that it has the right font, fill color, borders, alignment, and number format. For example, if the source displays a value as a percentage, its percentage number format is part of the formatting that may be copied too.
Use Format Painter for a quick copy. Go to Home > Format Painter. The pointer changes to a paintbrush. Click the destination cell to apply the source cell’s formatting. To format several neighboring cells, click and drag over the destination range instead.
Apply the paintbrush to more than one place if needed. If you have multiple separate destinations, double-click Format Painter before selecting them. Click or drag over each target range in turn. Press Esc when finished to turn off the paintbrush. A single click on Format Painter is normally for one application; double-clicking keeps it active for repeated applications.
Use Paste Special when you want a menu-based method. Select the source cell or range and press Ctrl+C. Select the destination cell or the upper-left cell of the destination range. On the ribbon, choose Home > Paste using the arrow beneath the Paste button, then choose Paste Special > Formats. The destination keeps its existing cell contents while taking the source formatting.
Check the result before continuing. Look at the destination and confirm that the intended appearance changed. If you copied a number format, check how the displayed value looks; a value such as 0.25 may show as 25% or as a date depending on the format. The underlying value is not converted by copying formatting.
Repeat for other ranges, using the right source each time. When different sections need different styles, use the matching source cell for each section. For example, copy from a heading cell to other headings, not from a heading to ordinary body cells. This keeps font, fill, alignment, and number display consistent within the right category.
Save the workbook. Once you have checked the formatted areas, save with Ctrl+S. If the result is not right, use Ctrl+Z immediately to undo the formatting operation, then select the correct source or destination and try again.

On Excel for Mac, the basic process is the same: use Home > Format Painter, or copy the source and use the Paste Special menu to choose formats. Keyboard shortcuts differ between Windows and Mac, so the ribbon or context menu is the dependable option when you are unsure. In Excel for the web, look for Format Painter on the Home tab; the Paste menu may present the formatting-only option with a slightly different label.

Common Mistakes

Choosing the wrong source cell: A cell can look similar while having a different number format, border, or alignment. Copying from a date cell instead of a currency cell, for instance, can make a destination number appear as a date. Inspect the source’s number format and other relevant details before copying.

Using ordinary Paste instead of a formatting-only option: Pressing Ctrl+V after copying normally pastes the source contents as well as its formatting. That can overwrite destination values or formulas. Use Format Painter or the Formats choice in Paste Special when you want the destination data to remain in place.

Selecting the wrong destination range: A drag that begins in the wrong cell, or a broad selection made before using Paste Special, can format more cells than intended. Select only the intended range and review the highlighted cells before applying the format.

Expecting column widths or row heights to copy: Cell formatting affects things such as font, fill, borders, alignment, and number display. It does not generally make a destination column the same width or a row the same height. If you need a matching column width, copy the relevant column and use the separate Paste Special > Column Widths option where available.

Assuming the visible appearance is always permanent: Conditional formatting can control how cells look based on their values or rules. A copied rule may affect what you see, and the displayed style can change when a value changes. If the result seems inconsistent, inspect the conditional formatting rules rather than repeatedly painting the same cells.

Tips

Choose Format Painter for speed. It is convenient for a single cell, a block of adjacent cells, or several target areas when you double-click the button. Use Paste Special when you prefer an explicit choice that says you want formats only.
Match source and destination shapes. For a multi-cell source, select a destination range that corresponds to it. A source row of differently formatted cells is not the same as one uniform-style cell; review the destination after applying the formatting.
Use a clearly styled example cell. Keep one well-formatted heading, date, or total cell in the sheet as a reference. This makes it easier to reuse a consistent style rather than trying to recreate it by eye.
Consider number formats before copying. Number formatting changes how a value is displayed, not the stored value itself. Applying a date or percentage format to unrelated numbers can make them appear misleading even though their underlying values have not changed.
Check formulas and values after a large operation. Formatting-only paste should preserve them, but confirming a few representative cells is a useful safeguard. Click a formula cell and check the formula bar if you need to verify that its formula remains intact.
Use undo promptly if the result is wrong. Ctrl+Z reverses the last operation in Excel. If you have made several other changes since applying the format, undo may also reverse those later actions, so inspect the result before continuing your work.

Troubleshooting

The destination’s values or formulas changed. You may have used ordinary Paste rather than Format Painter or Paste Special with Formats selected. Press Ctrl+Z right away, then repeat the operation using one of the formatting-only methods. If you cannot undo because you have already continued working, restore the affected data from a saved copy or re-enter it from a reliable source.

Nothing happens when you click Format Painter. Make sure you selected a source cell first and then clicked Home > Format Painter. After the button is active, click or drag over the destination; clicking the ribbon button alone does not apply formatting. If the sheet is protected, check whether editing or formatting cells is allowed, or ask the workbook owner to adjust its protection settings.

The result looks different from the source. Check whether the source contains conditional formatting, whether the destination has its own conditional formatting rules, and whether the destination is a merged cell. To review rules, select an affected cell and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the rules applying to the selected cells and their ranges. A conditional rule can change the visible style based on the destination’s value, even after you copy formatting.

Numbers display unexpectedly. The source’s number format may not suit the destination. Undo the operation if needed, choose a better source cell, or select the destination and set its number format from Home > Number. The underlying number is usually still there; the format changes how Excel displays it.

The column still has the wrong width. Formatting-only copy does not usually transfer column width. Set the width directly by dragging the boundary between column letters, or use the column-width option under Paste Special. Row height is also a separate layout setting and may need to be adjusted independently.

Frequently Asked Questions

How do I copy just the format in Excel without copying data?
Use Home > Format Painter and select the target cells, or copy the source and choose Home > Paste > Paste Special > Formats. Do not use ordinary Paste if the destination’s contents must stay unchanged.

Can I copy formatting to several non-adjacent cells?
Yes. Double-click Format Painter, then select each target cell or range. Press Esc after the last one. If the targets need different styles, repeat the process with the appropriate source cell for each style.

Does copying formatting copy formulas, values, or comments?
Format Painter and Paste Special with Formats selected are intended to copy formatting, not the source’s values or formulas. Check the Paste Special selection carefully; ordinary Paste can bring over contents too. The formatting operation also does not serve as a way to copy comments or notes.

Will copying a format change the actual number in a cell?
No. A number format changes how Excel displays the value, not the underlying value. For example, formatting a decimal as a percentage can change its display from 0.25 to 25% without changing the stored number.

Can I copy a column’s width with Format Painter?
No. Use Paste Special’s separate Column Widths option or adjust the column boundary directly. Row heights are also separate from ordinary cell formatting.

Can I copy formatting between worksheets or workbooks?
Yes, provided the source and destination are available and you can edit the destination. Select the source, use Format Painter or copy it for Paste Special, then move to the target sheet or workbook and apply the formatting. Take care not to select ordinary Paste, which can copy cell contents as well.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.