Create Diagrams In MS Visio 2010 By Linking Excel Spreadsheet

Microsoft Visio is an excellent Vector-Graphic software, carrying a lot of tools, functions, features and most importantly thousands of diagram types for creating generic diagrams, chart flows and designing system architecture. Like it’s counterparts, Microsoft Visio 2010 has also been augmented with some advance features, tools, and diagrams. In this post we will be covering data link feature which will let you collaborate with Excel 2010 spreadsheet, eventually simplifying diagram creation procedure by linking directly with selected spreadsheet.

To begin with, launch Visio 2010 and for creating a diagram from scratch, in backstage view, click Create.

create Create Diagrams In MS Visio 2010 By Linking Excel SpreadsheetView in gallery

As we are inserting Excel 2010 spreadsheet into Visio for collaboration, we have created a student record in Excel spreadsheet having fields; ID, Name, Course, Marks, Grade, Status, and Total.

tabkle Create Diagrams In MS Visio 2010 By Linking Excel SpreadsheetView in gallery

In Visio 2010, navigate to Data tab and click Link Data to Shapes button.

newView in gallery

It will bring up Data Selector wizard, select Microsoft Office Excel workbook and click Next.

2View in gallery

In this step, click Browse and select the Excel Worksheet which you want to insert for depicting data values in pictorial representation. Click Next to continue.

3View in gallery

Select the Excel worksheet which you want to include. You can also select range, by clicking Select Custom range. We will also enable first row of data contains column headings option, as in our case first row of Excel spreadsheet contains column headings. click Next to continue.

4View in gallery

Now select columns and rows which you want to include, click Select Columns and a dialog will appear showing all the columns in specified worksheet. Disable unwanted columns title and click OK.

5View in gallery

For inserting specific rows repeat the procedure to insert desired rows. You will see the selected columns and rows in the wizard dialog, now click Next to proceed further.

5-1View in gallery

This step is about selecting a column or row, which contains unique identifiers, i.e, unique value, as inserted Excel sheet has an ID field having unique values, so we will select ID field. Click Finish to end the wizard.

6View in gallery

In the bottom of Microsoft Visio window, you will see the inserted Excel spreadsheet containing selected rows and columns.

7View in gallery

Now you have to select the diagram type, for this, from More Shapes options, select the desired type of diagram. For instance, we are selecting Activity diagram which has been widely used for depicting software module’ activity. It falls in UML(Unified Modeling Language) category, which further contains a lot of software specific diagrams.

choose shapes 7View in gallery

Upon selection, you will see all corresponding shapes contained by specific type of diagram. As for UML Activity, it shows all the shapes which are essential to make an activity diagram.

Now select Initial State shape from the list and drag it over to main diagram area.

initial starteView in gallery

Select Action state and start dragging specific rows from inserted Excel worksheet into diagram. As Excel sheet is linked with the diagram, it will automatically show you the related Data Graphic (data value) with shape.

action 1View in gallery

For changing Data Graphic, right-click the inserted shape and from Data options, select Edit Data Graphic.

edit data shapeView in gallery

In the Edit Data Graphic dialog, inder Data Field you can change field which you want to show with inserted shape. For example, if you want to show the Name of student and Course, select the respective fields from drop-down list.

You don’t have to change it for each shape, it will automatically apply changes on all the inserted shapes. Once done, click OK to return.

fieldsView in gallery

To give you the generic idea, we are just depicting one student record in UML activity diagram, now we will manually add a State shape showing total marks secured by student. Drag the State shape in to main diagram area, navigate to Home tab and click Text, to add text in it.

add textView in gallery

Now start connecting shapes with Control Flow shape, select it from left sidebar and drag it over to main diagram window.

control flow 1View in gallery

By following the above procedure, you can make a diagram in Microsoft Visio 2010 out of any Excel datasheet, rather than looking up values in Excel spreadsheet and entering them in diagrams.

  • Bon felly

    Brilliant Post! Thanx a zillion….
    Can i have these visio & excel files…. just checked u guyz on google n seems like you guyz coverd up whole office 14…Awesome

  • Bon felly

    I did download visio beta….but i want these sample files u made in Visio n Excel 2010…i have tried linking exel & visio but in vain….:(

  • Pingback: Protect Document And Shapes In Visio 2010()

  • Mark

    Do you know how to create the drawing automatically without having to drag the data on to the drawing? Is there a way to do this based upon a template? Here is the example that I want:
    – Export data from a custom mvc application into excel worksheet
    – user saves data on their drive
    – user opens up visio and based on the template can create the drawing from the excel data without any further interaction.
    Do you know if this is possible?

  • Pingback: How To Edit Body Header Title In Visio 2010()

  • 1703geraldthorne

    Creative post , Incidentally , if your company is searching for a AAR Residential Lease Agreement , my boss filled out and faxed a template form here