03 Connect Visio Pro 2019 Organization Chart to Excel Employee List
HTML-код
- Опубликовано: 15 июл 2024
- In this series of videos, I’m showing you how to use the Microsoft Visio Pro 2019 desktop app to build org charts that are linked to Excel data. This video will teach you how to connect a Visio organization chart to an Excel employee list, including how to:
- create a permanent link between your Visio org chart and the list of employee data in Excel that you used to initially create the org chart
- show the Shape Data pane
- show the External Data window
- use the Quick Import command
- refresh the org chart if the Excel data changes 
Наука
Amazing, this is exactly what I have been looking for!
Thank you very much!!!
Thank you!!! Great Video!!!
Thanks, it was really helpful
Thank you very much for watching and commenting!
Thank you very much for the great explanation about excel date combining with Viso organisation charts.. I have still one question. You said in the video that all the shape information will be visable after refreshing even you have not selected them but how do I make this dat unvisable again?
Very nice! now how do you link an org chart and a spreadsheet that were not created using wizard?
I learned a lot from your video’s! But i have a question: i’ve linked the excelfile (external data with link icons check). But when i change the team and manager of a person, then the informatiin changes correctly, but the shape doesn’t move to the new team after refreshing. Is this possible?
Jantine, thank you for watching! Yes, it is disappointing that Visio cannot move and connect an employee's shape when we change their supervisor (manager). You have to do it manually. Just drag the employee's shape onto their new manager's shape in the Visio org chart diagram. Then click the Relayout button on the Ribbon to improve the layout. I hope to include this in a future video in this series, coming soon.
Thanks for the how-to... however, I'm curious if when the data refreshes if there is any way for a NEW employee to show up or do I have to drag them out of the external data section and connect them manually?
There is no way that I know of to make new employees appear on the org chart automatically.
I've found these videos incredibly helpful. I did have a question however. I was following along with your section on refreshing linked data. I made a name change to the excel file and closed it but when i try to refresh the data, it doesn't take, doens't refresh the name and removes the link that was previously there. Am i missing something? A+ on these videos by the way, super helpful for first time users.
Nicholas, thank you for you very kind compliments! It is hard to say what might be causing the link to break between a row of data in the Excel sheet and the corresponding shape on the org chart. Honestly, I have seen similar problems in my work with Visio; it is not flawless. If we assume that Visio is behaving exactly as we think it should, then the first question I would ask you is, "Are you using unique identifiers in your Excel list (like an Employee ID number column)?" If you are trying to use Employee Name as the identifier field, the link would break if you change an employee's name in the Excel list.
Hello - Your videos have been extremely useful. I have an org chart of 200 people that I have linked an external spreadsheet to. However, I was hoping to mimic the way Visio Data Visualizer works with Role Type and Colors automatically updating the Org Chart. I was hoping to watch the rest of your series but I do not see any more tutorials after this one.
Also wondering how to get rid of Name and Employee (orange) that appear after linking the external data. Thank you. I appreciate your knowledge.
Thank you, Mowgli, for your generous compliments! I hope to produce the rest of the videos in this series soon. In the meanwhile, the Name and Employee that appear in orange once you link to the external data are called Data Graphics. There are a couple ways to remove them; I usually select all the shapes (press Ctrl + A on the keyboard) and then go to the Data tab of the Ribbon, click the Advanced Data Graphics menu (near the far right end of the Ribbon) and choose None. As for updating the org chart the way the Visio Data Visualizer does, I don't think Visio Pro supports that level of interactivity with external data.
@@AccelerateComputerTraining Thanks so much! I'm having a tough time refreshing my dataset to reflect on the org chart. Trying again! ;)
Apologies.... how can I ensure that my dataset connects to the correct shape type? Do I have to adjust that manually? ie. Consultants
Hi Mowgli! Visio assigns Position Type based on whether an employee has subordinates or not. Beyond that, we have to manually change Position Type on those shapes where it guessed incorrectly.
@@AccelerateComputerTraining Me again! Sorry! I've noticed that Visio auto organizes by Name. Is there a way to organize the shapes by Department?
Hello, thank you for the videos. I learned from them how to create org charts in Visio and link them to Excel data. Very helpful materials and great explanation of each step. Too bad Visio does not allow you to update the org chart when a person's reporting changes. I hope this will be improved.
I also have one question, I have learned to create and save my own stencil of shapes and connectors to match the image to the branding of my company. It works when I create the org chart manually without involving Excel data, but I do not know how to make Visio use my stencil when I connect it to Excel data so that it happens automatically. Is this even possible?
Regards,
Aga
Aga, thank you for your kind compliments and for your question. I, too, have wanted better control over the shapes in my org charts, but I do not know of any way to make the Organization Chart Wizard use my custom stencil of shapes when creating a new org chart based on Excel data.
I have had some minor success editing the shapes in the Document Stencil after creating the org chart, but even there, it seems that Visio does not give us absolute control over the shapes used in dynamic data-driven org charts. If you or anyone discovers useful techniques, please share them here!
-Tim
@@AccelerateComputerTraining Hi Tim, sure thing. I will share my knowledge if I can figure it out. 🙂
Aga
Hi Thanks for the very useful tutorial. I want my data to be linked to the excel but I do not want the extra info next to the shapes, how do I remove it?
On the Data tab of the Ribbon, in the Show/Hide group, turn on the Data Graphic Fields checkbox if it is not already on. That will cause the Data Graphic Fields pane to appear. In the Data Graphic Fields pane, you see checkboxes for each data field. Turn off all the checkboxes.
Thanks for wonderful tutorial and was very useful. 1 question. Why all data in excel sheet not getting linked when I try to link. For few links created and for some no. Pls do the needful
Thank you for your question! It is difficult for me to guess why some rows of Excel data are able to link to shapes on your org chart and some are not. Is there a unique identifier (ID) column in your employee list in Excel?
Yes .i have employee I'd and supervisor I'd column. Employee I'd is unique
Question - is it possible to import the visio org chart into excel, so when I update the data in Excel, I can se the changes in the org chart direct?
Great question! To my knowledge, we cannot import a Visio diagram into an Excel workbook. However, a recent Excel Add-In called Visio Data Visualizer might do what you want. In a blank Excel sheet, go to the Insert tab of the Ribbon and look in the Add-Ins group for a button that looks like Visio and/or is labeled Visio Data Visualizer. If it is not there, you may want to update to the latest version of Excel (click File > Account > Update Options > Update Now). If it is there, click it. The Visio Data Visualizer will appear in a graphics frame and prompt you to trust it. It will then present a small assortment of diagrams. Click Organization Chart on the left, and then click Create under the org chart style you like. It will enter a small table of sample data into the worksheet and create a diagram that is linked to that data. Replace the sample data with your own, adding or removing rows as needed. At the top of the org chart diagram, click the Refresh button to see the changes. I admit that it is not nearly as powerful and flexible as the full Visio Pro desktop app, but it does let you see the org chart alongside the source data, which is pretty cool!
thank you for these video series they are very helpful, we are waiting next episodes. I have a problem, there is not "data" button on my Visio and also "Process" button. How can I add it? Could you help me with this? If I cannot use Data all my work wil be wasted :(
Hi Ceren! Only Visio Pro offers the external data features taught in this video. The Standard version of Visio does not have the Data features. To determine what version of Visio you have, launch Visio and click File > Account. If you have the Standard version, perhaps you can upgrade to Visio Pro. You would then be able to open the org chart you already created and use the external data features as shown here.
@@AccelerateComputerTraining I checked and identified that the version I use is standart. Even if I could not link the org charts with data, I came a long way with the help of your videos. Thank you for your short and very clear exposition!
@@cerenozdener6987 Ceren, a recent Excel Add-In called Visio Data Visualizer might provide you with an alternate solution. In a blank Excel sheet, go to the Insert tab of the Ribbon and look in the Add-Ins group for a button that looks like Visio and/or is labeled Visio Data Visualizer. If it is not there, you may want to update to the latest version of Excel (click File > Account > Update Options > Update Now). If it is there, click it. The Visio Data Visualizer will appear in a graphics frame and prompt you to trust it. It will then present a small assortment of diagrams. Click Organization Chart on the left, and then click Create under the org chart style you like. It will enter a small table of sample data into the worksheet and create a diagram that is linked to that data. Replace the sample data with your own, adding or removing rows as needed. At the top of the org chart diagram, click the Refresh button to see the changes. I admit that it is not nearly as powerful and flexible as the full Visio Pro desktop app, but it does let you create an org chart that is linked to the source Excel data without requiring the expensive Visio Pro software.
Thank you! Hoping I can get some help on a problem I'm having with this, though. If I change someone's name, it will show up as updated when I refresh the data. However! If I change who that person reports to, they do not move within the org chart visually. They will show in the external data window correctly, but not change in the visual on who they report to. How can I fix this so it happens when I refresh the data?
Patrick, thanks for watching, and for your question. Sadly, there is nothing we can do to make that happen automatically. You have to do it manually. Just drag the employee's shape onto their new manager's shape in the Visio org chart diagram. Then click the Relayout button on the Ribbon to improve the layout. I plan to include how to do this in a future video in this series.
Thank you for the response and looking forward to it!@@AccelerateComputerTraining
Hello. When I insert a row in the Excel table and refresh in Visio it succesfully imports the row into the External Data window however the data is not linked (no link icon) and thus is not inserted onto the Visio Org Chart surface. Is there a way to complete this automatically?
No, not that I know of.
Hello, Thank you for this Video, Very useful, I Have a problem, when I change Supervisor in Excel Database, then I refresh, It doesn't appear on vision chart !! Can you help me?
That is normal. Visio is not able to disconnect shapes or reconnect shapes when we change the Supervisor of an Employee on the Excel list. You have to do it manually. To connect an employee to a supervisor on the Visio org chart, drag the Employee's shape onto the Supervisor's shape. After that, you may wish to improve the layout. Try clicking the Re-layout button on the Ribbon; it usually does a good job of moving shapes around to fit better.
@@timjones7188 Understood, thank you Tim . I Think that point does not help to create dynamic organizational chart that communicates with Excel in both directions😏The only advantage of this link is that you can upload and assign easily data like Birth date, recruitment date, salary, etc to each employee in the chart
👌
What if the list data is in SharePoint on an excel file? How do I import that into Visio so others can update the list data?
At present, Brian, there is no way that I can find to do it directly. Visio can only base org charts on a few file types and not on files stored in document libraries in SharePoint or OneDrive. If someone else here has found a way, please let us all know!
I get an error message from Visio when i follow all your steps (which were very helpful by the way) i get an internal error #3415.
Jodi, I got errors sometimes, also, when I tried to connect to the Excel data file. Something about the file I developed got corrupted, I think. Are you using your own Excel data files? When does the error occur?
@@AccelerateComputerTraining thanks for responding, the error occurs when i click on Done. I am using my own excel file and my version of excel is 2010 but my visio version is the 2019 version.
@@jodilee10128yk Jodi, the error you are describing is not the same one I experienced. I have no idea of the cause of the error #3415 you are seeing. Sorry!
How can we link data to multiple pages in visio at one shot. Reason because my org chart has 1000+ users and it got slipt in multiple pages
I plan to produce more Visio training videos and one of them will cover organization charts that spill over multiple pages. Sorry I don't have it done yet.
how come the chart does not update after i added new employee data to the list?
Even after we add new employees to the Excel list and click the Refresh button on the Data tab of the Ribbon in Visio, Visio does not add new employee shapes to the org chart; we have to do it manually. First, in the Shapes window on the left, click once to select the shape for the type of employee you want to add; Executive, Manager, Position, or Assistant. Next, in the External Data window, drag the new employee's data row into a blank space in the org chart. You can drag many rows at once if they are all the same type of employee. Visio will place a new shape(s) on the diagram for the employee(s). Next, one at a time, drag each new employee shape (not their data row) onto their manager's shape to establish the supervisor relationship. Finally, click the Relayout button on the Ribbon to improve the layout. I plan to include this topic in a future video in this series.
@@AccelerateComputerTraining This seems redundant. We can achieve much through automating the process at time of creation but then having to perform a manual activity on refresh, in my mind, defeats the entire objective. Surely there must be a way to add the "ShapeName" to the Excel table such that when the data is refreshed the Visio Org Chart knows exactly what shape the record belongs to and is able to add the shape into the correct position? I don't understand how Visio can perform this without issue when creating the Visio Org Chart but is unable to perform the task when data is refreshed. Or maybe there perhaps a method for Visio to "re-draw" the Org Chart (just as it did when it automatically created the original drawing) on Data Refresh? Hoping you can help!
@@user-gi3cv6ft9u I could not agree with you more. If there is a way to make Visio add new shapes to the Org Chart after we add new rows of data to the Excel list, I do not know it.
where is the fourth video in this ?)))
Ahmed, thank you for your question. The answer is, we are still working on producing the remaining videos in this series. We hope to have the fourth episode out soon, so please check back or subscribe. Thanks!