Updating Values En Masse

The Paste Special command under the Edit menu has a few operations that allow you to update a range of cell values all at once. They are Add, Subtract, Multiply, and Divide and they are in the Operation section of the Paste Special dialog box.

Assume you have a list of merchandise and corresponding prices. If you need to update all your prices to reflect a 5% increase, you can do so without using a helper column and a formula. To do this, put 1.05 in an unused cell (we’ll delete it later) and copy it.

PasteMult1

Then select the range you want to update and choose Paste Special.

PasteMult2

Choose the Multiply radio button

PasteMult3

And the range will be increased by 5%

PasteMult4

You can then delete the cell with 1.05 in it.

2 thoughts on “Updating Values En Masse

  1. What is so great about this technique, is that it preserves formulas in the cells you are updating.

    If you know in advance that you will be “updating en masse”, try representing the values as formulas; =123 instead of 123. After (multiple) updating, you will always find how each cell was updated by looking at its formula; e.g. =((123)+10)*1.05

  2. I didn’t realize this function existed. That’s easier than the method I was using, which was this code:

    Sub text2num()
    Dim cell As Range

    For Each cell In Selection
    cell.Formula = cell.Formula
    Next
    End Sub

    This works too, but it’s easier to explain your way to the other people I work with. People who get clammy hands and cold sweats when you tell them to open the VBA editor. People who are probably better off leaving VBA alone.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.