Show Formulas In Cells

October 29, 2002 - by Bill Jelen

There are few issues which have come up recently. They are all great questions, but each is too short to merit a tip of the week slot. Today, I will combine several mini-tips in a single article.

Timmy in Orrville asks the first question.

I have a very complex worksheet and need to check all of the formulas. Is there any way to have Excel show or print the formulas rather than me having to move the cell pointer to check each formula in the formula bar?

Yes, Timmy - there is an easy way to do this. With your worksheet as the active sheet, hold down the Ctrl key and hit the tilde key. The tilde is the squiggly line above the n from your high school Spanish class. On U.S. keyboards, it is found above the tab key, in the upper left corner of your keyboard.

Hit this key combination, and presto - all of your formulas will be displayed on the screen at once. You can print the worksheet while the formulas are displayed. When you are done auditing your formulas, hit Ctrl tilde again to toggle back to the normal view.

Phil Jones passed along this next tip.

I have several columns of formulas which perform a multiplication. I need to change all of these formulas to do a division instead. When I try to use Edit - Replace to change every occurence of the "*" to a "/", Excel treats the asterisk as a wildcard and replaces my entire formula with slash. Is there a way to replace an asterisk using edit replace?

Buried deep in Excel help, Phil found this answer. To specify an asterisk or a question mark in the Excel Find or Excel Replace dialog box, precede the asterisk with a tilde. So, neat~? would search for the string neat? in a cell. Thanks to Phil for passing this along.

Finally, Dave asked:

How can I format a series of numbers (892, 665, 2542) so they appear as shown: .892, .665, 2.542?

Type the following in the custom number format box: #"."000

To get to the custom number format box, choose Format > Cells, click the number tab, choose custom from the Category tab, then click in the box under Type: and start typing.