To build an org chart from Excel data, first organize each employee on one row with a unique ID and a manager ID, then use Insert > SmartArt > Hierarchy > Organization Chart to create the chart. Excel’s SmartArt chart is not automatically connected to the rows, so changes to the source data will not update the boxes; for a chart that needs frequent updates, consider a data-linked tool such as Visio instead.
How employee data becomes a hierarchy
An organization chart represents reporting relationships: each person, except the top-level leader, reports to another person. A spreadsheet stores those relationships as rows and values, rather than as a picture. The important step is to record who reports to whom in a way that a person or charting tool can interpret consistently.
A useful starting table has one row per employee and columns such as:
- Employee ID: a unique, stable identifier for each person.
- Name: the label you want displayed in the chart.
- Job title: an optional second line in each box.
- Manager ID: the Employee ID of the person this employee reports to.
For example, an employee named Jordan Lee might have ID E104 and manager ID E021. The manager’s row would have E021 in the Employee ID column. The most senior person in the chart usually has a blank Manager ID. This parent-and-child structure is easier to validate than recording a manager’s name alone, because two people can share a name or have names entered in different ways.
Keep the source data in a simple rectangular range with one header row, no merged cells, and no blank rows inside the list. Converting the range to an Excel table with Insert > Table can make it easier to filter and extend as staff are added, though SmartArt itself will not turn that table into a live chart.
Clean and prepare the source table
Before drawing boxes, decide what the chart should communicate. A company-wide chart may show only names and titles, while a team chart might include departments or locations. Avoid placing sensitive or unnecessary details, such as personal contact information, in a chart that will be widely shared.
Use consistent IDs and ensure that every nonblank Manager ID matches an Employee ID in the table. Check that each employee appears once, that the intended top-level person has no manager, and that there are no unintended gaps in the reporting chain. If the chart is for one department, make clear whether people outside that department are omitted or shown as higher-level managers.
Excel can flag some common data problems. With Employee IDs in A2:A100 and Manager IDs in D2:D100, enter this in E2 and fill it down to check that each nonblank manager ID exists in the employee list:
With Employee IDs in A2:A100 and Manager IDs in D2:D100, the formula =IF(D2="","",IF(COUNTIF($A$2:$A$100,D2)=0,"Missing manager","OK")) returns “Missing manager” when D2 does not match an ID in A2:A100, and “OK” when it does. A blank manager cell returns a blank result, which is appropriate for a top-level employee but should still be checked against the intended chart structure.
With Employee IDs in A2:A100, enter this in F2 and fill it down to identify repeated IDs:
The formula =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate ID","OK") returns “Duplicate ID” when the value in A2 appears more than once in the list. These checks do not detect every possible issue: for example, they will not identify a circular reporting chain in which people ultimately report back to themselves. Review the hierarchy for cycles and confirm unusual reporting arrangements with the people responsible for the source data.
Build a chart with SmartArt in Excel
In Excel for Microsoft 365 on Windows, create a SmartArt org chart from the ribbon. SmartArt is a practical choice for a small or moderately sized chart that you can arrange and format by hand.
- Open the worksheet and choose Insert > SmartArt.
- In the Choose a SmartArt Graphic window, select Hierarchy, choose Organization Chart, and select OK.
- Select the chart, open the SmartArt Design tab, and choose Text Pane if the text pane is not already visible.
- Enter the top person first. Add each direct report as a subordinate under the appropriate manager in the text pane. Use Enter to add another item; use Tab to demote an item to a subordinate level and Shift+Tab to promote it.
- Continue until the outline represents the reporting structure. Review the boxes and edit the text to show the name and, if useful, the title.
The text pane is an outline, not a place to paste a four-column employee table and expect Excel to infer the manager links. For a larger dataset, you can use the Manager ID column to work out the correct outline before entering names. A small chart may be fastest to enter directly; for a long hierarchy, constructing and checking the outline first helps prevent people being placed under the wrong manager.
Excel for Mac has SmartArt on the Insert tab, though ribbon details can vary by release. The steps are broadly similar: insert a Hierarchy SmartArt graphic, then use its text pane to arrange names. This guidance is for Excel desktop; Excel for the web may not offer the same SmartArt creation and editing controls, so use the desktop app if a command is unavailable.
Turn the reporting table into a readable outline
Consider a simple example: Morgan is the director, Casey and Drew report to Morgan, and Alex reports to Casey. The outline must place Casey and Drew one level beneath Morgan, and Alex one level beneath Casey. The Manager ID values establish those links in the table; the indentation establishes them in the SmartArt outline.
That distinction matters when a company has multiple teams or several layers of management. Sorting the spreadsheet alphabetically does not create the correct hierarchy, and placing everyone with the same title on one line does not prove they share a manager. Follow each manager reference, starting with the top-level person, and indent only employees who report directly to the preceding manager. If the order is unclear, use an intermediate working sheet to list each manager followed by their direct reports.
SmartArt may not display every person comfortably when the chart becomes wide or deep. Try changing the layout or increasing the chart area before shrinking text to an unreadable size. For a chart intended for a slide or printout, it can be better to create separate charts for divisions and show the relevant senior leader in each one than to cram the whole organization into a single page.
Format, review, and maintain the chart
Select the SmartArt graphic to use the SmartArt Design and Format tabs. SmartArt Design includes layout and color options; Format offers shape and text formatting controls. Use a restrained color scheme, keep names and titles in a consistent order, and make sure reporting lines are easy to follow. Avoid relying on color alone to communicate departments or status, since readers may print the chart in grayscale or have difficulty distinguishing similar colors.
After arranging the chart, compare it against the source table. Check every manager-to-employee link, not just the top few levels, and verify spelling, titles, and vacancies. If the chart will be printed, use Print Preview to check whether boxes or labels are cut off. Add a date or version note near the chart so readers know when its structure was last reviewed.
SmartArt should be treated as a separate visual copy of the information. When someone joins, leaves, or changes manager, update the source table and the chart, then recheck the affected branch. Keeping the employee table as the approved record and assigning one person to update the graphic reduces the risk of the two falling out of sync. For recurring reporting, a process that regenerates or links the chart is usually more dependable than relying on manual edits.
Choose between SmartArt, shapes, and Visio
The right method depends on chart size, update frequency, and the amount of control you need. Excel is useful when the data and the chart belong in the same workbook, but there is no single Excel command that automatically converts arbitrary employee-manager rows into an editable, live-linked SmartArt chart.
- SmartArt: A quick way to create a conventional chart and adjust its appearance. It is convenient for smaller structures, but its boxes do not stay synchronized with a source table.
- Individual shapes and connectors: These allow precise placement and customized labels in a worksheet. They can take longer to build and maintain, especially when people move between teams.
- Visio: A more suitable option to investigate for larger or frequently refreshed organization charts. Visio’s organization-chart features can use employee data as a source, but the available workflow and licensing depend on the Visio edition and organization. Confirm the current requirements before choosing it.
- Another diagramming tool: If an organization already uses a diagramming or HR system, check whether it can produce an org chart from the approved employee data. Avoid creating another unofficial employee directory.
A common misconception is that turning the source range into an Excel table makes a SmartArt graphic dynamic. A table can expand as data is added, but that does not bind its rows to SmartArt boxes. If automatic refresh is essential, choose a tool with a documented data-linking workflow rather than expecting formatting or formulas in Excel to create that connection.
Common problems and practical fixes
People appear under the wrong manager: Check the Manager ID against the manager’s Employee ID, then check the indentation in the SmartArt text pane. Similar names can make a name-only source confusing, which is one reason stable IDs are useful during preparation.
The chart has several roots: More than one blank Manager ID may be intentional, such as separate businesses in a group, or it may indicate missing data. Decide whether to create separate charts or show a shared parent, and do not silently invent a reporting link to make the graphic look tidy.
The chart is too crowded: Show only information relevant to the audience, split the chart by division, or use a tool designed for larger diagrams. Removing a legitimate reporting level just to fit the page can misrepresent the organization.
The picture no longer matches the spreadsheet: Treat updates as a two-part change: revise the source data, then revise and verify the chart. If updates are frequent, that manual process is a warning that a data-linked alternative may be worth the added setup.
Frequently Asked Questions
Can Excel automatically create an org chart from employee and manager columns?
Not as a live-linked SmartArt chart using a standard Excel command. You can use the columns to prepare the correct hierarchy, then build it in SmartArt, or choose a diagramming tool with an employee-data workflow if automatic generation is a requirement.
What columns do I need for an Excel org chart?
At minimum, use an employee name and a manager reference. A unique Employee ID and a Manager ID are safer than names alone. A job title is optional, and a blank Manager ID commonly marks the top-level person.
Can I paste my Excel table directly into SmartArt?
A regular employee table does not automatically become a reporting hierarchy in SmartArt. Use its manager references to build an indented outline in the SmartArt text pane, or use a charting tool that specifically supports importing or linking employee data.
Will my org chart update when I change the Excel rows?
No. A SmartArt graphic is not automatically synchronized with the source rows. Update both, and verify the affected reporting links, or use a suitable data-linked tool for a chart that must refresh from the data.
How can I check that the reporting data is correct?
Check that IDs are unique, each nonblank Manager ID exists in the employee list, and the intended root has no manager. Then review the hierarchy for cycles, missing people, and relationships that do not match the organization’s approved structure.
Related
- Excel Insert X Rows: Fast Methods for Any Worksheet
- Excel Insert a Row Every X Rows: Methods That Work
- Excel Insert Lines Between Each Row: Simple Methods
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Bar and Whisker Chart: How to Create and Read OneAn Excel bar and whisker chart, more commonly called a box-and-whisker chart or box plot, summarizes the distribution…
- Conditional Progress Circle Chart in Excel: Build OneYes. In Excel for Microsoft 365 on Windows, you can make a conditional progress circle with a doughnut…
- Excel Pie Chart: Show Percentages ClearlyYes. In Excel for Microsoft 365 on Windows, select the pie chart, then choose Chart Design > Add…
- How to Plot a Waterfall Chart in Excel Step by StepTo plot a waterfall chart in Excel, arrange your categories in one column and the changes in value…
- Google Sheets Chart Trendlines: Add and Customize OneTo add a trendline in Google Sheets, open the chart editor by double-clicking your chart, choose Customize >…
- Excel Pie Chart: Create, Format, and Fix OneTo make a pie chart in Excel for Microsoft 365 on Windows, select a table with one category…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.