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.

Copy cell format and values in excel


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

Steps:

  • Type the cell reference you want to copy.

Copy cell format and values formula

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

Copy cell format and values format painter

  • Do the same and drag it down to J11.

  • We will get the following result.

Copy cell format and values formula with format painter

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

Copy cell format and values
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 cell format and values vba formula

  • 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

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

Copy cell format and values vba

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.

Copy cell format and values paste option

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

Copy cell format and values shortcut

  • Click on the cell where you want to copy your data and press CTRL+V.


Method 5 – Copy Cell Value and Format Using Paste Special

Steps:

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

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

Copy cell format and values paste special

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


Download Practice Workbook


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

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo