Identifying the number of self-loops for each node

Jul 13, 2011 at 4:22 PM


In my networks all nodes have several self-loops. I want to identify the number of self-loops for each node and express it through vertex opacity. Is there an easy solution for that?

Next, I have problems applying formulas in Node XL. I'm using version and Excel 2010. The [#This Row] part of any formula does not seem to work. Excel is suggesting me [@] as a substitute, but this does not work either..


Jul 13, 2011 at 5:26 PM
Edited Jul 13, 2011 at 5:31 PM

On the first question, you can accomplish this with one formula on the Edges worksheet and another on the Vertices worksheet.  There may be a fancier way to do it with a single formula, but I leave that to someone else.

Do the following:

1. On the Edges worksheet, add a column called "Self Loop."

2. In the first cell of the Self Loop column, insert this formula:  =IF(Edges[[#This Row],[Vertex 1]]=Edges[[#This Row],[Vertex 2]], Edges[[#This Row],[Vertex 1]], "")

(That formula says "if this edge is a self-loop, insert the name of the vertex; otherwise insert empty text.")

3. On the Vertices worksheet, add a column called "Self Loop Count."

4. In the first cell of the Self Loop Count column, insert this formula:  =COUNTIF(Edges[Self Loop], Vertices[[#This Row],[Vertex]])

(That formula says "count the number of cells in the Self Loop column on the Edges worksheet that have a value equal to this vertex name.")

5. In the Ribbon, select NodeXL, Visual Properties, Autofill Columns.

6. In the Autofill Columns dialog box, select Self Loop Count as the source column for Vertex Opacity.

7, Click Autofill.  Vertices with the largest Self Loop Count will be the most opaque.

On the second question, post the entire formula that is giving you a problem, and tell me in which cell you are trying to use it.

-- Tony

Jul 13, 2011 at 5:57 PM

Dear Tony,

Thank you so much for your answer -- it works great!

Regarding the second issue - your formulas worked perfectly fine. However, returning to a cell after the formula was executed, I saw it got a new syntax --->  =IF([@[Vertex 1]]=[@[Vertex 2]]; [@[Vertex 1]]; ""). This might be helpful to other people who might encounter the "#This Row" problem.



Jul 13, 2011 at 6:40 PM
Edited Jul 13, 2011 at 8:55 PM

It's an Excel 2010 vs. 2007 difference.  In Excel 2010, "@" means “[#This Row]”.  See "Table Improvements in Excel 2010" at

-- Tony

Sep 4, 2011 at 7:43 PM


The above post is really useful. Thanks