In this Excel tutorial, I will tell you everything you need to know about the Formula bar in Excel.
A lot of Excel users use the formula bar, and it could be especially useful if you’re working with large complex formulas in Excel
Where is Formula Bar in Excel?
A formula bar is a small strip that sits just above the worksheet area (and below the ribbon) as shown below.
It’s a thin bar that contains the Formula Bar and the Name Box.
By default, the formula bar is always visible below the ribbon in the Excel workbook.
But sometimes, it may be hidden, and you won’t be able to see it below the ribbon.
Also read: Microsoft Excel Terminology (Glossary)
Formula Bar Not Showing in Excel – How to Get it Back?
If you can’t see the formula bar above the worksheet area in your Excel file, you can easily get it back.
Below are the steps to make the formula bar visible again:
- Click the ‘View’ tab
- In the ‘Show’ group, check the ‘Formula Bar’ option
As soon as you do this, the formula bar would be back again below the ribbon.
Shortcut to Show/Hide Formula Bar in Excel
If you prefer using a keyboard shortcut instead, below the keyboard shortcut to show or hide the formula bar:
ALT + W + V + F
If the formula bar is hidden, and you use the above keyboard shortcut, it would make the formula bar visible (and vice versa, if it’s visible, it would hide the formula bar)
Also read: How to Show Ruler in Excel? (Excel Ruler Greyed Out)
Shortcut to Expand/Collapse Formula Bar
One area where the Formula bar could be really useful is when you’re entering a large complex formula in a cell.
Doing this directly in this cell could be slightly harder and annoying at times, as the cell has limited width and doesn’t offer a very clean experience of entering large formulas.
The formula bar allows you to expand it which makes it easier when you’re entering or auditing large formulas.
Below is the keyboard shortcut to expand or collapse the formula bar:
Control + Shift + U
If the formula bar is collapsed, using the above keyboard shortcut would expand it, and vice versa.
You can also expand or collapse the formula bar by clicking on the downward pointing arrow at the end of the formula bar.
If you would like to expand the formula bar further, place the cursor at the bottom of the formula bar field, and when the cursor changes to a double arrow icon, click and drag the formula bar field.
Note: If you change the height of the formula bar, it will stay that way for all the worksheets and workbooks (new as well as existing)
Insert Functions Using the Formula Bar
The main purpose of the formula bar is to make it easier for a user to enter a formula in a cell.
To help the user, there is also an Insert Function option right there in the formula bar that you can use to get a list of all the functions available in Excel.
If you click on the Insert Function icon (just below the left of the formula bar field), it will open the Insert Function dialog box.
Through this dialog box, you can search for any function in Excel, along with its syntax and detailed help.
While advanced Excel users, who are already well versed with their formulas, may not need it, I’ve seen a lot of basic and intermediate Excel users use this often while entering formulas in Excel
Formula Bar Makes Working with Large Formulas Easier
As I mentioned earlier, the formula bar is most useful when working with large complex formulas.
Within the formula bar, you get more space to go through the formula (which in itself is a better experience than working with the same formula in a cell).
You can also break the formula into separate lines within the formula bar so that it’s easier to read.
Below I have a long nested IF formula:
=IF(A1>1000,"Category A",IF(A1>800,"Category B",IF(A1>600,"Category C",IF(A1>400,"Category D",IF(A1>200,"Category E",IF(A1>100,"Category F","Category N"))))))
And here is how I can make it look in the formula bar so that it has been broken down into separate lines which makes it easier to read.
=IF(A1>1000,"Category A", IF(A1>800,"Category B", IF(A1>600,"Category C", IF(A1>400,"Category D", IF(A1>200,"Category E", IF(A1>100,"Category F","Category N"))))))
Below are the steps to break one large formula into multiple different lines:
- Select the cell in which you want to enter the formula
- Click on the formula bar box
- Enter the formula
- Place the cursor where you want to insert the line break
- Hold the ALT key and press the Enter key (use Control + Option + Enter if using a mac)
The above steps would break the formula into two separate lines depending on where you place the cursor before using ALT + Enter.
Below is the example of how my Nested IF formula looks in the formula bar once I have split it into multiple lines in the formula bar
In the above example, I have used multiple line breaks to have one IF formula in each line. This makes it easy for me (or anyone else) to understand and audit the formula.
Note that this will not impact your formula, and excel would still consider it as one single formula and execute it.
Some Useful Things to Know about the Formula Bar
- If you select any cell, the content of that cell (be it text or formula) would be visible in the formula bar. You can also edit the content of the cells directly from the formula bar
- When you select a cell, its reference would be visible in the Name Box. In case you select a range of cells, the cell reference of the active cell would be visible in the Name Box
- You can use the Name Box to quickly go to any cell or range in the Worksheet. For example, if I enter A1 in the Name Box, it will take me to cell A1. And if I enter A1:A10, it will select this range
- If you select a range of cells and then enter a text of formula in the formula bar, it would only be entered in the active cell
Other Excel tutorials you may also like:
