Paste & Match Destination Formatting in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to paste and match destination formatting in Excel and Google Sheets.
Match Destination Formatting
When pasting data from an external application (Word, PowerPoint, etc.) to Excel, you can either keep the formatting from the source document or match the formatting in Excel. Say you have the following sales dataset in Excel.
As you can see, the range now has banded rows (blue and white), with Price and Total columns with currency formatting. In a Word document, you have prices to paste in Excel, overwriting Column C.
First, copy the data from the external application.
- Then, right-click the cell where you want to paste (here, C1)
- Choose Match Destination Formatting under Paste Options.
As a result, the Word data is pasted in Column C in Excel. The new data is formatted as currency, not as it was formatted in Word.
If you paste data using the shortcut CTRL + V, it keeps formatting from the source (e.g., Word) file.
Quick Access Toolbar
To speed up the process described above, add the Paste and Match Destination Formatting icon to your quick access toolbar in Excel.
- Click on the Customize Quick Access Toolbar icon in the toolbar, and choose More Commands…
- In the Excel Options window, choose All Commands from the Choose commands from drop down.
- Choose Paste and Match Destination Formatting from the list. Click Add then OK.
Now, the Paste and Match Destination Formatting icon shows up in the toolbar.
Instead of right-clicking a cell and choosing Match Destination Formatting, you can just select the target cell and click on your new icon in the toolbar to do the same thing.
Match Destination Formatting in Google Sheets
You can also paste data from another application and match destination formatting in Google Sheets.
First, copy the data. Then, in Google Sheets:
- Right-click the cell where you want to paste (C1).
- Choose Paste Special.
- Then, click Paste values only (or use the keyboard shortcut CTRL + SHIFT + V).
As a result, the Word data is pasted to Column C in the Google spreadsheet with the formatting already set in Column C.
As in Excel, if you just paste data using the keyboard shortcut CTRL + V, it keeps the formatting from the source application.
