Linking Excel Ranges to Visio Shapes

David Parker

12 years ago

I have often linked Excel worksheets and ranges to Visio over the years. I used the Database Wizard initially, but have been using the Link Data to Shapes feature since Visio 2007. I had noticed that I sometimes didn’t get a full list of the available ranges when trying to link in Visio, so I decided to investigate why.

Firstly, I created a sample Excel 2010 worksheet with a header row, and three rows of data in Sheet1.

So, I then used the Link Data to Shapes feature in Visio 2010 and browsed to the workbook to attempt a link…

Sure enough, the first sheet was listed as available to link to in the Data Selector dialog:

The column names and sample values were displayed, as expected:

I was content that this would work, so I cancelled the dialog, and returned to Excel, where I used the Format as Table button on the table that I had just .

A quick gander at the Name Manager dialog shows that this feature has created a named range called Table1:

If I then create a named range that covers the same cells as this table, then the default behaviour of Excel 2010 is to create a reference to the underlying table:

So, I created a range named Names by selecting the cells in Excel, and I then ended up with a table and a named range that refers to that table:

Unfortunately, neither of these ranges appear in Visio when I attempt to link to them using the Data Selector. The answer is to either edit the Refers To for Names to use the cell references rather than the table reference:

Alternatively, you can untick the Use table names in formulas Excel option before creating the named range. This will have the same effect as editing the Refers To manually: