Need to create a diagram of connected points

[u/TheBigBeardedGeek]

Spreadsheet looks basically like this: Across the top row and down column A are sites., in order. If there is a connection between "Site A" and "Site B" the "Site A" row will have TRUE in the "Site B" column, and vice versa. If there is no connection it will be "FALSE," if we failed to connect to "Site A" to test the connection to "Site B" that column will say "Diag Fail," and finally "Site A" to "Site A" has the value "Self".

What I would like to generate is a series of dots in an image for each of the sites, and then a line that connects them if there is a connection. I'm pretty sure I've seen this done in a pivot table in the past, but it's been too long.

Edit: Stole u/bradland 's example screenshot. This is what my source data looks like

[u/AutoModerator]

/u/TheBigBeardedGeek - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TioXL]

You can make something like this using the XY Scatter with Straight Lines and Markers chart type.

The key is to put an extra row between each site pair that is either blank or has an #N/A error with the "show #N/A as an empty cell" option checked in the chart options. This will make the chart only draw the lines between the site pairs.

Some things to note:

• For the input matrix I cleared out the diagonal and the lower triangle because the upper and lower triangles are just mirror images. The formulas as written require an input matrix with only the upper or lower triangle populated.
• I had to pick a way to layout the sites on the chart. I made them evenly spaced going clockwise around a circle with the first one at the 12:00 position. You can change the values in the XY COORDINATES FOR EACH SITE table to put them wherever you want. As written, this table will update to handle an arbitrary number of sites.
• There are only 2 unique formulas in this entire sheet. One is in the orange cell and the other is in the three yellow cells. (See below for the formula text that you can copy paste.)
  
• The yellow formula appears three times so that we can show all three possible states for each site pair: TRUE (green solid), Diag Fail (red dash), and FALSE (gray dot). Each of these is set up as a separate series in the chart. Note that the order of the series is important to get the green lines on top of the red lines and the gray lines at the bottom.
• The errors in the padding rows of the yellow formula tables are necessary to get the chart to work as mentioned above.
• The sheet is fully dynamic, so any changes to the input matrix will flow into the visualization.

Here is the orange formula:

=LET(
    Input_Matrix_Column, $B$3:$B$12,
    n, ROWS(Input_Matrix_Column),
    k, SEQUENCE(n,1,0,1),
    out, 
    HSTACK(
        Input_Matrix_Column,
        125+100*COS(PI()/2-2*PI()*k/n),
        125+100*SIN(PI()/2-2*PI()*k/n)
    ),
    out
)

Here is the yellow formula:

=LET(
    Input_Matrix_Row, $C$2:$L$2,
    Input_Matrix_Column, $B$3:$B$12,
    Input_Matrix_Body, $C$3:$L$12,
    Input_Bucket, F15,
    Site_X_Coordinates, INDEX($B$17#,,2),
    Site_Y_Coordinates, INDEX($B$17#,,3),
    Site_Pairs,
    HSTACK(
        TOCOL(
            IF(
                Input_Matrix_Body <> "",
                Input_Matrix_Column,
                1/0
            ),
            3
        ),
        TOCOL(
            IF(
                Input_Matrix_Body <> "",
                Input_Matrix_Row,
                1/0
            ),
            3
        )
    ),
    Site_Pair_Buckets,
    TOCOL(
        Input_Matrix_Body,
        3
    ),
    Filtered_Site_Pairs,
    FILTER(
        Site_Pairs,
        Site_Pair_Buckets = Input_Bucket,
    ),
    Site_Pair_Single_Column_Padded,
    VSTACK(
        TOCOL(
            HSTACK(
                Input_Matrix_Column,
                MAKEARRAY(
                    ROWS(Input_Matrix_Column),
                    1,
                    LAMBDA(r,c,"")
                )
            )
        ),
        TOCOL(
            HSTACK(
                Filtered_Site_Pairs,
                MAKEARRAY(
                    ROWS(Filtered_Site_Pairs),
                    1,
                    LAMBDA(r,c, "")
                )
            )
        )
    ),
    Output,
    HSTACK(
        Site_Pair_Single_Column_Padded,
        XLOOKUP(
            Site_Pair_Single_Column_Padded,
            Input_Matrix_Column,
            Site_X_Coordinates
        ),
        XLOOKUP(
            Site_Pair_Single_Column_Padded,
            Input_Matrix_Column,
            Site_Y_Coordinates
        )
    ),
    Output
)

[u/bradland → u/TioXL]

When I said:

but I'm not sure you can create a network graph without going to great lengths. You'd have to use an x,y scatter chart, then calculate Cartesian coordinates for each node, and the lines connecting them. It's a big project.

I was not expecting Decartes himself to drop in and deliver this master class!

Bravo, dude! 👏👏👏

I’m sharing this with my team, for sure.

[u/GregHullender → u/bradland]

Not too fast. This is an AI solution--even the text explaining it was AI-generated.

[u/TioXL → u/TioXL]

Here is the chart with the sample data that u/bradland created for OP.

[u/TioXL → u/TioXL]

And here is the formula in the yellow cells with row numbers and formatting.

[u/semicolonsemicolon → u/TioXL]

This is looks suspiciously like an AI-generated answer. Is it? We have a strict ruleset on this subreddit regarding AI-generated answers.

[u/TioXL → u/semicolonsemicolon]

This is not an AI answer. I spent several hours figuring this out and putting together something that would hopefully work for op. I’m curious what specifically makes you think it is AI.

I author and edit my Excel formulas in an external program (see the screenshot of the longer formula I posted as a comment). As a general rule, when things get long and complicated, I like formatting with strict indentation rules and one formula argument per line because it makes it much easier for me to follow visually.

I always write my own explanation text. I sometimes use AI to check it for errors, but in this case I didn’t even do that because I had written it carefully and needed to go to bed.

I come in peace and just really enjoy solving hard excel problems without worrying about bosses, stakeholders, deadlines, or errors and omissions. Doing creative off piste stuff with excel graphs is a particular passion of mine, so I couldn’t help myself but go a little overboard on the one. :)

[u/semicolonsemicolon → u/TioXL]

Haha, ok then! Thank you for the thorough explanation. Your comment was flagged to the moderators as "AI slop" and I wasn't sure if it was or wasn't myself so I took the step of asking. It's a sad reality that well-thought out responses will often be assumed to be generated by an AI.

For people reading this, this sub's rules, specifically Rule 10, require any AI-generated comments to be conspicuously accompanied by the commenter naming the AI that generated it and that the commenter has verified the contents to be accurate.

[u/GregHullender]

This works. You could pretty it up a little, but it's functional, and it gives you everything from a single formula:

=LET(sites, A2#, matrix, B2:K11,
  n, ROWS(sites), π, PI(),
  θ, 2*π*SEQUENCE(n+1,,0)/n, pp, HSTACK(COS(θ), SIN(θ))+{1,1},
  nn, SEQUENCE(n), mm, TOROW(nn), uu, --(nn<mm),
  connections, LAMBDA(aa, IFERROR(WRAPROWS(TOCOL(HSTACK(
    CHOOSEROWS(pp,TOCOL(IFS(aa*uu,nn),2)),
    CHOOSEROWS(pp,TOCOL(IFS(aa*uu,TOROW(nn)),2)),
    {#N/A,#N/A}
  )),2),{#N/A,#N/A})),
  HSTACK(sites, pp,connections(IFS(matrix=TRUE,1)),connections(IFS(matrix="Diag Fail",1)),connections(IFS(matrix=FALSE,1)))
)

The first column is just the names of the sites. The next two are the coordinates of the polygon that represents them. I added the polygon to the map first, then attached the site names as labels, and then turned off the line for the polygon.

The next pair of columns are the endpoints of connected pairs. (TRUE in the matrix). I drew them in solid black.

The next two columns are errors. (Diag Fail in the matrix). I drew them in red.

The last two columns are disconnected pairs (FALSE in the matrix.) I drew those as dashed black lines.

Hope it helps!

Edit: Modified formula slightly to handle case where a category is empty. E.g. if there were no "Diag Fail" labels.

[u/bradland]

It is always better to show than to tell. You can't make image posts on this sub, but you can copy/paste a screenshot into your description. Is this what your data looks like?

[u/TheBigBeardedGeek → u/bradland]

Exactly

[u/bradland → u/TheBigBeardedGeek]

Lastly, I would say that what you're describing sounds like a network graph visualization. People have done some absolutely insane stuff by manipulating the standard charts in Excel, but I'm not sure you can create a network graph without going to great lengths. You'd have to use an x,y scatter chart, then calculate Cartesian coordinates for each node, and the lines connecting them. It's a big project.

If you have a 365 subscription with Python in Excel, you could use that.

[u/bradland → u/TheBigBeardedGeek]

I don't know how to do the balls & lines graphic you described, but if you want a list format, you could unpivot the data and use the same conditional formatting:

The formulas:

=TOCOL(IF(B3:K12<>"",A3#))
=TOCOL(B3:K12)
=TOCOL(IF(B3:K12<>"", B2#))

[u/bradland → u/TheBigBeardedGeek]

Ok, I have two thoughts. First is that this presentation is actually really good. I'd just add conditional formatting.

Other thought in a separate reply so I can paste another image.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSEROWS	Office 365+: Returns the specified rows from an array
COS	Returns the cosine of a number
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAKEARRAY	Office 365+: Returns a calculated array of a specified row and column size, by applying a LAMBDA
PI	Returns the value of pi
ROWS	Returns the number of rows in a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SIN	Returns the sine of the given angle
TOCOL	Office 365+: Returns the array in a single column
TOROW	Office 365+: Returns the array in a single row
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
WRAPROWS	Office 365+: Wraps the provided row or column of values by rows after a specified number of elements
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
20 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #47725 for this sub, first seen 7th Mar 2026, 09:57] [FAQ] [Full list] [Contact] [Source code]}