Today, you’ll learn how to copy values and number formatting in Microsoft Excel.

Working in Microsoft Excel to analyze and visualize data isn’t about your technical skills but your presentation skills.

You can make stunning reports in an Excel spreadsheet workbook and present your findings to employers or investors.

But here comes the problem of copying values and number formatting in Excel from one worksheet to another worksheet or workbook. The most common problem is missing number formatting styles.

Follow along with me as I demonstrate multiple methods for effortlessly copying values and numbers from source to destination in Excel. Let’s get started!

Copy Values and Number Formatting Using a Keyboard Shortcut

This method uses a keyboard shortcut to copy only the values and number formatting from one set of cells to another in Excel.

If you love to navigate in Excel using the keyboard to work faster, use this method.

It avoids copying formulas and unwanted cell styles while preserving number formats like currency, decimals, and percentages.

This is especially helpful when you want to share final results without carrying over calculation logic.

Let me walk you through the steps below:

Launch the Microsoft Excel app, go to the File tab on the top left corner, click Open, and select the workbook containing your data.

Click and drag your mouse over the cells you want to copy, or hold down Shift and use the arrow keys to select them.

Press Ctrl + C on your keyboard to copy the selection, and confirm that a dotted border appears around the copied area.

Click the target cell where you want to paste the values, or move to it using the arrow keys on your keyboard. The destination cell range could be in the same worksheet, the same workbook, or a different workbook.

Press Ctrl + Alt + V to open the Paste Special dialog box in the center of the screen.

In the dialog box, select the Values and number formats option below the Paste column (or use the arrow keys to select it manually).

Press Enter or click OK on the Paste Special dialog box to paste the copied content with values and number formatting only, excluding any formulas or cell styles.

That’s it! You’ve successfully copied values and number formatting styles from one dataset to another cell range.

Copy Values and Number Formatting Using the Paste Command

This method lets you copy only the values and number formatting of selected cells using an option available in the Clipboard group of the Home tab.

It’s useful when you want to remove formulas but keep the numerical display formats like currency, percentages, and decimal places. This method works best if you prefer using Excel’s ribbon and mouse instead of keyboard shortcuts.

Open your Excel file by launching Microsoft Excel, clicking File in the top-left corner, selecting Open, and choosing your workbook.

Select the cells you want to copy by clicking and dragging with your mouse or holding Shift while using the arrow keys.

Press Ctrl + C on your keyboard or click the Copy button in the Clipboard group on the Home tab of the ribbon.

Click on the cell where you want to paste the copied content.

Go to the Home tab, find the Clipboard group, and click the drop-down arrow below the Paste button to open the Paste context menu.

In the Paste Values section of the menu, click the option labeled Values and Number Formats (with a clipboard icon and %123 symbol).

The values, along with their number formatting, will now be pasted into the selected destination cells.

Copy Values and Number Formatting Using Excel Tables

This method involves converting your data into an Excel Table so you can work with structured data that automatically preserves number formatting.

When copying from a table, Excel treats the values and formatting more consistently than with regular ranges.

This is especially useful when working with large datasets where format integrity is crucial across reports or dashboards.

Open your Excel file by launching Microsoft Excel, clicking File in the top-left corner, selecting Open, and choosing your workbook.

Select the range of data you want to copy by clicking and dragging your mouse across it.

Go to the Insert tab on the ribbon and click the Table button in the Tables group to convert the range into an Excel Table.

In the Create Table dialog box, ensure the range is correct, check the My table has headers box if applicable, and click OK.

Select the table rows or columns you want to copy by dragging your mouse over them or clicking the top header and using Ctrl + Shift + Right Arrow and Ctrl + Shift + Down Arrow keys.

Press Ctrl + C to copy the selected portion of the table.

Click on the destination cell where you want to paste the data.

Go to the Home tab on the ribbon, click the drop-down arrow under the Paste icon in the Clipboard group, and select Values and Number Formats from the Paste Values section of the menu.

Drag and Drop Between Workbooks

If you need to copy values and number formatting from one workbook to another, you can use this technique.

Open the source workbook and the destination workbook.

Select the target dataset from the source workbook and drag it using the left mouse button. Keep the dataset dragged and don’t release the mouse button yet.

Now, use the Alt + Tab keys on the keyboard to cycle through the open windows on the PC. When you reach the destination workbook, let go of the Window switcher keys.

You’ll now see the dragged dataset’s selection box on the destination workbook. Place the selection box over the intended cell range and release the mouse button.

Congratulations! You’ve successfully copied values and number formatting from one workbook to another.

Use the Move Sheet Feature to Copy Values and Number Formatting Across Workbooks

This method uses Excel’s built-in Move or Copy Sheet feature to duplicate an entire worksheet while preserving all values and number formatting.

Unlike copy-pasting ranges, this approach ensures that all visible data and formatting, including column widths and number displays, are transferred exactly as seen.

It’s best suited for working with complete sheets when you want to create a copy without disturbing formulas or external references.

Open your Excel file by launching Microsoft Excel, clicking File in the top-left corner, selecting Open, and choosing your workbook.

Right-click on the worksheet tab at the bottom of the screen that contains the data you want to copy.

In the context menu that appears, click Move or Copy to open the Move or Copy dialog box.

In the To book dropdown, select the current workbook if you’re copying within the same file, or choose another open workbook if you’re copying to a different file.

In the Before sheet section, choose where you want the copied sheet to appear.

Check the box labeled Create a copy to ensure the original worksheet remains untouched.

Click OK to create the copy. Excel will generate a new sheet with all values and number formatting exactly preserved.

The copied dataset in the destination workbook will open automatically after copying it using the above steps.

Copy Values and Number Formatting Using Excel VBA

If you wish to automate the process of copying values and number formatting from one sheet to another or one workbook to another, you can use an Excel VBA macro.

Do you know how to create a macro from a VBA script? Skip the next section and move on to the actual VBA code.

If you don’t know how to set up a macro, go through this quick Excel guide:

📒 Read More: How To Use The VBA Code You Find Online

Once you’re ready to create the macro, start with this VBA script:

Sub CopyValuesAndNumberFormats()
    Dim sourceRange As Range
    Dim destRange As Range
    Dim prompt As String
    Dim confirmMsg As String
    
    ' Prompt to select the source range
    On Error Resume Next
    Set sourceRange = Application.InputBox( _
        prompt:="Select the source dataset or table range:", _
        Title:="Select Source Range", _
        Type:=8)
    On Error GoTo 0
    
    If sourceRange Is Nothing Then
        MsgBox "Source range selection cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt to select the destination cell
    On Error Resume Next
    Set destRange = Application.InputBox( _
        prompt:="Select the top-left cell of the destination range:", _
        Title:="Select Destination Range", _
        Type:=8)
    On Error GoTo 0
    
    If destRange Is Nothing Then
        MsgBox "Destination range selection cancelled.", vbExclamation
        Exit Sub
    End If

    ' Ensure destination has the same shape or is only the top-left cell
    If destRange.Cells.Count = 1 Or _
       (destRange.Rows.Count = sourceRange.Rows.Count And destRange.Columns.Count = sourceRange.Columns.Count) Then
        
        ' Perform copy: values and number formatting
        Dim r As Long, c As Long
        For r = 1 To sourceRange.Rows.Count
            For c = 1 To sourceRange.Columns.Count
                With destRange.Cells(r, c)
                    .Value = sourceRange.Cells(r, c).Value
                    .NumberFormat = sourceRange.Cells(r, c).NumberFormat
                End With
            Next c
        Next r
        
        ' Confirmation
        confirmMsg = "Copy completed successfully!" & vbCrLf & _
                     "Source: " & sourceRange.Address(External:=True) & vbCrLf & _
                     "Destination: " & destRange.Address(External:=True)
        MsgBox confirmMsg, vbInformation, "Copy Done"
    
    Else
        MsgBox "Destination range must be a single cell or match the shape of the source range.", vbCritical
    End If
End Sub

Once the VBA macro is ready, press Alt + F8 to launch the Macro dialog box.

Select the CopyValuesAndNumberFormats macro and hit Run to execute the macro.

It’ll ask you to select the source dataset through a dialog box.

Then, you’ll see another dialog box asking you to select the top left corner cell of the destination cell range. For example, if the target cell range is A1:C8 the top left cell will be A1.

The Excel VBA script will move the dataset and show a confirmation dialog box.

Conclusions

Now you know how to copy values and number formatting in Microsoft Excel using some popular and proven methods, like a keyboard shortcut, the Paste command, using an Excel Table, the drag and drop movement, and the Move or Copy Sheets command.

Furthermore, you learned how to automate the process using an Excel VBA macro.

If you liked the Excel guide, use the comment box to submit your suggestions or feedback. Don’t forget to share the guide with your colleagues, friends, and family.