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".
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
ReplyDeletemaking it an error value would work better but Glen's way is far superior
Deletereclicking the trace button will show the grandparents/children - great fun!
sympathy for Glen; never a nice task
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!
DeleteI 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.
DeleteTry 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
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!
DeleteYou 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
DeleteAlternatively (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
Thanks Jim
DeleteThese comments are getting technical - but it's all good stuff!
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
DeleteIt can be helpful if you have a lot of very short formulae!
DeleteJust 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)?
DeleteThere 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
DeleteDarren. 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.
DeleteDarren. 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.
Deleteand, having Found All, you can then select them all by pressing ctrl-a (then closing the Find dialogue box)
Deletelinks to other workbooks can also be caused by defined Names and Pivot Tables (or just appear to be there until you close and reopen)
Another Excel 'quirk' worth noting is that, having chosen to Find in Workbook, subsequent Find/Replaces will default to that until reset.
DeleteAlso, 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
Another Excel 'quirk' worth noting is that, having chosen to Find in Workbook, subsequent Find/Replaces will default to that until reset.
DeleteAlso, 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
This comment has been removed by the author.
ReplyDeleteMany thanks for that (obvious really!)
ReplyDelete