Formula to Copy Cell Value and Format in Excel (5 Ways)

In this sample dataset, the Name values are in Text format, Weekly Income is in Currency Format and other cells are formatted with text and cell color.

Method 1 – Using Format Painter to Copy Cell Value and Format

Steps:

Type the cell reference you want to copy.

Press the ENTER key.

Drag down using the mouse key to right to the right as per your dataset row number. In this case, we dragged from G to J as we have 4 rows.

Our rows look like the following image.

Do the same and drag it down to J11.

We will get the following result.

Select the entire dataset.

Click on Format Painter from the Home tab.

Without clicking anywhere else, select the whole dataset you just copied.

You will get the desired result.

Cell value and format are accurately copied.

Method 2 – Copy Cell Value and Format Using VBA

Steps:

Right-click on the sheet and go to View Code.

Copy and paste the VBA code below.

VBA code:

Sub Copycellvalues_Formats()
Range("B4:E11").Copy
Range("G4").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
End Sub

Press the F5 key or play button to run the code.

All the cells and formats are copied.

We have to define the range and command Excel to copy them through the code Range(“B4:E11”).Copy. And Range(“G4”).PasteSpecial (xlPasteAll) This code will paste everything from the dataset including format, values, fill color, text color, and conditional formatting if any.

Method 3 – Using Paste Option to Copy Cell Value and Format

Steps:

Select the first select the entire dataset and right-click.

Click on the cell where you want to copy your data and right-click.

When you click and paste as shown in the image, everything will be copied including values and formats.

Method 4 – Copy Cell Value and Format Using Shortcut Key

Steps:

Click on the first cell of the data range and press CTRL+SHIFT+ RIGHT ARROW +DOWN ARROW key. It will select the data entire data range. Press CTRL+C.