13 Must-Know Paste Special Shortcuts in Excel

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

You’re probably all too familiar with the basic copy-and-paste function in Excel, but did you know about the powerful capabilities of Paste Special? With Paste Special, you can select specific elements to copy, such as formulas, formatting, or values, and paste them into your desired location. With that said, let’s explore 13 tricks that Paste Special can do, from applying formulas to hard coding values, copying comments and notes, and even transposing data. By the end, you’ll feel confident maximizing Paste Special in your Excel projects.

13 Paste Special Shortcuts You Need to Know

  1. Paste Formulas
  2. Paste Values
  3. Paste Formats
  4. Paste Comments and Notes
  5. Paste Data Validations
  6. Paste Operations
  7. Paste Conditional Formatting
  8. Paste and Skip Blanks
  9. Paste Transposed Data
  10. Paste Column Widths
  11. Paste Without Borders
  12. Paste Links
  13. Paste Images (with Links)

Introduction to Paste Special

So, you’re a pro at copying and pasting in Excel. But did you know that there’s a lot more you can do with the Paste Special feature? It’s not just about duplicating data but about picking and choosing what you need.

With Paste Special, you can copy formulas, formatting, values, notes, data validation, and more. It’s a powerful tool that can save you a lot of time and effort.

In this article, we’ll explore 13 tricks with which Paste Special can amaze you. I’d suggest watching the video below for a complete step-by-step breakdown of each trick. Otherwise, keep reading, and you’ll learn what each one does and how to use it. 

Applying Formulas with Paste Special

To apply a formula with Paste Special, first, copy the cell containing the formula you want to apply. Then, select the range of cells where you want to apply the formula. Next, open the Paste Special window using the keyboard shortcut CTRL + ALT + V. 

In the Paste Special window, select the “Formulas” radio button under the “Paste” section and click “OK.” By doing this, you can apply the formula to the selected range of cells without changing the cell references. This is particularly useful when you have a formula that you want to apply to a large dataset.

Another shortcut you can use to paste only formulas is ALT + E + S + F.

Hard Coding Values

Hard coding formulated values come in handy when sharing documents with someone who doesn’t have access to the original data or if you don’t want the output able to be changed.

To hard code values, select the cells you want to copy and press CTRL + C to copy them. Then, use the shortcut ALT + E + S + V to open the Paste Special window and hit “Ok” to paste the values. 

Alternatively, you can navigate to the Paste Special options under the Home tab by clicking on the arrow below Paste. In the drop-down, select the icon button for Values. This will replace the formulas with the actual values.

paste special values

Copying and Pasting Formats

Copying and pasting formats in Excel is a simple and powerful feature that can save you a lot of time. Instead of manually formatting each cell, you can copy the formatting from one cell and apply it to others. This is especially useful when you want to keep a consistent look and feel throughout your data.

To copy the formatting from one cell to another:

  1. Select the cell with the formatting you want to copy, and press CTRL + C to copy it.
  2. Select the cell or range of cells where you want to apply the formatting and press ALT + E + S + T to bring up the Paste Special window. Click “Ok.”

Copying Comments and Notes

Copying comments and notes in Excel can be quickly done with the Paste Special feature. This is particularly useful when you want to include the same information for multiple cells or sheets.

To copy a comment or note, simply select the cell with the comment or note and copy it with CTRL + C. Next, select the cell where you want to paste the copy and use the shortcut keys ALT + E + S + C. The comment or note will then be copied to the new cell.

Applying Data Validation

Data validation is a powerful tool that can help ensure your data is accurate and consistent. With data validation, you can set up rules restricting the type of data entered into a cell. For example, you might set up a rule that only allows numbers between 1 and 100 to be entered into a specific cell.

To apply data validation mimicking another cell, first, copy the cell or cells containing the data validation. Next, select the range of cells to paste the validation and hit the shortcut keys ALT + E + S + N.

Once you have set up your data validation rules, you can test them out by trying to enter data that violates the rules. If the data is rejected, you’ll see an error message explaining why the data is invalid.

paste special data validation

Performing Mathematical Operations

When working with data in Excel, performing mathematical operations is a common task. The Paste Special feature in Excel offers several options to help you quickly adjust your numeric values or formulas with just a few keystrokes.

For example, let’s say you forgot to add a shipping cost to your total costs. You can use the Paste Special feature to recalculate your numbers by copying the shipping cost average you identified in a separate cell, then highlighting your total cost cells. 

Next, open the Paste Special window and choose the radio button for Multiply under the Operation section. Alternatively, you can use the Paste Special shortcut ALT + E + S + M. The result will show a formula of your total costs factoring in the shipping cost average.

If you’re dealing with empty cells, you may encounter a VALUE error. This can be easily fixed by applying an IFERROR function around your new formula to return a blank cell.

In addition to multiplication, the Paste Special feature also offers basic mathematical operations such as addition, subtraction, and division. 

paste special operations

Applying Conditional Formatting

While we did cover copying and pasting formats already, sometimes you just want to paste the conditional formatting and not all formats associated with the original cells. This can be done in short order using the Format Painter under the Home tab.

To apply this type of formatting, select the already formatted cells and copy them. Then, open the Home tab and select the Format Painter icon. 

paste using format painter

Highlight the cells to which you want to paste the conditional formatting, and when you let go of the mouse button, your new cells will have the copied format.

Skipping Blank Cells

When copying and pasting data in Excel, you may come across blank cells that you don’t want to overwrite. With the Paste Special feature, you can copy and paste values while skipping blank cells to avoid accidentally overwriting anything.

To use this feature:

  1. Select the cells you want to copy, including any blank cells you want to skip.
  2. Select the range of cells where you want to paste the values.
  3. Hit the shortcut key ALT + E + S to open the Paste Special window, select the Values radio button, and check off the “Skip blanks” option at the bottom.
  4. Hit OK, and Excel will paste the values while skipping any blank cells, leaving the original content.
paste special skip blanks

Transposing Data

Now, let’s talk about transposing data. This helpful feature in Excel allows you to convert rows into columns. It’s great when you have a lot of data that you want to reorganize or when you want to create a new table based on existing data.

To transpose data, you can use the Paste Special feature in Excel. First, select the cells that you want to transpose. Then, right-click and choose “Copy” or use the keyboard shortcut CTRL + C.

Next, select the cell where you want to paste the transposed data. Right-click and choose “Paste Special” or use the keyboard shortcut ALT + E + S. In the Paste Special window, check the box next to “Transpose” and click “OK.” Your data will now be transposed.

paste special transpose

You can also use the Paste Special shortcut ALT + E + S + E to transpose your data without going through the Paste Special window.

Matching Column Widths

When copying and pasting data from one table to another, matching the column widths is essential to ensure consistency and readability. Excel’s Paste Special feature makes it easy to do this quickly and efficiently.

To match column widths, first, copy the headers of the original table. Then, select the range of data in the pasted version. Next, press ALT + E + S + W, and hit OK to stretch the columns for an identical dataset.

Removing Borders

Now that you’ve learned about the many valuable features of Paste Special in Excel let’s talk about removing borders. Borders are a great way to visually separate cells and make your data easier to read, but sometimes they can be distracting or unnecessary. Fortunately, Paste Special has a quick and easy way to remove them.

First, select the cells you want to modify to remove borders from a range of cells and copy them. Then, open the Paste Special window using the shortcut ALT + E + S + X. Now that the radio button is toggled to paste all except borders, hit OK. 

This will remove all borders from the selected cells while keeping the formatting intact.

paste special all except borders

Paste Link

With Paste Link, you can link cells from one worksheet to another so that any changes made to the original worksheet will be reflected in the linked cells. This is especially useful when you have a lot of data to work with and don’t want to manually update each cell.

To use Paste Link:

  1. Copy the cell or range of cells you want to link.
  2. Navigate to the cell or sheet where you want to paste the link.
  3. Instead of using the regular Paste function, use ALT + E + S shortcut keys to bring up the Paste Link dialog box and click the button for Paste Link.
paste special paste link

To complete the same Paste Special task, you can also just copy the cell and use the shortcut keys ALT + H + V + N.

If you update the original source cell, any changes to the original worksheet will be reflected in the linked cells.

Paste Images

Did you know you can also paste images using the Paste Special feature in Excel? This is a great way to add visual aids to your spreadsheets, especially if you’re working with data that needs to be presented clearly and concisely.

To paste an image, first, you need to have the image copied to your clipboard. You can do this by right-clicking on an image and selecting “Copy” or using the keyboard shortcut CTRL + C. Once you have the image of your range copied, select the cell where you want to paste it and use the keyboard shortcut ALT + H + V + U.

This is great for sharing information you don’t want others to alter. However, this is just a snapshot or static image of data. What if you wanted to perform the same function but have the image updated with each change?

Well, you can!

You can take a bit of the last Paste Special trick and create a pasted image with a link leading back to the original data. To do this, copy your desired range again and use the shortcut ALT + H + V + I.

Now every update you make in your source range will reflect in the pasted image.

Conclusion

As you can see, the Paste Special feature in Excel is a convenient tool, giving you the power to pick and choose from an original data set. Whether you want to copy and paste formulas, values, or formats, or you want to transpose your data, match column widths, or skip blank cells, Paste Special has got you covered. And with the help of some keyboard shortcuts, you can perform these tasks even faster than ever before.

By mastering these 13 tricks, you can become a pro at working with data in Excel and impress your colleagues and supervisors with your skills. So the next time you need to copy and paste data, don’t settle for the basic method. Instead, try out some of these Paste Special features and see how they can make your life easier.

Leave a Comment