How To Copy Format In Excel

How To Copy Format In Excel Easy Way Shortcuts

by

In this tutorial, we will know how to copy format in excel with examples and shortcuts to understand easily.

Formatting in excel includes font, data, alignment, bold, italic, color, sizes and designing and more. Mostly we often need to copy it into another sheet or in the same workbook.

The good news is Excel has various ways to copy formatting, unnoticed and underestimated. Further on in this tutorial, you will find the most efficient ways to use Format Painter in Excel, and learn a couple of other techniques to copy formatting in your sheets.

This is still the continuation of our prior tutorial How To Format Cells In Excel With Examples.

How to Copy Formatting in Excel

Here are the various and efficient ways to copy your formatting in excel.

1. Copy Formats Using Paste Special

We have here a paste special method that literally copies and pastes formats. Obviously, pasting format is one of the options, which its task is to paste certain the copied object as is. The instructions below will clarify how to use paste special copying formats.

  • Select a group of cells you want to copy. Press CTRL + C to copy them.
  • On the first cell range, right-click to copy the format.
  • When the dialog box of format options appears, go to paste special options and select the formatting you want.Paste Special Options
  • For keyboard shortcut options, press Alt, E, S, T, Enter sequence.PRESS ALT, E, S, T
  • When you press Alt, E, S, the Paste Special dialog box opens.
  • The T key selects the Formats radio button in the dialog box.
  • The Enter key selects the OK command. This will close the dialog box and paste the copied format.

Whichever method you choose for Paste Special; the right-click context menu or the keyboard shortcut, the format will be copied to the new range.

2. Copy Formats Using Fill Handle

Fill handle in copying formats is often used to copy data in a cell. Though we don’t know it also use in copy format in excel in just a few clicks.

  1. Format the first cell as selected.
  2. Choose the cell that is formatted correctly and move your cursor over the fill handle (a small square at the lower right-hand corner). As you do this, the white selection cross on the cursor will change to a black cross.
  3. Hold and drag the handle over the range of the cell you want to use the formatting. As you noticed it copies the value of the first cell to the other, but we will solve that in the next step.
  4. Let go of the fill handle, click the drop-down menu next to Auto Fill Options, and choose Fill Formatting Only.
  5. That’s it! The cell values revert to their original values, and the desired format is applied to the remaining cells in the column.

How to Use Format Painter in Excel

Here are the steps to use format painter in excel.

  • Step 1: Select a range of cells or single cell, you want to copy the format.

  • Step 2: Go to Home Tab, Click on Format Painter feature; then the mouse cursor turns into Plus Paintbrush icon.

    Click Format Painter

  • Step 3: Click on a cell in another sheet (you can also shift between Excel workbook sheets by pressing Ctrl + PageUp/PageDown simultaneously). Only cell formation is replicated.

  • Step 4: Shift to another sheet, select the selected range to format & release.

    Format painter allows you to copy formats with conditional formatting and paste them into another sheet.Format cell copied using format painter

Shortcuts to Copy formatting in Excel

Microsoft Excel provides shortcuts in copying formats but not a single way it requires a sequence of shortcuts to this. This is work if you always prefer to use your keyboard, so we provide the following ways for you.

Excel Format Painter shortcut

If you don’t want to go on the ribbon tab format painter, do these:

  1. Select the cell containing the required format.
  2. Press Alt, H, F, P keys.
  3. Click the target cell where you want to apply the formatting.

Please note, the shortcut keys for Format Painter in Excel should be pressed one by one, not all at once.

  • Alt activates the keyboard shortcuts for ribbon commands.
  • H selects the Home tab on the ribbon.
  • F, P select the Format Painter button.

Paste Special formatting shortcut

Another way here show to copy format using paste special shortcuts.

  1. First, select the cell you want to copy format.
  2. Press Ctrl + C to copy the selected cell to the Clipboard.
  3. Choose which cell(s) the format should be applied to.
  4. In Excel 2016, 2013 or 2010, press Shift + F10, S, R, and then click Enter.

Summary

Recap on this tutorial we learned the different ways on how to copy format as is in a worksheet or in another worksheet. We noticed it’s possible to do this using the available commands on the ribbon and if you prefer using keyboard shortcuts excel also provides keys to do it, instructions are in this article.

Thank you for Reading!

Leave a Comment