If you’re planning a wedding, or another type of event, you might need a seating plan. Get this free Excel seating plan with charts, and modify it to show your guest names and table arrangement.  See how to use the sample file, and how to add more guests and tables.

Old Excel Seating Plan

This seating plan with charts is a new version of one that I posted long ago (2006), and the new version is easier to use.

You can read details about the old version, including how it was set up. That version used drop down lists, based on a formula that hid the guest names after they were assigned to a table.

The tables and chairs were circle shapes, and each chair was linked to a cell. That worked well, if you didn’t have too many guests, but it was a lengthy process to add more tables and seats for a large party.

Excel Seating Plan with Charts

I just created a new version, which is easier to use. Instead of circle shapes, this plan uses doughnut charts – thanks to Oz du Soleil for that idea.

The main sheet in the workbook is named TablePlan. On that sheet, you assign guests to seats at the different 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.

Excel Seating Plan with Charts

Here’s what I’ll cover in this blog post:

• First, I’ll show you how to use the sample file, with the fake guest list.
• Later, you’ll see how to add more guests or tables to the Excel seating plan with charts, or change the number of seats per table.
• After that, if you’re interested in the setup details, I’ll explain the formulas and other features in the sample file.
• Finally, there is a link where you can download the Excel seating plan with charts sample file.

How to Use the Sample File

Here are the basics on how to use the sample file, with its fake guest list. The sample file has 24 guests, who can be seated at 3 tables, which have 8 seats per table.

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.

In the Guest column, the cells have drop down lists. To assign a guest to a seat:

1. Select the cell at the table and seat where the guest will sit
2. Click the drop down arrow, to see the guest list
3. Scroll down to find the guest name
4. 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 the 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
• With the new chart selected, use one of these methods to change the data source:
  • Point to the border of the highlighted data source, and drag it down to the new table’s data.
    • 
  • OR
  • On the Excel Ribbon, under Chart Tools, click the Design tab
  • Click the Select Data command
  • Click in the Chart Data Range box, and select the name and table number cells for the new table.
  • Click OK to update the chart

Add More Chairs to a 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 the 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.

Get the Excel Seating Plan with Charts

Click here to download the new Excel seating plan with charts workbook. The zipped file is in xlsx format, and does not contain any macros.

______________________

The post Excel Seating Plan with Charts appeared first on Contextures Blog.

Original source: http://blog.contextures.com/archives/2018/03/08/excel-seating-plan-with-charts/