Articles

How to Build Your Employee Directory in Google Sheets (Plus Free Template)

In this guide, we'll walk through how to create a simple yet effective employee directory using Google Sheets, plus get a free employee directory template.

An employee directory is a useful tool for any organization to have. It allows you to store and organize key information about employees in one central location.

In this guide, we'll walk through how to create a simple yet effective employee directory using Google Sheets.

Google Sheets is a free, web-based spreadsheet application that makes it easy to build and manage an employee directory.

Step 1: Organize Your Employee Information

Before creating an employee directory, it's important to gather and organize relevant employee information into a centralized database. This includes details like full names, job titles, department, office location, contact info (email, phone, extension), start date, manager, and any other key data points about each team member.

Take time to collect accurate, up-to-date details and input them in a consistent format. For example, follow the same capitalization, date formats, and phone number style across entries. Maintaining clean, structured data from the start makes it easier to manage your employee directory long-term.

The goal is to create a usable, accessible resource for your organization. With the foundation of organized employee information in place, you'll be set up for success when it comes time to build out your staff directory in Google Sheets or any other platform.

Step 2: Create a New Spreadsheet

Go to Google Sheets and log into your Google account.
Click on the “Blank” template to create a new spreadsheet.

Step 3: Add Column Headers

Rename Sheet1 at the bottom to "Employee Directory"
In cell A1 type "Display Name"
In cell B1 type "First Name "
In cell C1 type "Last Name"
In cell D1 type "Job Title"
In cell E1 type "Email Address"
In cell F1 type "Phone Number"
In cell G1 type "Department"
In cell H1 type "Office"
In cell I1 type "Manager"
In cell J1 type "Hire Date"
In cell K1 type "Birthdate Date"

This creates column headers for the key pieces of information you want to track for each employee.

Step 4: Make a Department Directory

Create a new sheet and name it "Departments"
In cell A1 put "Department"
In cell B1 put "Department Head"
In cell C1 put "Description"
In column A, list each department
In column B, put the name of the department head
In column C, add the overview description of that department

This gives you a simple directory of all your departments.

Now you can link this new "Departments" sheet to the Employee Directory using data validation.

Go back to the Employee Directory sheet
Highlight the Department column (Column G)
Click Data > Data Validation > Add New Rule
Apply to Range must = 'Employee Directory'!G2:G1000
Under Criteria, select the 'Dropdown (from range)' option
Then enter =Departments!A2:A
Then click 'Done'

Now the Department column will have a dropdown list populated from the Departments sheet. When a new department is added to that sheet, it will automatically appear as an option.

This allows you to maintain departments in one place while having them dynamically link to the employee directory.

Step 6: Make an Office Directory

Create a new sheet called "Offices"
In cell A1 put "Office"
In cell B1 put "Street Address"
In cell C1 put "City"
In cell D1 put "State"
In cell E1 put "Country"
In cell F1 put "ZIP / Postal Code"
In cell G1 put "Phone Number"
In cell H1 put "Main Contact Person"
List each office location, address, and phone number

This gives you a directory of your different office locations.

Step 7: Link Offices to the Employee Directory

Now you can link this new "Offices" sheet to the Employee Directory using data validation.

Go back to the Employee Directory sheet
Highlight the Office column (Column H)
Click Data > Data Validation > Add New Rule
Apply to Range must = 'Employee Directory'!H2:H1000
Under Criteria, select the 'Dropdown (from range)' option
Then enter =Offices!A2:A
Then click 'Done'

Now the Office column will pull from the list of offices. When adding a new employee, you can select which office they are based on the dropdown. The "Offices" sheet acts as the source of truth for available options.

Step 3: Add Employee Data

Starting in row 2, add a new row for each employee and fill in their data under the appropriate column headers.
Make sure to use proper capitalization and formatting consistently.
For phone numbers, use (XXX) XXX-XXXX format. (see How to Format Fields for Phone Numbers at the end of the guide)
For hire date and birthdate, use MM/DD/YYYY format. (see How to Format Date Fields at the end of the guide)

Step 4: Add Filtering

To make your Google Sheets directory more user-friendly, you can add filters that allow us to quickly search, sort, and filter the data.

Highlight the entire header row.
Click Data > Filter views. This will add dropdown filters to each header.

Now you can easily filter by department, job title, start date, etc.

Step 5: Freeze Header Row

With the header row still highlighted, click View > Freeze.
Then choose Freeze '1 row'.
Then click View > Freeze again.
Then choose Freeze '1 column'

Step 6: Sort Columns

By default, data will show in the order it was entered.
To sort: Highlight the data (not including the header row).
Click Data > Sort sheet.
Choose how you want it sorted: by last name, department, etc.

To enable access: Click the Share button > Add people/groups and enter email addresses.
Choose editor or viewer permissions.
Click Send to complete sharing.

Now others in your organization can view and update your directory in Google Sheets!

Additional Tips

Here are a few additional tips for enhancing your employee directory in Google Sheets:

Highlight the Manager column (Column I)
Click Data > Data Validation > Add New Rule
Apply to Range must = 'Employee Directory'!I2:I1000
Under Criteria, select the 'Dropdown (from range)' option
Select Column A (Display Name)
This allows you to define reporting structures

How to Create an Org Chart in Google Sheets

Use the Manager column to generate your visual org chart
Insert > Chart > Org Chart
Pick the Display Name column and Manager column for the data

How to Add Profile Pictures

Insert a column for a profile photo
Use a formula like =image("URL","Size")
Source the URL from a cloud storage folder or drive

How to Format Fields for Phone Numbers

For consistent formatting, use the (XXX) XXX-XXXX phone number format
Apply this format to all existing phone numbers using the Find and Replace tool (Ctrl + H on Windows/ChromeOS, ⌘ + F on Mac)
For new entries, use Data Validation to select between a pre-defined list of phone number formats, like:
- (XXX) XXX-XXXX
- XXX-XXX-XXXX
- +1 (XXX) XXX-XXXX
- +1 XXX-XXX-XXXX
This ensures all numbers follow the same style, allowing for easier searching and filtering
Alternatively, use a custom phone number format like this:
- Select the phone number column
- Right-click and select "Format Cells"
- Under Number > Special formats, choose the phone number format
This will automatically apply the (XXX) XXX-XXXX mask as numbers are entered

How to Format Date Fields

For consistent formatting, use the MM/DD/YYYY date format
Apply this format to existing dates using Find and Replace (Ctrl + H or ⌘ + F)
For new entries, use Data Validation to select from a pre-defined list of date formats like:
- MM/DD/YYYY
- DD/MM/YYYY
- MM/DD/YY
- YYYY-MM-DD
This ensures all dates follow the same style for better organization
Alternatively, use custom date formatting:
- Select the date column
- Right-click and Format Cells
- Under Number > Date, select the desired format
This will automatically apply on data entry
Consider using color coding to quickly identify start dates
For example, highlight entries from the past year in green

Get Your Free Google Sheets Employee Directory Template

As you can see from all the steps above creating an employee directory from scratch in Google Sheets can be time-consuming.

Luckily, we have a free employee directory template for you that can give you a head start.

Access your free Google Sheets employee directory template here

Unsure of Building Your Employee Directory in Google Sheets?

While Google Sheets provides a free and simple way to build a people directory, it has some drawbacks when used as an ongoing employee database:

Limited Scalability: As your organization grows, managing an employee directory in Google Sheets can become complex and unwieldy. It's not designed to handle large amounts of data efficiently.
Non-User-Friendly Interface: The data in Google Sheets is often stored in a way that's not user-friendly, making it difficult for employees to quickly find the information they need.
Lack of Customization: Google Sheets doesn't offer the same level of customization as dedicated software. It may not meet the specific needs of your organization.
Limited Accessibility: Google Sheets may not be as accessible from all devices or locations, which can be a drawback in today's remote and flexible work environments.
No Integration with Other Systems: Google Sheets doesn't integrate with other systems or software your organization may use, which can lead to inefficiencies and data silos.

For these reasons, using modern employee directory software like OneDirectory provides major advantages:

Powerful people search and filtering
Comprehensive employee profiles
Centralized employee profile database
Automated syncing with Microsoft Entra ID
Interactive org chart visualization
Customizable fields and views

OneDirectory - Modern Employee Directory

Give OneDirectory a try!

Start by signing up for our 14-day free trial, where you will get to experience all the benefits of OneDirectory's modern employee directory solution.