Two ways to do this. The data way puts a table name beside each guest so formulas can check your counts. The visual way draws the room with shapes. Build the data first, then draw on top. The seven steps:
- Guest list sheet
- Tables and capacities
- Seat counts with COUNTIF
- Conditional formatting
- Draw the room
- Meal counts
- Print setup
1. Set up the guest list sheet
One row per person, never one row per couple. Name the sheet Guests:
| Column | What goes in it |
|---|---|
| A: Full name | One person per row |
| B: Party | Household or group |
| C: RSVP | Yes, No, or blank |
| D: Meal | Chicken, Fish, Veg |
| E: Allergies | Free text |
| F: Table | Table 1, Table 2 |
Type table names identically every time (Table 1, not T1 or table one), because every formula below matches on that text. Lock the header with View > Freeze Panes > Freeze Top Row. Excel's File > New gallery has party planner templates, though most are guest lists rather than room layouts; our free guest list template has these columns ready.
2. List your tables and their seat counts
On a second sheet named Tables, put each table name in column A and its capacity in column B: 8 for a 60-inch round, 10 for a 72-inch round, 8 for an 8-foot banquet table. Our seats per table guide has the full set of capacities. This tiny sheet is what turns a list of names into a plan you can trust.
3. Count the seats used with COUNTIF
In C2 of the Tables sheet enter =COUNTIF(Guests!$F$2:$F$300,A2) to count guests carrying that table name, and =B2-C2 in D2 for seats still free. Fill both down. If you would rather type names into a visual block of cells, one column per
table, count a column with =COUNTIF(B4:B13,"<>"), which counts every cell in
the range that is not empty. =COUNTA(B4:B13) does the same job.
| Formula | What it tells you |
|---|---|
| =COUNTIF(Guests!$F$2:$F$300,A2) | Seats used at the table named in A2 |
| =B2-C2 | Seats still free there |
| =COUNTIF(Guests!$C$2:$C$300,"Yes") | Confirmed guests so far |
| =COUNTIFS(Guests!$C$2:$C$300,"Yes",Guests!$F$2:$F$300,"") | Confirmed guests with no seat |
4. Let conditional formatting catch the mistakes
Select C2:C20 on the Tables sheet, then Home > Conditional Formatting > New Rule > Use
a formula to determine which cells to format. Enter =$C2>$B2, pick a red fill,
and any over-full table turns red as you type. On the guest list, select A2:F300 and add the rule
=$F2="" in amber, so every unseated guest lights up.
5. Draw the room on its own sheet
Add a sheet named Chart. Select all, set the column width to about 2 and the row height to about 12 so the cells come out roughly square: graph paper, one cell per foot of room. Then Insert > Illustrations > Shapes > Oval, holding Shift as you drag for a true circle. Right-click it, choose Edit Text, and type the table number inside. Copy and paste for the rest, and use Shape Format > Align > Distribute Horizontally to space a row evenly.
One catch: shapes float above the cells and know nothing about them, so the drawing does not update when you change the Table column.
6. Get meal counts with a pivot table
Click any cell in the guest list, choose Insert > PivotTable, drag Meal to Rows and again to Values, filter RSVP to Yes, and add Table to Columns for per-table meal counts. Refresh it after every edit. The RSVP filter matters more than it sounds: declines otherwise inflate every number you hand the kitchen.
7. Set it up to print
- Select the chart, then Page Layout > Print Area > Set Print Area.
- Orientation > Landscape suits almost every room.
- Scale to Fit: Width 1 page, Height 1 page for a floor plan or Automatic for a long list.
- Turn on Sheet Options > Gridlines > Print to show the grid on paper.
- Check View > Page Break Preview first: Excel splits pages where you would not.
Your file is not wasted either way
If the spreadsheet later starts doing too much, the free Seatful builder reads .xlsx and CSV directly, maps your columns, and flags likely duplicates before anything is added.
Where this approach gets hard
Excel is genuinely good at the list. It is the replies that wear people down.
- Replies arrive everywhere. Texts, emails, a card in the post. Each is a row you find and re-key.
- A decline does not free a seat. You clear the cell, decide who moves up, then fix the drawing.
- Meal counts go stale quietly. The pivot shows yesterday's numbers until you refresh it.
- The picture and the data drift. Shapes are not linked to rows, so the two stop agreeing.
- Nothing comes out guest-ready. Place cards, table numbers, and a door sign are three more jobs.
The same plan, with the data wired in
Seatful does the parts Excel cannot. Import that same spreadsheet, lay out round, banquet, and head tables, then tap a guest and tap a seat, or auto-fill everyone in one tap with households kept together. Send each guest a private RSVP link and replies land in the chart itself, so a decline frees the seat and meal counts move with it. Print the chart, a find-your-seat list, and a caterer report from the same file. Build it free in your browser, no account needed.
Prefer a different tool?
- Google Sheets, for editing together in real time.
- Canva, for a beautiful entrance sign.
- The full method, guest list to door.
- A free guest list template.