Data-linked diagrams: Linking data from an external data source

Yana Terukhova is a Product Marketing Manager on the Microsoft Visio team.

Visually displaying always up-to-date information on clearly laid out diagrams helps to communicate information effectively. In this post, we will see how to connect data from external sources to a diagram.

Linking data to Visio diagrams was first introduced in 2007 and has been overwhelmingly popular. We’ve improved the functionality since then and the capability has become increasingly more powerful. Associating data with shapes on a diagram does not require any code. There’s a simple wizard which guides you through linking your data source to your diagram. Once you’ve linked data to diagram shapes, apply data graphics to shapes–text, data bars, icons, or color by value conditional formatting–to visually display numerical data. The important point here is that data-linked diagrams are dynamic; the data graphics change when data is refreshed in the source (like Excel).

To get started, we created a simple organization chart in Visio. We’ve chosen the coin layout for this example. Once we have added the data, we can change the layout to better match the data format. As you can see, we have name, title, and photos for each of the members in our organization.

You can imagine that all types of data that be visualized–from vacation status/training completion, to number of documents in a particular step in a process, and to percent budget attained. Options are vast.

Before you add any data to a diagram, it is a good idea to decide exactly what data you want to see, because this will define which data visualization format best fits your needs.

A common task in organizations of all sizes is to track budget and spending, either by department or at the budget owner level. Let’s see how we can use Visio to show how each person is doing against their budget.

In this example, we will display revenue attainment, budget, and number of employees reporting to certain people. Here is the current view of our data in Excel.

Using this file as the data source, you could also link it directly to real-time budget information in a back office system (such as Microsoft Dynamics and other data sources).

It is important to remember that your data needs to be consistently arranged in the source, so Visio can understand all the logic behind it. For example, keep rows filled with consistent data and name all the columns in a consistent manner. Very often, when data is not displaying appropriately in a Visio data-linked diagram, there is a problem with how this data was originally organized in the source.

There are two steps to link data in Visio:

1. Import the data source into Visio. This creates the link between your Visio file and the data source, but does not link any of the shapes to that data. 

   Now we can see that the Excel and Visio files are linked together.

   

    

2. Link shapes to data. Once the data is associated with your diagram, you can associate individual rows in the data with shapes on the page (a single row may be associated with multiple shapes, such as visualizing multiple dimensions of data). Data can be associated by dragging individual rows from the shape data window or by automatically linking (if you have columns that correspond to shape data elements).

We linked automatically in this example, and Visio quickly associated all the shapes by unique names of each person in the chart.

You may be surprised by the result–there are text links to the right of the shapes. This is the default data graphic as text. Now we can change and add data graphics to the diagram to make it look exactly as we want it.

–Yana Terukhova, Product Marketing Manager