Paste Special Options in Excel

Paste special in excel is a must know for every user of excel, especially those using this powerful software for financial modelling. The first leg is always copying the data (Ctrl + C) and the second leg is deciding what to do with the copied data using the paste special options. You could choose to paste as values, formulas, just the format, paste as link and so much more. In this post, we would take you through the various actions included in paste special options and how they could be beneficial to you. Stay tuned.

Paste

We would call this the normal paste, this option pastes everything as is. What are the steps to paste?

Step 1: Select the cell or range you want to copy by using Ctrl + C

Paste Options regular Step 1

Step 2: Navigate to the new cell you want the output in and paste by right clicking and selecting paste under paste options, or by using Ctrl + V

Paste Options regular Step 2

Paste Special Values

The paste special values option as the name implies pastes the result of the formula from the copied cell. Meaning that the output would be an hardcoded number which is exactly same as the result in the copied cell. In our example for today, we are extending an annual budget for an Xmas party to the next year, and we desire to keep it in the same format as the previous year’s budget.

To begin, we know that our Venue rental, food & drinks, table and chairs rental are the same and we just want to paste special values to the next year’s budget.

To paste special values, simply follow these steps:

Step 1: Select the cell or range you want to copy by pressing Ctrl + C. In our case, the range is from cells D8:D10

Paste special Step 1

Step 2: Move to the new cell or range you want to paste to, right click and click values under ‘paste options’.

Paste Options Step 2

Result:

Paste Options Formula Step 3 1

Using the Paste Special dialog box

Note: For the shortcut of Paste Special values, after copying the cell or range, use the key combination: Alt + E + S + V and hit enter. After pressing the combination, a paste special dialog box would appear as seen below.

Paste Options Step 3

You are not limited to only pasting as values, Use Alt + E + S to bring the dialog box up, then you can choose to paste as either formulas, formats, link, comments and notes, widths, Validation or perform arithmetic functions such as multiplying, adding, subtracting or dividing the value in the copied cell to the values or functions in the destination cell. All you need to do is click on the check box of your preferred action and click ok.

Paste Special Formulas

On to the next one, this section is about pasting as formulas. This is specifically for cells or ranges containing a formula you want on another cell. Using the same example, let’s paste formulas the total food and venue cost in our 2023 column by following these steps:

Step 1: Select the cell containing the formula and copy by pressing Ctrl + C. In our example, this would be cell D7

Paste Options Formula Step 1

Step 2: Navigate to the new cell you want the formula pasted to. In our case, this is cell E7. Then right click and select formulas under “paste options”.

Paste Options Formula Step 2

Result:

Paste Options Formula Step 4

Paste Special Formats

Paste special formats is used to copy the formats of a cell or range to another cell. With this action, just the formatting of a cell such as font size, font color cell color etc are copied to the other cell. Numbers and formulas are left out. To paste as formats is just two steps away.

Step 1: Select the cell (D9 in the example below) containing the formatting you desire and copy by pressing Ctrl + C.

Paste Options Formatting Step 1

Step 2: Right click on the new cell and click formatting under paste options.

Paste Options Formatting Step 2

Result:

Paste Options Formatting Step 3

Paste Special Links

Paste special link is an easy way to link to another work sheet or cell. Take for example we need to link all 20X2 data in our Xmas sheet to a new sheet, how do we do it? Normally, when linking we would need to go through the long process of selecting the cell we want to link to, and then do some further copy and paste, and that’s if the first cell you linked to is contiguous, i.e not separated by any cell, row or column. Scrap all that long journey now and start linking with these shortcuts:

Step 1: Select the cell you need linking to and copy by pressing Ctrl + C

Paste Options Links

Step 2: Link by pressing the combination Alt + E + S + L, and then press enter. Note, you can also link by clicking the icon below under paste special options.

paste options links2

Leave a Comment