When pasting large data tables from your web browser into an Excel file you’ll probably be displeased to find that they do not match the formatting of the worksheet.
By default Excel will paste the data with the formatting it used on the source web page instead. This is rarely useful, since it’s very unlikely the web page had formatting that is compatible with your document. As you can see in the image above, using the source formatting can result in the inclusion of links, improper font and font size, and a number of other formatting issues.
To make Excel paste the data with destination formatting (i.e. the formatting of the destination Excel worksheet), you’ll need to add a special command to your quick access toolbar. To do this, select File > Options > Quick Access Toolbar. Above the left column, select the Choose commands from dropdown and choose All Commands. Scroll down the list and find Paste and Match Destination Formatting. Select it and click Add, then OK.
Now whenever you wish to paste web data into Excel, click the Paste and Match Destination Formatting button in the Quick Access Toolbar instead of using the standard paste tool. This will result in the data being automatically formatted to match the look of the rest of your worksheet.
Hi, This option doesn’t seem to work in excel 2016 – any ideas?
Thanks