The formula bar is a toolbar that appears at the top of Microsoft Excel and Google Sheets spreadsheets; it is also sometimes called the fx bar because that shortcut is right next to it. You use the formula bar to enter a new formula or copy an existing formula; its uses also include displaying and editing formulas. The formula bar displays:
- The text or number data from the current or active cell.
- Formulas in the active cell (rather than the formula answer).
- The range of cells representing a selected data series in an Excel chart.
Because the formula bar displays formulas located in cells rather than the formula results, it is easy to find which cells contain formulas just by clicking on them. The formula bar also reveals the full value for numbers that have been formatted to show fewer decimal places in a cell.
Editing Formulas, Charts, and Data
The formula bar can also be used to edit formulas or other data located in the active cell by clicking on the data in the formula bar with the cursor. It can also be used to modify the ranges for individual data series that are selected in an Excel chart.
These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Microsoft 365.
Expanding the Excel Formula Bar
When working with long data entries or complex formulas, you can expand the formula bar in Excel so that the data wraps on multiple lines. You can’t increase the size of the formula bar in Google Sheets.
To expand the formula bar in Excel:
- Hover the mouse pointer near the bottom of the formula bar until it changes into a vertical, two-headed arrow.
- Press and hold down the left mouse button and pull down to expand the formula bar.
Alternatively, the keyboard shortcut for expanding the formula bar is:
Hover the mouse pointer near the bottom of the formula bar until it changes into a vertical, two-headed arrow.
Press and hold down the left mouse button and pull down to expand the formula bar.
You can press and release each key at the same time, or hold down the Ctrl and Shift keys first and then press the U key. To restore the default size of the formula bar, press the same set of keys a second time.
Wrap Formulas or Data on Multiple Lines
After you’ve expanded the Excel formula bar, the next step is to wrap long formulas or data onto multiple lines. In the formula bar, click to place your insertion point, then press Alt + Enter on the keyboard.
The formula or data from the breakpoint onward moves to the next line in the formula bar. Repeat the steps to add additional breaks if needed.
Show/Hide the Formula Bar
To show or hide the formula bar in Excel or Google Sheets:
- Excel: Click on the View tab of the ribbon.
- Google Sheets: Click on the View menu option.
- Excel: Check or uncheck the Formula Bar option.
- Google Sheets: If the Formula bar option has a check next to it, then it’s visible; if there’s no check, then it’s hidden. Click the Formula bar option and to add or remove the check mark.
- The Formula bar should now be set to the visibility you selected.
Prevent Formulas From Displaying
Excel’s worksheet protection includes an option that prevents formulas in locked cells from being displayed in the formula bar. You can use this feature to prevent other users from editing the formulas in a shared spreadsheet. Hiding formulas is a two-step process. First, the cells containing the formulas are hidden, and then worksheet protection is applied.
Excel: Click on the View tab of the ribbon.
Google Sheets: Click on the View menu option.
Excel: Check or uncheck the Formula Bar option.
Google Sheets: If the Formula bar option has a check next to it, then it’s visible; if there’s no check, then it’s hidden. Click the Formula bar option and to add or remove the check mark.
The Formula bar should now be set to the visibility you selected.
First, hide the cells containing the formulas:
Until the second step is carried out, the formulas remain visible in the formula bar.
- Select the range of cells containing the formulas you want to hide.
- On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
- In the menu, click on Format Cells to open the Format Cells dialog box.
- In the dialog box, click on the Protection tab.
- Select the Hidden checkbox.
- Click OK to apply the change and close the dialog box.
Next, enable the worksheet protection:
Select the range of cells containing the formulas you want to hide.
On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
In the menu, click on Format Cells to open the Format Cells dialog box.
In the dialog box, click on the Protection tab.
Select the Hidden checkbox.
Click OK to apply the change and close the dialog box.
- On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
- Click on Protect Sheet at the bottom of the list to open the Protect Sheet dialog box.
- Check or uncheck the desired options.
- Click OK to apply the changes and close the dialog box.
At this point, the selected formulas won’t be visible in the formula bar.
Click on Protect Sheet at the bottom of the list to open the Protect Sheet dialog box.
Check or uncheck the desired options.
Click OK to apply the changes and close the dialog box.
Formula Bar Icons in Excel
The X, ✔, and fx icons located next to the formula bar in Excel do the following:
- X – Cancel edits or partial data entry in the active cell.✔ – Complete the entering or editing of data in the active cell (without moving the active cell highlight to another cell),fx – Provide a shortcut to inserting functions into the active cell by opening the Insert function dialog box when clicked.
The keyboard equivalents for these icons, respectively, are:
- The Esc key – Cancels edits or partial data entry.The Enter key – Completes the entering or editing of data in the active cell (moving the active cell highlight to another cell).Shift + F3 – Opens the Insert dialog box.
Editing in the Formula Bar with Shortcut Keys
The keyboard shortcut key for editing data or formulas is F2 for both Excel and Google Sheets — by default, this permits editing in the active cell. In Excel, you can disable the ability to edit formulas and data in a cell and only allow editing in the formula bar.
To disable editing in cells:
- Click on the File tab of the ribbon to open the drop-down menu.
- Click on Options in the menu to open the Excel Options dialog box.
- Click on Advanced in the left pane of the dialog box.
- In the Editing options section of the right pane, uncheck the Allow editing directly in cell option.
- Click OK to apply the change and close the dialog box.
Click on the File tab of the ribbon to open the drop-down menu.
Click on Options in the menu to open the Excel Options dialog box.
Click on Advanced in the left pane of the dialog box.
In the Editing options section of the right pane, uncheck the Allow editing directly in cell option.
Google Sheets does not permit direct editing in the formula bar using F2.
Get the Latest Tech News Delivered Every Day
