Excel - How Excel Knows to Copy a Formula into a Newly Entered Row - Episode 596

2,703 views · Published 14 April 2009 · 1:39 · Indexed 10 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Explaining the Excel Option for "Extend Data Range Formats and Formatting".

Welcome to another MrExcel netcast! In today's episode, we'll be discussing a cool feature in Excel that was brought to our attention by one of our viewers, Rod. He noticed that when he wrote a formula in one row and then went down to the next row, Excel automatically extended and copied the formula. Rod was curious about how this feature works and wanted to know more about it.

Well, the answer lies in the "Tools" and "Options" menu. Under the "Edit" tab, there is an option called "Extend data range formats and formulas". This feature is automatically turned on and it allows Excel to extend and copy formulas down a column if there are three identical formulas in a row. This can be a time-saving feature if you are expecting it, but it can also cause problems if you are not aware of it.

If you want to turn off this feature, simply go to "Tools" and "Options" and uncheck the "Extend data range formats and formulas" option. However, keep in mind that this feature will only work if there are three identical formulas in a row. If there are only two, Excel will not automatically extend and copy the formula. So make sure to keep this in mind when working with your spreadsheets.

Thank you, Rod, for bringing this feature to our attention and for sending in your question. We always appreciate hearing from our viewers and love to share helpful tips and tricks. And thank you to all of you for tuning in to another MrExcel netcast. We hope you found this information useful and we'll see you next time for more Excel tips and tricks. Don't forget to like and subscribe for more helpful content. See you soon!"

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-on-youtube/

Table of Contents:
(00:00) Introduction 
(00:17) Note from Rod 
(00:27) Magic formula in Excel 
(00:43) How to set up automatic extension 
(01:01) Potential problems and how to turn off automatic extension 
(01:17) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adult tickets
Automatic formula extension
Copying formulas in Excel
Excel data range
Excel feature
Excel formula
Excel tips and tricks
Extend data range formats and formulas
Kids tickets
Spreadsheet columns
Tools Options

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152352/



Rod notes that Excel has a cool feature that will cause Excel to automatically copy formulas when you add new rows of data. In Episode 596, we take a look at how this feature works, and how to turn it off.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel