Highlight Formula Reference On Another Sheet

  • Hi!
    Normally when you double-click on a formula you see its component cells highlighted. But when I do this with a formula that uses cells from different worksheets NO cells are highlighted. I wonder if anyone knows how to get the highlighting working for such formulas. Or is there an add-on which enables this?

  • Re: Formula Highlighting On Different Sheets

    Quote from Bob Phillips

    Double-click should do it.

    thanks...but the trouble is double-clicking doesn't work :( to clarify - I am working with two different worksheets of the same workbook each in its own window...arranged vertically (side by side).

  • Re: Formula Highlighting On Different Sheets

    When you are on a worksheet you can quickly view what cells are being used in a formula with out needing to double click the formula bar. Use these key shortcuts.

    Find what cells are being used:
    Click on the cell with the formula and press " Ctrl + [ "
    -This will select all the cells that this formula is using.

    Conversely, Find what cells are using that cell as a reference:
    Click on the cell and press " Ctrl + ] "
    -If you get a message that tells you "No cells were found" then you know that the cell you selected is not contributing to any formulas and thus can be edited safely.

    Here’s the problem. Excel doesn't let these key commands work across worksheets. Just like excel won’t show cell references across worksheet as highlighted colored boxes.

    The only thing that I know of that might be of some help to you is excel's "formula auditing" tool bar. Click View->tool bars->formula auditing. Use the "Trace Precedents" button. Its the second from the left on the toolbar. Click this button and you will now see arrows pointing from the cells that are being used to the cell where the formula is. A grey dotted line will show that there are cells in another work sheet. If you double click this grey line, the "go to" window will open. It will list the other cells being used. if you click on one of the cells in the list and then click OK, you will be taken to that cell and it will have been selected. Use this to find and edit the cells being used.

    Hopefully you can find some use out of this.

    Yet the real question is: Why are you looking to highlight these cells?

    Maybe someone knows a code to actually apply a color code to a cells dependents.


  • Re: Formula Highlighting On Different Sheets

    thanks a lot for such a detailed reply! I tried all the approaches you suggested and none of them works for me...((( in all cases Excel takes me to the cells that are referenced by the formula while I need them to be highlighted only. I ask this because I am building calculations on one sheet based on the calculations in the other. I am using two sheets because one of them is already full of columns with the formulas...)) Is there absolutely no way to get Excel highlight formulas on different sheets???? Maybe some pluigin does this??

  • Re: Formula Highlighting On Different Sheets


    Use the forum rules to your advantage. Assume you know nothing. I know you know something, because it sounds like you are doing pretty well so far with formulas and such, BUT, try posting up a new forum post with just your problem. You can tell people what you have tried, but you really should just tell people what you are trying to do.

    From your last post it sounds like you are trying to use the results in one cell as a reference in another...mixed with something else.

    Make a new post and just ask everyone what you want to have as a final product with the data you have. Someone may give you a new idea on how to accomplish your goal. Trust in the forum. It worked for my question. It will work for yours.

    Don't forget to finish your new post with a link to this post. And it might be helpful to reply to this post with a link to your new post.

    Good luck, and remember. the more general the better. that way it will help others too! :)

  • Re: Formula Highlighting On Different Sheets

    thanks again..but I do not think i can make this question more general :) I just need to see formula references highlighted on different sheets (while having two different sheets of the same workbook open at the same time side by side) as they are on a single sheet..looks like I have run into a insurmountable wall here....

  • Re: Formula Highlighting On Different Sheets

    I'm stubborn and by George I want to help. So would it be possible for you to type out an example of what your doing?

    Because I know very little about VB I have been forced to do a lot of things with formulas. I have gotten quite good at using only formulas to do different things. I have used formulas to reference formulas to make locations for other formulas to reference. Really... If its possible to do with formulas in excel I think I have done it.

    SO! Type out a little example of what you are trying to do and I might just be able to give you a solution to do it all on one worksheet with out needing to make so many formulas.


  • Re: Formula Highlighting On Different Sheets

    i very much appreciate your stubbornness...)) as a matter of fact I am myself a formula-only man....no vba knowledge at all!!!))) which I do not regret at all at the time being....as for my purpose for so many columns...I can only slighty remove the veail - this is a scientific project for constructing a complex adaptive model of a natural phenomen...sorry cannot go any further from that!)) anyways....if you get an idea on my topic problem - please let me know!

  • Re: Formula Highlighting On Different Sheets


    I totally understand where you coming from with the not being able to list an example. But if you think of a way you could but it in to a different format with out the actual data then someone might be able to suggest something.

    Then again...if the nature of your formulas gives away chaos theory or something....then you might not be able to.

    Good luck!

  • Re: Formula Highlighting On Different Sheets

    thank you...:).....I still hope someone here experienced the same problem and will be able to help me............

  • Re: Highlight Formula Reference On Another Sheet

    thanks....maybe you can recommend any excel plugin which extends the functionality of the standard compare-side-by-side tool in Excel 2003?

  • Re: Highlight Formula Reference On Another Sheet

    Without knowing "why" you want the cells highlighted in the 2nd book, would this be useful to you?

    select your cell containing the formula and goto menu: Tools\Formula Auditing\Trace Precedents, now blue lines will connect to your cells in the active workbook and any references to your other book will have dashed black line with a small sheet icon. If you double click the dashed line it will bring a popup box with all of the links and you can choose one and it will goto the 2nd book and highlight that cell.

  • Re: Highlight Formula Reference On Another Sheet

    thank you for your reply...i need this formula highlighting to visually "debug" or check the code as I programm the model in excel...unfortunately I already tried the method you proposed and it didnt work...(((

  • Re: Highlight Formula Reference On Another Sheet

    Quote from Sicarii

    ... if you double-click the dashed line...

    Quote from lovejoy

    I already tried the method you proposed and it didnt work

    You should try again -- click the line, not the icon.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!