Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to copy values and formatting from a range to another in Excel?

When you want to only copy a range of cells' values and format, and do not copy the formula in cells, how can you quickly copy it? The following tricks are telling you how to get it done.

Copy values and all formatting with Paste Special in Excel

Copy values and all formatting with VBA

Copy data with Kutools for Excel good idea3

Transpose Table Dimensions(transpose a cross table to list, and vice versa.)

doc transpose cross to list

In Excel, copy values and formatting only can do as follows:

1. Select the range you want to copy;

2. Click Edit > Copy, or right click and select Copy;

3. Select a cell or range you want to paste the values and formatting;

4. Choose Edit, Paste Special or right click and choose Paste Special, and click Values and source formatting, see screenshot:

5. Then only the values and cell formatting are pasted in the selected cell.

Running VBA can quickly copy values and formatting.

formula Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Click here to know Auto TextClick here to get free trial

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Copy values and all formatting

Sub CopyValuesAndNumberFormats()
'Update 20130815
Dim CopyRng As Range, PasteRng As Range
xTitleId = "KutoolsforExcel"
Set CopyRng = Application.Selection
Set CopyRng = Application.InputBox("Ranges to be copied :", xTitleId, CopyRng.Address, Type:=8)
Set PasteRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
PasteRng.PasteSpecial xlPasteValuesAndNumberFormats
PasteRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

3. Click Run button or press F5 to run the VBA. And there is a dialog displayed on the screen, and you should select the range cells that you want to copy the values and formatting. See screenshot:

5. Click Ok and another dialog is displayed for you to select a cell to paste the result. See screenshot:

6. Click Ok, then the cells' values and formatting are pasted in the selected cell. See screenshot:

Note: With this VBA code, you can also paste the values and formatting into other worksheets that you want..

If you just want to copy values only, format only or values and number formatting only, you can use Kutools for Excel’s Copy Ranges.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installed Kutools for Excel, just do as below please:

1. Select the data, and click Kutools > Copy Ranges. And then select the option you need.

doc copy ranges 1

doc arrow down

doc copy ranges 2

2. Then click Ok, and select a cell to paste the data.

Paste Formulas
Paste Values
doc copy ranges 3
doc copy ranges 4
Paste Formats
Paste Displayed value
doc copy ranges 5
doc copy ranges 6
Paste Formulas and number formats
Paste Values and number formats
doc copy ranges 7
doc copy ranges 8

Paste Comments

doc copy ranges 9

Note: If you want to keep the row height or column width, just check Including row height or Including column width.

Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here

Relative articles:

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.