How to locate formulas in a worksheet
Submitted by Rishie Khurana on Wednesday, 2 September 20098 Comments
In one of my earlier works, I was supposed to crack an already-built spreadsheet. The workbook was basically a complex model that contained a lot of data and has almost 40 worksheets and each worksheet had lots of numbers.
Now, to understand the functioning of each of the sheets, I had to find out which cells on each sheet contained functions / formulas! But with each sheet (MS Excel 2003) having 256 columns and 65536 rows (which means 16777216 cells!!) it was really impossible for me to dig down each cell.
But MS people are very intelligent. They did not provide you only a tool to create spreadsheets, but also a tool which one can use to decipher a the tool.
To view which cells in a worksheet contains functions, one can use this tool. This tool basically toggles the worksheets view from normal to a view function view*. The function view can be activated by pressing the following key combinations: CTRL and ~
The key above the TAB key is used in the above key combination. The same combination is also used to bring back the view to normal view.
Notes: *The functions view is not a standard terminology. I have used this terminology to be explain the context.
You can also identify formulas by using Go To Special. Press the F5 key, then click on Special, or pres ALT-S. Click on Formulas, or press F, to highlight all cells that contain a formula.
Hi Arthur,Thanks for putting this here. This is indeed a cool way.
Alternatively, it is easy to show all formulas from all worksheets in one list:Control F (i.e., edit/find)Find what: =Within: WorkbookLookin: FormulasOf course the = above could be anything part of a particular formula that you wanted to see.
The Ctrl+~ shortcut won't work on all keyboards. The UK/US version is Ctrl+` (left apostrophe, the odd key at the top left of the keyboard).Alternatively use the full menu command sequence Tools, Options, View, Formulas.
Hi Jonathan,Thank for pointing this out. But as far as I can see on my keyboard, both the symbols, ~ and ` are on same key, the one which we both mentioned.Are these symbols located differently on the keyboards in US/UK?
Hi, JonathanIn Excel 2007 go to Formula Audit and select Show FormulasKeyboards have many versions and it is difficult to find equivalences.
Mohit,On the US/UK keyboard the ~ is on the # key.If anyone knows how to do this on a German keyboard I would be keen to hear from them!Jonathan
Quick and dirty: you can also find equal sign, "=" or apostrophe's for those coming from Lotus world. For formulas linking to other sheets, find exclamation marks "!" and links to other workbooks, find parentheses "["
Leave your response!
My Google Page Rank
eBooks by Excel Matic
Recent Comments
Sponsored Links
Popular Posts
Email subscription
Useful Links
What I'm Doing...
Powered by Twitter Tools
Sponsors
Previous Posts
Categories
Users: %GUESTS_SEPERATOR%1 Guest%BOTS_SEPERATOR%
Recent Posts
Most Commented
Optimized by SEO Ultimate
Powered by WordPress | Log in | Entries (RSS) | Comments (RSS) | Arthemia theme by Michael Hutagalung