How to paste external content to Excel always match destination formatting?
When you copy and paste data from external sources such as web pages, Word documents, or other applications into an Excel worksheet, Excel typically applies the original formatting from the source content. This can lead to inconsistent styles, differing fonts, or mismatched table designs that disrupt the uniform appearance of your worksheet. To align the pasted data with the formatting of your Excel document—ensuring consistent font, size, color, and borders—you can use the “Match Destination Formatting” option each time you paste.
However, selecting this option manually every time can be repetitive and time-consuming, especially if you frequently import data. To streamline your workflow and eliminate the need to adjust formatting after every paste, you can set “Match Destination Formatting” as the default paste behavior. This article provides step-by-step guides for two practical solutions: using Excel’s built-in Options and employing a VBA macro. Each method includes relevant scenarios and tips so you can choose the most suitable approach for your needs.
Paste external content to worksheet always match destination formatting with Excel Options
If you regularly need to paste data from external files or sources and want the formatting of your Excel worksheet to be preserved, Excel provides a handy paste option for you to handle it easily.
Use the Paste Options Floatie
This is the simplest built-in method and requires no prior setup.
- Copy the content you need—whether from Excel cells or other applications (e.g., Word, a web browser).
- Paste it into your target Excel cell. Immediately after pasting (using Ctrl + V), a Paste Options floatie button will appear at the lower-right corner of the pasted content
- Click the floatie button and select the second icon: Match Destination Formatting (visually represented as a clipboard (📋) with a paintbrush).
This action strips all original formatting from the source and seamlessly adapts the pasted content to the formatting of the target cells (e.g., font, color, borders).
Add to Quick Access Toolbar
If you use this function regularly, adding it to the Quick Access Toolbar (QAT) allows for one-click paste-and-match operation, saving time and clicks.
- Click File > Options. In the Excel Options dialog box:
- In the Excel Options dialog:
- Select Quick Access Toolbar in the left pane;
- Under Choose commands from, select All Commands.
- Scroll through the list to locate and select Match Destination Formatting.
Note: In newer versions, the command name has been updated from the legacy "Paste and Match Destination Formatting" to this simpler title.
- Click the Add >> button to include this command to the Customize Quick Access Toolbar box on the right.
- Click OK to save the changes.
The Match Destination Formatting icon will now appear on your Quick Access Toolbar in the top-left corner of the Excel window.
Now, after copying any content, select a destination cell, then simply click this icon instead of using Ctrl + V to paste and match the destination formatting in one step.
Paste external content to worksheet always match destination formatting with VBA code
If you need “Match Destination Formatting” to be the default action whenever you press Ctrl + V to paste, you can create a VBA macro to automate this process. After following these steps and assigning a shortcut, every paste operation will automatically match your worksheet’s existing formatting, speeding up repetitive tasks.
This VBA solution is ideal for advanced Excel users, those comfortable with macros, or anyone who wants to integrate this functionality into custom workbooks or distribute it across teams. Before activating VBA, ensure macros are enabled in your Excel settings, and save your workbook as a macro-enabled file (.xlsm) to preserve functionality.
1. Press ALT + F11 to open the Microsoft Visual Basic for Applications editor window.
2. Click Insert > Module to add a new module. Copy and paste the following code into the Module window:
VBA code: Set Match Destination Formatting as the default paste
Sub PasteWithDestinationFormatting()
'Updated by Extendoffice 20250904
On Error Resume Next
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
ActiveCell.Value = .GetText()
End With
If Err.Number <> 0 Then
Err.Clear
ActiveCell.PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False
End Sub
Double-check the code to ensure accuracy, as mistakes can cause unexpected behavior. When pasting VBA code, make sure not to overlap it with existing macros unless intended. For troubleshooting, if an error occurs, use Debug in the VBA editor to check which line is causing the issue.
3. Save and close the VBA editor. Next, press Alt + F8 to open the Macro dialog box. Select the code name you used, then click Options. In the Macro Options dialog, input v as the shortcut key. This sets Ctrl + v as the keyboard shortcut for running the macro instead of the usual paste command. See the screenshot below for reference:
4. Click OK in the Macro Options dialog, and then close the Macro dialog box completely.
Now, whenever you use Ctrl + V to paste content from external sources into your worksheet, the macro will run automatically, ensuring all pasted data instantly matches the destination formatting.
If multiple macros are assigned to similar keys, conflicts may occur. Also, this macro operates only in the current workbook unless you save it as part of your Personal Macro Workbook (PERSONAL.XLSB) for broader use. If you experience errors or unexpected behavior, confirm macro security settings and ensure the macro-enabled file format is used.
Related article:
How to set paste values as default paste when using Ctrl + V in Excel?
Demo: Paste external content to Excel always match destination formatting
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in