How to create an Excel seating plan, for a wedding reception dinner, or other events. Assign party guests to specific table number and seat number. Name list, visual chart. Step by step instructions, free workbook
Author: Debra Dalgleish
To plan a wedding reception dinner, or another type of event, Excel can help you create a seating plan.
Enter the names from your guest list, then choose a table number and seat number for each guest. After you assign each guest to a seat and table, their name appears in the visual seating chart.
To use this Excel seating plan, start by downloading the free workbook. Then, clear out the sample data from that file, and enter your guest names. After that, you can assign your guests to a specific table, and seat number.
In the sections below, see step-by-step details on how to use the sample file. Also, see how to add more guests and tables, if needed.
The first time that you open the Excel seating plan sample file, take a couple of minutes to experiment with the sample data, and see how the seating assignment works. Later, you can clear out the sample data, and enter your own guest list.
The sample file has:
For each table, the guest names and table number cells are the source data for one of the doughnut charts. The chart for Table 1 is shown below, with its source data highlighted.
To test the seating plan sample data, go to the main sheet, named TablePlan. That is the sheet where you assign each guest to a seat at one of the tables.
On the TablePlan sheet:
IMPORTANT: The list must be sorted by Table Number and Seat Number. DO NOT sort the list in any other order or the seating charts will show incorrect information
To assign a guest to a specific table and seat, follow these steps:
NOTE: Choose names that at the top of the list, above the “END OF LIST” item.
The selected guest’s name appears in the chart for that table.
In the sample file, there is sheet named Lists. In column B on that sheet, there are 24 guest names. After the last guest name, there is a final entry – END OF LIST.
To create your own guest list:
Then, update the list in columns H and I:
If you need more tables in the seating plan, follow these steps:
Here is the new list for Table 4.
Next, follow these steps to create the chart for the new table:
Next, with the new chart selected, use this simple trick to change its data source:
The sample file is set up with tables that have 8 chairs each. If your tables have more seats, follow these steps to add more chairs:
In this screen shot, I’ve inserted 2 rows below Ian’s name at Table 1. The doughnut chart shows 10 seats now, with 2 empty seats.
NOTE: The doughnut chart slices were manually changed to pink and grey, so the new slices could be coloured too.
In the next few sections, there are details on how the Excel seating plan with charts works. You can skip down to the Download section, if you don’t need to know the “under the hood” stuff.
This version of the seating plan keeps all the guest names in the drop down list, instead of removing them. That prevents data validation error warnings from appearing on the sheet.
In the drop down list (shown above),
There are formulas in columns C, D, E and F on the Lists sheet, beside the list of Guest names.
If you add more rows for guest names, be sure to copy those formulas down to the last row of guest names.
In column C, the formula returns the table each guest has been assigned to, if any. Or, if a guest has accidentally been assigned to more than one table, the result is “Multi”.
NOTE: If you add more tables to the TablePlan sheet, change the cell references (in red) in the INDEX and MATCH functions, to include all the data entry rows.
In the screen shot below, Ken has been assigned to multiple tables, so that needs to be fixed on the TablePlan sheet.
In column D, the formula checks the Table number column (C), and if the result is “—” then it returns the next available ID number for that guest.
Those numbers will be used to create the drop down list of guests.
In the drop down list of guests, the guests who are already assigned to a table will be shown at the end of the list. The formulas in E and F created the numbering system for those guests
The formula is column E returns the next available ID number for each assigned guest.
The formula in column F adds those numbers to the maximum number in column D (unassigned guests).
In the screen shot below, the “END OF LIST” item has the Unused ID of 16, and the first assigned guest (Dan), has the Used ID number of 17.
In column H, there is a list of numbers that were manually entered – from 1 to 25. That is the number of guests, plus 1 for the “END OF LIST” item.
In column I, a formula returns the guest names with the matching numbers.
The formula will return a name from column B, based on the number in column H.
NOTE: If you add more guests, add a number for each guest, and copy the column I formula down to the last number.
The list of names in I2:I26 is a dynamic named range – NamesUse. That name is the source for the data validation drop down lists on the TablePlan sheet.
This OFFSET formula is used to define the named range, and is based on the highest number in column H.
If you add or remove numbers in column H, the NamesUse range will automatically adjust in size. Just remember to fill in all the numbers in column H – don’t leave blank cells after adding guest names.
Excel Seating Plan: Click here to download the Excel seating plan with charts workbook. The zipped file is in xlsx format, and does not contain any macros.
Last updated: March 30, 2022 11:38 AM