Excel Seating Plan with Chart

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

Excel Seating Plan List & Chart

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.

Seating Plan Sample File

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:

• fake guest list, with 24 names
• 3 tables for guests
• 8 seats per table
• some guests have already been assigned to seats and tables

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.

Try the Sample File

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.

• Note: These instructions show how to assign those fake guests to the 3 tables.
  Later you’ll see how to create your own guest list, and assign them to tables.

On the TablePlan sheet:

• In column C, there is a list of table numbers, with each number repeated 8 times
• In column D, there is a list of seat numbers, numbered 1 through 8, for each table
• In column B, you will use the drop-down list to assign a guest in each row

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

Assign Guest to Table and Seat

To assign a guest to a specific table and seat, follow these steps:

• Decide which table and seat you want the guest to use
• In column C, find the table number you want to use
• In column D, find the seat number you want to use
• In column B, click the drop-down arrow, to see the guest list
• Scroll down to find the guest name
• Click on the guest name to select it.

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.

How to Edit Guest List

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:

• Clear all the names from column B on the Lists sheet – leave the END OF LIST entry at the bottom of the list
• If you have fewer than 24 guests, delete the extra rows, from the middle of the list
• If you have more than 24 guests, insert more worksheet rows, somewhere in the middle of the list. This will affect the list in columns H:I too – that’s okay.
• Then, type the names of your guests, in column B
• Be sure that END OF LIST is in the cell below the last guest name
• Then, copy down the formulas, in columns C, D and E, to the bottom of the guest list (including the END OF LIST row)

Then, update the list in columns H and I:

• in column H, renumber the list, so this a number for each guest.
• In column I, fill the formulas down to the bottom of the list.

How to Add More Tables

If you need more tables in the seating plan, follow these steps:

Add the Guest List Cells

• On the TablePlan sheet, copy one of the existing table lists, such as B10:D17
• Paste the copied cells below the last table list
• Change the table number in the pasted list, and clear any Guest names

Here is the new list for Table 4.

Add the Table Chart

Next, follow these steps to create the chart for the new table:

• Copy one of the existing table charts, and paste it onto an empty area on the sheet
• Change the table number in the chart title

Next, with the new chart selected, use this simple trick to change its data source:

• Point to the border of the highlighted data source
• Drag it down to the new table’s data.

Add More Chairs to Table

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:

• insert a couple of rows, to add extra chairs at one or more of the tables. The doughnut chart will adjust automatically, to show the extra chairs.
• Add the table number in column C
• Renumber the seats, from 1 to 10, in column D

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.

How Excel Seating Plan Works

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.

Drop Down END OF LIST

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),

• Guests who have NOT been assigned to a seat appear first
• After those names, there is an “END OF LIST” item
• Below that, you’ll see the names of guests who are already assigned to a seat

Guest List Formulas

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

• =IF(COUNTIF(TablePlan!B:B,B2)>1, “Multi”, IFERROR(INDEX(TablePlan!$C$2:$C$25, MATCH(B2, TablePlan!$B$2:$B$25,0)),”—”))

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.

Number the Unassigned Guests

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.

=IF(C2=”–“,SUM(MAX(D$1:D1),1),””)

Those numbers will be used to create the drop down list of guests.

Number the Assigned 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.

=IF(D2<>””,””,SUM(MAX(E$1:E1),1))

The formula in column F adds those numbers to the maximum number in column D (unassigned guests).

=IF(E2=””,””,SUM(MAX(D:D),E2))

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.

Source List for Drop Down

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.

=INDEX(B:B,MATCH(H2,IF(H2<=MAX(D:D),D:D,F:F),0))

The formula will return a name from column B, based on the number in column H.

• If the value in column H is less than or equal to the highest number in column D (unassigned guest IDs), it looks for a match in column D.
• Otherwise, it looks for a match in column F (assigned guest IDs).

NOTE: If you add more guests, add a number for each guest, and copy the column I formula down to the last number.

Name the Guest List Range

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.

=OFFSET(Lists!$I$1,1,0,MAX(Lists!$H:$H),1)

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.

Download Sample File

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.

More Chart Tutorials

Cluster Stack Pivot Chart

Charts, Interactive

Charts, Line-Column 2 Axes

Pie Charts

Show Hidden Data in Chart

Waterfall Chart