Clusters and Workstations Linked to Excel using Visio Professional

I was recently asked how to easily link an Excel worksheet that contains names of personnel sitting at workstations in clusters to a Visio floor layout. My own approach would probably have been to create an Access or SQL Server database, but I recognise that not everyone is confident about doing that, so I present a way that it can be done simply with just Excel.

The extra complication requested was the ability to easily control the visibility of specific data, so I have added an example of that into the mix.

Firstly, I mocked up a table in Excel. I decided to have separate Cluster and Workstation columns, and so I combined the two together in a column headed Location with a simple formula. I like doing this because I can then create a Pivot Table or Chart easily to give a count of workstations per cluster. I used the 1970 FA Cup winning Chelsea team for the Name and Department columns.


I then used Link Data to Shapes to select this Workstations worksheet, and dragged a row onto a simple rectangle shape. This automatically creates Shape Data rows labelled the same as the Excel worksheet column headers.


I modified the default Data Graphics in order to get the text values nicely aligned left, center, and right. Notice that I used the Text callout that can display the Label as well as the Value, for the left and right, but have chosen not to display the label, but did reverse the label position for the Location value. In fact, I had to put a space in the Label, thus overwriting [Default], to prevent the value from displaying on the left. I used Heading 2 for the Name value. I changed the Border Type to None for all three values.


So far, I have got the values that I want displayed. Now I added three Shape Data rows manually to the page. All three are defined as Boolean, when means TRUE or FALSE. If you do see the Name on the Define Shape Data dialog, then you tick Run in developer mode from File / Options / Advanced.


I then went back to edit my Data Graphic items so that I can control the display of each of these values. The Field dialog can be opened from the More Fields… option in the Data Field drop-down list.


In each case, I manually edited the formula to only display relevant value if the page Shape Data row was set to FALSE. (i.e. do not hide the value):

=IF(ThePage!Prop.HideLocationLabels,””, {Location})

Therefore, the user can select which labels to hide easily:


Finally, duplicated my rectangle to provide one for each row in my Workstation table, and dragged and dropped each row onto an individual rectangle. I then grouped the workstation rectangles for each cluster into a Container, and labelled it accordingly.


I now have data linked rows for each workstation in each cluster. These can be placed over the top of any actual desk layout, that might even be an embedded CAD file. Now whenever the Excel worksheet is updated, the Visio diagram just needs to be refreshed.


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: