2

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about.

One of the biggest changes the client wants is to remove numerous cells that are no longer needed by his business. Most of these cells do not have other dependent cells or formulas, but I don't know how to check that for sure.

How can one check to see which cells or formulas or functions or whatever are associated with a particular cell?

How do I get a list of excel objects that are dependent on the existence of a particular cell?

4 Answers 4

7

In Excel 2007 there is the ability to have Excel show or trace the dependents. I'm not sure how to activate this feature in Excel 2010, but if it should help here is the process for Excel 2007.

  • Select the cell you wish to view all dependents for
  • Go to the Formulas tab
  • In the Formula Auditing section click the button Trace Dependents

This will activate a blue arrow to every dependent cell in the same worksheet as well as a black arrow to an icon of a spreadsheet if there are any dependent cells in other worksheets/workbooks. To view the dependent cells just double click the arrow (in the case of the black arrow to other sheets/book a window will open with a listing of the external links).

When done, deactivate the arrows with the button Remove Arrows in the Formula Auditing section.

4
  • 2
    It's the same in 2010. Jun 27, 2011 at 21:45
  • 1
    Just to be clear - Trace dependents can and will only show dependent links to other workbooks if they are also open at the time you use it. Trace precedents (going the other way) will show them all the time.
    – AdamV
    Jul 6, 2011 at 15:47
  • Also note that Trace precs and deps will not show anything where there is a named range in the middle eg if A1 is part of a named range "MyData" and you check for dependents, it won't find cells with formulas which use this name.
    – AdamV
    Jul 6, 2011 at 15:58
  • Works in Excel 2013, too. Apr 6, 2013 at 21:26
3

Ctrl + [ or ] will actually select all precedent / dependent cells - if there is only one, this will jump to it to show you where it is, even across sheets and workbooks. If there are multiple, you can then format them (for example) so you can find them again later. If you add a Shift in there (ie Ctrl + { or } ) it goes to precs / deps more than one step away to all levels. In your case, select all precedents for a cell, format them (eg yellow), wash rinse repeat on every cell which has a formula in until you have highlighted all precs for all cells. Any blanks (non-yellow cells) are the ones which are not needed. This is a fairly blunt instrument, blindly formatting stuff, without actually gaining any more understanding of the structure and function of the workbook. Using the trace arrows is more "scientific".

1

I bought an Addin, Formula Manager, which will print out a report to give me a list of all references in a sheet. The report can then be searched for the reference you want.

Note: It says it works with Excel 2010, but I've only used it in Excel 2003, so you'll have to see.

Related is this free Workbooks Statistics Add-In, that lists a lot of reference stats and does dependencies for the workbook.

0

As others have indicated, in Excel 2010 go to the Formulas tab then within the "Formula Auditing" group click on the TRACE DEPENDENTS button. My challenge was following links to other worksheets and/or workbooks (aka external links).

I just found this on another site, within a YouTube video by TeachUComp (link at "http://www.youtube.com/watch?v=82tDl7d_VIc")

To save you watching the video if you have the same question, (it's actually so easy I felt stupid for not trying it) you simply double-click on the BLACK DASHED ARROW that leads to the little spreadsheet icon. I kept clicking hopefully on the icon itself.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .