Plan Your Party Seating with Excel

If you're having a party this weekend, you can use Excel to plan the guest seating. And if you sent me an invitation, it hasn't arrived yet!

In this example, you'll enter the guest names on an Excel worksheet, then fill the tables by selecting names from data validation drop down lists. After you've selected a name, it will disappear from the drop down lists, so you can't accidentally assign one of the guests to two different seats.

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.

Draw the Tables and Chairs

On a sheet named TablePlan, use the Excel drawing tools to create the tables and chairs. I used the Oval shape to draw 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, create a list of the tables and seat numbers. If you colour coded the tables, use the same colours in the table list.

In the next column, format the cells where you'll select the guest names. You'll add data validation to those cells later.

Add a Formula to the Guest List

On the Lists worksheet, you'll add a formula to check if the name in that row has been assigned a seat. The first name is in cell B1, so enter this formula in cell C1:

=IF(COUNTIF(TablePlan!$D$2:$D$25,B1),"",ROW())

Copy the formula 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.

Create the List of Unassigned Guests

In column D, you'll add an array formula to list the unassigned names. This list will be used for the data validation drop down lists.

In this example, cells D1:D24 are selected, and this formula is array entered (press Ctrl+Shift+Enter)

=IF(ROW(B1:B24)-ROW(B1)+1>COUNT(C1:C24),"",
INDEX(B:B,SMALL(C1:C24,ROW(INDIRECT("1:"&ROWS(B1:B24))))))

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.

Define a Named Range

Next, you'll create a dynamic named range for the unassigned guests lists.

1. On the Excel Ribbon, click the Formulas tab
2. Click Define Name
3. In the New Name dialog box, type NameCheck as the name
4. In the Refers to box, type this OFFSET formula, then click OK

=OFFSET(Lists!$D$1,0,0,COUNTA(Lists!$D$1:$D$24)
-COUNTBLANK(Lists!$D$1:$D$24),1)

Add the Data Validation Drop Down Lists

Next, you'll add the drop down lists:

1. On the TablePlan sheet, select the cells for Guest names, cells D2:D25 in this example.
2. On the Excel Ribbon, click the Data tab
3. Click Data Validation
4. In the Data Validation dialog box, from the Allow drop down, select List
5. In the Source box, type:  =NameCheck
6. Click OK

The drop down lists are added to the cells, and you can select a guest name.

Link the Seats to the Guests

To show the guest names on the assigned seats, you can link the shapes to the cells.

1. Click on the shape for Seat 1 at Table 1.
2. Click in the Formula Bar
3. Type an equal sign, then click on the guest list, where the Table 1 Seat 1 name will be entered
4. Press Enter to complete the link

Repeat these steps to link all the seats to the guest link cells.

Assign the 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.

Download the Sample File

If you're planning your own party, you can download the sample file with an Excel seating arrangement example.

__________