ysskrishna logoysskrishna

Chapter 6 of 6 · 4 min

Recipe: from raw export to readable report, and back

Keep one data tab and one report tab. Build the report with By Value, Every N and Concat; recover data with Unmerge + fill.

This recipe ties the course together with a two-tab pattern that never loses data.

Starting point (sample export)

RegionTeamMemberEmail
SouthCoreAshaasha@acme.io
SouthCoreRajraj@acme.io
SouthWebLili@acme.io
NorthWebMariamaria@acme.io

Step 1: two tabs

  1. Keep the export on a tab called data. Never merge here.
  2. Duplicate it as report. All merging happens on report.

Step 2: sort

On report, sort by Region, then Team (Data → Sort range → Advanced range sorting options). By Value only merges consecutive repeats.

Step 3: merge the grouping columns

  1. Select the Region column (A2:A5). By Value, vertical, Merge repeated values, Apply Merge. "South" now spans three rows.
  2. Team is nested inside Region, so merge it one region at a time: select B2:B4 (South's teams), By Value, Merge repeated values, Group by Each column on its own, Apply Merge. "Core" spans two rows; "Web" stays one.
  3. Repeat for North (B5). Selecting per region matters: if you selected B2:B5 at once, South's "Web" (row 4) and North's "Web" (row 5) are consecutive and would merge across the region boundary.

"All columns follow the first" is not right here: it would make Team copy Region's three-row block and hide the Core/Web split. Use it for columns that belong to the same record, like Team and Team lead (chapter 3).

Step 4: a contact cell per team (optional)

To show each team's emails in one cell instead of one per row, add a column that lists the team's emails, select the email cells of one team, and use Concat with a semicolon separator and Skip empty cells, so the cell can be pasted into an email "To" field. Repeat per team.

Step 5: format and share

Center/middle alignment on merged cells, freeze the header row, and share or print report.

Step 6: when someone needs the data

Point them to data. If only report exists (someone deleted data), duplicate report, then Unmerge with Copy merged value to every cell on. Concatenated cells stay joined; split them with Data → Split text to columns using the same separator.

Checklist

  • data tab: unmerged, sortable, the source of truth
  • report tab: sorted before By Value
  • Group by "All columns follow the first" for related columns
  • Concat only where people need joined values
  • Recovery path tested: Unmerge + fill on a copy

Why this order of steps

Each step protects the next one:

StepWhat breaks if you skip or reorder it
Two tabs (data + report)Merges in the data tab break lookups, pivots and future sorting
Sort before mergingSheets will not sort ranges containing merged cells, so you cannot fix the order later without unmerging
Merge Region, then Team per regionMerging Team across the whole column joins teams from neighbouring regions
Format lastBorders and alignment applied before a merge are partly lost on the merged cells

Keeping the report fresh

Most reports are rebuilt every week or month. Two ways to keep it cheap:

  1. Rebuild from scratch. Paste the new export into the data tab, copy it to a fresh report tab, sort, merge, format. With the add-on this is a few minutes, and nothing stale survives.
  2. Formula-fed report. Point the report tab at the data with a formula such as =SORT(Data!A2:D). Formulas spill into cells and cannot be merged over, so this route only works if you drop the merges and use formatting (borders and bold first-of-group labels) instead.

For a shared, read-only report, option 1 gives the cleanest result. For a live dashboard, option 2 is better, and Merge Tools is the wrong tool there.

Hand-off note

Add a short note at the top of the report tab: where the data comes from, when it was last rebuilt, and "Do not sort this tab; use the Data tab". Readers who try to filter a merged report otherwise get an error and assume the file is broken.

Quick check

  1. Why sort before By Value?Only consecutive repeats merge.

  2. Which tab is never merged?The data tab.

  3. How do you split a concatenated cell back into columns?Data → Split text to columns with the same separator.

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