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

Watch on YouTube & Subscribe to our Channel

Download the Excel File

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

Seating Chart Planner - Excel Hash Feb 2021.xlsx (1.1 MB)

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.

Dynamic Seating Chart
Click to enlarge

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.

Excel Hash 2021

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.

Excel hash participants
Link to YouTube playlist of everyone's entries

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.

Seating Chart Alphabetical Order by Last Name

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.

Raw data for seating chart

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

Click to enlarge

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

Excel Seating Chart Planner Admin View Sheets

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.

Seating Planner for Row Configuration
Click to enlarge
Seating Planner for Table Configuration
Click to enlarge

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.

Card view with custom data type

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.

Dynamic array formulas used for seating chart spill range
Click to enlarge

3. The EVEN Function

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

Even function in conditional formatting
Click to enlarge

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.

Cutout people
Click to enlarge

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.

Solver Order

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!

  • I did something similar that we found useful: I created a cubicle map for my large office department. The employee population was rather dynamic, but all it took was maintaining the data table. We made poster-size print and placed by entrance.

  • Just blown away by this, and the possibilities it offers…

    I work in music education… thinking about using this approach to manage ensemble/orchestral seating plans… excellent!

  • Jon, I watched the other presentations and I have to say I found that yours was the best. I have always liked your presentation style – to the point, not rambling and easy to understand, esp. for those of us that, as another commenter said, aspire to your level.
    Thank you.

    ps. where was this solution when I was in charge of the company’s customer conventions!

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >