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

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.

Copy cell format and values in excel


Download Practice Workbook


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

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.

Copy cell format and values formula

  • 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.

Copy cell format and values format painter

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

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

Copy cell format and values formula with format painter

  • 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.

Copy cell format and values
Cell value and format are accurately copied.

Read more: How to Use Format Painter in Excel


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.

Copy cell format and values vba formula

  • 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
 

Copy Cell values and Format formula vba

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

Copy cell format and values vba

That’s it, all the cells and formats are copied.
Here we have to define the range and commanded 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.

Read more: VBA to Format Cell in Excel


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

Why use lengthy formulas to copy cells 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.

Copy cell format and values paste option

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

Read more: How to Copy Cell Format in Excel


Similar Readings


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.

Copy cell format and values shortcut

  • After that, click on the cell where we want to copy our data ad 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.

Copy cell format and values paste special

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.

Copy cell format and values


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.


Further Readings

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo