Would you like to automatically create a detailed, polished Visio flowchart from an Excel workbook? You can do this by using the Data Visualizer templates. Each template has an associated Excel workbook used to convert process data into a flowchart in Visio. You can also use your own custom Excel template, map Excel data to shapes in the Create Diagram from Data wizard, and automatically create the diagram. You can even add additional columns of data in Excel that are converted to Visio shape data.
Visio also supports two-way synchronization between the Visio diagram and the Excel workbook. Not only can you create the flow chart, by editing data in Excel and refreshing the diagram from Visio, but you can also make changes to the Visio Diagram and then update the Excel data from the diagram.
The ability to use the Data Visualizer templates is only available with Visio Plan 2. If you're not sure what version of Visio you're using, select File > Account > About Visio. See Visio Plan 2 to learn more about the subscription version of Visio.
If you don't see the Data Visualizer templates or if you have other questions around getting started, visit the Data Visualizer frequently asked questions page.
Note: In order to use this feature, you must have an Microsoft 365 subscription. If you are an Microsoft 365 subscriber, make sure you have the latest version of Office. If you are an IT professional managing the Microsoft 365 update process, check out the channel releases page to learn which updates are provided in each channel.
We also have three new templates that allow you to create a chart without having a Visio license:
In this article
Create a Data Visualizer diagram
Before you begin
Gather your ideas and work them out in a draft form on paper or by inking. This rough draft can help you jump-start your work and make the following steps go smoother.
To create a Data Visualizer diagram, you can work simultaneously in Excel and Visio. You might find it easier to work in two windows on your Windows device or in a multi-monitor environment. Visually comparing your work between Excel and Visio helps confirm you are doing what you intend to do.
Recommended Watch this Microsoft Office video to see it in action: Data Visualizer: Demo.
Stage 1: Choose a Data Visualizer template
-
Open Visio, or select File > New if you are editing a diagram.
-
Select TEMPLATES.
-
Select Flowchart.
-
Select Basic Flowchart - Data Visualizer, Cross-Functional Flowchart - Data Visualizer, or Audit Diagram - Data Visualizer.
A Data Visualizer template has an Excel icon in the top left corner.
For more information on flow charts and their shapes, see Create a basic flowchart and Create a cross-functional flowchart.
Stage 2: Create an Excel workbook
-
In the template preview, select Excel data template.
Note If you want to use the same sample Excel templates used in this article for reference, you can download them from the following links:
-
Select File > Save, specify a computer, network server, or SharePoint document library, enter a file name, and then select Save.
-
To add a new worksheet, at the bottom of the workbook, select New sheet
. -
Create an Excel table. For more information on using Excel tables, see Overview of Excel tables, Create or delete an Excel table, and Resize a table by adding or removing rows and columns.
-
Add columns and rows to the table which are used to create the Visio diagram. There are specific requirements for the Excel table column headers and values. Use the section, How Excel columns interact with Visio flowchart components as a visual guide.
Stage 3: Add additional shape data to the Excel table (optional)
-
Examine the Process Map tab in the Excel template. The columns in the Excel table are colored blue and green. The color provides a visual clue about the difference between the columns.
The blue columns are used to create the flowchart in the Visio diagram. All columns are also added as shape data.
-
To add additional shape data, add one or more columns to your Excel table.
The green columns indicate additional shape data you can add to each shape. You can add any number of additional columns. By default, this data is not visible in the diagram, unless you add data graphics. To see the shape data in the Visio diagram, select Data > Shape Data Window. For more information about shape data and data graphics, see Add data to shapes and Data graphics, an overview.
Stage 4: Create a Visio diagram with the wizard
-
Switch back to Visio, in the template preview select your unit of measurement, and then select Create.
-
In the first page of the Create Diagram from Data wizard, do the following:
-
Under What diagram do you want to create from data, select a template: Basic Flowchart, Cross-Functional Flow Chart (horizontal), or Cross Functional Flow Chart (vertical).
-
Under Select the Excel workbook, select a workbook from a list of most recently used workbooks, or select Browse to locate the workbook you created in Stage 2.
-
Under Select a table or a custom range in the workbook, select the table containing your Excel process data.
-
Select Finish. This accepts all default values that Visio fills in for the other pages of the wizard.
-
As a result, the diagram is automatically created inside a container. Additional Visio pages are added depending on the size of the flow chart.
Stage 5: Save the Visio diagram
-
Select File > Save, find a location, enter a file name, and then select Save.
It's a good idea to keep the Excel and Visio files in the same location because they work hand-in-hand.
Stage 6: Synchronizing the data and the diagram
After you create a diagram with Data Visualizer, you may want to make changes to it. Visio supports bi-directional synchronization between the source data and the diagram.
Tip Keep both files synchronized and up-to-date after you finish working with them. When you change the Excel data, refresh the diagram; when you change the diagram content, update the Excel data.
To modify the data in Excel and then refresh the diagram
-
Make changes to the source data in the Excel table. You can add, update, or delete rows.
-
Save the Excel workbook.
-
In Visio, click in the diagram container to enable the Data Tools Design tab in the ribbon, and then select Refresh Diagram.
As a result, the changes you made in the Excel workbook are displayed in the Visio diagram. Note the following:
-
If you have made any changes to the diagram that may get lost after refreshing, Visio displays a Refresh Diagram Conflict dialog box.
-
Refreshing only makes changes to the shapes and connections inside the container.
-
Any formatting changes you make to the shapes or connectors inside the container are preserved.
-
Keep all diagram shapes and connector lines inside the container. Any other objects on the page but outside the container are not affected. For more information about containers, see Clarify the structure of diagrams by using containers and lists.
To modify the content in the diagram and then update the data in Excel
-
Make changes to the Visio diagram. You can add, update, or delete shapes and connectors.
-
If the Excel workbook is on a desktop computer or network file server, make sure it is saved and closed.
-
Click in the diagram container to enable the Data Tools Design tab in the ribbon, and then select Update Source Data.
As a result, the changes you made in the Visio diagram are updated in the Excel workbook. If data may get lost in Excel, Visio displays the Update Source Data Conflict dialog box. To avoid problems, use the following best practices:
-
Save a backup of the Excel data before you overwrite changes.
-
In the Excel workbook, use an Excel Table as your data source, not a custom range. After you select Overwrite in the Update Source Data dialog box, Visio converts the custom range to an Excel table.
-
Do not use formulas in the Excel data source. After you select Overwrite in the Update Source Data dialog box, Visio converts the formula to the formula result.
-
You created the diagram using the following option in the Create Diagram from Data wizard where data columns are mapped to functions and phases: Mapping > More Options > Retain the order of column values from your data to create > Select Function or Swim lane or Select Phase or Timeline. In this case, Visio requires an additional column in your data to represent the connection between shapes. After you select Overwrite in the Update Source Data dialog box, the additional column is added.
-
You created a cross-functional flow chart diagram with both swimlanes and phases based on the order of data values in Excel. You created the diagram using the following option in the Create Diagram from Data wizard where data columns are connected to process steps and activities: Mapping > Specify how to connect the process steps/activities > Connect sequentially. In this case, Visio requires an additional column in your data to represent the connection between shapes. If you change the order of the swimlanes or phases in the diagram, the order is changed in the data source. After you select Overwrite in the Update Source Data dialog box, the swimlanes and phases in the diagram no longer follow the order of the data in the Excel workbook.
Stage 7: Customize the interaction between the Excel table and the Visio diagram (optional)
There are times when you may want to customize the creation of your Data Visualizer diagrams. For example, you may want to use different shapes to conform to your organization standards, or map to different column names in the Excel table.
-
When you select Create to enter the Create Diagram from Data wizard and after you enter values in the first page (See Stage 4), select Next or Back to display each page in the wizard.
-
Select Help (?) to get more information about how to customize each option on the page. For more information, see Use the Create Diagram from Data wizard.
How Excel columns interact with Visio flowchart components
Use the following image walkthrough to understand how each Excel column is used to create the Visio flowchart.
Note that a series of "tabs" ("ID," "Description," "Next ID," and so on) are used below to illustrate the various columns of an Excel table. Click a tab name to see the illustration for that column.
Process Step ID A number that identifies each shape in the flowchart. This column is required, and each value in the Excel table must be unique and not be blank. This value does not appear on the flowchart.
Process Step Description Text added to each shape in the flowchart.
Next Step ID The Process Step ID of the next shape in the sequence. A branching shape has two next steps and is represented by comma-separated numbers such as 4,5. You can have more than two next steps.
Connector Label For branching shapes, connector labels are represented as text separated by a comma, such as Yes,No. Yes corresponds to 4 and No corresponds to 5 in the example.
Shape Type The type of shape you want to represent in the Visio diagram, such as Process or Decision. Microsoft recommends using the drop-down lists to help validate your choices.
Function and Phase The function (or swimlane) and phase (or timeline) that each shape belongs to. This column only applies to a Cross-Functional Flowchart diagram and is ignored by a Basic Flowchart diagram.
Use other stencils to create a Data Visualizer diagram
Besides using the Data Visualizer templates, you can also use stencils from other templates when you create a Data Visualizer diagram. For example, create process diagrams for financial transactions or inventory management. There are other process-oriented stencils including BPMN Basic and Work Flow Objects templates. You can even add custom shapes by using the My Shapes stencil. For more information, see Create, save, and share custom stencils.
-
Open a Data Visualizer diagram (Basic Flowchart or Cross-Functional Flowchart) that you have already created.
-
Select Create to enter the Create Diagram from Data wizard and after you enter values in the first page, select Next until you display the Shape Mapping page in the wizard.
-
Add one or more stencils using the More Shapes option.
-
Select any shape and map it to the data values of your Shape Type column by dragging it.
Create and link to subprocess diagrams
Currently, the Data Visualizer feature doesn't support the creation of sub-processes from a single Excel table. However, you can create multiple diagrams using different tables in one Visio file, and then link shapes as an alternative solution.
-
In the Visio desktop app, select a Data Visualizer template and then select the Excel data template link to open Excel. Then select Create to open the Visio Data Visualizer template.
-
Use the Excel data template to create tables for your processes and sub-processes. Put the table for each sub-process in a different sheet and then save the Excel file.
For example, Sheet 1: Main process, Sheet 2: SubProcess1, Sheet 3: SubProcess2.
-
Switch back to the Visio desktop app and use the wizard to import the Main Process table to Page 1 of your Visio canvas.
-
Below the canvas, select the + icon to create a Page 2. In the Data tab, select Create to launch the import wizard again. Choose to import data from the next sheet in the Excel workbook and complete the steps in the wizard.
-
Continue to create new pages in Visio and using the wizard for each of the sub-process data tables you've created.
-
Finally, select the page with the main process diagram and right-click a shape to link to a sub-process. Select Hyperlink and link it to any shape on one of the sub-process diagrams.
Apply Data Graphics to your diagram
You can enhance your diagram by adding data graphics, which helps you visualize shape data on the diagram. In effect, you can turn your diagram into a process dashboard in which the data graphics display Key Performance Indicator (KPI) information using data graphics icons based on your shape data.
-
Open the Data Visualizer diagram.
Select the container for which you want to apply Data Graphics.
-
Select Data > Data Graphics Fields.
-
Select the data graphic fields you want to add to your diagram.
The data graphics are applied to the entire diagram. To hide data graphics for certain shapes, keep the data column values blank in the Excel workbook for those rows.
-
If you want, you can set additional options in the Data tab. For more information, see Data graphics, an overview.
Manage the Excel workbook linked to the diagram
Occasionally, you may need to relink or update the associated Excel workbook. You can conveniently do this from Visio.
Relink the Excel workbook with the Visio diagram
If the Excel workbook linked to a Data Visualizer diagram is renamed or moved to a different location, then the link between the Visio diagram and Excel workbook is broken. This issue can also occur if you rename the Excel Table.
-
Select the container for the Data Visualizer diagram. This enables the Data Tools Design tab in the ribbon.
-
Select Data Tools Design > Relink Source Data.
-
Browse to the renamed or moved Excel workbook.
-
Select a table or custom range in the workbook.
-
To fix the broken link, select Relink.
When you try to refresh the diagram or update the source data and the link is broken, Visio also prompts you to relink.
Important Don't change the column names in the Excel Table linked to the diagram. Otherwise, the relink operation fails.
Open the Excel workbook linked to the diagram
You can change the linked content in Excel directly from Visio. For example, you may want to add a lot of content and find it easier to edit the data in a spreadsheet.
-
In Visio, select the container for the Data Visualizer diagram. This enables the Data Tools Design tab in the ribbon.
-
Select Data Tools Design > Open Source Data.
-
Make your changes in Excel, and then save and close the workbook.
-
To see the changes in the diagram, select Data Tools Design > Refresh Diagram.
Next steps
Here are a few tasks you might want to do after creating a Data Visualizer diagram.
-
Themes A theme in Visio is a set of colors and effects that you can apply to a drawing with a single click. Themes are a quick way to give your drawings a professionally designed look. For more information, see Apply a professional look to your drawings with themes.
-
Layout Visio has manual guides and automatic tools to help ensure the shapes in your diagram are aligned and spaced well. That helps make your drawing as clear as possible and give it a well-ordered look. For more information, see Align and position shapes in a diagram.
-
Export Export a Data Visualizer diagram and associated Excel workbook to a Visio template package (.vtpx). A Visio template package saves diagram settings, stencils, and data for others to use as a quick starting point to create their own Data Visualizer diagram. Other users can install the template package on their computers. For more information, see Export and install a Data Visualizer template package.
No comments:
Post a Comment