Formula Bar (fx bar) in Spreadsheets

What Is the Formula or fx Bar in Excel and What Would I Use it For?

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

The formula bar - also called the fx bar due to the fx icon located next to it - is the multi-purpose bar located above the column headings in Excel and Google Spreadsheets.

Generally speaking, its uses include displaying, editing, and entering data that is located in worksheet cells or in charts.

Displaying Data

More specifically, the formula bar will display:

  • 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.

Since 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 just by clicking on the data in the formula bar with the mouse pointer.

It can also be used to edit the ranges for individual data series that have been selected in an Excel chart.

It is also possible to enter data into the active cell, again just by clicking with the mouse pointer 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 as shown in the image above.

The formula bar cannot be expanded in Google Spreadsheets.

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 - as shown in the image;
  2. At this point, 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 the letter U key pressed and released on its own.

    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

    Once the Excel formula bar has been expanded, the next step is to wrap long formulas or data onto multiple lines, as seen in the image above,

    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 break point in the formula;
    3. Press the Alt + Enter keys on the keyboard.

    The formula or data from the break point onward will be placed on the next line in the formula bar. Repeat the above steps to add additional breaks.

    Show/Hide the Formula Bar

    There are two methods available for hiding/displaying the formula bar in Excel:

    The quick way - shown in the image above:

    1. Click on the View tab of the ribbon;
    2. Check/uncheck the Formula Bar option located in the Show group of the ribbon.

    The long way:

    1. Click on the File tab of the ribbon to open the drop down menu;
    1. Click on Options in the menu to open the Excel Options dialog box;
    2. Click on Advanced in the left pane of the dialog box;
    3. In the Display section of the right pane, check/uncheck the Formula Bar option;
    4. Click OK to apply the changes and close the dialog box.

    For Google Spreadsheets:

    1. Click on the View menu to open the drop down list of options;
    2. Click on the Formula bar option to check (view) or uncheck (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, like locking cells, is a two-step process.

    1. The cells containing the formulas are hidden;
    2. Worksheet protection is applied.

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

    Step 1:

    1. Select the range of cells containing the formulas to be hidden;
    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. On this tab, select the Hidden check box;
    6. Click OK to apply the change and close the dialog box.

    Step 2:

    1. On the Home tab of the ribbon, click on the Format option to open the drop down menu;
    2. Click on Protect Sheet option 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 should be hidden from view in the formula bar.

    ✘, ✔ and fx Icons in Excel

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

    • ✘ - canceling edits or partial data entry in the active cell;
    • ✔ - completing the entering or editing of data in the active cell - does not move 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 on.

    The keyboard equivalent 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 - moves 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 Spreadsheets.

    By default, this permits editing in the active cell - the insertion point is place 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 Spreadsheets does not permit direct editing in the formula bar using F2.