/ / / How to locate formulas in a worksheet

How to locate formulas in a worksheet

,
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.

Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

8 comments:

  1. UnknownSeptember 4, 2009 at 4:32 AM

    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.

    ReplyDelete
  2. Excel MaticSeptember 4, 2009 at 4:37 PM

    Hi Arthur,

    Thanks for putting this here. This is indeed a cool way.

    ReplyDelete
  3. Arthur LittleSeptember 5, 2009 at 1:37 AM

    Alternatively, it is easy to show all formulas from all worksheets in one list:

    Control F (i.e., edit/find)
    Find what: =
    Within: Workbook
    Lookin: Formulas


    Of course the = above could be anything part of a particular formula that you wanted to see.

    ReplyDelete
  4. JonathanSeptember 7, 2009 at 1:57 PM

    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.

    ReplyDelete
  5. Excel MaticSeptember 7, 2009 at 2:02 PM

    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?

    ReplyDelete
  6. AnonymousSeptember 8, 2009 at 4:20 PM

    Hi, Jonathan
    In Excel 2007 go to Formula Audit and select Show Formulas
    Keyboards have many versions and it is difficult to find equivalences.

    ReplyDelete
  7. JonathanSeptember 9, 2009 at 11:44 AM

    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

    ReplyDelete
  8. AnonymousSeptember 11, 2009 at 3:56 AM

    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 "["

    ReplyDelete

Subscribe to: Post Comments ( Atom )

Thursday, September 3, 2009

How to locate formulas in a worksheet

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.

How to locate formulas in a worksheetSocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:34 AM
Labels: ,

8 comments:

Unknown,  September 4, 2009 at 4:32 AM  

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.

Excel Matic,  September 4, 2009 at 4:37 PM  

Hi Arthur,

Thanks for putting this here. This is indeed a cool way.

Arthur Little,  September 5, 2009 at 1:37 AM  

Alternatively, it is easy to show all formulas from all worksheets in one list:

Control F (i.e., edit/find)
Find what: =
Within: Workbook
Lookin: Formulas


Of course the = above could be anything part of a particular formula that you wanted to see.

Jonathan,  September 7, 2009 at 1:57 PM  

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.

Excel Matic,  September 7, 2009 at 2:02 PM  

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?

Anonymous,  September 8, 2009 at 4:20 PM  

Hi, Jonathan
In Excel 2007 go to Formula Audit and select Show Formulas
Keyboards have many versions and it is difficult to find equivalences.

Jonathan,  September 9, 2009 at 11:44 AM  

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

Anonymous,  September 11, 2009 at 3:56 AM  

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 "["

Post a Comment

Subscribe to: Post Comments (Atom)