Hi guys,

1) I have a column of cells filled with different numbers. I want to increase the number for each of these cells by 1. Is there any way of doing it at once?



2) If I want to combine the cells in column A and B for multiple rows. How to do it at once instead of doing it row by row?

thx.

1) Enter 1 in an empty cell somewhere and copy it.
Select the range or the cells you want increased.
Right Click - Paste Special -Check "add" - OK

2) In C1 enter =A1&B1 then drag down as needed or double click the cross-hair at the right lower corner of the selected cell

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thx!

1) is very useful when the number of cells to modify is not too large for click & drag (sometimes I need to handle up to a thousand cells in a single column).

2) Not sure what C1 is. The only better way I currently use is to combine the cells in the 1st row, and then click & drag the cross-hair down for all succeeding rows. But like again, that wouldn't be a good way for handling a very large number of rows.

Here's a general method for copying a formula from one cell to many. I'll pretend you're copying that value 1 that Peco told you about, but it'll work with anything.

Let's say you have continuous data (ie no empty cells) in column A, running from row 2 down to row 99 999. You want to create a 1 in B2:B99999. Do this:

1. Enter 1 in B2.
2. Copy B2. I usually do that by hitting <Ctrl-Ins>, because I'm a grizzled old Windows user; but you can use <Ctrl-C>, or <Alt-H>,C, or mouse to the Copy icon, or whatever pleases you.
3. Now there's a dotted line blinking around B2, showing that it's ready to be copied somewhere. Don't complete the copy yet; just hit <Left> on in some other way move the cursor to A2, or indeed to anywhere between A2 and A99999.
4. Hit and release the <End> key.
5. Hit and release the <Down> key.
6. If there were no empty cells between A2 and A99999, you're now at the last cell in the range, ie A99999. Hit <Right> or in some other way move the selection one column to the right, back to B99999.
7. Hold down the <Shift> key.
8. While still holding down <Shift>, strike and release <End>
9. Still not releasing <Shift>, strike and release <Up>.
10. You're now back at B2 where you started, but you have the whole range B2:B99999 selected—and B2 still has the blinking border around it indicating that it's ready to be copied. So hit <Enter> to copy B2 to the whole range B2:B99999. (The fact that it'll copy over itself is of no consequence.

There, it's done. I took 10 careful steps to describe the whole thing, but if you know all the keystrokes you can collapse them mentally into just this:

1. Copy the formula at the top.
2. Move to your index column and <End><Down> to the bottom of the range.
3. Move back to your formula column and <Shift-<End,Up>> to the top again.
4. Complete the copy with <Enter>.

This obviates having to scroll down and up a long, long time. But it depends on there being at least one column in your data that is unbroken from top to bottom.

Thx!

<End><Down> to reach the bottom of column A. <<Shift>+hold><End><Up> to select the same range in column B. <Enter> to paste the copied data in all B cells.

Please read my answer before answering. I NEVER talked about dragging, only selecting your range before Paste Special Add

Select the first cell - While holding down the Ctrl and the Shift key hit the down arrow. Voilà -

As for your second request, I do not understand what you want.
EDIT : on second thought i added you to my "ignore list"

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post