Hide long Excel formulas
- Oct. 20, 2016
Introduction
Here is a rather short article, but a trick that is of use relatively often. If you have a dashboard kind of report, where formulas are calculating summary values for instance, you might end up with long and complicated formulas. Probably that long and complicated such that the formula does not fit 1 line of the formula bar:
You might want to increase the formula bar, but that takes away real estate. And is not useful for all other formulas that are short and clear.
The thing is, with formulas like these, they draw my attention when selecting cells. What are the solutions to get this beast of a formula out of sight ?
- Definitely the simplest, use a cell reference to another sheet, that you can hide, and put the formula over there. Probably a "calculation sheet" which the report sources.
- Mark the cell with that formula as hidden (see the Format Cells... dialog screen, last tab Protection). The formula will be invisible IF you also protect the sheet. A password is optional.
- Use Alt+Enter after the equals sign. Put the cursor after the = sign and enter that shortcut combination. All but the = sign will shift to the second line in the formula bar.
Here is a formula found in a forum:
becomes:
and clicking the downward arrow will then show you the rest of the formula.
This Alt+Enter trick is very useful when chopping up a large difficult formula in several lines. That way, with an enlarged formula bar, you are much more likely to understand the formula quickly. Just add an Alt-Enter where you want to start a new "line" in the formula. The formula will work exactly the same as before.
Finally, the shortcut key combination Ctrl+Shift+u is useful: you can expand and collapse the formula bar.
