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

Watch on YouTube

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