How to Create a Randomized Seating Chart

 


Summary.

In this tutorial, we show you step by step how our Randomized Seating Chart was designed. This is a very beginner friendly tutorial and requires no prior knowledge of Google Sheets. 

This tutorial is broken down into 4 Parts.

  • Part 1 - Create the Borders and Color Palette
  • Part 2 - Create the List for Student Names 
  • Part 3 - Create the Desks
  • Part 4 - Create the Randomization Formula 

Before you start, please do the following. 

  1. Sign in or create a Google Account if you don't have one already.
  2. Open a new Google Sheets.


Part 1. Creating the Borders and Color Palette.

  1. You'll need Rows 1 - 39 & Columns A - T.
  2. Use these sizes for formatting.
    • Rows 1 & 39 : 25
    • Rows 2 & 38 : 30
    • Row 3 : 70
    • All other Rows: Fit to Size
    • Columns A & T : 25
    • Columns B, D, H, S : 30
    • Column C: 150
    • Columns E & G : 100
    • Column F : 15
    • Columns I & R : 50
    • Columns J - Q : 90
  3. These are the colors used for the border, headers, and desk.
    • #E3BBAC
    • #AAC4FF
    • #FEBE8C
    • #BCE29E
    • #B1B2FF

Part 2: Create the List for Students

  1. There are 3 Lists : All Students list, Seat Together list, & Do not Seat Together list.
Figure 1. Randomized Seating Chart Google Sheets


2. All Students list starts at cell C5 and ends at cell C37. Select C6:C37 and add a dotted border to the bottom of the cell.

Figure 2. All Students List

3. Seat Together List starts at Cell E5 and ends at Cell G20. Range E7:E20 is the left side of the pairing and range G7:G20 is the right side. Select E7:E20 & add a bottom border. Select G7:G20 & add a bottom border. 

Figure 3. Seat Together List


    
Figure 4. Seat Together List



Figure 5. Add Border

4. Do not Seat Together List starts at Cell E22 and ends at Cell G37. Range E22:E37 is the left side of the pairing and range G22:G37 is the right side. Select E22:E37 & add a bottom border. Select G22:G37 & add a bottom border. 


Part 3: Create the Desks

1. Merge M16:N17 & type "My Desk" into the cell.

Figure 6. Create My Desk

2. Student Desk start at cell J20 & end at P35. Four desks together make a table. So for Table 1, we have the following desks.

    • Desk #1. J20:J21.
    • Desk #2. K20:J21.
    • Desk #3. J22:J23.
    • Desk #4. K22:J23.

3. For Table 2, we have the following desks.

    • Desk #5. M20:M21.
    • Desk #6. N20:N21.
    • Desk #7. M22:M23.
    • Desk #8. N22:N23.

4.  For Table 3, we have the following desks.

    • Desk #9. P20:P21.
    • Desk #10. Q20:Q21.
    • Desk #11. P22:P23.
    • Desk #12. Q22:Q23.

5. For Table 4, we have the following desks.

    • Desk #13. J26:J27.
    • Desk #14. K26:K27.
    • Desk #15. J28:J29.
    • Desk #16. K28:K29.

6. For Table 5, we have the following desks.

    • Desk #17. M26:M27.
    • Desk #18. N26:N27.
    • Desk #19. M28:M29.
    • Desk #20. N28:N29.

7. For Table 6, we have the following desks.

    • Desk #21. P26:P27.
    • Desk #22. Q26:Q27.
    • Desk #23. P28:P29.
    • Desk #24. Q28:Q29.

8. For Table 7, we have the following desks.

    • Desk #25. K32:K33.
    • Desk #26. L32:L33.
    • Desk #27. K34:K35.
    • Desk #28. L34:L35.

9. For Table 8, we have the following desks.

    • Desk #29. O32:O33.
    • Desk #30. P32:P33.
    • Desk #31. O34:O35.
    • Desk #32. P34:P35.

Part 4: Create the Randomization Formulas

1. There are two pieces to getting the randomization to work. The first piece is the checkbox and the second piece is creating 32 formulas.

2. Merge cells L8 & L9. Insert a checkbox.

Figure 7. Insert Checkbox


3. Now it's time to create the Randomization Formulas. The logic behind these formulas is to randomly select a student from the All Students list, check if the student is in either Seat Together or Do Not Seat Together List. If the student is in Seat Together list, there will also be seated at the same table. If the student is in the Do Not Seat Together list, their pair will not be at the same table. 

At the same time, the formulas checks if the randomly selected student has already been selected. If the student has been seated already, the formulas will not reselect the student. 

Figure 8. One of the Randomization Formulas


4. For simplicity, Table 1 is shown below. The formulas for other tables are the same. The only change are the desk reference cells. 

    • Desk #1. Cell J21: =IF(L8=TRUE,IFERROR(INDEX(C6:C, RANDBETWEEN(1, COUNTA(C6:C))),)
    • Desk #2. Cell K21: =IFERROR(IF(J21=E7,G7, IF(J21=E8,G8, IF(J21=E9,G9, IF(J21=E10,G10, IF(J21=E11,G11, IF(J21=E12,G12, IF(J21=E13,G13, IF(J21=E14,G14, IF(J21=E15,G15, IF(J21=E16,G16, IF(J21=E17,G17, IF(J21=E18,G18, IF(J21=E19,G19, IF(J21=E20,G20, IF(J21=G7,E7, IF(J21=G8,E8, IF(J21=G9,E9, IF(J21=G10,E10, IF(J21=G11,E11, IF(J21=G12,E12, IF(J21=G13,E13, IF(J21=G14,E14, IF(J21=G15,E15, IF(J21=G16,E16, IF(J21=G17,E17, IF(J21=G18,E18, IF(J21=G19,E19, IF(J21=G20,E20,INDEX(FILTER(C6:C, C6:C <> J21), RANDBETWEEN(1, COUNTA(C6:C)-1))) ))))))))))))) )))))))))))))),)
    • Desk #3. Cell J23: =IF(L8=TRUE,IFERROR(IF(AND(OR(J21=E7,K21=E7),J21<>G7,K21<>G7),G7, IF(AND(OR(J21=E8,K21=E8),J21<>G8,K21<>G8),G8, IF(AND(OR(J21=E9,K21=E9),J21<>G9,K21<>G9),G9, IF(AND(OR(J21=E10,K21=E10),J21<>G10,K21<>G10),G10, IF(AND(OR(J21=E11,K21=E11),J21<>G11,K21<>G11),G11, IF(AND(OR(J21=E12,K21=E12),J21<>G12,K21<>G12),G12, IF(AND(OR(J21=E13,K21=E13),J21<>G13,K21<>G13),G13, IF(AND(OR(J21=E14,K21=E14),J21<>G14,K21<>G14),G14, IF(AND(OR(J21=E15,K21=E15),J21<>G15,K21<>G15),G15, IF(AND(OR(J21=E16,K21=E16),J21<>G16,K21<>G16),G16, IF(AND(OR(J21=E17,K21=E17),J21<>G17,K21<>G17),G17, IF(AND(OR(J21=E18,K21=E18),J21<>G18,K21<>G18),G18, IF(AND(OR(J21=E19,K21=E19),J21<>G19,K21<>G19),G19, IF(AND(OR(J21=E20,K21=E20),J21<>G20,K21<>G20),G20, IF(AND(OR(J21=G7,K21=G7),J21<>E7,K21<>E7),E7, IF(AND(OR(J21=G8,K21=G8),J21<>E8,K21<>E8),E8, IF(AND(OR(J21=G9,K21=G9),J21<>E9,K21<>E9),E9, IF(AND(OR(J21=G10,K21=G10),J21<>E10,K21<>E10),E10, IF(AND(OR(J21=G11,K21=G11),J21<>E11,K21<>E11),E11, IF(AND(OR(J21=G12,K21=G12),J21<>E12,K21<>E12),E12, IF(AND(OR(J21=G13,K21=G13),J21<>E13,K21<>E13),E13, IF(AND(OR(J21=G14,K21=G14),J21<>E14,K21<>E14),E14, IF(AND(OR(J21=G15,K21=G15),J21<>E15,K21<>E15),E15, IF(AND(OR(J21=G16,K21=G16),J21<>E16,K21<>E16),E16, IF(AND(OR(J21=G17,K21=G17),J21<>E17,K21<>E17),E17, IF(AND(OR(J21=G18,K21=G18),J21<>E18,K21<>E18),E18, IF(AND(OR(J21=G19,K21=G19),J21<>E19,K21<>E19),E19, IF(AND(OR(J21=G20,K21=G20),J21<>E20,K21<>E20),E20,INDEX(FILTER(C6:C, C6:C<>J21,C6:C<>K21), RANDBETWEEN(1, COUNTA(C6:C)-2))) ))))))))))))))))))))))))))),),)
    • Desk #4. Cell K23: =IF(L8=TRUE,IFERROR(IF(AND(OR(J21=E7,K21=E7,J23=E7),AND(J21<>G7,K21<>G7,J23<>G7)),G7, IF(AND(OR(J21=E8,K21=E8,J23=E8),AND(J21<>G8,K21<>G8,J23<>G8)),G8, IF(AND(OR(J21=E9,K21=E9,J23=E9),AND(J21<>G9,K21<>G9,J23<>G9)),G9, IF(AND(OR(J21=E10,K21=E10,J23=E10),AND(J21<>G10,K21<>G10,J23<>G10)),G10, IF(AND(OR(J21=E11,K21=E11,J23=E11),AND(J21<>G11,K21<>G11,J23<>G11)),G11, IF(AND(OR(J21=E12,K21=E12,J23=E12),AND(J21<>G12,K21<>G12,J23<>G12)),G12, IF(AND(OR(J21=E13,K21=E13,J23=E13),AND(J21<>G13,K21<>G13,J23<>G13)),G13, IF(AND(OR(J21=E14,K21=E14,J23=E14),AND(J21<>G14,K21<>G14,J23<>G14)),G14, IF(AND(OR(J21=E15,K21=E15,J23=E15),AND(J21<>G15,K21<>G15,J23<>G15)),G15, IF(AND(OR(J21=E16,K21=E16,J23=E16),AND(J21<>G16,K21<>G16,J23<>G16)),G16, IF(AND(OR(J21=E17,K21=E17,J23=E17),AND(J21<>G17,K21<>G17,J23<>G17)),G17, IF(AND(OR(J21=E18,K21=E18,J23=E18),AND(J21<>G18,K21<>G18,J23<>G18)),G18, IF(AND(OR(J21=E19,K21=E19,J23=E19),AND(J21<>G19,K21<>G19,J23<>G19)),G19, IF(AND(OR(J21=E20,K21=E20,J23=E20),AND(J21<>G20,K21<>G20,J23<>G20)),G20,IF(AND(OR(J21=G7,K21=G7,J23=G7),AND(J21<>E7,K21<>E7,J23<>E7)),E7, IF(AND(OR(J21=G8,K21=G8,J23=G8),AND(J21<>E8,K21<>E8,J23<>E8)),E8, IF(AND(OR(J21=G9,K21=G9,J23=G9),AND(J21<>E9,K21<>E9,J23<>E9)),E9, IF(AND(OR(J21=G10,K21=G10,J23=G10),AND(J21<>E10,K21<>E10,J23<>E10)),E10, IF(AND(OR(J21=G11,K21=G11,J23=G11),AND(J21<>E11,K21<>E11,J23<>E11)),E11, IF(AND(OR(J21=G12,K21=G12,J23=G12),AND(J21<>E12,K21<>E12,J23<>E12)),E12, IF(AND(OR(J21=G13,K21=G13,J23=G13),AND(J21<>E13,K21<>E13,J23<>E13)),E13, IF(AND(OR(J21=G14,K21=G14,J23=G14),AND(J21<>E14,K21<>E14,J23<>E14)),E14, IF(AND(OR(J21=G15,K21=G15,J23=G15),AND(J21<>E15,K21<>E15,J23<>E15)),E15, IF(AND(OR(J21=G16,K21=G16,J23=G16),AND(J21<>E16,K21<>E16,J23<>E16)),E16, IF(AND(OR(J21=G17,K21=G17,J23=G17),AND(J21<>E17,K21<>E17,J23<>E17)),E17, IF(AND(OR(J21=G18,K21=G18,J23=G18),AND(J21<>E18,K21<>E18,J23<>E18)),E18, IF(AND(OR(J21=G19,K21=G19,J23=G19),AND(J21<>E19,K21<>E19,J23<>E19)),E19, IF(AND(OR(J21=G20,K21=G20,J23=G20),AND(J21<>E20,K21<>E20,J23<>E20)),E20,INDEX(FILTER(C6:C, C6:C<>J21,C6:C<>K21,C6:C<>J23), RANDBETWEEN(1, COUNTA(C6:C)-3))) ))))))))))))))))))))))))))),),)


Comments

Post a Comment