Trending October 2023 # How To Use Paste Special In Excel? (18 Shortcuts &Amp; Examples) # Suggested November 2023 # Top 10 Popular

You are reading the article How To Use Paste Special In Excel? (18 Shortcuts &Amp; Examples) updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Use Paste Special In Excel? (18 Shortcuts &Amp; Examples)

What is Paste Special in Excel?

Paste Special in Excel is a feature that allows you to decide exactly what detail of a value you want to copy and paste from one cell to another.

Shortcuts to open the Paste Special dialog box in Excel for Windows are Alt + E + S or Ctrl + Alt + V. The Paste Special shortcut in Excel for Mac is Ctrl + Cmd + V.

Furthermore, if you want to directly copy-paste values or formats, you can use their respective paste special shortcut in Excel, like Alt + E + S + V for Values and Alt + E + S + T for Formats. We have provided a detailed explanation of these shortcuts in the example section.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Table of Contents

Before we tell you about the handy shortcuts for Paste Special operations, let us first see how to use Paste Special in Excel.

How to Access Paste Special in Excel?

There are various ways to access and open Paste Special in Excel. Here are some common methods:

You can download this Paste Special in Excel Template here – Paste Special in Excel Template

Method 1: Keyboard Shortcut (Alt + E + S)

Select the cells you want to copy.

Press Ctrl + C to copy the cells.

Press Alt + E + S on your keyboard.

A dialog box will open with various paste options. Choose the desired option by pressing the corresponding letter key.

Method 2: Ribbon Menu

Select the cells you want to copy.

Go to the “Home” tab in the Excel ribbon.

Look for the “Clipboard” group.

A menu will appear. Choose “Paste Special“.

Select the cells you want to copy.

A context menu will appear. Choose “Paste Special” from the menu.

10 Paste Special Shortcut in Excel (With Examples) Shortcut 1: Paste Values

Purpose: This shortcut allows you to paste only the values from the copied cells without any formatting or formulas.

Example: Let us see how to paste special values in Excel.

Now, select the destination where you want to paste the values.

Now, Excel will paste only the calculated values from the original range to the destination cells without including the underlying formulas.

Shortcut 2: Paste Formulas

Purpose: This shortcut allows you to paste only the formulas from the copied cells, keeping the cell’s original formatting as it is.

Example:

Let’s say there are two Stationery shops: Shop A and Shop B. We have information about the products, quantities, and prices for both shops. We have already calculated the total price for each product in Shop A. Now, we want to find the total price for Shop B using the “Paste Special” feature as a formula.

Let’s see how to do it:

Copy the cell or range of cells containing the formulas

Select the corresponding column or cell in shop B where you want to apply the formula.

Press Alt + E + S + F. It will directly select “Formulas” in the Paste Special dialogue box.

This will copy the formulas used in the Shop A table and paste them into the selected cells in the Shop B table. However, it will not paste the formatting for Shop A, such as color, bold, etc.

Shortcut 3: Paste Formats

Purpose: This shortcut copies only the formatting of the selected cells, allowing you to apply it to other cells.

Example:

Suppose we have temperature records for two cities: New York and Los Angeles. We have applied color formatting to the New York City table to represent different temperature levels. The three categories we have used are: high, medium, and low. We now need to replicate the same formatting for Los Angeles using Paste Special Shortcut in Excel for pasting format.

Let’s see how to find it using the Paste Special feature.

First, copy the table of New York where the color formatting is already applied.

Then, select the entire table of Los Angeles.

Press Alt + E + S + T. It will open the Paste Special dialogue box with “Formats” already selected.

The temperature values for Los Angeles will now have the same color formatting as the corresponding values for New York.

Shortcut 4: Paste Transpose

Purpose: This shortcut quickly swaps rows with columns and columns with rows when pasting data.

Example:

You have a table with data organized in columns but want it to be organized in rows instead. Instead of copying each cell one by one and manually placing it, we will use the Paste Transpose feature to achieve this transformation in a single step. Let’s see how:

Select and copy the range of cells that you want to transpose.

Choose the cell where you want the transposed data.

Press Alt + E + S + E. It will directly select “Transpose” in the Paste Special dialogue box.

The data will be pasted in rows instead of columns, with the values switched accordingly.

Example:

Let’s see how to do that:

Press Alt + E + S + C. It will automatically select “Comments” in the Paste Special dialog box.

Result will appear as follows:

Shortcut 6: Paste Column Width

Purpose: This shortcut copies and applies the column widths from one set of cells to another set of cells.

You have a table with columns for employee name, ID, and location. Each column has specific widths assigned to ensure it looks good and is easy to read. If you copy and paste the table somewhere else, the formatting will stay the same, but it won’t copy the column widths. For that, we will use the “Paste Special” option to copy the column widths.

Let’s find out how to do it:

Select and copy the entire table

Now, select the table where you want to apply the same column structure and widths.

Press Alt + E + S + W. It will directly select “Column Width” in the Paste Special dialogue box.

Excel will create the second table with the same column structure and widths as the original table, ensuring consistency in formatting.

Shortcut 7: Paste Values and Add

Purpose: This shortcut allows you to add the value of the copied cell with the values at the destination cell and paste the combined value in the destination cell.

Example:

In Example 1, we learned about using Paste Special to copy values. Now, let’s learn how to paste values after adding a specific amount.

Suppose we have a list of employees with their respective salaries, and we want to give each employee a $2000 bonus. Instead of manually adding the bonus to each salary, we can use “Paste Special and add” to quickly calculate the total. It allows us to add a single value to a range of cells without using the SUM function for each cell.

Let us see how we can do this:

Select the source cells to copy the values you want to add.

Select the destination cells where you want to paste the added values.

Press Alt + E + S + V + D. It will directly select both “Values” and “Add” in the Paste Special dialogue box.

The result will show a $2000 bonus added to each employee’s salary.

Shortcut 8: Paste Values and Subtract

Purpose: This shortcut allows you to subtract the value of the copied cell from the value present in the destination cells.

Example:

In Example 6, we saw how to add a particular value in a cell range directly. Similarly, we can also subtract a value from the existing values. Bryn, a salesperson, aims to set aside $500 from his monthly sales. He wants to determine the remaining amount he will have after deducting the savings. Let’s explore how he can use the “paste values” and “subtract” features to calculate this.

First, to copy the values you want to subtract, select the cells and press “Ctrl + C”.

Select the destination cells where you want to paste the subtracted values.

Press Alt + E + S + V + S. It will directly select “Values” and “Subtract” in the Paste Special dialogue box.

The result will show $500 subtracted from Bryn’s sale.

Shortcut 9: Paste Values and Multiply

Example:

Imagine you have a list of fruits along with their respective prices. You intend to purchase each fruit in a quantity of 6. To determine the total cost of each fruit, you need to multiply the price by the quantity. We will use the “Paste Special” feature to accomplish this.

Let us see how to use paste special multiply in Excel:

Copy the cells which have the values you want to multiply.

Select the destination cells where you want to paste the values with multiplication.

Press Alt + E + S + V + M. It will directly select “Values” and “Multiply” in the Paste Special dialogue box.

The result will show the total price of each fruit item.

Shortcut 10: Paste Values and Divide

Purpose: This shortcut pastes the values from the copied cells and divides them by the existing values in the destination cells.

Example:

Imagine you have a list of vegetables and their respective total price. Each vegetable has a quantity of 6. To determine the price per unit for each vegetable, you need to divide the total price by the quantity of 6.

Let’s see how to use paste special divide in Excel:

Copy the source cells that contain the values you want to divide.

Select the destination cells where you want to paste the values with division.

Press Alt + E + S + V + I. This will directly select “Values” and “Divide” in the Paste Special dialogue box.

The result will show the price of each vegetable.

Snapshot: All 18 Paste Special Shortcut in Excel

Note: If you find it difficult to remember all these shortcuts, don’t worry. Simply check which letter is underlined for that particular option in the dialog box. For example, if you want to check the shortcut key for “Formulas”, you can see that the letter “F” is underlined, meaning it is the shortcut letter.

Frequently Asked Questions (FAQs)

Answer: To enable Paste Special in Excel, follow these steps:

Select the last feature, “Options”.

Choose the “Advanced” tab from the “Excel Options” dialogue box.

Scroll down to search “Cut, copy, and paste”.

Tick the first checkbox for the “Show Paste Options button when content is pasted”.

Q2. What is the difference between Paste and Paste Special in Excel?

Answer:

Paste in Excel

Paste Special in Excel

Inserts copied data as it is. Allows the user to choose how the data should appear.

Limited customization options. More customization options for various data types.

Pastes data with formats, formulas, etc. Selectively pastes elements like values, formats, etc.

Answer: Yes, the “Paste Special” option is available in both Microsoft Word and Powerpoint. The available options are different for both depending on the versions as well.