Linking Excel Ranges to Visio Shapes

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:


Now that the Names range is not using the new Table feature in Excel 2010, it becomes visible in the Data Connector dialog in Visio:


Consequently, I could link the named range as an External Data Recordset in Visio:


Let’s hope that this bug is fixed in the next version of Visio…

Finally, here are some useful articles about named ranges and tables in Excel:


3 Responses to “Linking Excel Ranges to Visio Shapes”

  1. Scott Helmers Says:

    Nice detective work, David!

  2. Chris Roth Says:

    Just to clarify: If you create a named range *first*, then it works, but if you name a formatted-as-table range, then it doesn’t show up properly without your fix. At least that’s what I am seeing.

  3. davidjpp Says:

    Good point Chris … I love the the Format as Table option, and so I invariably use that first … thus creating the Table references.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: