This is a really simple one, but I've lost count of the amount of times I've used this in front of an experienced Excel user who has stopped me and said "What did you just do there?".
Like all simple things, it's only simple if you know it!
This is a really easy tip in Excel to copy a formula down to the bottom of your data - without scrolling.
There are many times - particularly if you lay out your data as per my advice in earlier posts - that you wish to copy a formula down to the bottom of your data. If you have thousands of rows of data this can be a tedious and time consuming task, as you scroll down page after page.
But it doesn't have to be like that. Simply select the cell that you want to copy down and double-click on the black square at the bottom right of your selected cell (as highlighted in the image above).
If the rest of this column is empty, it will copy the contents down to the bottom of the data in the column adjacent to it.
If you already have a formula copied all of the way down, and you edit the top one, this will copy it to the bottom of this column (overwriting the older version of the formula).
That's it for this week. Simple, but really handy if you didn't already know about it.
Click here for our our exclusive offer on Online Excel Training
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".
Excelent!
ReplyDeleteWow...all this time i was dragging the black square till the end of the table to copy the formula....you are right...it's only simple if you know it!
ReplyDeleteThat's exactly how I felt when I first saw it!
DeleteI've been in this situation hundreds of times and scrolled. Yeesh, how easy!
ReplyDeleteIt's really easy when you know how. It's just one of those that you wouldn't stumble across!
DeleteTHANKS!! You just saved me oodles of time!
ReplyDeleteThank you Glen and thank you Google for putting this a the top of my search!
ReplyDeletePleased you found it useful.
DeleteI've had the same experience Glen - many experienced users have never learned this trick. Thanks for sharing.
ReplyDeleteRelated - if you want to select an entire column without the top few rows, click on the top cell, and press SHIFT+END+Down. This will select everything down to the end of the data table. It also works going up with SHIFT+HOME+Up. This is useful to quickly make plots or copy data.
ReplyDeleteWow, great solution!
ReplyDeleteOh this is exactly what I needed! Thank-you so much.
ReplyDelete