Excel - Double-Click Fill Handle To Copy Formulas For A Whole Column In Excel - Episode 1974
Microsoft Excel Tutorial - Fast Formula Copy, Joining text from two cells, converting Excel text to Proper Case.
Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing a handy Excel tip that will save you time and frustration when copying formulas. This tip comes from my book "MrExcel XL", which is filled with over 40 Excel tips, cartoons, jokes, and more.
The tip we will be focusing on today is called "Fast Formula Copy" and it was inspired by a hilarious illustration from Emily DeMarco. During an Excel seminar, someone asked if the CONCATENATE function was the same as using the concatenation character. This sparked Emily's doodling and the rest is history. But let's get back to the tip - how do we quickly copy a formula down to hundreds or thousands of rows of data?
For those of you who are keyboard shortcut pros, there is a method called the "Excel Olympics Method" which involves a series of keystrokes that can copy a formula in less than a second. But for the rest of us, there is a simpler way. You may be familiar with the fill handle method, where you drag the fill handle down to copy the formula. However, this can become tedious and time-consuming. That's where the "fill handle dance" comes in - when you accidentally drag too far and have to go back up. But fear not, there is a faster way!
Simply double-click the fill handle and Excel will automatically copy the formula down to the last row of data. This works for any number of rows, whether it's 112 or 1.1 million. However, there is one scenario where this method may not work - when there is a blank cell in the column to the left. But don't worry, in Excel 2010 and newer versions, the program will now look in all columns to the left to determine how far to copy the formula. No more getting fooled by blank cells!
Now, for those of you who are familiar with Roman numerals, you may have noticed the nod to them in the title of my book "MrExcel XL". In Excel 2013, there is a ROMAN function that can convert numbers to Roman numerals and vice versa. And just for fun, if you put in the Roman numeral XL (which represents 40), and ask for the ARABIC function, it will convert it back to the number 40. So, in honor of my 40th book, I decided to use the Roman numeral XL in the title, with the tagline =ROMAN(40) to give a nod to all the movies that use Roman numerals.
To wrap up, I want to thank all of you who have suggested this tip to me through tweets and emails. And this is just one of the many tips included in my book "MrExcel XL". So, if you want to learn more time-saving and useful Excel tips, click the info button in the top right-hand corner to find out how you can purchase the book. Thanks for tuning in and we'll see you next time for another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Fast Formula Copy with Double-Click the Fill Handle
(01:02) The 5 step keyboard shortcut ending with Ctrl+D in Excel
(01:31) Drag fill handle to copy formulas is too slow "fill handle dance"
(02:17) Double-clicking the fill handle to copy formulas to the bottom of the data
(03:00) Algorithm improved in Excel 2010 to handle some blank cells
(03:30) ROMAN and ARABIC functions in Excel
(04:01) Recap of the tips discussed in this episode
(04:41) Clicking Like really helps the algorithm
#excel #microsoft #excelformula
This video answers these common search terms:
how to use the fill handle in excel to copy formulas
how to use fill handle to copy formulas excel
how to use the fill handle to copy a formula in excel
how do i use the fill handle to copy a formula in excel
how to copy formula excel autofill
how to down fill in excel
how to copy formulas for a whole column in excel
how to copy the formula to the whole column in excel
how do i copy an excel formula to a whole column?
how do i copy formulas in columns in excel
how to copy formula through column in excel
how to copy excel formulas down column
how to copy a formula down an excel column easy
how to copy excel formula multiple rows
how to copy excel formula down mutliple rows
Tip #1 from the MrExcel XL book - fast ways to copy a formula down a column. Also in this video:
Ctrl+Down Arrow to move to end of a block of data
Ctrl+Shift Up Arrow to select to top of a block of data
Ctrl+D to fill the formula from the first row of a selection down
Double-click the fill handle
PROPER function
& character for concatenation
ROMAN function
ARABIC function
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - Double-Click Fill Handle To Copy Formulas For A Whole Column In Excel - Episode 1974 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 01 August 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 10,509 once and liked it 152 people.