Copy cell formatting

Format cells quickly by using the format of other cells as a template.

EQ

This tip is perfect for when you need to quickly update formatting of a presentation or report, or change the look of a worksheet in a hurry.

The Quick Tip shows you how to quickly copy cell formats to other cells or any size range of cells, with two keyboard shortcuts to choose from.

The Bonus Tip lets you turn back time and make formatting decisions after you’ve already pasted the copied cells!

Extending this tip using the Expert Tip gives you a great way to copy formats to many different cells or ranges, again and again.

We hope you find these tips useful.

Excel Quicker!

Quick Tip

How to copy cell formats

Make quick work of formatting your summary worksheets. Here’s how to quickly copy cell formats.

Copy cell formats using keyboard shortcuts

You can use these keyboard shortcuts to copy formatting from one cell to another:

StepActionShortcut
1Select cells with chosen formatting
2Copy cellsCtrl + C
3Select cells to receive formatting
4Paste formattingCtrl + Alt + V
then
T
then
Enter
5Clear the copy modeEsc
Keyboard shortcuts to copy cell formats using the Paste Special dialog

This tip also works on a range of cells; you can select more than one cell at once.

On a Mac, you can use ⌘ + Ctrl + V instead of Ctrl + Alt + V.

For example

Suppose you copy the range B3:F3 (1 row x 5 columns), in order to paste those formats in the destination range D8:H8 (also 1 row x 5 columns).

Copy cell formats example - before
Copy cell formats – before
Copy cell formats - after
Copy cell formats – after

How to specify the destination cells

In step 3, you can copy formatting from one cell range to another in one of two ways:

Method 1: First cell in destination

After copying the formatted range of cells, select a single cell at the top left* of the destination range (in step 3). Formats will be pasted in the destination range using the same sized range as was copied.

In the above example, after copying B3:F5, just select D8 and then paste formatting*.

Method 2: Exact destination range

Instead of selecting a single cell, you can instead select the entire destination range (in step 3).

In the above example, after copying B3:F3, select D8:H8 and then paste formatting.

Use the cell context menu to paste formatting

In step 4, the shortcuts shown above use the “Paste special” function. An alternative is to use the selected destination cell’s context menu.

You can activate the cell’s context menu in any of the following ways:

  • Press Shift + F10
  • Right-click the mouse in the selected destination cell
  • Press the Context menu key (if you have one; not all keyboards have them).

If you use the context menu method, you need to press R instead of T afterwards, and the Enter is not necessary.

With the modified step 4, the above sequence then becomes:

How to paste cell formatting using the context menu

StepActionShortcut
1Select cells with chosen formatting
2Copy cellsCtrl + C
3Select cells to receive formatting
4Paste formattingShift + F10
then
R
5Clear the copy modeEsc
Keyboard shortcuts to paste cell formats using the cell context menu

Fill and repeat cell formatting

This method is also useful if your destination range is larger than your source range and you want the formats to fill and repeat.

The chosen formats will repeat across the destination range.

For example

Suppose you have a source format range B4:C6, and want to copy the format to E4:H12.

Fill and repeat formats - before
Fill and repeat formats – before

Just select B4:C6, copy, then select E4:H12, then paste formats.

Fill and repeat formats - after
Fill and repeat formats – after

Voilà!

 

Bonus Tip

Please log in to see the bonus tip.

No login? Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

All logins also get access to the bonus tip each week.

Get even more

Get even more with one of our reasonably priced paid plans, with access to features like:

  • Quicksheet PDF summaries
  • Example files
  • VBA syntax
  • Additional expert tips each week
  • Advance publication

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

* This tip is affected by the reading direction used, and can vary between different language installations.

It is written for languages that read left-to-right (in these installations, the row headings are on the left side of the screen or application window).

For languages that read right-to-left (installations where the row headings are on the right side of the screen or application window), make the appropriate inferences.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.

Delayed schedule

This tip is on the Delayed Schedule. It works just like normal, but it’s old news.

To view the current Tip of the week, log in with an account that has access to our Advance Publication schedule.

Don’t have a login?

Get one >>

For more information about publication schedules and access to tips, see our plans.

Share this tip