Search Not Just Numbers

Tuesday 7 July 2015

Excel Tip: Find out how a cell is linked to others

Before I get into today's post, just a quick reminder for those of you in London about the Twenty Principles event I mentioned in my last post.

I believe that there are still some places available for Wednesday evening, and it would be great to see you there.

I am currently working on a large project trawling through some incredibly complex spreadsheets built by someone else, and I thought that I would share with you a simple, but very valuable Excel feature that I am using a lot on this project.

Before I can make decisions about any particular cell in the spreadsheet, I need to know how it is linked to other cells.

These links go in both directions. If the cell contains a formula, I might want to know what other cells this cell is dependent on (referring to the formula will help in this case).

The contents of the cell will be of no help with the other direction, and this is the one I need to know most often, i.e. what cells are dependent on this one. This is crucial, because I can't delete it or change it, until I know if there are any implications, and if so, what they are.

Fortunately Excel has a tool for both of these.

On the Formulas ribbon, there is a section called Formula Auditing which includes, among other things, the following three buttons:

  • Trace Precedents
  • Trace Dependents
  • Remove Arrows
If you select a cell with a formula in it, and click Trace Precedents, a number of blue arrows will appear, pointing from the cells that are referred to in the formula to the cell itself. If the formula refers to cells on another sheet, a broken arrow will come from a small box. In this case you can double-click on the arrow itself, and the references on other sheets will be listed. You can even double click on a reference in the list to go to that cell.

Even more useful is the Trace Dependents button. This works in exactly the same way, but the arrows go from your cell to the cells whose results depend on that cell. Where the cell is referenced from other sheets, the same approach (with the little boxes and broken arrows is used).

Finally, the last button simply removes the arrows from the screen.

Have a play with this. It can be very useful when you are trying to understand how a particular spreadsheet works.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

18 comments:

  1. I have a simpler rough and ready method of finding dependents. I simply multiply the contents of the cell by a very large number and then any cell which depends on it suddenly stands out

    ReplyDelete
    Replies
    1. making it an error value would work better but Glen's way is far superior
      reclicking the trace button will show the grandparents/children - great fun!

      sympathy for Glen; never a nice task

      Delete
    2. Both handy tips. Thanks for the sympathy. Don't feel too sorry for me though, it's a nice big project to get my teeth into. It's also quite satisfying as I gradually get it under control!

      Delete
    3. I have a little VBA "hack" that I find works well - it scans the sheet and colours all formulas. There is another macro to remove the colouring. While this does not show the precedents and dependents it shows where to look at formulas. It is a bit rough and ready, as it may muck up cell colouring, but uses a unique colour (not quite yellow) so that it is not easily confused with other colouring.

      Try this ,,,

      Sub ColourMyFormulas()
      '
      Dim rngSelection As range

      ' Store current selection
      Set rngSelection = Selection

      ' Select start of sheet
      ActiveSheet.range("A1").Select

      ' Select range of cells with data
      ActiveSheet.range("A1", ActiveSheet.Cells(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row, _
      ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column)).Select

      ' Step through cells using temporary variable "cell"
      Dim cell As range

      For Each cell In Selection.Cells
      If Left(cell.Formula, 1) = "=" Then
      ' Has a formula in cell
      cell.Interior.Color = RGB(253, 254, 0)
      Else
      ' No formula in cell
      ' - do nothing
      End If
      Next cell

      ' Restore original selection
      rngSelection.Select

      End Sub


      Sub ColourMyFormulasClear()
      '
      Dim rngSelection As range

      ' Store current selection
      Set rngSelection = Selection

      ' Select start of sheet
      ActiveSheet.range("A1").Select

      ' Select range of cells with data
      ActiveSheet.range("A1", ActiveSheet.Cells(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row, _
      ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column)).Select

      Dim cell As range
      For Each cell In Selection.Cells
      If cell.Interior.Color = RGB(253, 254, 0) Then
      cell.Interior.Pattern = xlNone
      End If
      Next cell

      ' Restore original selection
      rngSelection.Select

      End Sub

      Thanks,
      Alan

      Delete
    4. Thanks for that. I've used macros myself for this (and also to highlight cells with dependents in a particular colour). This goes a bit beyond the scope of this post, but can be very helpful in getting the bigger picture in a large spreadsheet. The problem with Trace Dependents/Precedents is that you can only apply it to each cell individually!

      Delete
    5. You could also achieve this by using Go To, Special to select all cells with formulae (I use F5, Alt-S…, but there is a button for it on the Editing section of the Home tab), which you could then format or cycle through as required. This can then be Undone with ctrl-z
      Alternatively (if you want to edit the cells before undoing the formatting but a little more advanced), you can use conditional formatting to highlight all formula cells which can then be "undone" by removing the rule
      To do this, first create a new Name via the Name Manager (ctrl F3 to bring this up) of (eg) "IsFormula = GET.CELL(48,INDIRECT("rc",))" - this is really VBA which is why it needs to be done via the Name method, then create your conditional format using the formula "=IsFormula" and apply your chosen format to the whole sheet
      If you want to save a workbook with this, then you need to save as type xlsm (because of the VBA - like I said, a bit more advanced)

      OK, that was abit off topic but it can be a very useful tip - I use this to highlight where users of my forecasting sheets have replaced a default value (a formula) with a value of their own; I can see at a glance what has been updated

      Jim

      Delete
    6. Thanks Jim

      These comments are getting technical - but it's all good stuff!

      Delete
    7. there's also the option to show formulae, ctrl-` (left of 1 on my keyboard, which also shows all numbers unformatted), but I've only done this by mistake (when going for ctrl-1 = format cells) and never found it useful

      Delete
    8. It can be helpful if you have a lot of very short formulae!

      Delete
    9. Just a quick related query. When you log into a spreadsheet, and it says that there are cells linked to another spreadsheet, how do you identify those cells (without clicking on every cell on every sheet)?

      Delete
    10. There is easy way to do this in Excel. You can search for all formulas containing '[' which will find all cells with links to external spreadsheets (and possibly others as well eg: text containing that character). My add-in, FormulaDesk, has a 'Workbook Detective' button that will show you exactly which cells are linking to which external spreadsheets. http://www.formuladesk.com

      Delete
    11. Darren. Gareth has said what I was going to say (thanks Gareth). The only thing I would add is to make sure that you choose to look in Workbook when you do Find All, as the default is Worksheet, which will obviously only return those in the active worksheet.

      Delete
    12. Darren. Gareth has said what I was going to say (thanks Gareth). The only thing I would add is to make sure that you choose to look in Workbook when you do Find All, as the default is Worksheet, which will obviously only return those in the active worksheet.

      Delete
    13. and, having Found All, you can then select them all by pressing ctrl-a (then closing the Find dialogue box)
      links to other workbooks can also be caused by defined Names and Pivot Tables (or just appear to be there until you close and reopen)

      Delete
    14. Another Excel 'quirk' worth noting is that, having chosen to Find in Workbook, subsequent Find/Replaces will default to that until reset.
      Also, when in workbook mode, Find/Replace will act on the entire sheet, even if only a small range is selected
      Furthermore, Undo doesn't always work on large Replaces throughout a workbook
      So, MAKE SURE YOU RESET IT BEFORE QUITTING THE FIND DIALOGUE!
      Otherwise you'll try to Replace within a small range within a huge spreadsheet, which will then act on the whole workbook and not be undoable (you may sense a painful personal experience here!)
      See also similar warnings under multiple sheet tip: http://www.notjustnumbers.co.uk/2013/11/excel-tip-making-same-change-to.html

      Delete
    15. Another Excel 'quirk' worth noting is that, having chosen to Find in Workbook, subsequent Find/Replaces will default to that until reset.
      Also, when in workbook mode, Find/Replace will act on the entire sheet, even if only a small range is selected
      Furthermore, Undo doesn't always work on large Replaces throughout a workbook
      So, MAKE SURE YOU RESET IT BEFORE QUITTING THE FIND DIALOGUE!
      Otherwise you'll try to Replace within a small range within a huge spreadsheet, which will then act on the whole workbook and not be undoable (you may sense a painful personal experience here!)
      See also similar warnings under multiple sheet tip:
      http://www.notjustnumbers.co.uk/2013/11/excel-tip-making-same-change-to.html

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Many thanks for that (obvious really!)

    ReplyDelete