Written by Allen Wyatt (last updated May 17, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
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 Advanced options in the Excel 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 (9095) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Forcing Editing to Be Done in a Cell.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Excel makes it easy to delete rows in a worksheet, but it can be more difficult to figure how to delete rows if you only ...
Discover MoreWhen importing Excel information into Access, you need to be concerned with the condition of the data. Here's how to make ...
Discover MoreAs you use Excel to collect data over time, sometimes winnowing out the latest data can present a challenge. Here are a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-05-17 14:47:32
J. Woolley
My Excel Toolbox includes the ToggleFormulaBar macro (Ctrl+T F B) to turn the formula bar off or on. The macro supports Undo (Ctrl+Z). Here is an abbreviated version:
Public Sub ToggleFormulaBar()
Const myName As String = "ToggleFormulaBar"
With Application
.DisplayFormulaBar = (Not .DisplayFormulaBar)
.OnUndo myName, (ThisWorkbook.Name + "!" + myName)
End With
End Sub
Similar macros include ToggleRibbon, ToggleStatusBar, ToggleScrollBars, and ToggleFullScreen. ToggleEditInCell will disable or enable editing within cells. All of these macros support Ctrl+T keyboard shortcuts and Ctrl+Z Undo.
See https://sites.google.com/view/MyExcelToolbox
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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