ysskrishna logoysskrishna

Chapter 2 of 6 · 4 min

Merge every N cells

Merge a selection into fixed-size blocks along rows or columns, and decide what happens to the leftover cells at the end.

Every N merges the selection into blocks of a fixed size. It is the tool for regular layouts: a schedule with three slots per day, a sign-up sheet with five lines per team, a printed form with pairs of rows.

Options

From the sidebar:

  • DIRECTION: e.g. "Vertical - merge down rows", or horizontal across columns.
  • GROUP SIZE: a number, shown as "3 rows per group".
  • ALIGNMENT: Horizontal (e.g. Center) and Vertical (e.g. Middle).
  • ADVANCED OPTIONS: a toggle Merge last partial group.
  • Button: Apply Merge.

How blocks are formed

With a vertical direction and group size 3 on A5:A14 (10 rows):

RowsResult
5-7one merged block
8-10one merged block
11-13one merged block
14leftover, partial group

With Merge last partial group on, the leftover row(s) become their own (smaller) block. With it off, they stay as normal cells. The examples workbook has an EveryN_Leftover tab for exactly this case.

Each block keeps a value the way Sheets merges do: the first cell's. Put labels in the first cell of each block before merging.

Multiple columns

If the selection spans several columns, each column is merged in blocks independently with the same group size (A5:C14 → three columns of blocks).

Horizontal merges

Switch DIRECTION to horizontal to merge across columns, e.g. every 2 columns in a header row so each label spans a pair of columns (EveryN_Horizontal tab in the workbook).

Step by step: a three-slot schedule

  1. In A2:A10 type days in every third row: Mon in A2, Tue in A5, Wed in A8. In B2:B10 type times: 09:00, 11:00, 14:00 repeated.
  2. Select A2:A10. Open Every N.
  3. DIRECTION vertical, GROUP SIZE 3, ALIGNMENT Center / Middle.
  4. Click Apply Merge. Each day now spans its three time slots.
  5. Try A2:A11 (ten rows) and toggle Merge last partial group to see the difference on row 11.

When not to use Every N

  • When block sizes vary with the data. Use By Value (chapter 3), which follows the values instead of a fixed count.
  • On data you will sort.

Practice: a shift rota

Start with this data in A1:B10 (header plus nine rows), where every three rows are one shift:

ShiftPerson
Mon AMAsha
Mon AMRavi
Mon AMMeera
Mon PMJohn
Mon PMLi
Mon PMSara
Tue AMDev
Tue AMKiran
Tue AMOmar
  1. Select A2:A10 (the Shift column without its header).
  2. Open the Every N tab. Set DIRECTION to vertical, GROUP SIZE to 3.
  3. Set Vertical alignment to Middle, then click Apply Merge.

Expected result: three merged cells, A2:A4 ("Mon AM"), A5:A7 ("Mon PM") and A8:A10 ("Tue AM"). The Person column is untouched.

Now try the edge case: add a tenth person in row 11 under a new shift "Tue PM" and select A2:A11. With Merge last partial group off, row 11 stays as a single cell; with it on, the leftover single row is still a group of one, so the visible result is the same here. The toggle matters when the leftover group is two or more rows, for example a GROUP SIZE of 4 on ten rows leaves a partial group of 2.

Every N or By Value?

In the rota above both tools give the same answer because each shift has exactly three people. The difference shows when the data is uneven: if Mon PM had only two people, Every N would still merge rows 5-7 and swallow the first Tue AM row into the Mon PM block. By Value looks at the content and would merge exactly the rows that say "Mon PM". Use Every N only when the block size is a rule of the layout (time slots, fixed-size forms), not a property of the data.

Common mistakes

  • Including the header row in the selection, which shifts every block by one row.
  • Selecting several columns when only the label column should merge; every selected column is merged in the same blocks.
  • Forgetting that each merged block keeps only its top-left value. With Every N that is fine when the rows in a block repeat the same label, and it silently discards data when they do not.

Quick check

  1. 10 rows, group size 4, partial group on. How many blocks?Three: 4 + 4 + 2.

  2. Where should the label go in each block before merging?In the first cell of the block.

  3. Team sizes differ from 3 to 7 rows. Every N or By Value?By Value.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min