Copying the same value in one column in a large spreadsheet

New Contributor

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"?

5 Replies

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




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. 

Thanks for that@Damien Rosario I couldn't find it anywhere in Help - and I'll use it a lot.

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies