Excel: Increment all cells in a column by greater than 1

I have a tracking sheet for exercise, and I’m about to move to a new sheet. I need to bump all of the numbers from the “weight” column by 5, preferably not manually because it is 111 rows, and I am lazy. The numbers also jump around somewhat, for example:

13.5
14
14.5
13.5
14.5

And I just want to increase those by 5.

Let’s say the number 13.5 is in cell B2. Type this into cell C2, then press enter:

=B2+5

Next, click on cell C2 (a regular down-up click), then hit Ctrl C.

Next, down-click on C3 (keep left mouse button down), and drag mouse downward until you get to the last row, all the while staying in column C. Then release mouse button.

Hit Ctrl V.

You can also use SEQUENCE.

=SEQUENCE(A, B, C, D)

Where:

A is the number of rows you want
B is the number of columns you want
C is the number to start with
D is your incremental step amount

So, “=SEQUENCE(1, 10, 5, 5)” returns:

5 10 15 20 25 30 35 40 45 50

Not they are on different sheets, and I would prefer not to have dependencies. I basically want to copy a column from one sheet, then add 5 to each cell in that column.

Sequence doesn’t work because the numbers do not follow a simple sequence. Thanks, though.

So paste that column into column A on the new sheet. In B1 (or preferably B2, since row 1 should be a header) make it “=A2+5”. Then you copy that cell down the rest of column B.

Also, if you select cell B2, notice that the bottom-right of the outlined cell has a small bolded square on it. If you hover over that square, your cursor turns into a “+” sign. Click that square, and drag it down the column.

If you then want to get rid of Column A, select Column B and copy it (Ctrl+C), select Column A, and hit Ctrl+Shift+V (or right-click on Col A, Paste Special->Values).

Now delete Column B. You’re all set.

You can do this without having to create formulas.

In any blank cell, enter 5, then copy the cell.

Then select the column with the values you want to increment, right-click anywhere in it and choose Paste Special. Under Operation, select Add and hit OK.

Whoa! That’s a fun one.

To avoid dependencies, you might get into the habit of copying then pasting values. You can do this “in place” without using a new column – just select something with formulas in it, copy, and “Paste Values” back over itself. The shortcut is to hold down Alt then type H-V-V.

You can do this with an entire row, column, or spreadsheet in one fell swoop, stripping the whole thing of all formulas, and cementing the present values in place permanently.

That did it. Thanks.

That is one of the features missing in the web version of Excel, by the way.

Yup, that’s the one I came to say.