MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding constant to column of numbers

Posted by Joan L. on January 14, 2002 11:15 AM

Is there a way to add a constant to a column of numbers, without creating a separate column to do so?

In other words, at the end of each quarter, I want to add a constant number to all the amounts in a single column. What comes to mind is to insert a column, and create the formula in B1: =A1 + X, and copy it down. (A1 being the first cell with the amount I want to add the constant X to.) Then eliminate the first column (A), leaving the second column (B) in its place with the amounts I need.

Is there another more efficient way to just add a constant to all the numbers in the column, in the first place?

THANKS (as usual)!

Posted by Loren on January 14, 2002 11:23 AM

type your constant in an empty cell and copy it. Select your numbers, Edit Paste Special Add.
that's it. Works for subtract, multiply, divide too

Posted by Aladin Akyurek on January 14, 2002 11:23 AM

Put X in an empty cell. Copy it.
Activate the target cell.
Activate Edit|Paste Special => Add.

You can turn this even into a macro, I guess.



Posted by Joan L. on January 14, 2002 11:33 AM

Thanks, Loren. So fast. I like Alan's answer too, but your solution is a slam dunk. There are all these things in Excel that I just haven't ever gotten to, and wouldn't without your help. You guys are incredible. So many thanks for your diligence with us amateurs!