ysskrishna logoysskrishna

Chapter 3 of 6 · 4 min

Merge repeated values, or a value with the blanks after it

By Value merges follow your data: runs of the same value, or a value plus the empty cells below it. Plus the two Group by options for multi-column ranges.

By Value is the mode for real data, where group sizes vary. The sidebar describes it as: "Merge repeated values along a column or row, or merge each value with the empty cells below or to the right."

Options

  • DIRECTION: e.g. "Vertical - merge down columns".
  • MODE: Merge repeated values, or Merge value with empty cells.
  • ALIGNMENT: horizontal and vertical.
  • ADVANCED OPTIONS → Group by: Each column on its own or All columns follow the first.
  • Button: Apply Merge.

Mode 1: merge repeated values

Consecutive cells with the same value become one merged cell.

Before (sample)After
CoreCore (spans 3 rows)
Core
Core
WebWeb (spans 2 rows)
Web
MobileMobile

Only consecutive repeats merge. Sort the data by that column first if the same value appears in separate places. Because the merged cells all held the same value, nothing is lost.

Mode 2: merge value with empty cells

A value extends over the blank cells after it, until the next non-empty cell. This fits exports that already "look merged" by leaving repeats blank:

BeforeAfter
CoreCore (spans 3 rows)
(blank)
(blank)
WebWeb (spans 2 rows)
(blank)

The examples workbook's ByValue_Blanks tab shows this layout.

Group by: multi-column ranges

When the selection has several columns (say Team in A, Project in B):

OptionBehaviour (from the listing)Use when
Each column on its ownEvery column decides its merge pattern independentlyColumns are unrelated
All columns follow the firstThe first column sets the pattern; the others mirror it exactlyColumns describe the same record, like Team and Team lead

"Follow the first" prevents a confusing result where Project merges across two teams just because the value repeats at the boundary.

Step by step: a team roster

  1. Columns: A Team, B Lead, C Member (sample data). Sort by Team.
  2. Select A2:B20 (Team and Lead only, not Member).
  3. By Value, DIRECTION vertical, MODE Merge repeated values, Group by All columns follow the first.
  4. Apply Merge. Each team and its lead span the team's member rows.

Practice: repeated values versus blanks

Two columns that look similar on screen need different modes:

RowColumn A (repeated)Column C (blanks)
2FinanceFinance
3Finance
4Finance
5SalesSales
6Sales
  1. Select A2:A6, open By Value, choose MODE Merge repeated values, click Apply Merge. Expected: A2:A4 becomes one "Finance" cell, A5:A6 one "Sales" cell.
  2. Select C2:C6 and choose the mode that merges each value with the empty cells below it. Expected: the same two blocks, because each label is followed by blanks.

If you run "Merge repeated values" on column C instead, nothing useful happens: no two neighbouring cells hold the same value, so there is nothing to merge. That is the quickest way to tell which mode a column needs: repeated text means the first mode, a label followed by gaps means the second.

Watch for look-alike values

"Finance" and "Finance " (with a trailing space) are different values, so a repeated-values merge stops at the row with the space and you get two Finance blocks. The same happens with "finance" versus "Finance". Clean the column first: the Text Power Tools add-on's Trim both and case options, or a helper column with =TRIM(A2), fix this in seconds. See Trim and clean.

Common mistakes

  • Merging an unsorted column. Repeated values only merge when they are next to each other, so sort by the column first.
  • Using "Each column on its own" for columns that describe the same record (team and team lead). The lead column would then merge on its own pattern, and if two teams share a lead the blocks no longer line up with the team blocks.

Quick check

  1. "Core" appears in rows 2-4 and again in row 9. What merges?Rows 2-4 only. Sort first to bring repeats together.

  2. Your export leaves repeated values blank. Which mode?Merge value with empty cells.

  3. When do you pick "All columns follow the first"?When the other columns belong to the same record as the first column.

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