Forum Discussion
Copying the same value in one column in a large spreadsheet
I have a spreadsheet of 5000+ rows, and I need to insert a YEAR column at the beginning with "1936" in every cell. The spreadsheet is too long for me to drag from top to bottom. Is there a formula that allows me to populate, say, A2:A5852 with the value "1936"?
As Damien_Rosario suggests, type the number 1936 or text '1936 into the top cell A2.
Then double-click the fill handle at the bottom right of the cell and the value will fill down to match adjacent columns.
Another possibility to consider is inputting the value 13150. That is the date value for the 1/1/1936 and the custom number format can be set to display 1936.
The options make no difference if the value is just for visual effect but if the field is referenced by formulas it is worth getting it right.
5 Replies
As Damien_Rosario suggests, type the number 1936 or text '1936 into the top cell A2.
Then double-click the fill handle at the bottom right of the cell and the value will fill down to match adjacent columns.
Another possibility to consider is inputting the value 13150. That is the date value for the 1/1/1936 and the custom number format can be set to display 1936.
The options make no difference if the value is just for visual effect but if the field is referenced by formulas it is worth getting it right.
- Brilliant! I never thought to double-click.
Lijadaka , as a comment, simple dragging is not quite bad as well - dragging the cell down position mouse outside Excel window, e.g. on task bar - speed of scrolling will increase dramatically. For 5000 rows it takes about 10 sec all together. That's not a preferable option, but it also works good enough.
Hi Lijadaka
Quickest way I can think of:
1. Type 1936 into A2.
2. In the Home tab, use the Find & Select > Go To... and type A2:A5852 in the Reference box.
3. Press OK and Excel will select that range of cells.
3. In the Home tab, under Editing, use the Fill command and choose Down.
You should now have 1936 filled for the entire range.
Hope that does the trick. Let me know?
Cheers and best wishes
Damien
Thanks for thatDamien_Rosario I couldn't find it anywhere in Help - and I'll use it a lot.
