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.
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.
In Visio 2010, navigate to Data tab and click Link Data to Shapes button.
It will bring up Data Selector wizard, select Microsoft Office Excel workbook and click Next.
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.
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.
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.
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.
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.
In the bottom of Microsoft Visio window, you will see the inserted Excel spreadsheet containing selected rows and columns.
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.
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.
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.
For changing Data Graphic, right-click the inserted shape and from Data options, select Edit Data Graphic.
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.
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.
Now start connecting shapes with Control Flow shape, select it from left sidebar and drag it over to main diagram window.
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.