Excel Auto-fill Issues

[officeboy09]

Hi



Everyone is familiar with the excel auto-fill function. But lets say if you have a column or row that included thousands of cells, then in such case, even 'click and drag' would take you very long to complete the filling.

Is there any quicker way to complete the filling in this case?

Also, I tried using auto-fill to fill in the dates in a year and noticed that it doesn't switch from 31-01 to 01-02 but instead it keeps going to 32-01, 33-01 etc., that isn't what I want. How to fix this problem? I already selected date 'dd-mm' in the cell format option.

thx!

[Pecoflyer]

Question 1 :
You can enter A1:A30000 in the Names box, left of the formula bar, to select the range.

Then type the formula in the formula bar, press and hold the Ctrl key, then press Enter.

Question 2 :
You have to enter the entire date (31/12/2013) in the first cell , format as needed, then drag down

[SteveWcg]

Originally Posted by officeboy09

Hi

Everyone is familiar with the excel auto-fill function. But lets say if you have a column or row that included thousands of cells, then in such case, even 'click and drag' would take you very long to complete the filling.

Is there any quicker way to complete the filling in this case?

Also, I tried using auto-fill to fill in the dates in a year and noticed that it doesn't switch from 31-01 to 01-02 but instead it keeps going to 32-01, 33-01 etc., that isn't what I want. How to fix this problem? I already selected date 'dd-mm' in the cell format option.

thx!

Apart from Pecoflyers suggestions;

If the cells to the left are filled ctrl-click the fill handle to fill to the bottom. Doesn't work as well to the right. (Note if you have some data in the column you are filling below where you start it only fills to the last blank cell)

b) Set a custom fill series, haven't tried dd/mm but should work, see http://www.dummies.com/how-to/conten...pageNum-8.html

[officeboy09]

Originally Posted by Pecoflyer

Question 1 :
You can enter A1:A30000 in the Names box, left of the formula bar, to select the range.

Then type the formula in the formula bar, press and hold the Ctrl key, then press Enter.

Question 2 :
You have to enter the entire date (31/12/2013) in the first cell , format as needed, then drag down

Qu 1:

I tried A1:A30 in Name box and just type 300 in the formula bar and then Ctrl+Enter. But only A1 is filled with 300.

Qu 2:

Just noticed that in the cell format option for date, only the date formats which are marked with '*' is applicable for auto jumping between months.

[Pecoflyer]

After you entered the range in the name box next to the formula bar, did the chosen range highlight?

Just noticed that in the cell format option for date, only the date formats which are marked with '*' is applicable for auto jumping between months.

???????????

[officeboy09]

Originally Posted by Pecoflyer

After you entered the range in the name box next to the formula bar, did the chosen range highlight?

After you entered the range A1:A30, and then press Enter, the chosen range is highlighted, but only A1 is selected and displayed in the Name box.

My 2nd statement means only the date formats that are marked * can auto-switch from one month to the next.

[Pecoflyer]

It works perfectly for me.

[SteveWcg]

Originally Posted by officeboy09

After you entered the range A1:A30, and then press Enter, the chosen range is highlighted, but only A1 is selected and displayed in the Name box.

Yes A1 is selected and displayed after typing a1:a30 in name box, Ta Pecoflyer that's something new for me, as that's where you type the formula. Then CTRL-Enter fills the formula to the selected cells.

Not sure how that would work for an autofill that isn't a formula, ie fill A1=1 A2=2 to a30

Originally Posted by officeboy09

My 2nd statement means only the date formats that are marked * can auto-switch from one month to the next.

I don't know what version of excel you're on but *Dates are used to translate to a new locale.
Typing 1/1 or 31-Jan or 1/1/14 and then 2/1 (as per original) will autofill when the handle is dragged or you use CTRL-downarrow.

I've only ever seen the problem with filling dates in any format when you've pasted or imported the dates and Excel is confused about the format. Format cells and selecting dd/mm or dd/mmm/yy ofn doesn't work. Best way I've found to get Excel unconfused is to
1. select the text to be filled and then paste values or
2. select the Use Text wizard option when you paste.
3. Sometimes the Error box allows changing to dates but that's usually for numbers stored as text.

[officeboy09]

Originally Posted by SteveWcg

Yes A1 is selected and displayed after typing a1:a30 in name box, Ta Pecoflyer that's something new for me, as that's where you type the formula. Then CTRL-Enter fills the formula to the selected cells.

Not sure how that would work for an autofill that isn't a formula, ie fill A1=1 A2=2 to a30

Yes, after typing a1:a30 in name box, and press "enter" or "ctrl enter" straight away would select the whole range (a1:a30) typed in the name box. But then, the name box is auto-filled with only a1, and whatever value you type in the formula bar, excel only input that value into a1 and not the whole range.

My version is MS Office Excel 2003 SP3.