Excel - Why is Excel AutoFilling my Formulas!?: Episode 1658

1,021 views · Published 4 March 2013 · 4:35 · Indexed 14 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel AutoFilling Formulas: Stop Automatically Creating Calculated Columns | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast. In today's episode, we will be addressing a common issue that many Excel users face - why is Excel AutoFilling my formulas? This question was sent in by Bob, who was having trouble with his database that contained information in multiple currencies. He wanted to convert all the values to pounds, but every time he entered a formula, it would automatically fill down to all the cells, making it impossible for him to filter the data by currency. 

After some investigation, we discovered that the issue was caused by the data being formatted as an official table using the Home tab or Ctrl+T shortcut. This feature, while useful in some cases, can cause unexpected behavior when it comes to AutoFilling formulas. To solve this problem, we need to look for the AutoCorrect Options drop down and select "Stop Automatically Creating Calculated Columns". This will allow us to copy the formula down to only the visible cells, giving us the desired result. 

Another method to turn off this AutoFill feature is to convert the table back to a range using the Table Tools Design tab. However, this may not be a permanent solution as the person who set up the table may not be happy with the change. In tomorrow's episode, we will explore a better solution that will allow us to leave the AutoFill feature on and still achieve our desired result. So make sure to tune in for episode 1659 to learn more. 

For those of you who are new to Excel, don't worry - this podcast is designed for beginners. We understand that there may be more advanced methods to solve this issue, but we wanted to keep it simple for Bob's sake. However, for our regular listeners, we know you may have some better solutions in mind and we will be exploring those in tomorrow's episode. So make sure to come back for more Excel tips and tricks from MrExcel. Thank you for watching and we'll see you next time!

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) Excel AutoFilling Formulas
(00:14) Bob's Database and Issue with AutoFilling
(00:24) Bob's Attempt to Convert Currency
(00:34) Solution: Stop Automatically Creating Calculated Columns
(01:45) Alternative Solution: Convert to Range
(03:49) Introduction to "Don't Fear the Spreadsheet"
(04:00) Better Solution to Bob's Issue
(04:25) 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:
AutoCorrect options in Excel
AutoFilling formulas in Excel
Convert table to range in Excel
Converting currencies in Excel
DON'T FEAR THE SPREADSHEET podcast episode
Excel formula fill down issue
Excel tips for beginners
Learn Excel
Solving currency conversion in Excel
Stop automatically creating calculated columns in Excel


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


Don't Fear The Spreadsheet: The Excel Series for Beginning Excel Users with Questions.

Bob asks: "My Database contains different currency types; I set up a conversion filter to show British Pounds...but the Formula is AutoFilling all the way down. Why and what can I do to stop this because I am getting the wrong conversion figures?"

Following along with Bill in Episode #1658 to learn why this is happening and what to do to correct the issue.

If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet". http://www.mrexcel.com/dftss_home.html

More from this channel