Expand Excel Formula Bar

Microsoft Excel’s formula bar is the area of the spreadsheet window that is above column letter headings, to the right of the current cell location indicator. It is the area highlighted in yellow in the following screen shot.

FormulaBar1

The formula bar in the above figure does not show anything because cell C6 does not contain anything (i.e. it is a blank cell). The formula bar will display the contents of a cell whenever the cell contains something. This ‘something’ is going to be either text, a value, or a formula. The formula bar in the next screen shot shows that cell C6 contains the SUM function adding a few cells. [People new to spreadsheet programs should be aware that cell C6 will display the answer to the formula, but that the cell actually contains a formula.]

FormulaBar2

What happens if a formula is so long that it takes up more space than the formula bar? In Excel 2003, the formula bar would automatically expand to display the entire formula as seen in the next screen shot. Notice that column letter headings are not obscured. This behavior could cause problems for a user trying to trouble-shoot a formula.

FormulaBar2b

Starting with Excel 2007 the formula bar will normally display one line of the formula bar, even if the formula extends to multiple lines. How does a user know that the entire formula is not being displayed? By the up/down arrowheads now being displayed toward the right end of the formula bar as seen in the next screen shot. Using the up/down arrowheads allows a user to scroll through a formula one line at a time.

FormulaBar3

The end most icon (that looks like a ‘v’) allows you to expand the formula bar to three lines as seen in the next screen shot. The ‘v’ icon flips 180 degrees to resemble the carat symbol (^) when the formula bar is expanded. The carat also moves above the up/down arrowheads. The formula bar does not expand beyond three lines. Clicking the carat returns the formula bar to the normal one line display. The expansion/contraction of the formula bar can also be achieved via the following keyboard shortcut: CTRL+SHIFT+U.

FormulaBar4

What if you have a formula longer than three lines? Rather than using the method from the last paragraph, you can manually resize the formula bar.

Move the mouse to the bottom of the formula bar until the mouse pointer becomes a double-headed arrow.

FormulaBar5

Then drag with the mouse to make the formula bar as large as you need.

FormulaBar5b

Being able to change the size of the formula bar is a vast improvement over Excel 2003. Not only does the formula bar expand, but the column letter headings continue to be displayed. This makes for much easier formula verification.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd