If you’re having a party this weekend, you can plan your party seating with Excel. 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 disappears 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:

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

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)

**=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.

#### Named Range

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

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

#### 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.

### Download the Sample File

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

**NOTE**: There is a newer seating plan here: Excel Seating Plan with Charts

__________

Deborah,

If only you posted this 3 years ago…..!!!!

I DID arrange my wedding with Excel, from the seating (without the shapes), to the soundtrack list, with alternative lists in case the DJ wasn’t able to find what we wanted, and of course, the budget…

Before that, I’ve had the same approach for a seminar we sponsored years ago, and before that, for a Scout’s festival, but that was with DBbase III+…

sorry about the mispell, Debra…

It would be a great addition if there was a macro that created x number of tables with y seats and liked the cells, ready to be used! Please consider updating the template

Kind Regards

Theo

@Martin, sounds like your wedding was very efficiently planned! And I hope you were able to stay on budget too. π

@Theo, thanks for your suggestion to add a macro. If I update the sample file, to automatically create table and seats, I’ll write about it here.

This is excellent!

Maybe you and Mike Alexander can get together to plan some kind of bacon party!

βToadβ

Toad, thanks, and if Mike brings the bacon, I’ll arrange the seating! Maybe he’ll use this technique for the Excel boot camps that he’s running.

That’s really good, I never considered using shapes like that.

For me doing table plans, I used a slightly different route, in that I plotted the attendees on an XY Scatter plot.

I had the chart set to A3 size, which gives me nine spaces, which I can fit 6/8/10/12 to a table. I did without any shapes and just used the attendees names on the plot, which seems to work for me….

cheers

Steve

Steve, thanks for describing your table plan method. Very interesting approach!

What modifications would I need to make if I had potentially less guests than seats?

I am modifying this solution for a classroom seating plan and want it to handle *up to* 30 students at 30 desks. I also want to be able to add or remove students from the class.

I have a sheet containig producion figures (a1 = Date, B1= Qty. C1= description, and so on) i would like to have in a separate sheet only on entry which day the maximum qty producted and related date on the same row (complete row shoulfd be copied)

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

If we delete “-ROW(B1)+1?, turn it to =IF(ROW(B1:B24)>COUNT(C1:C24),””, INDEX(B:B,SMALL(C1:C24,ROW(INDIRECT(“1:”&ROWS(B1:B24)))))), if runs well too.

Why adds?

Fantastic guide thank you, just used it to create my wedding seating plan.

I’m not that great with excel and want to change the column c in the lists sheet to say no rather than the row number, I can do this but ran out of talent in getting column d to still work as it should. What do i need to change the formula to in column d to make it work

Thanks

idont understand what iam wrong but both formulas showing me with errors.

Love the seating chart but have a guest list of nearly 200. The array will only copy to d24. That means only 24 names appear in the drop-down menu when I go to assign guests to tables. Any solution?

The event is Saturday so please help. π Mary

I have guest of 100 people for a 3 days seminar and I need to arrange 4 guest on each table (total 25 tables) and everyday I want different set of 4 people on each table. How do I do this on an excel sheet. please guide

Thank you for sharing this Debra! 6 years on, and it’s still good!

I took your example spreadsheet and expanded it for my wedding. We’re expecting around 130 guests – I am reasonably skilled in excel, but it’s often hard to start from scratch! Now I’ve modified your spreadsheet to have 14 tables, each with up to 10 guests (and I got a scaled picture of the room from a PDF and used it as the background to not only doing seating plan, but also table arrangements. With a little bit of searching I was able to understand how and where you’d done your data validation etc and expand these lists. Many thanks again for a great headstart! π

Allan

@Allan, thanks for letting me know that the seating plan file helped together started. I hope that all your wedding planning goes smoothly!

Is there a way to add an alphabetical sort to the array formula? I’ve tried adding additional INDEX parameters but it’s just not working for me.

Rich

Great guide very simple and well explained, had trouble with the unnasigned guests part so i left that formula out. this was extremely helpful and our wedding table plan looks great.

Many Thanks

This has helped me tremendously. I was wondering if there was potential for me to include another layer of fun, and include people’s dietary requirements, like a conditional formatting of changing the colour of that seat to fit with the person with a dietary? Thank you again!

Laura, please let me know if you figure out the dietary issues! I could really use that info! Thanks!

This is seriously awesome! I am using this to plan our wedding of 200 folks. With some tweaking I was able to create the head table and surrounding tables. Gold star for you Debra! (And any Sources you used)

Thank, Graham! I hope everything goes smoothly

Hi,

I am planning to use this amazing template to plan our wedding. Do I need to have knowledge of array functions to build it though?

TY,

Shay

Getting NAME# error when using the array on the list π

In an attempt to expand this to 60, I’m struggling to comprehend how to edit the array in D to run through 60 columns. It doesn’t seem to work via copy & paste method, any other way to do it?

OMG thank you so much for this!!! Saved me from pulling out my hair at 12AM the day the final seating plan was due to the venue.

thanks..that is really helpful π