Please Help: How to find all cells those value are resulted from other cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1504595%22%20slang%3D%22en-US%22%3EPlease%20Help%3A%20How%20to%20find%20all%20cells%20those%20value%20are%20resulted%20from%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504595%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20that%20contain%20both%20numeric%20number%20in%20constant%20and%20formula.%20My%20problem%20is%20I%20want%20to%20find%20all%20the%20cells%20those%20value%20are%20resulted%20from%20other%20cells.%20Please%20see%20the%20below%20example%20(in%20formula%20view%20and%20value%20view).%20The%20cells%20I%20want%20to%20find%20highlighted%20in%20yellow%20color.%20I've%20tried%20%22Go%20to%20Special%22%20%26amp%3B%20%22Formula%22%20but%20the%20results%20I%20get%20back%20are%20both%20B4%20(not%20linked%20cell)%20and%20B3%20%26amp%3B%20B6%20(linked%20cells).%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Khanh_Mai_0-1593920666009.png%22%20style%3D%22width%3A%20319px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203340i4699C3D54FEC50FC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Khanh_Mai_0-1593920666009.png%22%20alt%3D%22Formula%20View%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFormula%20View%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Khanh_Mai_1-1593920684389.png%22%20style%3D%22width%3A%20180px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203343iE73B400E1CBF3886%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Khanh_Mai_1-1593920684389.png%22%20alt%3D%22Value%20View%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EValue%20View%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%26nbsp%3B%3C%2FP%3E%3CP%3EKM%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504595%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504642%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%3A%20How%20to%20find%20all%20cells%20those%20value%20are%20resulted%20from%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689194%22%20target%3D%22_blank%22%3E%40Khanh_Mai%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20Trace%20Precedents%20%2F%20Dependents%20function%20from%20the%20Formula%20tab%2C%20which%20adds%20an%20arrow%20for%20formula%20linked%20to%20another%20cell%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Charla74_0-1593937743016.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203357i1A9124BFE93FCECD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Charla74_0-1593937743016.png%22%20alt%3D%22Charla74_0-1593937743016.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F3402-excel-trace-dependents-multiple-cells.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F3402-excel-trace-dependents-multiple-cells.html%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1593060%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%3A%20How%20to%20find%20all%20cells%20those%20value%20are%20resulted%20from%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1593060%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20help%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%2C%20It%20is%20useful.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20doesn't%20work%20I%20desired.%20Is%20there%20any%20way%20to%20get%20the%20answer%20to%20my%20question%20like%20the%20%22Find%20%26amp%3B%20Replace%22%20function%20works%3A%20First%2C%20select%20the%20range%20and%20then%20select%20only%20the%20cells%20those%20values%20are%20resulted%20from%20other%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1593195%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%3A%20How%20to%20find%20all%20cells%20those%20value%20are%20resulted%20from%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1593195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689194%22%20target%3D%22_blank%22%3E%40Khanh_Mai%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHighlight%20all%20of%20the%20cells%20you%20want%20to%20check.%20Select%20Conditional%20Formatting%20on%20the%20Home%20tab.%3C%2FP%3E%3CP%3ESelect%20New%20Rule...%20then%20select%20Use%20Formula%20to%20determine...%3C%2FP%3E%3CP%3EIn%20the%20box%20put%26nbsp%3B%20%3Disformula(b3)%26nbsp%3B%20Make%20sure%20B3%20is%20the%20upper%20left%20corner%20cell%20of%20the%20ones%20you%20are%20wanting%20to%20check.%20If%20it%20is%20not%2C%20place%20that%20cell%20reference%20in%20there.%20Only%20put%20in%20the%20upper%20left%20cell%2C%20without%20any%20%24.%20Then%20select%20the%20Format%20button%2C%20select%20the%20Fill%20tab%2C%20and%20select%20a%20highlight%20color.%20Say%20ok%20until%20you%20are%20out.%20all%20of%20the%20cells%20with%20formulas%20should%20now%20be%20highlighted%20the%20color%20you%20chose.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, 

 

I have a workbook that contain both numeric number in constant and formula. My problem is I want to find all the cells those value are resulted from other cells. Please see the below example (in formula view and value view). The cells I want to find highlighted in yellow color. I've tried "Go to Special" & "Formula" but the results I get back are both B4 (not linked cell) and B3 & B6 (linked cells). 

Formula ViewFormula View

 

Value ViewValue View

 

Many thanks. 

KM 

 

3 Replies

@Khanh_Mai 

 

You can use the Trace Precedents / Dependents function from the Formula tab, which adds an arrow for formula linked to another cell: 

 

Charla74_0-1593937743016.png

 

https://www.extendoffice.com/documents/excel/3402-excel-trace-dependents-multiple-cells.html

Thank you for your help, @Charla74, It is useful. 

 

But it doesn't work I desired. Is there any way to get the answer to my question like the "Find & Replace" function works: First, select the range and then select only the cells those values are resulted from other cells. 

Many thanks. 

@Khanh_Mai 

Highlight all of the cells you want to check. Select Conditional Formatting on the Home tab.

Select New Rule... then select Use Formula to determine...

In the box put  =isformula(b3)  Make sure B3 is the upper left corner cell of the ones you are wanting to check. If it is not, place that cell reference in there. Only put in the upper left cell, without any $. Then select the Format button, select the Fill tab, and select a highlight color. Say ok until you are out. all of the cells with formulas should now be highlighted the color you chose.