Seating Chart Planner – Excel Hash – Episode 3

Bottom Line: In this post, I give an overview of my submission for the Excel Hash competition.  My solution is a seating chart planner that optimizes group seating for events, whether the venue seating is in rows or tables.  

Skill Level: Advanced

Watch the Tutorial

Download the Excel File

You can download the Excel file with the Seating Chart Planner here:

Seating Chart Planner

Here is a preview of the “Table View” for the seating chart planner that the attendee can use to find their seat.

There is also a “Row View” for row/aisle seating layouts. The planner also has admin views to control the seats per row/table, room layout, sort order, and group order/optimization.

The Excel Hash Competition

This solution is my submission for Episode 3 of Excel Hash.

What is Excel Hash?

It's a competition between my fellow Microsoft MVPs and YouTubers where we're given a list of Excel features (our ingredients, so to speak), and we have to create a solution in Excel using all of those features. This is the third time we've done it, and it's a really fun way to learn different uses for features in Excel.

The Ingredients

The rules are to use 4 of the following Excel features to create a solution, with two of those features being required.

1. LET Function
2. Cutout People (required)
3. EVEN Function (required)
4. LAMBDA Function
5. Dynamic Arrays Formulas
6. Power Query Custom Data Types

I chose to use Dynamic Array Formulas and Power Query Custom Data Types along with the required Cutout People and EVEN function.

The Contestants

As in previous years, this friendly competition included some really sharp minds and Excel pros, so I encourage you to like and subscribe to their channels and blogs to further increase your Excel knowledge.

Here's a playlist on YouTube of everyone's entries.

Note, there is no voting this year.

Here are links to Episode 1 and Episode 2 as well, which you can also learn a lot from.

My Inspiration for the Seating Chart

With the last name of Acampora, I almost always sat in the front of the classroom. Since a lot of teachers create seating charts based on alphabetical order by last name, I was pretty much guaranteed a seat up front.

This also meant going first for presentations and discussions. 😬

Ultimately, I think it's a good thing to sit up front. So I wanted to give others a chance by creating a seating chart planner that easily allows the teacher to sort by OTHER attributes besides last name. This could be GPA, attendance, age, etc. Or it could be random order.

I mentioned it to my wife, and she said, “what about weddings?” This became quite a challenge, but the planner evolved to handle round table seating for events and conferences.

I also wanted a way to have groups or families to sit together while using the least number of rows or tables. This was also challenging, but I was able to use the SORTBY function and Solver Add-in to optimize the sort order. More on that below.

Overview of the Seating Chart Planner

The planner starts with a list of names in an Excel table. It can also include columns for attributes about each person like attendance, grade point average, performance, age, primary language, group name, etc.

Dynamic Array formulas are then used to create a spill range in either the row or table views.

There are admin pages that allow the event coordinator to control the layout and sort order.

The planner also has front-end views that attendees can use to find their seating assignment after selecting their name from a drop-down list.

The planner optimizes seating arrangements, allowing you to use the least number of tables or rows. It also keeps everyone in their group sitting together. And you can customize the setup to match your venue by changing the number of seats per table, rows, and/or number of tables.

The Four Ingredients (Excel Features)

Let's take a look at how the four Excel Hash ingredients are used in the planner.

1. Custom Data Types in Power Query

The first “ingredient” in my hash is custom data types. This is a new feature of Power Query that allows you to store information from multiple columns in a single cell.

You can see the contents of the data in the card view by clicking on the icon in the cell.

We can extract this data into formulas and we can also use it in spill ranges. I've used the data in the cards to populate my spill range (see the image of the visual seating chart below).

2. Dynamic Array Formulas

I use a lot of dynamic array functions in this model, but particularly SEQUENCE and SORT BY functions. These are two very powerful functions to that help to arrange the participants in the two seating formats.

The dynamic formulas allow us to change the properties such as number of seats or the order of placement. For example, if you want to have 6 seats per row instead of 8, the planner instantly rearranges the optimal seating plan in a reformatted spill range.

3. The EVEN Function

To implement the EVEN function, I used it with conditional formatting to shade alternate rows.

Above is an image of the conditional formatting manager. As you can see in this rule, the EVEN function rounds up a number to the nearest even number, testing that against the current row number. If it returns true, the row gets shaded.

4. A Cutout Person

As you saw in the Row Configuration Seating Planner, I used a cutout person to hold a sign. Excel has a large library of cutout figures that you can add to spruce up your reports, forms, or presentations. To access them, go to the Insert tab on the Ribbon, click on Icons, then the Cutout People tab.

I also used some of the new Illustrations from that menu on the attendee view sheets.

The Group Optimization Feature

If you're planning any type of event, then you typically want to maximize the space with the most number of attendees. This can be challenging if you have small groups or families that want to sit together. How do you find the best combination of small groups to use the least amount of rows or tables?

Well, Excel can solve for this!

I'm using the SORT BY function and the Solver add-in to iterate through all the possible combinations of sequences of groups and the number of people in each group.

It will find the order that the groups should be sorted in to best fit the given seating availability. That's what you are seeing in the Solver Order column below. This optimizing feature finds the least number of tables or rows that we need by changing the order of the groups.

Table/row and seat numbers are then calculated for each person based on the group order. SEQUENCE and SORTBY are used to create the spill ranges based on the seat number order.

Conclusion

That's an overview of the Seating Chart Planner, my entry for Excel Hash: Episode 3. I'll be creating an additional post(s) to walk through some of these features in more detail.

Don't forget to checkout the other submissions on this YouTube Playlist.

Please leave a comment below if you have any questions or suggestions. Thanks!