Determine if a cell (value) is used in any formula

Question

I am trying to reverse engineer an ancient program written using VBA in excel and formulas in cells. There are many cells with values scattered across everywhere. Is there a fast way to check if a cell (value) is being used in another cell's forumla; or if the cell is just sitting there doing nothing so that I can delete/igonore it safely? Thanks in advance.

I used the Ctrl+F find tool and it seems like it does work, but I just want to be sure if there is another better way.

When you say that it is written entriely using VBA does that mean that there are no formulas in the actual worksheet? i.e. all calculations and updating of cells values is controlled by VBA? — Alex P, Mar 24 '11 at 12:09
@Remnant well, i should not say entirely then. at least 50% of it are formulas in the actual worksheet cells. sorry for the confusion. — Jake, Mar 24 '11 at 12:12
This can be really hard in general. There are lots of ways for cells to get used that can't be easily detected without running the program. Especially when VBA is involved and you don't have pure formulas. The comment thread on this question might be of interest: http://stackoverflow.com/questions/1897906/is-this-the-regex-for-matching-any-cell-reference-in-an-excel-formula — jtolle, Mar 24 '11 at 13:18

score 39 · Answer 1 · answered Jul 19 '12 at 10:18

39

On Excel 2010 try this:

select the cell you want to check if is used somewhere in a formula;
Formulas -> Trace Dependents (on Formula Auditing menu)

answered Jul 19 '12 at 10:18

Benjamim

512
5
9

1

Thanks but this will not show the cells linked by controls – Jake Jul 19 '12 at 11:33
This also works on Excel 2007. – Tom Hubbard May 15 '14 at 15:53
When you trace dependents in 2010, you can double click on the dotted line of the arrow in order to see the list of dependent cells. – PretoriaCoder Oct 01 '17 at 19:01

score 28 · Accepted Answer · answered Mar 24 '11 at 11:43

28

Have you tried Tools > Formula Auditing?

answered Mar 24 '11 at 11:43

trickwallett

2,418
16
15

that would help me with half of what i need because some of the cells are for example entered via Drop Down boxes. – Jake Mar 24 '11 at 12:15
1

Can you tell me what does it mean when the "trace dependant" shows a dotted line joining to an icon that looks like a table? Thanks. – Jake Mar 25 '11 at 12:11
OK I think it means that the dependency is on another worksheet. Any idea how i can get to the worksheet? Thanks. – Jake Mar 25 '11 at 12:23
1

Hi - double clicking the arrow should take you to the cell – trickwallett Mar 25 '11 at 14:53
hmm I can't click the arrow. Don't know why. I'll figure out sometime... many thanks. – Jake Mar 25 '11 at 19:23
7

For anyone reading this: You have to double click very exactly on the arrow, a double click on the table symbol DOES NOT work! – wlfbck Oct 04 '14 at 21:22

Determine if a cell (value) is used in any formula

2 Answers2