Q. What is the difference between a regular paste in Microsoft Excel and Paste Special?
A. As an accountant, you know how to copy or cut and paste in Microsoft Excel. On PCs, it's as simple as Ctrl+C or Ctrl+X followed by Ctrl+V. What you may not know are all the different ways Excel's Paste Special options can make your life easier.
This article explores some of the features that Paste Special offers, such as pasting formulas as values; increasing numbers by a percentage; pasting numbers with the destination formatting; converting a positive number into a negative number; inserting Excel cells, tables, or worksheets into Microsoft Word and PowerPoint; and inserting Word content into an Excel file.
Note that this content was based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Paste as values
Paste Special can paste the contents of your spreadsheet as values. This is very useful when, for example, you are emailing an Excel workbook that contains formulas to a client. You want to ensure all your calculated values are delivered correctly and not accidentally altered due to changes in formula references. To change all formulas to values, simply select the cells you want pasted as values, or select the entire worksheet.
Once you have selected the cells or worksheet, copy the cells by clicking Ctrl+C on a PC or Cmd+C on a Mac. Place the cursor where you would like to paste the cells, or select the entire worksheet. On a PC, click Home on the Ribbon, then click the drop-down arrow under Paste in the Clipboard group. Choose Paste Special (or click Ctrl+Alt+V), select Values, and click OK. Paste Special is located on the Edit tab on a Mac.
Increase numbers by percentage
Paste Special also can be used to increase numbers by a percentage. To do this, enter the amount of the percentage you want to increase the numbers by in a blank cell. The percentage should be entered as a decimal added to the number 1, as this will be the value multiplied by the numbers you want increased. For example, if you want to increase numbers by 20%, enter 1.2 in a blank cell. Copy the cell that contains the percentage increase, and then select the cell or range of cells that contain the numbers that should be increased.
Let's look at an example. Say you want to increase all salaries by 20%. Select and copy cell D1, the cell that contains the amount of the percentage increase. Then, select cells B2:B12, the cells that contain the numbers (salaries) that need to be increased 20%. Click Home on the Ribbon, then click the drop-down arrow under Paste in the Clipboard group. Choose Paste Special, then select Multiply, as shown in the screenshot below.
Click OK. All of the salaries have now changed to reflect the 20% increase. See the updated salaries in the screenshot below.
Destination formatting
Paste Special can paste numbers with the formatting of the destination where they are being pasted. To do this, copy the numbers to be pasted. Choose Paste Special from the Home tab, select Values and number formats, and click OK. For example, in the screenshot below, we want to copy the numbers in cells A1:J1 and paste them in cells A3:J3, retaining the purple, outlined formatting on row three. A simple paste would retain the formatting from row one. Using the steps above, however, will paste the numbers on row three and retain the purple, outlined formatting.
Change positive numbers to negative ones
Converting a positive number into a negative number is done much the same way as a percentage increase. You can use Paste Special to multiply the positive number(s) by -1. Enter -1 into a blank cell and copy that cell. Then select the cells that need to be converted to a negative number and choose Paste Special from the Home tab. Select Multiply and click OK.
Pasting into Word and PowerPoint
Although a simple copy and paste can copy the contents of Excel into Word and PowerPoint, it will not update the Excel contents with Excel capabilities. Paste Special can be used to paste Excel cells, tables, and worksheets into Word and PowerPoint while retaining Excel capabilities.
To do so, copy the Excel cells, table, or worksheet. In the Word or PowerPoint document, place the cursor where you would like the Excel contents pasted and choose Paste Special. Select Microsoft Excel Worksheet Object and click OK. See the screenshot below.
When using Paste Special, simply double-click on the pasted Excel cells to edit. The screenshot above shows a PowerPoint document after I double-clicked on the pasted Excel cells. I then had all Excel capabilities to edit the Excel portion of my slide. The same applies to Word.
You can also paste Word data into Excel and retain Word capabilities. Again, a standard copy and paste would transfer the actual data, but if you are looking to retain the content as a part of Microsoft Word, you must use Paste Special. Copy the content from Microsoft Word that you want to transfer to Excel. Place your cursor in Excel where you would like the Word content to be pasted, and choose Paste Special. Select Microsoft Word Document Object and click OK. See the screenshot below.
The image from Word pastes as an object instead of just pasting the Word data, as shown in the screenshot below. When you double-click the object, you have all the capabilities of Word to edit.
Transpose and video
Transpose is another feature available with Paste Special. It can easily convert horizontal data to vertical and vertical data to horizontal. We covered this feature in detail in the February 2022 JofA.
I have created an Excel workbook with examples of using Paste Special, and you can view a video below.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
