Paste & Match Destination Formatting in Excel & Google Sheets

This tutorial demonstrates how to paste and match destination formatting in Excel and Google Sheets.

 

paste and match destination formatting 1a

 

Match Destination Formatting

When pasting data from an external application (Word, PowerPoint, or similar) to Excel, you can either keep the formatting from the source document or match the formatting in Excel. Say you have the following sales data set in Excel.

 

paste and match destination formatting

 

As you can see, the range now has banded rows (blue and white), with Price and Total columns with the currency. On the other hand, in the Word document, there are prices you want to paste in Excel, Column C.

 

paste and match destination formatting word

 

First, copy the data in the external application.

  1. Then, right-click the cell where you want to paste data (here, C1)
  2. Choose Match Destination Formatting in the Paste Options.

 

paste and match destination formatting 1

 

As a result, the Word data is pasted in Column C in Excel, keeping the Excel formatting.

 

paste and match destination formatting final

 

If you paste data using the shortcut CTRL + V, it will keep formatting from the source file.

 

paste and match destination formatting 3

 

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.

  1. Click on the Customize Quick Access Toolbar icon in the toolbar, and choose More Commands…

 

match destination formatting quick access toolbar

 

  1. In the Excel Options window, choose All Commands from the Choose commands from the drop-down list.

 

match destination formatting quick access toolbar 1

 

  1. Select Paste and Match Destination Formatting in the list, click Add, and OK.

 

match destination formatting quick access toolbar 2

 

Now the Paste and Match Destination Formatting icon is in the Toolbar.

 

match destination formatting quick access toolbar 3

 

Instead of right-clicking a cell and choosing match destination format, you can just select that cell and click on this icon in the toolbar in order to paste data and keep the Excel formatting.

Match Destination Formatting in Google Sheets

You can also paste data from the third application and match destination formatting in Google Sheets.

First, copy the data. Then, in Google Sheets:

  1. Right-click the cell where you want to paste (C1).
  2. Choose Paste Special.
  3. Then, click Paste values only (or use the keyboard shortcut CTRL + SHIFT + V).

 

google sheets paste and match destination formatting

 

As a result, the Word data is pasted to Column C in the Google spreadsheet with the same formatting already in Column C.

 

google sheets paste and match destination formatting 2

 

Similar to Excel, if you just paste data using the keyboard shortcut CTRL + V, data will keep the format from the source application.

 

google sheets paste and match destination formatting 3