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.
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
Let’s jump right in.
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.
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:
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.
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!
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
Thank you. I would never have thought of paste add to convert text to numbers.
Glad it helped 🙂
I hadn’t see that paste special before! learn something new everyday. Thank you!
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!
Excel is a BIG place and I learn stuff all the time too 🙂
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.
Great suggestion … thanks!
Wow! This is crazy helpful! Thanks so much.
Tina … yay, glad it helps!!
Mind Blown! Thanks so much!
What a lifesaver Paste add 0 will be working with exported files. Thank you!
Love this! I use paste special values all the time, but didn’t know there were so many other ways to use paste special.
Thank you very much for showing these techniques. Did not know there were so many ways to use paste special. Very helpful!