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

- LET Function
- Cutout People (required)
- EVEN Function (required)
- LAMBDA Function
- Dynamic Arrays Formulas
- 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!

That looks amazing Jon – I aspire to be that good!

Thanks Nicolette! The possibilities with Excel are endless, along with the problems to solve. I’m happy to hear you are inspired and I know you will get there!

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.

That’s a great idea! Thanks for sharing, Kim.

This is what i’m currently trying to do by using Jon’s template but i’m not too savy!

You and Minda are the best! Keep up the good work.

Thanks Jeffrey! We appreciate your support.

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!

Jon

This file is exactly what I need to do a seating chart for an annual banquet that we do. I would love it if you explained the Admin-Group sheet. I understand that you’re using Group numbers to keep people together at the table, but that’s about all I understand. I could also use some explanation on the Table Patterns sheet. We have 10 seats per table and I think we will have 20 tables. Do I need to do anything to change the patter on the 10 person table section? I so wish you would have done the deep dive video on this file. I’m so excited to use this file for our banquet, but it might be too complicated for me without some help. Loved the video! You did a great job of explaining.

I am unable to get it to work. i get #name and #value in most of the fields. It looks like it would be awesome to use though.

Jon, this is a great piece of work. I found it by accident while looking for a seat planner. I have found that I want 30 tables, in 3 rows of 10. Looking at the example it looks like it is possible, yet when the values are entered into the appropriate cells to achieve this, the table overlay stops at 5th column for all 3 rows of tables. Yet the table number in is correct. Tables for row 1 from 6 to 10, row 2 from 16 to 20 and row 3 from 26 to 30 are not visible. What controls the displaying of the tables for the other 5 columns? How can I get all the tables visible. Thanks Nigel

Great spreadsheet – very impressive! Question – what if the user knows where they want to place certain people at specific tables (for the reception tab). How do you keep the spreadsheet from randomly assigning them to different tables based on the size of their groups? Also, instead of using a drop down menu to select a name, is there a possibility to type in a name instead? For venues/large conferences with 1000s of attendees, it would be labor intensive to use a drop down menu vs a search filter/splicer.

Rec’ing a lot of #name and #value errors in most of the fields

Attendee – Seating Chart, =$C$3

Attendee – Tables, =”Table # “&’Attendee – Tables Setup’!C5

Admin – Groups, Seat No., All Seats, Ful List, Group

Table Arrays, X Selected, Y Selected

Attendee – Tables Setup, Seat, Table, Group

Good idea

1). I like this piece of Excel, but how would you do this for

a). 2 rows at each row at an angle facing the front for adult training session.

b). Cannot place names for the seats until delegates arrive for the training session.

2). How would you convert it to a ‘U’ shape classroom if suddenly you had to change the above to a ‘U’ shape?

Great work Jon!! I plan to adapt it to visually present the equally complex results of my ongoing project (financial analysis). Thank you so much for you talent and dedication to Excel excellence.

I wish I could have had this last year for my 25-wedding anniversary. : ) I will definitely share this info for my niece’s upcoming wedding.

Dear Jon

Thank you for your excellent Excel file for seat allotment purpose, taking into account several requirements.

Several years ago I had developed a very simple seat arrangement or order matrix for allotting seats to students in an examination hall. Only one function namely “Random” was used. It is of course very basic compared to yours.

I have downloaded the file but on data types I get unknown and the reason is that I need office 365. Is that correct? I am using office 2021 for windows

Thanks for the great job