Operations & HR

Org Chart Template in Excel: Build It from an Employee List and Keep It Current

How to make an org chart in Excel or Google Sheets: an employee list with manager IDs, formulas that catch reporting errors and show span of control, and three ways to draw the chart: SmartArt, from data in Microsoft 365, and the Google Sheets organizational chart.

Published 4 min read

An org chart shows who reports to whom. The most reliable way to build one in Excel is to start from an employee list with a “manager ID” column and generate or update the chart from that list, instead of redrawing boxes every time someone joins, leaves or changes roles. This guide covers the list, a few formulas that catch errors before you draw, and three ways to produce the chart: SmartArt, a data-driven diagram in Microsoft 365, and the organizational chart in Google Sheets.

The employee list comes first

Column Example Purpose
ID E01 Unique key, never reused
Name Dana Lopez Box label
Title Practice Manager Second line in the box
Department Front Office Colors and filters
Manager ID (blank for the top person) Draws the reporting lines
Status Active, open role, on leave Shows planned positions

Link to the manager’s ID rather than their name; names repeat and get misspelled. Adding open roles with “Open role” in the name column turns the chart into a hiring plan everyone can see.

Checks before you draw

With IDs in column A and manager IDs in column E:

  • Manager exists: =IF(E2="","Top",IF(COUNTIF($A:$A,E2)=0,"Unknown manager ID","OK"))
  • Span of control (direct reports): =COUNTIF($E:$E,A2)
  • Nobody reports to themselves: =A2=E2 should be FALSE on every row.
  • Exactly one top person: =COUNTBLANK(E2:E200)-COUNTBLANK(A2:A200) should return 1.

Option 1: SmartArt for small teams

  1. Insert > SmartArt > Hierarchy > Organization Chart.
  2. Open the text pane and type the top person on the first line.
  3. Enter adds someone at the same level, Tab demotes, Shift + Tab promotes.
  4. Shift + Enter inside a box adds a second line for the title.
  5. Use SmartArt Design to change colors and layouts, such as a hanging layout for lower levels.

SmartArt is quick and prints cleanly but is not linked to the list, so every staffing change has to be repeated in the diagram. It works well up to about 20 people.

Option 2: generate it from data (Microsoft 365)

In Excel for Microsoft 365, the Visio Data Visualizer add-in (Insert > Add-ins, search “Visio Data Visualizer”) creates an org chart from a table with employee ID and manager ID. When the table changes, refresh the diagram. Editing options depend on your Microsoft 365 account type, so test it with a small table first.

Option 3: the Google Sheets organizational chart

  1. Prepare two columns: column 1 with each person’s label (“Dana Lopez – Practice Manager”) and column 2 with their manager’s label, blank for the top person. An optional third column becomes a tooltip.
  2. Select the range, choose Insert > Chart, and pick Organizational chart as the chart type.
  3. Adjust node size and colors under Customize.

The manager label in column 2 must exactly match that manager’s label in column 1. Build both with formulas (name & ” – ” & title, and the manager’s label via XLOOKUP on the ID) to avoid typos. The chart updates whenever the list does and can be shared with a link.

Example: a dental practice with 12 people

ID Title Reports to Direct reports
E01 Owner dentist — 3
E02 Associate dentist E01 3
E03 Practice manager E01 4
E04 Lead hygienist E01 1
E05–E07 Dental assistants E02 0
E08–E10 Front desk and billing E03 0
E11 Hygienist (open role) E03 0
E12 Hygienist E04 0

The span-of-control column raises a useful question: the open hygienist role sits under the practice manager while the other hygienist reports to the lead hygienist. Clinical roles reporting through the clinical lead keeps responsibilities clear, so the role is moved under E04 before the hiring ad goes out.

Tips for a readable chart

  • Name and title only in each box; keep everything else in the list.
  • One color per department, matching your other internal documents.
  • Above roughly 50 people, use an overview chart down to department heads and a detailed chart per department.
  • Put the “as of” date in a corner.

Privacy

Names and titles are personal data. For internal use they are usually enough; leave personal contact details out. If you publish the chart, for example on your website, show names only for people in public-facing roles or with their agreement.

The same employee list feeds the employee schedule template, the leave tracker and the payroll template. New hires get the chart as part of the employee onboarding checklist. All guides are in Operations & HR.

Frequently asked questions

Is Excel or PowerPoint better for an org chart?

PowerPoint suits a one-off presentation. Excel keeps the employee list and the chart in one file, which makes it easier to keep current.

How do I show someone with two managers?

A tree chart has one direct manager per person. Use the line manager in the manager column, note the functional manager in a comments column, or draw that line dashed by hand.

How often should the org chart be updated?

With every hire, departure or role change. If it is generated from the list, updating the list is enough.

Should open positions appear?

Yes. Showing open roles makes the planned structure visible and helps with hiring.