Written by Allen Wyatt (last updated May 21, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003
Rodolfo knows that he can configure Excel to allow editing in both the Formula bar and the cell itself, but he wants to configure it so that editing can be done only in the cell, not in the Formula bar.
There is no way to do this in Excel. The closest you can come is to make sure that cell editing is enabled (so that editing can be done in either the Formula bar or the cell) and then hiding the Formula bar. You can hide the Formula bar by these steps:
Figure 1. The View tab of the Options dialog box.
If you prefer, you can also programmatically turn off the Formula bar for a specific worksheet. You can do this by using the following two macros, which should be assigned to the code for the specific worksheet you want to affect. (You can display the proper code window by right-clicking the worksheet's tab and selecting View Code from the resulting Context menu.)
Private Sub Worksheet_Activate() Application.DisplayFormulaBar = False End Sub
Private Sub Worksheet_Deactivate() Application.DisplayFormulaBar = True End Sub
The first macro turns off the Formula bar when the worksheet is activated, and the second turns it back on when the worksheet is deactivated (when another worksheet is selected).
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3229) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Forcing Editing to Be Done in a Cell.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Need to enter the current time into a cell? It's easy to do using this keyboard shortcut. The shortcut is a handy one to ...
Discover MoreHave you ever typed something in Excel, only to have it replace whatever is to the right of the insertion point? That's ...
Discover MoreIf you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments