A spreadsheet seating chart works if you build in a few checks up front — a per-table count, a way to flag conflicts, and validation that stops typos before they become a wrong seat assignment. Most DIY versions skip all three and end up as a flat list someone has to proofread by eye. If you're still deciding whether a spreadsheet is the right starting point at all, our free template comparison covers where Sheets and Excel win over Canva or Word. Here's a version that actually catches its own mistakes, in both Excel and Google Sheets (the formulas are nearly identical in both).
Set up your columns
Start with one row per guest, not one row per table — this is the single most important structural decision, because it lets you sort, filter, and formula against individual guests while still grouping by table.
| Guest Name | Party | Table Number | Keep With | Keep Apart | Dietary | Confirmed |
|---|
- Guest Name: one guest per row, even for couples or families — don't combine "John & Jane Smith" into one cell, or your per-table counts and dietary tracking break.
- Party: a shared ID (household name or number) so you can filter and view a family unit together without merging rows.
- Table Number: the actual assignment — this is the column your formulas will check against.
- Keep With / Keep Apart: the name(s) of guests this person must sit with or must not share a table with. Free text is fine; you'll cross-check it manually or with a formula below.
- Dietary / Confirmed: useful for catering counts and for filtering out guests who haven't RSVP'd yet so you're not seating someone who might not come.
COUNTIF per table: catch overfilled tables automatically
Add a small summary block off to the side — a row per table number, with a live count of how many guests are currently assigned to it:
`` =COUNTIF(C:C, "1") ``
Replace C:C with your actual Table Number column range, and "1" with each table number in turn (or better, reference a cell so you can drag the formula down: =COUNTIF($C:$C, F2) where F2 holds the table number). Add a second column next to it with your table's actual capacity, and a third with a simple difference formula:
`` =G2-F2 ``
where G2 is capacity and F2 is the live count. Any negative number means that table is over capacity — format that column with conditional formatting (below) so it's visually obvious without reading numbers.
Data validation: stop typos before they become seating errors
Without validation, nothing stops someone from typing "Tabel 7" or "7 " with a trailing space into the Table Number column — and that guest silently vanishes from your COUNTIF totals because it doesn't match. Fix this at the source:
In Google Sheets: select the Table Number column, go to Data → Data validation, choose "List of items" or "List from a range" if you've listed valid table numbers somewhere else, and set it to reject invalid entries rather than just warn.
In Excel: select the column, go to Data → Data Validation, choose List under Allow, and either type your valid table numbers (1,2,3,4,5...) or point to a range containing them. Set the Error Alert to Stop, not Warning — a warning is easy to click through without reading.
Do the same for a Confirmed column if you're using yes/no/pending values, so a typo doesn't silently exclude someone from your headcount.
Conditional formatting: flag conflicts visually
You won't build a perfect automated conflict-checker in a spreadsheet without real effort, but you can get a useful visual flag with a formula-based conditional formatting rule.
For a simple version — highlighting any table that's over capacity — apply conditional formatting to your table-count summary column using the difference formula from above:
- Format cells where: custom formula is
=H2<0(where H2 is your capacity-minus-count column) - Formatting: red fill
For keep-apart conflicts, a lightweight approach: add a helper column that checks whether a guest's "Keep Apart" name shows up in the same Table Number group, using a formula like:
`` =COUNTIFS($C:$C, C2, $A:$A, D2) ``
(where C is Table Number, A is Guest Name, D is Keep Apart) — this returns a number greater than 0 if the person named in "Keep Apart" is seated at the same table number. Conditionally format that helper column red when it's greater than zero. It's not foolproof — it only catches exact name matches, so keep names spelled consistently — but it turns a silent mistake into something you'll actually see before you print anything.
Sorting and filtering without breaking your data
Once formulas are in place, be careful sorting the sheet — sorting a selected range instead of the whole row set is the most common way spreadsheet seating charts get corrupted, because a name ends up next to the wrong table number. Always select entire rows (click the row numbers, not just the data columns) before sorting, or better, convert your range to a proper Table (Excel: Insert → Table; Sheets: use a named range or filter view) so sort and filter operations keep rows intact automatically.
Exporting to CSV
When your chart is finalized, export a clean CSV for printing, sharing with a caterer, or importing into a dedicated tool:
Excel: File → Save As → CSV (Comma delimited). Google Sheets: File → Download → Comma-separated values (.csv).
Before exporting, strip out your helper/formula columns (the COUNTIF and conflict-check columns) if you're handing the file to someone else — they only need Guest Name, Table Number, and whatever operational columns (dietary, confirmed) matter to the recipient. Keep a separate working copy with your formulas intact for your own use.
That same clean CSV — Guest Name, Party, Table Number, Dietary — is also what pastes straight into Seatwise if you've been building your list in a spreadsheet and want the auto-seat, conflict-checking, and print-export features a spreadsheet can't do on its own: paste the columns in, and it picks up your existing table assignments rather than making you start over.
FAQ
Does this work the same in Excel and Google Sheets? Nearly identical — COUNTIF and COUNTIFS syntax is the same in both. The main difference is where the Data Validation and Conditional Formatting menus live, noted above.
Can I automate seat assignment with formulas alone? Not realistically — assigning seats based on multiple constraints (keep-together, keep-apart, table balance) at once is a fit problem, not a lookup, and spreadsheet formulas aren't built for that. Formulas are good at flagging conflicts after you've assigned seats manually, not generating the assignments themselves.
Why does my COUNTIF return 0 when I know the table has guests? Almost always a data-type or whitespace mismatch — a table number stored as text ("7 ") won't match a formula checking for the number 7. Data validation (above) prevents this going forward; for existing data, use =TRIM() and =VALUE() to clean the column first.
What's the fastest way to check all my keep-apart rules at once? Use the COUNTIFS helper column described above and filter the whole sheet to show only rows where that column is greater than zero — that gives you a single filtered view of every conflict instead of checking rules one at a time.
When to stop fighting the spreadsheet
This setup genuinely works up to roughly 100–150 guests with a manageable number of keep-apart rules. Past that, or once you're re-seating people more than a couple of times as RSVPs change, the manual formula maintenance starts costing more time than it saves — that's the point to paste your CSV into a tool built for it rather than keep patching formulas.