Sunday, July 19, 2009

Show Formulas in Cells

each formula in the formula bar?

Sample Data Range

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.

Formulas Displayed

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.

No comments:

Post a Comment