How to Build Your Employee Directory in Microsoft Excel (Plus Free Template)

Every organization today places exceptional importance on managing their employees, their duties and contact information.

A Microsoft Excel employee directory provides a simple way for companies to organize and manage staff details all in one place.

In this extensive and easy to comprehend tutorial, we will demonstrate how to design a unique spreadsheet employee directory working in Microsoft Excel.

Let's get going...

Why Use Excel for an Employee Directory?

An Excel employee directory is essentially a structured spreadsheet that consolidates critical information about each staff member—such as their name, job title, department, office location, email address, and phone number—into one convenient resource. Here are the benefits of using Excel for your employee directory:

Accessibility & Familiarity:

Microsoft Excel is a tool most business professionals already know. This familiarity means employees can quickly input and retrieve data without extensive training.

Authoritative Insight:
According to Gartner’s research on productivity tools, familiarity with widely used software often leads to higher adoption rates, reducing resistance to new internal processes.

Customizable Data Fields:

Excel’s flexible grid layout allows you to tailor the directory to your organization’s unique needs—add columns for departments, roles, locations, and contact information. This customization ensures that you record the data points most relevant to your business.

Easy Integration with Other Tools:

Excel files can be imported into HR systems, CRM platforms, or even advanced employee directory solutions like OneDirectory. This makes Excel a good foundational tool that can scale as your company evolves.

Step-by-Step: How to Build Your Employee Directory in Excel

Step 1: Organize Your Employee Information

Before you start creating your employee directory in Microsoft Excel, the first step is to organize all the relevant employee information. This could include their names, job titles, contact details, start dates, and any other critical details about each employee.

Make sure that the information you collect is accurate, up-to-date, and easy to manage.

Step 2: Open a New Excel Workbook

Once you’ve organized the information, create a new workbook in Microsoft Excel. To do this, open Excel and click on “File” then “New Workbook.” This will create a blank workbook where you can start designing your employee directory.

Step 3: Set Up Your Column Headers

Start by labeling your column headers with the types of information you want to include in your directory. We recommend using the following fields and structure:

• Rename Sheet1 at the bottom to "Employees"
• 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 "Workplace"
• In cell H1 type "Department"
• In cell I1 type "Manager"
• In cell J1 type "Hire Date"
• In cell K1 type "Birthdate Date"
• In cell L1 type "Pronouns"

Feel free to add or remove columns depending on your organization's needs.

Step 4: Format Your Headers

Make your headers stand out by applying some formatting:

1. Highlight the header row.
2. Choose a bold font and increase the font size.
3. Apply a background color to make the headers pop.
4. Add borders to separate the columns.

Step 5: Create a Workplace Directory

• Create a new sheet called "Workplaces"
• In cell A1 put "Workplace"
• In cell B1 put "Workplace Type"
• In cell C1 put "Country"
• In cell D1 put "Street Address"
• In cell E1 put "State"
• In cell F1 put "ZIP / Postal Code"
• In cell G1 put "Phone Number"
• In cell H1 put "Main Contact Person"

This gives you a directory of your different workplaces and their locations.

Step 6: Link Workplaces to the Employee Directory

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

• Go back to the "Employees" sheet
• Highlight the Workplace column (Column G)
• Click Data > Data Validation
• Under Validation Criteria, select the "List" option
• Enter =Workplaces!$A$2:$A$500
• Make sure the 'ignore blank' checkbox is ticked
• Make sure the 'in-cell drop-down' checkbox is ticked

Now the workplace column will pull from the list of workplaces. When adding a new employee, you can select which workplace they belong to from the dropdown.

Step 7: Create 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.

Step 8: Link Departments to the Employee Directory

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 H)
• Click Data > Data Validation
• Under Criteria, select List from a range
• Enter =Departments!$A$2:$A$2500
• Make sure the 'ignore blank records' checkbox is ticked

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.

Step 9: 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.
• For hire date and birthdate, use MM/DD/YYYY format.

Step 10: Format Your Data

To make your Excel staff directory easy to read, apply some formatting to the data:

1. Highlight the data row you want to edit.
2. Choose a clear, legible font and font size.
3. Apply alternating row colors to improve readability.

Follow the steps below to apply some advanced formatting to certain fields in Excel.

How to Format Phone Numbers in Excel

Once you've entered all the phone numbers into your Excel employee directory, take a moment to format those fields so the numbers appear consistently.

By default, Excel will remove any formatting from numbers and display them as general values. So a phone number like 123-456-7890 could show up as 1234567890.

To make phone numbers stand out and be easily scannable, you need to adjust the cell formatting.

• Select the phone number column.
• Click on the Home tab and open the Number Format drop-down menu.
• Select the Special option for phone numbers.

This will add dashes between the numbers so they follow the standard phone number format of 123-456-7890.

You can also customize the format to match your preference:

• Add parentheses around the area code: (123)-456-7890
• Display with a dot separator: 123.456.7890
• Include the country code: +1 123-456-7890

The key is to pick one phone number format and stick with it for consistency throughout your staff directory in Microsoft Excel. Properly formatting the numbers makes the information much easier to parse visually.

How to Format Dates in Excel

In addition to phone numbers, you may also want to ensure any date fields in your Microsoft Excel directory are properly formatted.

Dates can be displayed in a number of different ways. Excel defaults to showing the full date and time (3/14/2020 1:30:00 PM). But for an employee directory, you may likely only need to show the date, not the timestamp. To format the date fields:

1. Select the date column
2. On the Home tab, open the Number Format drop-down
3. Select the Short Date format (3/14/2020)

This will display just the day, month and, year in an easy-to-read format. You can also customize the date format further based on your preferences:

• Display as MM/DD/YYYY
• Use text instead of numbers for the month (Mar 14, 2020)
• Show the day of the week (Friday, March 14, 2020)

The key is to select one date format and use it consistently in all the date fields. This makes scanning and comparing dates much easier.

Step 11: Adjusting Column Widths and Row Heights

Now that you've entered all your employee data and formatted your fields in your Excel people directory, you'll likely need to adjust some column widths and row heights for the best fit.

By default, Excel will set column widths and row heights based on the size of the data in the cells. This can lead to columns being too narrow to fully display text or rows being spaced too far apart. Here are some tips for adjusting widths and heights:

Column Widths

• Hover over the right border of the column header
• When the resize cursor appears, double-click to auto-fit the width based on cell contents
• Alternatively, drag the border to manually adjust the width

Row Heights

• Select the row(s)
• Right-click and choose Row Height
• Enter a value in points to set the height

Be sure to double-check that your data fits within the column widths and that the row spacing looks proportional.

Step 12: Sort and Filter Your Data

Excel has a number of helpful features that can help you organize and analyze your employee data quickly and efficiently.

For example, you can use the "Sort" feature to organize your employee data alphabetically by name, department, or position.

You can also use the "Filter" feature to isolate specific employee data based on certain criteria. Additionally, Excel's "Conditional Formatting" feature can be used to highlight certain data points in your spreadsheet, such as employees who have completed specific training.

1. Highlight your entire data table, including headers.
2. Click the 'Data' tab and then click 'Filter'.
3. Use the drop-down arrows next to each header to sort and filter your data as needed.

Step 13: Freeze the Header Row

To keep your header row visible while scrolling through your Excel employee directory, freeze the top row:

1. Click on the 'View' tab.
2. Go to the 'Freeze Panes' dropdown menu.
3. Select 'Freeze Top Row'.

Step 14: How to Protect Sensitive Information in Excel

It's important to protect sensitive employee information from unauthorized access. You can do this by protecting certain cells or ranges of cells in your spreadsheet.

1. To do this, select the range of cells you want to protect.
2. Go to the "Review" tab, and click "Protect Sheet."
3. Set a password to protect the cells from being edited by anyone without the password.

Step 15: Save and Share Your Directory

Finally, save your directory by clicking 'File' > 'Save As' and choosing a suitable file name and location. Share your masterpiece with your team by emailing the file or uploading it to a shared drive.

And there you have it – your very own employee directory in Microsoft Excel!

Get Your Free Excel Employee Directory Template

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

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

Download your free employee directory template below:

Unsure of Having Your Employee Directory in Excel?

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

• Limited collaboration - Excel doesn't allow for easy sharing and simultaneous editing by multiple users. Version control can become an issue.
• Not optimized for relational data - Excel can only display data in a flat table. It's difficult to establish relationships between entries or build structured org charts.
• Manual upkeep - Any changes to the directory must be manually inputted. There's no simple way to connect Excel to HR systems to sync employee data.
• Feature limitations - Options for access controls, permissions, reporting, and workflows are lacking compared to dedicated directory software.

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

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.

Extra Tips on Employee Directories in Microsoft Excel

About the Author

Greg Bennett, COO at OneDirectory, has over 10 years of experience helping businesses streamline their internal communication and organizational data management strategies. He has guided numerous clients through the journey from simple Excel-based directories to robust, scalable solutions integrated with Microsoft 365.

For personalized guidance or support in choosing the right employee directory approach for your organization, contactOneDirectory. We specialize in data-driven strategies that help companies maintain accurate, accessible, and secure employee information as they grow.