Using the Formula Bar in Excel and Google Sheets

Formula bar is a toolbar at the top of Microsoft Excel and Google Sheets spreadsheets; it is also sometimes called fx bar because that label is right next to it. You’re using Formula bar to enter a new formula or copy an existing formula; its use also includes displaying and editing formulas. The formula bar shows:

  • Text or numeric data from the current or active cell.

  • Formulas in the active cell (not the answer formula).

  • A range of cells that represents the selected data series in an Excel chart.

As far as Formula bar displays formulas in cells instead of the results of formulas, it’s easy to see which cells contain formulas by clicking them. The formula bar also shows the full value for numbers formatted to show fewer decimal places in the cell.

Remark † These instructions apply to versions of Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Contents
  1. Edit formulas, charts and data
  2. Excel Formula Bar Extension
  3. Wrap formulas or data across multiple lines
  4. Show/hide formula bar
  5. Prevent display of formulas
  6. Icons on the formula bar in Excel
  7. Edit in the formula bar with keyboard shortcuts

Edit formulas, charts and data

Formula bar can also be used to edit formulas or other data in the active cell by clicking the cursor on the data in the formula bar. It can also be used to change the ranges of individual data series selected in an Excel chart.

Excel Formula Bar Extension

When working with long data records or complex formulas, you can expand Formula bar in Excel to wrap data across multiple rows. You cannot increase the size formula bars in Google Sheets.

To expand the formula bar in Excel:

  1. Mouse over at the bottom of the formula bar until it rotates vertically double headed arrow

  2. Press and hold while holding the button eft mouse And drag down to expand the formula bar.

The keyboard shortcut to expand the formula bar is also:

You can press and release each key at the same time or keep the keys pressed ctrl And Shift first and then press the key you † To return the formula bar to its default size, press the same set of keys again.

Wrap formulas or data across multiple lines

After expanding the Excel formula bar, the next step is to wrap long formulas or data across multiple lines. On the formula bar Click to place the insertion point, then click Alt+Enter on keyboard.

The formula or data, starting at the breakpoint, moves to the next line in the formula bar. Repeat the steps to add more pauses if necessary.

Show/hide formula bar

To show or hide the formula bar in Excel or Google Sheets:

  1. excel † Click on the tab Vision on the tape.

    Google Sheets : select menu item Vision

  2. excel † Check or uncheck Formula bar

    Google Sheets : if next to option Formula bar the checkbox is checked, it is visible; if there is no check mark, it is hidden. Press the button Formula bar and check or uncheck the box.

  3. Formula bar should now be set to your chosen visibility.

Prevent display of formulas

Excel sheet saver includes an option that prevents formulas in locked cells from appearing in the formula bar. You can use this feature to prevent others from editing formulas in a shared worksheet. Hiding formulas is a two-step process. First, cells with formulas are hidden, and then worksheet protection is applied.

Until the second step, the formulas remain visible in formula bars

Hide the cells with the formulas first:

  1. Select cell range The which contains the formulas you want to hide.

  2. On the tab ribbons Select option Format to open the drop-down menu.

  3. In the menu, press Format cells to open the dialog Format cells

  4. In the dialog box, click the tab Protection

  5. Check box Hidden

  6. Click Okay to apply the changes and close the dialog box.

Then enable sheet protection:

  1. On the tab ribbons Select option Format to open the drop-down menu.

  2. Click Protect sheet bottom of the list to open the dialog box Protect sheet

  3. Check or uncheck

  4. Click Okay to apply the changes and close the dialog box.

At this time, the selected formulas do not appear in the formula bar.

Icons on the formula bar in Excel

badges X and fx next to the formula bar in Excel, do the following:

  • X † Cancel editing or partial data entry in the active cell.
  • Complete – Finish entering or editing data in the active cell (without moving the selection from the active cell to another cell),

  • fx † Provide a shortcut to insert functions in the active cell by opening a dialog box Insert function when you press .

The keyboard equivalents for these icons are respectively:

  • Key esc – cancels changes or partial data entry.

  • Key Enter – completes entering or editing data in the active cell (moving the selection from the active cell to another cell).

  • Shift+F3 – opens a dialog box Insert

Edit in the formula bar with keyboard shortcuts

Shortcut key for editing data or formulas − F2 for Excel and Google Sheets – allows you to edit in the active cell by default. 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:

  1. Press File tab on the adhesive tape to open the drop-down menu.

  2. Click parameters: in the menu to open the dialog box Excel options

  3. Click Additionally in the left pane of the dialog box.

  4. In section Editing options in the right pane, uncheck Allow editing directly in a cell

  5. Click Okay to apply the changes and close the dialog box.

Google Sheets does not allow direct editing in formula bars using F2

Leave a Reply

Your email address will not be published.