MrExcel's Learn Excel #596 - Auto Formula Copying

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 14, 2009.
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!
maxresdefault.jpg


Transcript of the video:
Welome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a note sent in by Rod.
Rod noticed a cool feature in excel and kind of wondered exactly what's going on.
He said he has a simple spreadsheet with columns for the number of adult tickets and kids tickets and he wrote a formula over here in column C that says adult tickets times 10 kid tickets times 6 and he said there's something kind of magic happening if he goes down to a new row and says there was 1 adult ticket and 3 kids tickets excel automatically extends that formula and copies it down to the next row.
Well, this is set up under "Tools" "Options".
On the "Edit" tab, there's something that's called "Extend data range formats and formulas" and basically the way it works is you have to have a series of three formulas that are exactly the same.
Once you have those in a row going down a column excel then says, Well hey, I'm going to automatically extend things.
That's great. If you're expecting it.
It can cause problems if you're not and if you want to turn it off, just go to "Tools" "Options" and Uncheck "Extend data range formats and formulas".
Now it doesn't work if you just have two items that's not enough.
It actually needs a series of three identical formulas in order to copy it down.
So thanks to Rod for sending that in. Great idea !
and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top