Data Mapping in Excel for Org Charts: A Practical Guide

Charts & pivot tables · Updated November 19, 2026

To map employee data in Excel for an org chart, make a table with one row per person and separate columns for an employee ID, name, job title, and manager ID; then check the reporting links and use the table to build a SmartArt hierarchy or a diagram with shapes. Excel does not automatically turn a manager-ID table into a live, data-linked org chart: SmartArt and shapes must be arranged and maintained as a diagram.

What data mapping means for an org chart

Data mapping is the process of deciding which source fields describe each person and how those records connect. For an organization chart, the central relationship is usually employee-to-manager: a person’s manager ID points to the employee ID of their direct manager. That relationship determines who reports to whom; names, titles, and departments supply labels or context.

This is different from simply putting names into boxes. A list such as “Avery, Blake, Casey” has no reporting structure unless the data says which person manages which other person. A manager ID is more dependable than a manager name because names can be duplicated, changed, or spelled inconsistently.

For example, an employee record with ID 112 and manager ID 111 means that employee 112 reports to employee 111. If employee 111’s manager ID is 110, the chain continues upward. The top person normally has no manager ID. A chart can then show the hierarchy from that root person down through managers and their reports.

Design a source table that supports the hierarchy

Start with a rectangular list: one header row, one person per row, and one field per column. A practical starting layout is:

  • EmployeeID: a unique, stable identifier for each person.
  • Name: the preferred display name for the chart.
  • Title: the person’s role, if it should appear in the chart box.
  • ManagerID: the EmployeeID of the direct manager; leave it blank only for the top-level person or people.
  • Department: an optional field for filtering, color-coding, or checking the data.

For instance, rows could represent Morgan Lee, CEO, with no manager; Sam Patel, Operations Director, with Morgan’s ID as ManagerID; and Taylor Kim, Operations Manager, with Sam’s ID as ManagerID. The field names are not special Excel requirements, but clear headers make the mapping easier to understand and reuse.

Keep employee IDs as text if they may contain leading zeros, letters, or punctuation. Do not use row numbers as IDs: sorting or inserting rows changes row positions, whereas a real identifier should continue to refer to the same employee. Convert the range to an Excel Table using Insert > Table. Tables make the source easier to filter and extend, although adding a row to a table does not automatically add a corresponding SmartArt node.

Clean and validate reporting relationships

Before drawing anything, confirm that each employee ID is present and unique, each nonblank manager ID matches an employee ID, and the reporting structure has no unintended gaps or loops. A typo in a manager ID can leave someone disconnected; duplicate IDs make a manager reference ambiguous. Check that the intended root record is clear and that each person has only one direct manager if the chart is meant to show a conventional single-line reporting structure.

With employee IDs in A2:A6 and manager IDs in D2:D6, enter this formula in F2 and fill it down to flag blank manager IDs as roots and check nonblank references:

=IF(D2="","ROOT",IF(COUNTIF($A$2:$A$6,D2)=1,"OK","CHECK"))

In a sample where A2 is 100, A3 is 110, and D3 is 100, the result in F3 is OK because ID 100 occurs once in the employee-ID range. The row with a blank manager ID returns ROOT. This check does not prove that the hierarchy is sound: two employees could have blank manager IDs, or two people could report to each other and create a cycle. Review the root count and investigate unusual chains as well.

To identify duplicate IDs, with employee IDs in A2:A6, enter this in G2 and fill down:

=IF(COUNTIF($A$2:$A$6,A2)=1,"OK","DUPLICATE")

If ID 100 occurs once, G2 returns OK for that record; if it occurs twice, both matching rows return DUPLICATE. These formulas use standard Excel functions and comma separators. For a larger workbook, apply filters to the check columns and resolve every exception before creating the diagram.

Translate rows into a hierarchy

The source table is a relationship map, not yet a chart. To turn it into an outline, start with the top person, then place each direct report beneath that person, followed by that report’s own direct reports. Use a consistent label such as “Name — Title” so readers can distinguish people with similar names. Department can be included where it adds useful context, but long labels make boxes crowded.

A small example might become:

  • Morgan Lee — Chief Executive Officer
    • Sam Patel — Operations Director
      • Taylor Kim — Operations Manager
    • Jordan Wu — Finance Director

In this outline, Sam and Jordan report to Morgan; Taylor reports to Sam. The order of rows in the original table does not have to match the final chart order. However, sorting or grouping the source can make it easier to find each manager’s reports and reduce missed nodes. For a large hierarchy, plan sections or levels before drawing; a very wide chart may be hard to read on screen or on a printed page.

Create an org chart with SmartArt in Excel

In Excel for Microsoft 365 on Windows, choose Insert > SmartArt > Hierarchy > Organization Chart, then select OK. The exact dialog appearance can vary slightly with Office updates. Use the SmartArt Text Pane to enter the hierarchy: add a person on a new line, use Tab to make that entry a subordinate of the preceding level, and use Shift+Tab to move it up a level. Check the resulting levels carefully; indentation represents reporting structure, not merely visual spacing.

Enter concise labels and use the SmartArt Design and Format tabs to adjust the layout, colors, and appearance. You can add or remove shapes with the SmartArt controls, but keep the source table as the authoritative record. SmartArt is useful for a small or medium chart that needs quick formatting; it is not a cell-linked visualization. Changing a name or manager ID in the table will not reliably rebuild or reorganize the SmartArt automatically.

Excel for Mac also supports SmartArt, though ribbon layout and dialog details can differ from Windows. Excel for the web may not offer the same complete authoring and formatting experience as the desktop application, so use desktop Excel if the SmartArt controls you need are unavailable in your browser version.

Use shapes when the layout needs more control

For a chart that needs custom spacing, notes, or a layout not provided by SmartArt, create boxes with Insert > Shapes and connect them with connector lines. Put a name and title in each box, and use connector shapes so the lines attach to the boxes rather than appearing as unrelated strokes. Arrange the top role first, then align the next management level beneath it. Excel’s alignment and distribution tools can help make rows more even.

Shapes can be easier to customize, but they also require manual work. Adding a manager may mean moving several boxes and reconnecting lines. If using this approach, keep a copy of the source table in the workbook, give the chart a clear “as of” date, and review the connections after every update. SmartArt and shapes are both presentation tools; neither should be treated as the underlying employee database.

Keep the chart accurate as staff changes

Org charts become outdated when people change roles, leave, or move between managers. Establish a repeatable update process: refresh the source table from its approved source, rerun the validation checks, revise the outline, and compare the finished diagram with the table before sharing it. Include a revision date and, when useful, a note explaining whether the chart shows solid-line managers only or also includes dotted-line relationships.

For recurring updates, keep a clean source-data sheet separate from the presentation sheet. Protecting the source headers and documenting what each column means can reduce accidental edits. Excel data validation lists can help standardize fields such as department, but a dropdown alone cannot guarantee that a manager ID refers to a current employee. Re-run the relationship checks after imports and edits.

If the structure is too large or changes too often for manual diagram maintenance, consider a dedicated diagramming or HR system that supports importing employee-manager data. Verify its import format, privacy controls, and update behavior before relying on it. Excel remains useful for preparing and checking a small dataset, even when another tool is better for publishing the final chart.

Common mistakes and practical limitations

  • Using names as the link: two people may share a name, and spelling changes break text-based matching. Use stable IDs for manager relationships.
  • Assuming a chart is live-linked: editing the table does not automatically rearrange SmartArt or shapes. Treat chart updates as a separate step unless using a tool specifically designed for data-linked diagrams.
  • Leaving missing or invalid managers unchecked: a nonmatching ManagerID can strand a person outside the expected hierarchy. Validate references before drawing.
  • Including too much detail in each box: email addresses, phone numbers, and lengthy descriptions can make a chart unreadable. Keep the diagram focused and store additional information in the table.
  • Ignoring privacy and audience: an org chart may expose names, roles, or reporting relationships. Share only the fields and version appropriate for the audience, following the organization’s data-handling rules.

Another common misconception is that an Excel chart type such as a hierarchy chart will automatically interpret manager IDs. Excel’s standard chart features do not infer employee reporting relationships from a table. SmartArt provides a hierarchy layout, but the person-to-person structure still has to be entered and maintained as diagram content.

Frequently Asked Questions

Can Excel automatically create an org chart from employee and manager columns?
Not with the standard Excel worksheet and SmartArt features alone. A table can hold the relationships and help you validate them, but you must enter the hierarchy into SmartArt or draw it with shapes, or use a separate tool that supports data-driven org charts.

What columns do I need for an Excel org chart?
At minimum, use a unique employee ID, a display name, and a manager ID that refers to another employee ID. Add job title or department if those details belong in the chart. The top person’s manager ID is usually blank.

Can I use names instead of employee IDs?
You can, but it is less reliable when names repeat or change. A unique, stable ID makes it possible to identify a manager unambiguously; names should generally be used as labels rather than relationship keys.

Will my SmartArt org chart update when I change the Excel table?
No. SmartArt is not automatically synchronized with an employee-manager table. Update the diagram separately and check its reporting lines against the validated source data.

How do I show two managers or dotted-line reports?
A conventional org chart usually shows one direct reporting line per person. If you need to show matrix or dotted-line relationships, document what each line means and consider using connector shapes or a diagramming tool that supports those relationships clearly.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.