The *Formula Bar* in Excel has more to it than what meets the eye and today's tutorial will give you a rundown on it. (A run down and up if we're honest!)

The *Formula Bar* is one of the core features of Excel and we will guide you on what it is, how to hide/display, expand/contract it, and how to use formula bar icons.

Let's get formulating!

Table of Contents

## What is the Formula Bar in Excel

The *Formula Bar* in Excel shows the formula or value of a selected cell and can be used to edit any selected cell's value. In the case of a formula, the cell will display the result while the *Formula Bar* will show the formula. The *Formula Bar* will display the underlying value of a cell while the cell may show a formatted value.

As soon as you start typing in a cell, the *Formula Bar* will start displaying the contents. The *Formula Bar* shows the contents of a single cell and therefore, in a selected range, the *Formula Bar* will show the contents of the first selected cell.

It is located between the column headers and the Ribbon, to the right of the Name Box. The given size of the *Formula Bar* is not a limitation, and it can be expanded or contracted vertically as well as horizontally. It is possible to completely hide and restore the *Formula Bar*.

## How to Hide Formula Bar

By default, the *Formula Bar* is displayed in Excel. You might want to temporarily hide it to fit a few more rows into the worksheet space. Also, when you start typing in a cell, you are using the cell to add the contents instead of the *Formula Bar* so you might find that you prefer to hide the *Formula Bar* while working, making more rows visible too.

The *Formula Bar* can be hidden using the option in the *View* tab (by the mouse or keyboard) or in *Excel Options*. The methods are given below.

### Method #1 – Using Ribbon Menu

Hide the *Formula Bar* in Excel by changing the view of the spreadsheet using the *View* tab in the *Ribbon* menu. The process is complete in a couple of clicks, here's what to do:

- Go to the
*View*tab in the*Ribbon*and click on the*Formula Bar*checkbox in the*Show*

- With the
*Formula Bar*box unchecked, the*Formula Bar*has been removed from view:

After the *Ribbon*, the column headers have shifted upward, taking place of the *Formula Bar*. Check the *Formula Bar* box any time in the *View* tab to bring the *Formula Bar* back.

### Method #2 – Using Excel Options

The next method of hiding the *Formula Bar* in Excel is through *Excel Options*. Find out how to access *Options* in Excel and how to use it to remove the *Formula Bar* from display with the steps given ahead:

- Select the
*File*tab from the set of tabs above the*Ribbon*. - Click on
*Options*from the bottom of the left side pane.

- From the left panel, select the
*Advanced* - Scroll down to the
*Display*section and unmark the*Show formula bar*

- Click on the
*OK*button to apply the setting.

This will close the *Excel Options* dialog box, return you to the worksheet and hide the *Formula Bar* from display.

### Method #3 – Using Keyboard Shortcut

Use the keyboard instead of the mouse to quickly remove the *Formula Bar* from view. This keyboard shortcut takes the route of the *View* tab to hide the *Formula Bar*. The keyboard shortcut for hiding the *Formula Bar* in Excel is:

**Alt, W, V, F**

Enter the keys above one after the other.

**Alt **key displays the shortcut keys for the tabs.

**W **key selects the *View* tab.

**V, F **keys select the *Formula Bar* checkbox.

The keyboard shortcut has led to the previously checked box being unchecked and therefore hiding the *Formula Bar*:

## How to Show Formula Bar

If the *Formula Bar* has been hidden, using any one of the methods above will bring it back. While cells are mostly edited by direct typing, with the values of the surrounding cells, the contents of the active cell may become confusing, making the *Formula Bar* a better choice for editing data.

To restore the *Formula Bar* above the worksheet area, use one of the methods listed below.

### Method #1 – Using Ribbon Menu

The *Formula Bar* option in the *View* tab is used to hide and unhide the *Formula Bar* in Excel. To unhide the *Formula Bar*:

- In the
*View*tab's*Show*group, check the*Formula Bar*box which should be unchecked at this point:

- After check-marking the box, the
*Formula Bar*will be added back below the*Ribbon*:

### Method #2 – Using Excel Options

To show the *Formula Bar* in the spreadsheet, you can use *Excel Options*. Follow these steps to bring the *Formula Bar* back on display:

- Select the
*File*tab and then*Options*from the left.

- Next, select the
*Advanced*tab and tick-mark the*Show formula bar*

- Hit the
*OK*button to confirm the setting and close the dialog box.

The *Formula Bar* will return above the column headers:

### Method #3 – Using Keyboard Shortcut

Use a keyboard shortcut to display the *Formula Bar* in Excel. This keyboard shortcut will lead to the *View* tab to select the *Formula Bar* checkbox. Enter the keys below in succession:

**Alt, W, V, F**

The *Formula Bar *box will be checked, showing the *Formula Bar* in the spreadsheet again:

## How to Expand or Contract Formula Bar

The *Formula Bar* is not small by any means and can house a fairly long formula or cell value. But for longer formulas or cell values with line breaks, you will find that expanding the *Formula Bar* is much easier to work with. Also, when you're working with multiple windows and have downsized your Excel window, the *Formula Bar* will consequently have lesser space than before.

The first suggestion to resize the *Formula Bar* is to resize your spreadsheet window. The drawback of that however is that a smaller window will not contract the *Formula Bar* to display the complete value of the cell or the formula; if either is too long, it will be hindered from view. Therefore, if it's preferable not to resize the window, the *Formula Bar* can be contracted and expanded vertically in a couple of ways as well as horizontally. All three ways cater to resizing the *Formula Bar* differently. Details are given below.

### Using Expand/Collapse Arrow

The small arrow at the end of the *Formula Bar* is for expanding or collapsing the *Formula Bar*. In the example ahead, the formula in cell E3 cannot be viewed completely. We will expand the *Formula Bar* to view the formula using the arrow:

The *Formula Bar* has expanded by another line and displays the whole formula now:

**Note:** This arrow will expand the *Formula Bar* to the size last set by the resizing arrow (coming up in the section ahead). If you haven't used the resizing arrow before, the expanding arrow will only extend the *Formula Bar* by one more line.

Use the same arrow to contract the *Formula Bar* back to a single line:

**Pro tip:** Use the keyboard shortcut **Ctrl + Shift + U** to expand/collapse the *Formula Bar*. The shortcut toggles the *Formula Bar*'s expand/collapse arrow.

### Using Resizing Arrow

If the expand/collapse arrow isn't giving you the right amount of view in the *Formula Bar*, you can expand the bar to the desired setting using the resizing arrow. You may be familiar with resizing arrows used on windows or objects. Two such arrows are available for the *Formula Bar* and they work to resize it vertically and horizontally (the latter explained in the next section).

This can be used as a preset for the expand/collapse arrow. We'll let you know how. For now, have a look at the example case below. We have an address given in B13. The *Formula Bar* makes it evident that the address in B3 contains at least one line break as we cannot see the complete contents.

If we use the expand/collapse arrow at this point, we will only be able to view one more line and ideally, we should be able to view four in this case. To expand the *Formula Bar* to our preference, we will resize it instead by placing the cursor at the lower edge of the bar until a double-headed arrow appears:

Click and drag the *Formula Bar* using this arrow and extend it to the preferred length (four lines as per our example case). Now the *Formula Bar* will display the full contents of B13:

Use the same double-headed arrow to contract the *Formula Bar* to the preferred point or click the expand/collapse arrow once to restore the single-line *Formula Bar*.

After this, when you use the expand arrow, the *Formula Bar* will be extended to four lines instead of two since the expand arrow extends the *Formula Bar* to the last size set by the resizing arrow.

### Resizing Horizontally

The *Formula Bar* in Excel can also be resized horizontally. You may need to expand the *Formula Bar* if you contracted it previously. Contracting it horizontally gives more room to the *Name Box* to display a longer name (e.g. of a range or cell).

Below in the example, we have named all the cells containing contact numbers as PhoneNumber, PhoneNumber2, and onwards so that they are easier to find. The *Name Box* with its current size can't display the full name so we will resize the *Formula Bar*.

To contract the *Formula Bar* horizontally, place the cursor on the 3-dot menu icon between the *Name Box* and the formula bar icons until a double-headed arrow shows:

Click and drag to adjust the size of the *Formula Bar*. You can see below that the *Formula Bar* has contracted, expanding the *Name Box*:

Use the resizing arrow to expand the *Formula Bar* again or double-click the 3-dot menu to restore it to the default size.

## Formula Bar Icons

The formula bar icons are used for actions regarding the *Formula Bar*. The icons are situated before the *Formula Bar* itself, on the right of the *Name Box*:

Now would be a good time to tell you what these icons represent and how they can be used. As a rule, the icons are usable if they change color from gray when you hover the cursor over them. If the icon remains gray, it is unusable at that time.

**Cancel button** – The *Cancel* button works as a replacement for the **Esc** key. It is only activated in cell edit mode i.e. the active cell (whether via the *Formula Bar* or the cell). Clicking on the button exits cell edit mode while the cell remains selected. If any changes have been made to the active cell, the *Cancel* button will revert the cell value to the value before editing, maintaining the cell selection. That means that the button only works for the edits of the active session.

**Enter button** – The *Enter* button works a bit like the *Enter* key but only for the active cell and also maintains the cell selection. If the button is used without making any changes to the active cell, cell edit mode will be exited while the cell remains selected. If the button is used after making changes to the active cell, the cell will take on the edited value, cell edit mode will be exited and the same cell will remain selected.

**Insert Function button** – The *Insert Function* button is used for inserting a function in a cell. It's kind of like a functions encyclopedia. The button (or the **Shift + F3** keys) opens the *Insert Function* dialog box where you can search for a function.

Search for a function by category. The default display of the functions is the *Most Recently Used *category. Other categories are groups of functions and can help you find a function according to those groups. The *All* category will alphabetically list all the functions.

Another easy way is to search for a function by entering a description. Functions with matching descriptions will be displayed for you to choose from. E.g., for counting blank cells, we can type the description "count blank cells" and hit *Go*. These would be the results:

The syntax and description of the selected function will be shown in the dialog box. (For more help, click on the *Help on this function* link at the bottom of the dialog box and it will lead to the Microsoft website.) Select a function by double-clicking it and you will be led to the *Function Arguments* dialog box which will aid you in forming the formula correctly with the arguments and will also break down the results:

When done, the *OK* button will enter the formula and display the results in the selected cell on the worksheet.

The *Insert Function* button does not need an active cell to be usable. In the active cell, it can only be used if the cell is either blank or contains a formula. With any other value present in the cell, the *Insert Function* button will remain disabled.

The button can be used on any selected cell (without entering cell edit mode). If the button is used on a cell containing a formula, the *Function Arguments* dialog box will open with the function that the blinking cursor is positioned on. Using this case as an example, if we keep the blinking cursor at the IF function in the formula, and select the Insert Function button,

The IF function will be emboldened in the *Formula Bar* while the *Function Arguments* dialog box opens with the details of the IF function in this particular formula:

Note that while *Insert Function* can overwrite a cell's value, it cannot overwrite the format; the function used will result in the set cell format (if any).

Today we got up close and personal with the *Formula Bar* in Excel. Who should our next guest of honor be? We'll get busy with that while you can experiment with the new bits and bobs you learned about the *Formula Bar*. Enough tricks we got there to keep you occupied? Only one way to find out. Ready? Tricky? Go!