Mahbubur Rahman
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.
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.
- 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.
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.
- 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 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 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.
- 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
- Use Format Painter Shortcut in Excel (5 Ways)
- How to Custom Format Cells in Excel (17 Examples)
- Format Cell Based on Formula in Excel (13 Examples)
- How to Use Excel Cell Format Formula (4 Effective Methods)
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 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.
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.
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.
