filling a month down a column without incrementing it

[miconian]

I often make worksheets that have a column for the month. Many records contain the same month in the "month" column.

Say my data occupies the range B2:H10, and column A is the "month" column. I decide that I want to assign the month "January" to every item in the list. If I enter "January" in cell A2 and double-click the fill button, the months will be incremented as they fill down the column. I can copy and paste into the entire column, but that's annoying (is there a shortcut key combo to select from the current cell down to the last empty cell in the same column? Even if there is, there must be an easier way than copying and pasting.)

What would be ideal is if I could just turn off the increment feature of the fill button event.

Thanks.

 

[Yogi Anand]

I often make worksheets that have a column for the month. Many records contain the same month in the "month" column.

Say my data occupies the range B2:H10, and column A is the "month" column. I decide that I want to assign the month "January" to every item in the list.<font color="red"> If I enter "January" in cell A2 and double-click the fill button, the months will be incremented as they fill down the column.<font color="black"> I can copy and paste into the entire column, but that's annoying (is there a shortcut key combo to select from the current cell down to the last empty cell in the same column? Even if there is, there must be an easier way than copying and pasting.)

What would be ideal is if I could just turn off the increment feature of the fill button event.

Thanks.

Hi miconian:

Welcome to MrExcel Board!

Right after you have clicked on the fill handle (marked in red above), with the cells A2:A10 selected, and A2 as the active cell,

click in the formula bar where you see January, hold the CTRL key and press ENTER -- bingo, cells A2:A10 will all now be January.

Will this work for you?

 

[Tazguy37]

Yogi:
I was trying to get this to work, and it took me a minute to figure out the process. What I did was: Entered "January" in cell A2 (but did not hit enter or anything). Then I dragged the fill handle down several rows, and saw the pop-up change (February, March, etc). Then I held the CTRL key, and released the mouse button. Behold! All the cells said January.

(Actually, a simpler way is to hold down the control key then drag the fill handle down.) Both worked for me. I think maybe I was just confusing myself reading your directions. Very neat trick.

 

[Yogi Anand]

Hi Taz:

I am sorry if my description was confusing. The reason I described the procedure the way I did in my post, the OP has already gone throught the steps of creating a January through September list in cells A2:A10, cells A2:A10 are selected, cell A2 is the ActiveCell ,and A2 is equal to January, so to accomplish what the OP wants, the only additional step required is ...

click in the FormulaBar, hold the CTRL key and press ENTER

so this is the botom line, all the statements in the first paragraph are just to lay the background.

 

[Yogi Anand]

Yogi:
I was trying to get this to work, and it took me a minute to figure out the process. What I did was: Entered "January" in cell A2 (but did not hit enter or anything). Then I dragged the fill handle down several rows, and saw the pop-up change (February, March, etc). Then I held the CTRL key, and released the mouse button. Behold! All the cells said January.

<font color ="red">(Actually, a simpler way is to hold down the control key then drag the fill handle down.<font color="black">) Both worked for me. I think maybe I was just confusing myself reading your directions. Very neat trick.

Hi Taz:

If the cells involved are more than 1 pageful, then having to drag down to wherever, may not be that convenient. In the first method, only clicking on the top most cell of the selection (the ActiveCell) is required -- and everything is in view all the time -- just a thought!

 

[PaddyD]

or right-click on fill handle, drag down, release, select copy cells...

 

[Tazguy37]

That works, too, PaddyD. I keep forgetting about that one.

 

[miconian]

Yes, the truth is that the table goes on for thousands of lines, and dragging the fill handle down is not practical. I'll try the other solution in the morning when I have access to Excel again. Many thanks.

 

[Krishnakumar]

A keyboard shortcut,

Select the Range including the month to be filled down and

Ctrl+D

for across Ctrl+R

Kris

 

[miconian]

The problem with the ctrl-D solution is that I must first select the remainder of the column (from where I am down) and no more. But what often happens when I attempt this with ctrl-shift-down-arrow is that I select the column I'm in downward way past the bottom of the table. I realize that this is probably due to my having used cells down in that part of the worksheet at one point, but there's not much I can do about that (is there?).