Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible, after selecting trace precedents on a cell, to select or highlight all the cells at once that has an influence on the active cell and being able to move through the workbook and see all highlighted.

I am working on a summary sheet and has totalled all the cells of the various sheets onto this sheet, but have missed one or two along the line and it is extremely time consuming to follow each reference individually.

Anyone with some advice?

share|improve this question

1 Answer 1

Check out Aaron Blood's Formula Auditing Tool Explode
The FAQ will explain how to install it:

Q: How do I install it on my machine?

A: To install the add-in, all you need to do is put the "EXPLODE.XLA" file in the directory of your choice and then run Excel. From the Excel menu toolbar select "Tools" and then select "Add-ins". If you do not see "Explode" listed in the add-ins window you'll have to use the "Browse" button to find it. Once you've added a checkmark next to the "Explode" add-in you're all set!

  1. Once you have done this, right click your formula and select explode..

    Explode..

  2. Select the cell color tool

    Cell Color Tool

  3. Select the data items and hit color

    Selection and Color

You can do this for each set of data on each sheet and format the cells.

Original FAQ in case link goes down again

Thanks for downloading the Explode add-in. I hope you enjoy using it as much as I do. -Aaron EXPLODE FAQ's

Q: Where's the Explode Button?

A: I got rid of it. Now Explode appears as an option at the bottom of the right-click cell popup menu.

Q: What's an add-in?

A: Explode is an add-in for Excel. This means it's just an Excel file with an ".xla" extension. It's pretty much like any other Excel file except it has the ISADD-IN property enabled so the usual worksheet interface is not visible.

Q: How do I install it on my machine?

A: To install the add-in, all you need to do is put the "EXPLODE.XLA" file in the directory of your choice and then run Excel. From the Excel menu toolbar select "Tools" and then select "Add-ins". If you do not see "Explode" listed in the add-ins window you'll have to use the "Browse" button to find it. Once you've added a checkmark next to the "Explode" add-in you're all set!

Q: What do I do with the "EXPLODE.XLA" file?

A: You can put the file anywhere you want. However, if you're working with a standard installation of Excel, you may want to put the "EXPLODE.XLA" file with all the other add-in files that came with the Excel application. On my machine, the directory is "C:\Program Files\MSOffice\Office\Library". It could very likely be something different on your machine. If you don't know which directory holds the standard add-ins for Excel just right-click the Windows "Start" button, select "Search", and look for "*.xla" on your "C:" drive. Remember, if all else fails it doesn't really matter what directory you put the file in.

Q: I have an older version of Explode. What's the easiest way to update it?

A: Just download the current version of Explode and with the Excel application closed, copy the new "EXPLODE.XLA" file directly over the old one. No adjustments are required in the Excel application.

Q: How do I completely uninstall the Explode add-in?

A: Just remove the checkmark from the Excel add-ins window. Then you can close the Excel application and delete the "EXPLODE.XLA" file. You may also want to delete the "Explode" toolbar if your Excel application saved a version of it. The next time you open the add-ins dialog you should not see "Explode" at all. You may see a message once that says "Explode" can not be found. Just respond affirmatively when it asks you if you want to delete the entry.

Q: I have users whose English is not so good. Are there any foreign language ports available?

A: If you're willing to do the translation, I'm willing to provide a port for your language. Just send me an email and we can discuss it.

Q: I installed it OK, but I can't figure out how to use it.

A: There is a full set of instructions included in the application itself. Just click on the "Help" button in the main Explode dialog window. You will see an info splash screen appear that includes a button for "Explode Directions".

Q: I tried to install it on a network machine and it gives me a message that says it can’t find the macro?

A: I have noticed that on Novell Networks, if users do not have admin rights, some macros that require the use of customized userforms will not function. The Excel application simply does not recognize them. It doesn’t seem to matter if an admin user installs the add-in or not. If the user does not have admin rights it just doesn’t work. Unfortunately, I’m not a network guru so I don’t know why it happens. The only solution I’m aware of is to grant admin rights to the person who wants to use the Explode add-in. If you figure out a way to get around it, let me know. I agree it’s irritating, but there’s nothing in the Explode add-in or Excel that’s causing it to do that. It has to do with the network security settings.

Explode Add-in A free application distributed by: ©2000, 2001 Aaron T. Blood www.XL-Logic.com

Email questions or comments to: Aaron[at]XL-Logic[dot]com

share|improve this answer
    
+1 Does the FAQ say anything about backward compatibility like Office 2003? Btw. can't see the FAQ –  nixda Jul 31 '13 at 17:49
    
@nixda it actually seems that the original source of this, XL-Logic.com doesn't have a page for it. I found this repository though. Anyway, it was developed using 2003. –  Raystafarian Jul 31 '13 at 17:51
    
@nixda test if you'd like to test –  Raystafarian Jul 31 '13 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.