Formula Bar (fx Bar) in Spreadsheets

How to work with the formula bar in Excel and Google Sheets

the Formula or fx Bar in Excel and Google Spreadsheets
(Ted French)

The formula bar is a toolbar that appears at the top of Microsoft Excel and Google Sheets spreadsheets. It is also called the fx bar because it is labeled fx. You use it to enter a new formula or copy an existing formula. Its uses also include displaying and editing formulas.

Displaying Data

The formula bar displays:

  • The text or number data that is located in the current or active cell
  • Formulas that are located 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 edit the ranges for individual data series that have been selected in an Excel chart.

It is possible to enter data into the active cell just by clicking with the cursor to enter the insertion point.

Expanding the Excel Formula Bar

For long data entries or complex formulas, the formula bar in Excel can be expanded and the formula or data wrapped on multiple lines. The formula bar cannot be expanded in Google Sheets.

To expand the formula bar with the mouse:

  1. Hover the mouse pointer near the bottom of the formula bar until it changes into a vertical, two-headed arrow.
  2. Press and hold down the left mouse button and pull down to expand the formula bar.

To expand the formula bar with shortcut keys:

The keyboard shortcut for expanding the formula bar is Ctrl + Shift + U.

These keys can be pressed and released all at the same time or the Ctrl and Shift keys can be held down and then the letter U key is pressed. To restore the default size of the formula bar, press the same keys a second time.

Wrap Formulas or Data on Multiple Lines in the Formula Bar

After the Excel formula bar has been expanded, the next step is to wrap long formulas or data onto multiple lines. In the formula bar:

  1. Click on the cell in the worksheet containing the formula or data.
  2. Click with the mouse pointer to place the insertion point at the breakpoint in the formula.
  3. Press the Alt + Enter keys 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:

  1. Click on the View tab of the ribbon.
  2. Check or uncheck the Formula Bar option.

To show or hide the formula bar in Google Sheets:

  1. Click on the View menu to open the drop-down list of options.
  2. Click on the Formula bar option and enter a check to view the formula bar or remove the check to hide it.

Prevent Formulas From Displaying in the Excel Formula Bar

Excel's worksheet protection includes an option that prevents formulas in locked cells from being displayed in the formula bar. Hiding formulas is a two-step process.

First, the cells containing the formulas are hidden, and then worksheet protection is applied.

Until the second step is carried out, the formulas remain visible in the formula bar.

First, hide the cells containing the formulas:

  1. Select the range of cells containing the formulas you want to hide.
  2. On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
  3. In the menu, click on Format Cells to open the Format Cells dialog box.
  4. In the dialog box, click on the Protection tab.
  5. Select the Hidden checkbox.
  6. Click OK to apply the change and close the dialog box.

Next, apply worksheet protection:

  1. On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
  2. Click on Protect Sheet at the bottom of the list to open the Protect Sheet dialog box.
  3. Check or uncheck the desired options.
  4. Click OK to apply the changes and close the dialog box.

At this point, the selected formulas are hidden from view in the formula bar.

X, ✔, and fx Icons in Excel

The X, ✔, and fx icons located next to the formula bar in Excel can be used for:

  • X – canceling edits or partial data entry in the active cell
  • ✔ – completing the entering or editing of data in the active cell (without moving the active cell highlight to another cell)
  • fx – providing 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 in Excel

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. The insertion point is placed in the cell when F2 is pressed.

In Excel, it is possible to edit formulas and data in the formula bar rather than the cell. To do so:

  1. Click on the File tab of the ribbon to open the drop-down menu.
  2. Click on Options in the menu to open the Excel Options dialog box.
  3. Click on Advanced in the left pane of the dialog box.
  4. In the Editing options section of the right pane, uncheck the Allow editing directly in cell option.
  5. Click OK to apply the change and close the dialog box.

Google Sheets does not permit direct editing in the formula bar using F2.