Long Formulas in the Formula Bar

November 22, 2021 - by Bill Jelen

Problem: In legacy versions of Excel a long formula would spill out of the formula bar, covering part of the grid. This is particularly annoying in row 1 because the formula covers the value from the formula.

Strategy: You can expand or collapse the formula bar. If you choose to expand the formula bar (with Ctrl+Shift+U), the grid actually shifts downward so that you can see the formula bar and the cell in the grid.

Here the formula bar is collapsed. You see only the first line of the formula.

The formula bar is showing the first part of a formula ='January 2014a'!G14+'January. This formula clearly is not complete, but there is no indication in the formula bar that there is more to the formula. — Figure 168. Only one part of the formula appears.

If you press Ctrl+Shift+U or click the down arrow icon at the right edge of the formula bar, Excel will shift the grid down and show more lines of the formula.

When you expand the formula bar, the entire formula goes on for seven lines, choosing individual cells from various worksheets. — Figure 169. Excel lowers the grid.

Gotcha: With a particularly long formula, the formula might not fit in the expanded formula bar. Grab the bar at the bottom edge of the formula bar to make the formula bar even taller.

This article is an excerpt from Power Excel With MrExcel

Title photo by Roman Mager on Unsplash