If you’re having a party this weekend, you can use Excel to plan the guest seating. Get this sample Excel seating workbook, enter the guest names on the Lists sheet, then fill the tables by selecting names from data validation drop down lists. After you’ve assigned a guest to a table, that guest’s name will disappear from the drop down lists, so you can’t accidentally assign a guest to two different seats.
NOTE: There is a newer seating plan here: Excel Seating Plan with Charts
Excel Seating Plan Tables
Here’s what a seating plan table looks like, after the guest names are selected.
Enter the Guest Names
On a worksheet named Lists, the guest names are typed in column B. Sort the names in alphabetical order, so the names will be easier to find in the data validation drop down lists.
In the sample workbook, you can clear out those names, and enter the names of your guests.
Draw the Tables and Chairs
On a sheet named TablePlan, use the Excel drawing tools to create the tables and chairs. In the sample workbook, there are 3 tables, with 8 chairs at each table.
Tip: To draw a circle, hold the Shift key while you use the Oval shape drawing tool.
You can number and colour code the tables, to make it easier to keep track of things.
List the Tables and Seats
To the left of the table diagram, there is a list of the tables and seat numbers. The tables have the same colour coding as the tables, but that isn’t required — it just makes them easier to match up.
In the next column, there are drop down lists where you’ll select the guest names. The drop downs were created with data validation.
How the Seating Plan Works
If you’re interested in how the seating plan works, here are the details on the formulas and drop down lists.
Guest List Check
On the Lists worksheet, there is a formula to check if the name in that row has been assigned a seat. The first name is in cell B1, and this formula is in cell C1:
That formula was copied down to the last name.
If a guest has been assigned to a seat, their name will appear in column D on the TablePlan sheet. In that case, this formula will return an empty string. If a seat hasn’t been assigned, the formula will return the row number.
List of Unassigned Guests
In column D, there is an array formula to list the unassigned names. This list is used for the data validation drop down lists.
To create this array formula, cells D1:D24 were selected. Then, this formula was typed, and array entered (press Ctrl+Shift+Enter)
This is a multi-cell array formula (by DanielM.) that moves blank cells to the bottom of the list. For more information on this formula, see Excel Data Validation – Hide Previously Used Items.
In the screenshot below, some of the guest have been assigned to seats, and their names don’t appear in column D.
Next, a dynamic named range was created for the unassigned guests list — it will automatically grow or shrink as guest names are assigned to tables.
Here are the steps for creating that named range
- On the Excel Ribbon, click the Formulas tab
- Click Define Name
- In the New Name dialog box, type NameCheck as the name
- In the Refers to box, type this OFFSET formula, then click OK
Data Validation Drop Down Lists
Next, the drop down lists were created, with these steps:
- On the TablePlan sheet, select the cells for Guest names, cells D2:D25 in this example.
- On the Excel Ribbon, click the Data tab
- Click Data Validation
- In the Data Validation dialog box, from the Allow drop down, select List
- In the Source box, type: =NameCheck
- Click OK
After the drop down lists are added to the cells, you can select a guest name.
Link Seats to Guests
To show the guest names on the assigned seats, the Seat shapes were each linked to one of the Guest name selection cells. Here are the steps for that:Click on the shape for Seat 1 at Table 1.
- Click in the Formula Bar
- Type an equal sign, then click on the guest list, where the Table 1 Seat 1 name will be entered
- Press Enter to complete the link
Repeat these steps to link all the seats to the guest link cells.
Assign Guest Seats
Now you can select guest names from the drop down lists on the TablePlan sheet, and get your party organized. Keep all those arguing relatives at separate tables, and everything should go well.