Excel - Lock Formula in All Directions: Episode 1573
1,819 views · Published 25 July 2012 · 2:12 · Indexed 8 November 2026
Channel: MrExcel.com · 2012 · Education
Microsoft Excel Tutorial: Lock Formulas in All Directions in Excel | Excel Tips and Tricks | MrExcel Podcast. Welcome back to the MrExcel Podcast. In today's episode, we will be discussing a helpful tip sent in by Patricia on how to lock formulas in all directions in Excel. As many of you know, the F4 key is commonly used to add dollar signs to cell references in formulas. However, if you use the mouse to select a range of cells and press F4, the dollar signs will only be added to the last cell in the range. This can be frustrating for those who prefer to type in their cell references. But thanks to Patricia's trick, there is a simple solution. By clicking on the left or right side of the colon in a cell reference and pressing F4, you can toggle the dollar signs on that side. However, the most efficient way is to click on the colon itself and press F4, which will add dollar signs to both sides simultaneously. This trick may seem small, but it can save a lot of time and frustration for those who prefer to type in their cell references. I personally discovered this trick while teaching a class and it has been a game-changer ever since. So thank you, Patricia, for sharing this helpful tip with us. Thank you for tuning in to this episode of the MrExcel Podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time for more Excel tips and tricks. 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) Locking Formula References in Excel (00:14) Using F4 to add dollar signs to cell references (00:30) Patricia's trick for toggling both sides of a reference (01:24) Alternative method for adding dollar signs (01:36) Interesting discovery in a class (01:50) 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: Dollar signs in Excel Dollar signs in formulas Excel tips and tricks F4 key Learn excel Lock Formula in All Directions Lotus arrow key method Selecting the colon in Excel Toggling addresses in Excel Typing references in Excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153383/ [This Podcast Rendered at an Altitude of 33K Feet] A tip from Patricia today! Several ways to get those " $ " signs where you want them when working out your Forumula! Follow along with Bill in Episode #1573 to see how it's done! "The Learn Excel from MrExcel Podcast Series" Visit us: MrExcel.com for all of your Microsoft Excel Needs!
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