CCC Tutorial: Using Excel’s Paste Special to Copy Complex Items

How often do you paste data into an Excel spreadsheet or from one Excel spreadsheet to another? So often that you use command + V without even realizing it. It is second nature. What happens when you need to copy and paste complex items? This is where Paste Special becomes your best friend.

Paste Special gives you the control to paste and format data in different ways according to your desired outcome. This tutorial will show you how to use Paste Special in Excel.

When you have an Excel file open, copy the data that you want to paste and format.

 

Go to Edit > Paste > Paste Special to see the dialog box.

Paste Special Dialog Box

 

Select your Paste option. You can use the following table to understand each option.

 

Paste option Action
All Pastes all cell contents and formatting of the copied data.
Formulas Pastes only the formulas of the copied data as entered in the formula bar.
Values Pastes only the values of the copied data as displayed in the cells.
Formats Pastes only cell formatting of the copied data.
Comments Pastes only comments attached to the copied cell.
Validation Pastes data validation rules for the copied cells to the paste area.
All using Source theme Pastes all cell contents in the document theme formatting that is applied to the copied data.
All except borders Pastes all cell contents and formatting applied to the copied cell except borders.
Column widths Pastes the width of one copied column or range of columns to another column or range of columns.
Formulas and number formats Pastes only formulas and all number formatting options from the copied cells.
Values and number formats Pastes only values and all number formatting options from the copied cells.

 

Next, select your Operation. These are mathematical operations. The following table describes each operation.

 

Operation Action
None Specifies that no mathematical operation will be applied to the copied data.
Add Specifies that the copied data will be added to the data in the destination cell or range of cells.
Subtract Specifies that the copied data will be subtracted from the data in the destination cell or range of cells.
Multiply Specifies that the copied data will be multiplied with the data in the destination cell or range of cells.
Divide Specifies that the copied data will be divided by the data in the destination cell or range of cells.
Skip blanks Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.
Transpose Changes columns of copied data to rows and vice versa when you select this check box.
Paste Link Links the pasted data on the active worksheet to the copied data.

 

Next, Click OK. That’s it. Once you understand the different options in Paste Special, you will be able to copy and paste complex items quickly and efficiently.

In two weeks, we will be taking this a step further and show you keyboard shortcuts for Paste Special. If you have questions about Excel or you would like us to conduct a training class for your organization, please contact us.

FREE ebook:
Build More Revenue with Less Follow Up Fatigue

Recent Blogs

From Chaos to Control: Jolly Distributor’s CRM Success

From Chaos to Control: Jolly Distributor’s CRM Success

Jolly Distributors had a problem. They grew quickly and now struggled to keep up. With a team of 15, including 5 salespeople, they grappled with lead generation and lead management. The Team Ann Jolly, CEO, ran marketing and sales and Mike Jolly ran the warehouse and...

CRMs – Make It Work for You

CRMs – Make It Work for You

When 2 salespeople contacted the same prospect with different offers, Sarah, CEO of Streamline Solutions Inc (SSI), knew she had a problem. SSI offers litigation support services to law firms and employs 15. SSI was growing. Its 5 salespeople used spreadsheets,...

Less Sales Stress, More Conversions

Less Sales Stress, More Conversions

Business owners and sales managers are stressed about sales. It’s natural. We all want to make sales and generate profits. Stress is generated by low conversion rates. When you convert 20% vs 10% of your leads, you are more profitable and have less stress. What are...