Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I will need to sum long column of values dividing them to some groups. And I do not want to miss some row. So I need to highlight all cells that I have already used in some formula (e.g.=D1+D2).

When I double click the formula, used cells are highlighted. I want this effect. I found that CTRL [ shall do this trick but it has no effect on my computer with Excel 2010. Is there some other possibility? Thanks

E.g. I have two columns, the first are data, the second is grouping. A4 is not used, A1..A3 shall be highlighted as already used.

**1**    =A1+A2
**3**    =A3
share|improve this question
It isn't clear, but it sounds like you are trying to highlight (at one time) ALL cells used in ALL formulas. Is that correct? – CharlieRB May 14 '14 at 11:50
Yes, I needed to easily identify unused cells in that column. Meanwhile I just compared sum of all rows and sum of all groupings to verify that they matches. But it would be useful to know the trick for future. – Leos Literak May 14 '14 at 11:54
up vote 3 down vote accepted

I don't think the layout is what you want, but this may suffice: You may be able to use Trace Dependents/Precedents

It lives in the Formula's toolbar, under Formula Auditing (and within the same place is the option to remove the arrows)

enter image description here

More details

However, I don't like the above. Now, it really depends on your needs, but I quickly wrote this VBa for you

Sub Button5_Click()


Dim row As Integer

Dim numberOfRows As Integer

Dim columnWithFormula As String
columnWithFormula = "E"    ' THE COLUMN WHERE THE FORMULAs ARE (I suspect you are using B if your example is accurate))

Dim colourIndex As Integer


For row = 1 To numberOfRows

If range(columnWithFormula & row).Value <> "" Then

   Dim result As String

   result = range(columnWithFormula & row).Formula

   result = Replace(result, "(", " ")
   result = Replace(result, ")", " ")
   result = Replace(result, "-", " ")
   result = Replace(result, "+", " ")
   result = Replace(result, "*", " ")
   result = Replace(result, "/", " ")
   result = Replace(result, "=", " ")
   result = Replace(result, ",", " ")

   Dim cells() As String
   cells = Split(Trim(result), " ")

   For j = 0 To UBound(cells)
    range(cells(j)).Interior.ColorIndex = colourIndex
   Next j

End If

Next row

End Sub

The above produces the following (I enabled formula so you can see what cells did have a formula in)

The macro isn't perfect, but I don't know your needs really so if you (as per the example in your post) have simple formula it should work fine.

Please note, it will change the cell background colour and undo won't work! So, if you are using highlighting already then you'll need to update the code! It also means after you run this macro, you'll need to manually select the work sheet and revert the background colour to your choice...

enter image description here

To expand this, you could update the for loop and replace it with

For Each Cell in ActiveSheet.UsedRange.Cells

to check every single cell in the worksheet to see if there is a formula any where...

share|improve this answer
Thanks, it does the job though I have to click this function for each formula manually. You should see dense net of arrows :-) – Leos Literak May 14 '14 at 12:12
@LeosLiterak, I updated it so it is (may) be more automated for you. – Dave May 14 '14 at 12:30
Thanks. I just tried and I failed. 1) typo: Dim reslut -> result. 2) How to use this script? I opened VB from Development bar. Then I chose View code on my sheet and copied Sub Button5_Click() code there updating mentioned variables. 3) what is the next step? – Leos Literak May 20 '14 at 8:08
@LeosLiterak, sorry, there was a typo, I had to a lot of the code from memory.. Can you try now, I updated the script (So recopy the code above) – Dave May 20 '14 at 8:13
This was different typo than I meant :-) Dim reslut As String – Leos Literak May 20 '14 at 8:21

go to a blank cell - in this cell type =V40=1:1048576 where the first reference (in this case v40) refers to another blank cell. The second part highlights the entire sheet.

Press enter and then click on the cell, click on view formulas and double click trace precedents. This will draw a blue link to every cell that is referred to in another cell. Look for the ones with no lines - they have been missed.


share|improve this answer

I prepared following macro to classify the selected cells into "Input", "Calculation" and "Output" and code will assign styles named as "Input" "Calculation" and "Output". This is useful to understand the sheets prepared by somebody else or debug sheets.

The output of the code will look like as follows (I have enabled "show formulas"). enter image description here

Put the below code in "Sheet code" not in module.

'Macro to classify the cells from selected range into "input", "calculation" and "output"
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    With ActiveSheet.Cells
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = xlAutomatic
        .Font.Bold = False
    End With
    Dim rCell As Range
    Application.EnableEvents = False
    For Each rCell In Target.Cells
        If IsEmpty(rCell.Value) = True Then GoTo AllDone:
        If Not rCell.HasFormula Then
            If HasDependents(rCell) Then
                rCell.Style = "Input"
                GoTo AllDone:
                GoTo AllDone:
            End If
            If HasDependents(rCell) Then
                rCell.Style = "Calculation"
                GoTo AllDone:
                rCell.Style = "Output"
                GoTo AllDone:
            End If
        End If
    Next rCell
    Application.EnableEvents = True
End Sub
Public Function HasDependents(ByVal Target As Excel.Range) As Boolean
    On Error Resume Next
    HasDependents = Target.Dependents.Count
End Function
share|improve this answer

Your Answer


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.