Formula Bar in Excel (Table of Contents)
What is Formula Bar in Excel?
Formula bar is also called a formula box which is a section or a toolbar commonly shown in Microsoft Excel and spreadsheet applications. In short, we call it the bar. The content of the current cell or where the selection is pointed will be visible in the formula bar. The visibility includes:
- Current cell selection.
- The formula applied in the active cell.
- The range of cells selected in the excel.
How to Use Formula Bar in Excel?
The formula bar can be used to edit the content of any cell and can be expanded to show multiple lines for the same formula. Let’s understand the function with a few examples.
Formula Bar in Excel – Example #1
The formula applied for the total column is the sum of amount and GST. The formula “=D2+E2” is applied to the entire column. Within the formula bar, the formula applied is visible and the corresponding cells are filed with the value after applying the formula.
After applying the formula in the total column the values are used to sum the Grand total. Now the formula bar is showing the current formula applied in the cell E10. “=SUM (F2: F9)”, the range of cells are represented within the formula.
Formula Bar in Excel – Example #2
From the options available in the menu bar it is possible to hide the formula bar as per your need. From the view menu, you can customize what are the tools that you want to display in the available interface.
Go through the below steps to show or hide the formula bar within the excel sheet.
4.5 (303 ratings)
View Course
- Click on the View menu.
- Tick or untick the rectangle box given to enable or disable the formula bar.
- According to your selection, the visibility will be set.
How to Use Formula Bar Icons?
You can see some icons associated with the formula bar. It includes the icon next to formula bar used for the following.
- × – This icon is to cancel edits and partial entry of formula in the current cell.
- √ – Helps to edit the data in the current cell without moving the active highlighted cell to another.
- ƒx – This is a shortcut to insert the different functions used in the calculations.
The below dialogue box will drop down the different functions available.
The same can be activated using the shortcuts as
- Esc key – Remove the edits and a partial entry.
- Enter key – Edit the data in the current cell without moving the active highlighted cell to another.
- Shift + F3 – To get the dialog box to insert the functions.
How to Expand the Visibility of Formula Bar?
By selecting the formula bar, rather than the values a formula will be displayed. While you are opening an excel sheet the cells will show the values. But if you select a specific column the formula applied to the cell will be shown in the formula bar.
If the formula bar is already expanded, then “Collapse Formula Bar” option will be visible on the right click.
When you use complex formulas and a large number of entries the expanding of formula bar is allowed. By expanding the formula bar, the data is wrapped into more than one line. This helps you to edit the formula easily other than doing within the corresponding cell.
- By clicking the arrow at the end of the formula bar
- By expanding the mouse pointer selection with the two-headed arrow
- By right-clicking, the formula bar select the option from the drop menu
The alternate option is to use keyboard shortcut Ctrl + Shift + U. If the same keys are press repeatedly then the default size will be restored.
To wrap the complex formulas or data into multiple lines then point your selection where you want to break your data or formula press Alt + Enter. The data or formula will be moved to the next line in the formula bar.
How to Protect the Formula from Unwanted Editing?
You can protect the formulas that you have applied in different cells. There are chances to delete the formulas when multiple persons are handling the same excel sheet. Using some simple steps, you can protect your formulas from editing by unauthorized users.
The first step is to hide the cells or range of cells where the formula being applied
- Select the range of cells where you have already applied some formulas and want to hide From the menu bar click home and go to format option to get a drop down
- Select the “Format Cells” option from the drop box
A dialog box will appear to select the “Protection” tab Select the hidden checkbox by enabling the tick mark Press “OK” to apply your selection and close the dialog box.
Next stage is to enable the worksheet protection
- From the same format option given in the home menu click for the drop down
- Go to “Protect Sheet” option from the drop-down list
- You will get a dialogue box which helps you to give protection to the selected cells
- By checking the “Protect worksheet and contents of locked cell” the protection will be enabled
- For protecting the cells with the password you have the option to give a password
- Once you want to unprotect the same selected cells you can use the given password.
After setting the protection if you try to make any changes you will get a warning message to use the password to make any changes
The same options are also available when you select the range of cells and right click
While selecting the format cells you will get the same dialog box to hide the formulas. To protect the sheet, you can right click on the sheet name and will get the same dialog box to protect the sheet.
You will get a dialog box to set the password if you want to make the sheet password protected.
By using the short cut key F2 it is possible to edit the active cell. This is a default setting. If you need to make any change on this.
Go to File menu in the “Option” menu will get Advanced editing options to disable the “Allow editing directly in the cell”.
This will prevent unwanted editing of the inserted formula by mistake.
Things to Remember About Formula Bar in Excel
- Since the formula bar is always pointing to the active cell while applying the formula you should ensure the active cell is correct
- Better to use formula bar for editing an applied formula. This will help you to manage the long formulas or data easily
- Apart from the menu bar use the shortcuts and right-click selection to make your work easy.
- Ensure the formulas are protected from editing if you are sharing the excel document to multiple users. This will help to prevent data loss.
Recommended Articles
This is a guide to Formula Bar in Excel. Here we discuss how to use Formula Bar in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
