Excel-to-Visio Data Integration

 

There have been times when I needed to sketch out a site map or Use Case map in Visio based on use cases I have tracked in Excel.  In the past, I have used the Visio Organizational Chart Wizard to build out these hierarchical trees.

The wizard can use an Excel and “Parent Ids” to automatically layout org chart Shapes.  It’s really an efficient way to get shapes on the page with data fields that can be refreshed when the Excel data is updated.  Unfortunately, the diagram is an Org Chart so there are limitations to what you can do with the Shapes.

Today, I was working through a “tear-off" sheet” full of Post-It Notes created from a RIP (Rapid Initial/Iteration Planning) session with a client.  Into Excel they went.  And because it’s been a while since I’ve used the Org Chart wizard, I was poking around Visio 2007 to find where Microsoft decided to move the damn thing.

Under the “Data” tab in the Ribbon, I found the Link “Data to Shapes” button.  The Org Chart wizard uses this same data integration feature but this / *MY* method affords you with some more flexibility about what Shapes you can use, what changes you can make to the Shapes, and how the Shapes are connected.

1. In Excel (2010, if you’re wondering), create a table.  It doesn’t matter whether you leave it as simple range or if you “Format as Table.”  You just need a collection of records to work with.  Add a column for a unique Id.

Hint:  I use the Fill as Series feature (Alt + E,  I , S is the old Excel 2003 shortcut that still works, for those who are wondering).  Then, I apply the Conditional Formatting for unique or duplicate values to the column to make sure I don’t accidentally create a non-unique Id.

1. Save the File where you’ll be able to find it.  Keep in mind that you will need to have the Excel file open while you’re working with the data in Visio.
2. In Visio (2010), you can create a new drawing –or- use one you already have.  This is one of the nice things about using a custom data connection because the Org Chart wizard creates a new drawing.
3. In the Visio Ribbon, select Data > Link Data to Shapes.

1. When the data connection opens, you can select Excel as the data source.  There are other options but Excel is the simplest.  It would be great one of these options was Team Foundation Server (TFS) to synch up with backlog stories quickly but if that’s your goal, one of the options will certainly get you there with a little more effort.
2. Click Next.

1. Browse to the Excel file you saved and click Next.
2. In the next screen, you can select  an entire worksheet if you don’t have much else on it but the table, otherwise, you can click on “Select Custom Range…” to pick an area.  Be warned though, that you cannot use a Named Range so you will need to run the Refresh Configuration to update your data source range when you add more records.
3. Click Next.

1. Click on the “Select Column…” button if you’d like to customize what columns to pull over.
2. Uncheck any columns you don’t think you’ll use and click OK.

1. You can also filter what records you want to use by clicking on the  “Select Rows…” button.  For example, you may want to only use records that have an In-Scope value that’s not blank.
2. Click OK.

1. Click Next.
2. In the next window, select which column is the unique Id field you created in Excel.  You may have multiple columns that make up a unique key but whether it’s one or many columns, there should only be one record per combination.
3. Click Next.
4. Click Finish.

1. You should see a window open in Visio called “External Data.”  Mine is shown docked on the right but by default, it docks at the bottom.
2. If you don’t see the window, click on the Data tab in the ribbon and check the “External Data Window” option in the Show/Hide section of the Data ribbon.
3. In the list, you’ll see all the records that were pulled in based on the settings you created in the wizard with their columns and values.

1. Select a record in the External Window and drag it into your drawing.  It will use the default Shape or the last one you selected.
2. To use a Specific shape, just click on the one you want in your Stencil.
3. Dragging the record into your drawing will drop a Shape into your drawing with the data from its record in Excel.
4. By Default, the Data Graphic (as it’s called) is positioned outside of the Shape to its right.  This is a useful format if you are using the text inside the Shape.  I use the record data instead so I move the Data Graphic into the Shape and hide the Shape’s text box.  The Data Graphic is basically a group of text boxes and images that sit on top of the Shape so trying to using the Formatting tools won’t work on the Shape to change its color of font colors.  You’ll need to select each section within the Data Graphic to make changes and even so, these changes won’t apply to all your Shapes.

1. To change the formatting of the Data Graphic, right-click on the Shape and select Data… > Edit Data Graphic… from the context menu.

1. In the Edit Data Graphic window, you can add additional columns from your Excel table.  I believe it defaults to only the first two columns but you can add more.  Be careful because it will be easy to think you can show all the data you have but it will quickly become crowded and hard to use.
2. Change the Default position to “Center” and “Middle”
3. Select to “Hide shape text when data graphic is applied to make sure that any text you have in the Shape will not appear.
4. In the “Applies to” to All Shapes with this data graphic.  This option assumes that you don’t want different layouts for different records or Shapes but you can certainly manually select which Shapes to update.
5. Click OK.Double-Click on a field in the Edit Graphic window to make additional Changes

1. Double-Click on a field in the Edit Graphic window to make additional Changes, like how the data field should be displayed:  Text, Data Bar, Icon Set, Color by Value.  Many of these will be family in the Excel Conditional Formatting features.
2. Each Type will have additional options.  For example, there’s a Style that can be selected.
3. Depending on how many fields you use, you will want to adjust the position of each to make sure they sit properly.

1. Here’s a basic layout with the Use Case Id on top and its Name in the middle.

1. Here’s a more complicated version.  I don’t recommend it but it shows you a few more options based on the Status (Icon Set) and In-Scope (white vs. grey).
2. In the External Data window, you can see the chain link next to the records that indicate which ones have shapes in the drawing.

1. Don’t like the Shape you used?  Or you want to use a Shape you already laid out on the page?
2. Drag a record from the External Data window into the Shape.  it will automatically create the connection.

1. Did you add some new records or make updates?  Right click in the External Data window and select “Refresh Data…” from the context menu.  Or select “Refresh All” from the Data tab.

You’ll see the connection get refreshed and the new records appear in the External Data window.

Bam.  Enjoy.