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

Sometimes while working in Excel we need to copy cells. But sometimes while copying the format of the cells may change which will make our task difficult if we need to perform calculations. In this tutorial, we are going to show you some easy Excel formula to copy cell value and format.

We are going to use a sample dataset containing Name, Age, Country, and Weekly Income. Here, the Name values are in Text format, Weekly Income is in Currency Format and other cells are formatted with text and cell color.

Formula to Copy Cell Value and Format in Excel: 5 Simple Ways

Here, we will see the use of Format Painter, Paste Special, and VBA Excel formula to copy cell value and format.

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

If our data set is small, we can easily use this method to copy cell values and formats. Let’s see how.

Steps:

• First, type the cell reference you want to copy.

• Now, press the ENTER key.

• After that, drag down using the mouse key to right to the right as per your dataset row number. In this case, we have 4 rows that are why we dragged it to J from G.

• Now, our rows look like the following image.

• Now, do the same and drag it down to J11.

• As a result, we will get the following result.

• At this point, select the entire dataset.

• After that, click on Format Painter from the Home tab.

• Finally, without clicking anywhere else, select the whole dataset you just copied.

At last, we will get our result as we wanted.

cell value and format are accurately copied.

Method 2: Copy Cell Value and Format Using VBA

Now, we will see the Excel VBA formula to copy cells and formats. All we need to know is the dataset range.

Steps:

• First, right-click on the sheet and go to View Code.

• After that, 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

• After that, press the F5 key or play button to run the code.

That’s it, all the cells and formats are copied.
Here 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

Why use lengthy formulas to copy cell values and formats when you can do it with a few clicks?

Steps:

• First, select the first select the entire dataset and right-click the mouse button.

• Now, click on the cell where you want to copy your data and again right-click the mouse button.

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

Method 4: Copy Cell Value and Format Using Shortcut Key

When you have a huge data range to copy, shortcut keys can be pretty handy. Let’s see how to utilize that.

Steps:

• First, 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 whatever the size is, and now, press CTRL+C.

• After that, click on the cell where we want to copy our data and press CTRL+V.

That’s all. Done.

Method 5: Copy Cell Value and Format Using Paste Special

What if, we only want the values and formats of cell numbers, not the fill color and text color? In this case, Paste Special comes to the rescue.

Steps:

• First, select the first select the entire dataset and right-click the mouse button.

• Now, click on the cell where you want to copy your data and again right-click the mouse button.

After clicking Values from Paste Special our data set will look like the following image.

Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

Download Practice Workbook

Conclusion

These are 5 different methods of using Excel formula to copy cell values and format. Though a couple of methods is not the conventional formula, still those may come in handy to know. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

<< Go Back to Copy Cell Value | Formula List | Learn Excel