How To Create Copy Of Worksheet In Excel

The Paste Options and Paste Special tools in Microsoft Excel give you control over how you copy and paste content. Learn how to use these tools.

Image: Anatoliy Babiy, Getty Images/iStockphoto

Copying and pasting in Microsoft Excel can be tricky. You may want to copy the contents of one cell to another. Or you may want to be selective about what you copy, choosing to copy the entire cell, just the data, just the formula, just the formatting, or a combination of items. The good news is that you can master copy and paste in Excel just by using the Paste Options and Paste Special tools. By choosing the right option ahead of time, you can control what you paste, so it comes out right the first time.

LEARN MORE: Office 365 Consumer pricing and features

I'm using Excel from an Office 365 subscription, but the tips and tricks offered here should work with the past few versions of Excel as well. If you want to follow along with my steps, launch Excel and open or create a spreadsheet with a few rows and columns of numbers. Sum up the total of each column except for the last column. Apply special formatting to the bottom totals rows as well (Figure A).

Figure A

Now, copy one of the total cells of any column. Move your cursor to the blank cell in the last column where a total should be. Right-click to call up the popup menu. Move your cursor to the first icon under the Paste Options section in the menu. The first icon is for Paste--this is the icon you would choose when you want to paste everything, including the formula and the formatting. Remember that by default when you copy a cell with a formula in Excel, the cell references are relative, meaning the cells included in the formula refer to your new row or column and not the row or column from which you copied the cell (Figure B).

Figure B

Delete the item you just copied. Copy one of the other total cells again. Right-click on the empty cell again. Move to the second icon in the Paste Special section. This icon is for Value. This icon pastes the exact static number or text in the cell you copied without any formulas. You might use this if you need to paste the same value in multiple cells and don't want Excel to include a formula (Figure C).

Figure C

Delete the item you just copied. Copy one of the other total cells again. Right-click on the empty cell again. Move to the third icon in the Paste Special section. This icon for Formula pastes a formula with the relative cell references but does not include the formatting of the source cell (Figure D).

Figure D

Delete the item you just copied. Copy one of the other total cells again. Right-click on the empty cell again. Move to the fifth icon in the Paste Special section. (The fourth icon for Transpose is used to rearrange a group of rows into columns or columns into rows, so we're not going to cover the Transpose option in this article.) The fifth icon for Format copies just the formatting of the source cell and not the content or formula. This may be useful for copying the same formatting into a series of blank cells, so they're already formatted the way you want (Figure E).

Figure E

Delete the item you just copied. Copy one of the other total cells again. Right-click on the empty cell again. Move to the sixth and final icon in the Paste Special section. This icon for Paste Link copies the value of the source cell and maintains a link to it, creating an absolute reference. If you change the value in the source link, the value then changes in the pasted cell. You may use this if you want to maintain the same value in any pasted cells (Figure F).

Figure F

The options in the Paste Options section should suffice for most copy and paste scenarios that you would need in Excel; however, you can delve further by reviewing the Paste Special section. Delete the item you just copied. Copy one of the other total cells again. Right-click on the empty cell again. Move to the Paste Special section. All of the items in the Paste Options section are duplicated in the Paste Special section, although Paste Special throws in a few extra tricks.

The first section for Paste contains icons for Paste and Formulas but also icons for Formulas and Number Formatting, Keep Source Formatting, No Borders, Keep Source Column Widths, and Transpose, so most of these let you determine whether to use the source or destination formatting for a pasted cell (Figure G).

Figure G

The Paste Value section offers icons for Values, Values & Number Formatting, and Values & Source Formatting. Again, these let you decide whether to use the source or destination formatting for a pasted cell (Figure H).

Figure H

The Other Paste Options section offers icons for Formatting, Paste Link, Picture, and Linked Picture (Figure I).

Figure I

Finally, to see the full array of paste options, click on the link for Paste Special from the Paste Special menu (Figure J).

Figure J

Microsoft Weekly Newsletter

Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays

Also see

Microsoft Office 365: A cheat sheet (TechRepublic)
30 things you should never do in Microsoft Office (free PDF) (TechRepublic)
Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
How to add a drop-down list to an Excel cell (TechRepublic)
Excel Ideas: An intelligent data visualisation tool (TechRepublic)
How to fix common printing problems in Microsoft Excel (TechRepublic)
Microsoft brings ability to turn photos of table data into Excel spreadsheets to iOS (ZDNet)
You've been using Excel wrong all along (and that's OK) (ZDNet)
Use your phone to turn a photo of data into a Microsoft Excel spreadsheet in a snap (CNET)
Microsoft Office vs Google Docs Suite vs LibreOffice (Download.com)
Get more must-read Microsoft coverage (TechRepublic on Flipboard)