Paste Special Add

In addition to the standard Copy and Paste commands, Excel offers a variety of options for pasting values and formulas. In this post, I’ll chat about one of those options … Paste Special Add. It works with values AND formulas. Let’s dig in.

Overview

Before we get into the details, let’s zoom out for a moment. In Excel, we can select a cell and perform a standard Copy. We can use the copy command icon or a keyboard shortcut (Ctrl C for Windows and Cmd C for Mac).

If we then perform a standard Paste, Excel actually pastes many things, including for example the cell formula or value, the cell formatting, style, conditional formatting, and so on. Often, a standard Paste works just fine. However, there are times when we want to be more surgical about which attributes we paste.

Excel has a Paste Special command that allows us to be specific about which element we actually paste. There are many Paste Special options available, and in this post, we’ll cover one such option: Add.

We’ll use the Paste Special Add option in three ways:

  • Add values
  • Convert text to number
  • Formulas

Let’s jump right in.

Add Values

If we copy a cell or range of cells, we can then select a destination and do a Paste Special Add to have Excel add the values together. This absolutely could also be done with formulas as well. Which method you use really depends on what you are working on and personal preference.

Copy Single Cell

You can copy a single cell value when you want to add the same amount to each cell in the destination range.

For example, let’s say we have a list of amounts, like this:

Now let’s say we want to add 5 to all of them. We could type the number 5 in a cell and copy it. We then select the destination range like this:

And then click the Paste > Paste Special command. In the resulting Paste Special dialog, we select Add as shown below:

We click OK, and bam … 5 is added to each of the values in the destination range:

Copy Range of Cells

Instead of copying a single cell, you can also copy a range of cells when you want to add different amounts to the destination range.

For example, let’s say we have a list of Amounts, like this:

And let’s say we want to adjust this list by adding a different value to each corresponding cell. The list of adjustments is in a range like this:

We do a standard Copy and then select the destination range. We do a Paste Special Add, and bam:

Again, these results could also be attained by writing formulas, but this is another option. In addition to adding values, Paste Special Add can also be helpful when you want to convert a number stored as text to a numeric data type.

Convert text to numbers

Sometimes if we import or copy/paste a bunch of data, values that appear to us to be numbers are actually stored by Excel as text values. Left-aligned values can indicate this situation because it is the default alignment for text values:

As this is Excel, there are several ways to remedy this situation, and one way is Paste Special Add. Here’s how. We basically select any empty cell and do a standard Copy.

Then, we select the range and do a Paste Special Add. What happens is that Excel adds 0 to the destination cells. By forcing Excel to perform this operation, we cause Excel to change the data type from text to number. Right-aligned cells are an indication of numeric values because it is the default alignment for numbers:

In addition to using Paste Special Add with values, we can also use it with formulas. The formulas can be on the Copy side, the Paste side, or both sides.

Formulas

Let’s say we have a bunch of formula cells like this:

If we Copy a cell that contains the value 10, and then do a Paste Special Add, we end up with this:

As you can see, Excel preserves the original formulas and simply updates them to add the copied value, 10.

This technique also works when the cell we copy contains a formula. For example, let’s say we copy a cell that contains this formula:

=24*60

Then we do a Paste Special add

As you can see, this preserves the original expression and uses the addition operator to combine the copied formula.

Conclusion

Excel is a big place, and there are many ways to accomplish any given task. Knowing additional options enables you to select one based on the workbook.

If you’d like a safe place to practice these Paste Special Add techniques, just use the file below.

And if you have any thoughts, comments, or questions … or other Paste Special options you use frequently, please share by posting a comment below, thanks!

Sample file:

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

14 Comments

  1. Ann Gallagher on April 14, 2021 at 7:17 am

    Thank you. I would never have thought of paste add to convert text to numbers.

  2. Nix Wilcox on April 14, 2021 at 8:29 am

    I hadn’t see that paste special before! learn something new everyday. Thank you!

  3. Shair on April 14, 2021 at 8:58 am

    Wow – I learn something new with every post! I have used special paste – multiply by 1 to change a “text” number to an actual number before, but never knew I could add formulas onto formulas. 20+ years of Excel and still learning. Thank you for all you do!

    • Jeff Lenning on April 14, 2021 at 8:59 am

      Excel is a BIG place and I learn stuff all the time too 🙂

  4. Alex Blakenburg on April 14, 2021 at 9:18 am

    I see so many people use Paste Special “Values” when in 8 out of 10 cases they would be so much better off using Paste Special “Values and number formats”. The main benefit of including the number format is that dates retain their formatting and don’t just show up as a 5 digit number.
    The easiest way to access that option is to place it on your QAT.

  5. Tina Powell on April 14, 2021 at 9:37 am

    Wow! This is crazy helpful! Thanks so much.

  6. Janice Costley on April 14, 2021 at 11:53 am

    Mind Blown! Thanks so much!

  7. SusieB on April 15, 2021 at 3:54 am

    What a lifesaver Paste add 0 will be working with exported files. Thank you!

  8. Deb on April 15, 2021 at 7:47 pm

    Love this! I use paste special values all the time, but didn’t know there were so many other ways to use paste special.

  9. Anne on April 15, 2021 at 9:18 pm

    Thank you very much for showing these techniques. Did not know there were so many ways to use paste special. Very helpful!

Leave a Comment