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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920