Add text and data

Copy cell formatting

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Copy cell formatting using the copy and paste options, Format painter, and dragging techniques.

Copy cell formatting
Method 1
  1. Select the cells that have the formatting you want to copy.

  2. Copy the cells, using Ctrl+C.
    Or, right-click the cells, and then select Copy.

  3. Select the cells where you want to use the copied format.

  4. Right-click the cells, and in Paste Options, select Formatting (R).
    Formatting (R)

Method 2
  1. Select the cells that have the formatting you want to copy.

  2. Copy the cells, using Ctrl+C.
    Or, right-click the cells, and then select Copy.

  3. Select the top cell where you want to use the copied format.

  4. Press Ctrl+Alt+V.

  5. In the Paste Special box, in Paste, select Formats, and then select OK.

Method 3
  1. Select the cells that have the formatting you want to copy.

  2. Select Home > Format Painter, and then select the cells where you want to use the format.

Copy formatting to multiple cells
  1. Select the cells that have the formatting you want to copy.

  2. Select Home > Format Painter, and then select the top cell to paste the format.

Copy formatting on non-adjacent cells
  1. Select the cells that have the formatting you want to copy.

  2. Select Home > Format Painter, and then select cells to paste the format.

  3. Press Esc on the keyboard to deactivate the paint brush.

Copy formatting by dragging
  1. Select the column that contains the formatting you want to copy.

  2. Hold the right mouse button, drag the right edge of the column, and then select Copy Here as Formats Only.

Copy formatting by dragging down the column
  1. Select the cell that contains the formatting you want to copy.

  2. Place the mouse cursor on the lower-right corner of the cell (pointing towards the Fill handle), hold the right mouse button, and then drag downwards.

  3. Select Fill Formatting Only when you release the right mouse button.

Want more?

Discover more courses like this at LinkedIn Learning

Copy and paste conditional formatting to other cells

Let's point out a few facts about formatting.

In the cells that we're looking at, in the cells A2 to A7, we're seeing that that's italicized, it's a bluish text on top of a yellow background, and we might want to copy the look of that, the format of it to some other cells.

Maybe we want to copy this into the Texas column, or maybe all these cells here.

What we might not be thinking about is the fact that there is also a numeric format here even though we're not seeing it.

These are text entries.

All cells have general format unless you've applied a different format.

General format doesn't use commas, doesn't use any kind of decimals unless they are specifically typed.

So, when we copy this format we will be seeing a different look of the numbers as well.

We're not always thinking that of course but it will happen.

So, this data is highlighted, let's right-Click and copy, or press Ctrl + C.

Suppose we want to apply this to the Texas numbers right here, highlight the data.

So how do we paste formatting?

You can go a number of different ways, one way is simply to right-click the cells here.

We've got some Paste Special Options, as we slide across these, one of them, and you eventually will see, is formatting, and we can see right now ahead of time, we're copying the look of those cells over in column A, but we're also copying that general format.

I'll simply click there for the moment, and let's say that's not what we expected although overtime we certainly will learn that yes there's a numeric format, it got copied as well.

Let's press Ctrl + Z.

Now in the lower lists, the same kind of data but maybe here we don't care, it's a different context completely.

Let's try this.

Let's begin first of all by copying the format that we see in cells A2 to A7.

How can we copy this format?

Press Ctrl +C, or Copy.

Let's go down and highlight these cells, and we need to actually only highlight the top cell.

We can also get to format cells by way of Ctrl +Alt+V, and choose Formats, and OK.

So, we can copy the format that way.

Maybe a more efficient way is I press on Undo.

Let's go back and highlight these cells here, and press Format Painter, and then simply come down here and click cell A11, like that.

Now, what if we like this format, we want to highlight these cells, we want do this for Texas and Florida, sort of offset the data, the every other data column kind of look.

We'd like to use the Format Painter now on multiple cells.

We can do this in a couple different ways.

We could begin by highlighting these cells here, and double-clicking Format Painter, and then, for example, click just the top Texas cell, just the top Florida cell, we're doing it that way.

Notice how the paintbrush is still active here. If we don't want to use Format Painter anymore we'll simply press Esc.

Let me undo that with Ctrl + Z, Ctrl + Z.

There's another approach.

We need only click on cell A11.

Suppose we want to highlight just some of the cells here to offset them a bit, to make them available for people who are going to be looking at this for special attention, something like that.

We can double-click Format Painter, and then for example, click here, click there, drag across these two cells, click here, click there.

We can do this indefinitely until we press the Esc key, so there will be times when we want to copy a format on adjacent cells, and a number different locations throughout the Worksheet.

So it's really easy to copy formats, a lot of different ways.

Here's another technique. Over in column J, I'm using a different font here, this is called Candera font.

It makes numbers look a little strange here, but let's say it's sort of an artistic look that you like, we would like to use that same font over here.

Now, along with it we want to use the background color, the fact that we are using white text.

There's nothing else in the column except the data we're seeing here. It goes down to row 34.

We'll simply click column J, and now with the right mouse button, I'm going to drag the edge.

I can drag the right edge, top edge, left edge, doesn't maKe any difference, but holding down the right mouse button, I am going to drag into column K, and let go of the right mouse button.

How about copy here is formats only.

We're not changing the data, but we are certainly changing the look of this in terms of the colors.

So, we copied the format, and that includes the date format, as well as the colors which is most important, to this example here.

Now, there's another use of that feature, in a different sense.

I like this format here, I like the color combination, I want to copy it down the column.

If I were to double-click, I would be copying the data.

Don't want to do that, they would copy the formats too, but it would obliterate all this data.

So, now I'm pointing to the fill handle in the lower right hand corner and holding down the right mouse button, dragging downwards here.

Now, if it's a long column unfortunately this is just going to take a long time, but if we simply drag over the data, I'm holding down the right mouse button.

That always means shortcut menu, but we are not seeing it yet.

As soon as I release the right mouse button we will see the menu, and what do we want to do here?

Fill formatting only.

We didn't change the content of the data, but we certainly did copy the format.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×