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.

SeatPlan01

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.

SeatPlan02

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.

SeatPlan03

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.

SeatPlan04

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.

SeatPlan05

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)

SeatPlan06

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

SeatPlan07

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

SeatPlan08

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.

__________

You may also like...

27 Responses

  1. Martin says:

    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+…

  2. Martin says:

    sorry about the mispell, Debra…

  3. Theo says:

    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

  4. @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.

  5. Toad says:

    This is excellent!

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

    –Toad–

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

  7. Steve says:

    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

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

  9. Debra M says:

    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.

  10. Abi says:

    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)

  11. leezhihong says:

    =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?

  12. Rascal852 says:

    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

  13. nerijus says:

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

  14. Mary Yohon says:

    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

  15. Sunil Jain says:

    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

  16. Allan McElroy says:

    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! :D

    Allan

  17. Rich says:

    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

  18. Paul says:

    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

  19. Laura says:

    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!

  20. Jessica Kaplan says:

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

  21. Graham O'Bray says:

    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)

  22. Shay Antonir says:

    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

  23. Christine says:

    Getting NAME# error when using the array on the list :(

  24. Vinnie says:

    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?

  25. Vanathi says:

    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.

Leave a Reply to Jessica Kaplan Cancel reply

Your email address will not be published. Required fields are marked *