Cells and Rows
Row/column operation toolkit — conditional row insertion, empty row deletion, cell merging, string joining, multilevel numbering.
Insert Rows
DVD Addin → group Cells and Rows → split-button Insert Rows.
Sub-menu
| Command | Description |
|---|---|
| Insert blank rows above | Insert N empty rows above the selected row. |
| Insert blank rows below | Insert N empty rows below. |
| Insert at every Nth row | Insert one empty row every N rows (e.g. every 5th row). |
| Insert when value changes | Detect when the selected column value changes → insert a separator row. |
| Insert N header rows per group | Insert empty header at the start of each group. |
Example "Insert when value changes"
Before:
| No. | Region | Item |
|---|---|---|
| 1 | Hanoi | Foundation |
| 2 | Hanoi | Column |
| 3 | HCMC | Foundation |
| 4 | HCMC | Column |
Select column B → "Insert when value changes" → result:
| No. | Region | Item |
|---|---|---|
| 1 | Hanoi | Foundation |
| 2 | Hanoi | Column |
| (blank) | ||
| 3 | HCMC | Foundation |
| 4 | HCMC | Column |
→ Easier to read + easier to print region-grouped reports.
Delete Empty Rows
DVD Addin → group Cells and Rows → split-button Delete Empty Rows.
Sub-menu
| Command | Description |
|---|---|
| Delete fully empty rows | Delete rows where ALL cells are empty. |
| Delete rows with empty key column | Pick a column → delete rows where that column is empty. |
| Delete duplicates (keep first) | Detect duplicates by chosen column → keep first row, delete subsequent. |
| Delete duplicates (keep last) | Same, keep last. |
| Delete by condition | Dialog: enter expression (e.g. column E < 100) → delete matching rows. |
Example "Delete duplicates keep first"
Before:
| Code | Name | Price |
|---|---|---|
| A001 | D16 rebar | 30000 |
| A001 | D16 rebar | 32000 |
| A002 | D20 rebar | 40000 |
| A002 | D20 rebar | 41000 |
Select Code column → "Delete duplicates keep first" → result:
| Code | Name | Price |
|---|---|---|
| A001 | D16 rebar | 30000 |
| A002 | D20 rebar | 40000 |
Merge Cells
DVD Addin → group Cells and Rows → split-button Merge Cells.
Sub-menu
| Command | Description | Free / Premium |
|---|---|---|
| Merge keep data | Merge cells but keep ALL data (concatenate). | Free |
| Merge identical cells | Detect contiguous cells with same value → merge. | Free |
| Merge rows by key | Merge rows sharing a key column value. | Premium |
| Auto Group | Auto group rows by Outline level. | Premium |
| AutoFit merged cells | Auto-fit row height for merged cells. | Free |
| Select visible | Filter to select visible cells only (post-AutoFilter). | Free |
| Copy/Paste visible | Copy + Paste with visible cells only. | Premium |
Example "Merge identical cells"
Before:
| Region | Item |
|---|---|
| Hanoi | Foundation |
| Hanoi | Column |
| Hanoi | Floor |
| HCMC | Foundation |
| HCMC | Column |
Select "Region" column → "Merge identical cells" → consecutive "Hanoi" cells merge into one large cell, same for "HCMC".
Join Text
DVD Addin → group Cells and Rows → click Join Text.
Similar to =TEXTJOIN() but:
- User-friendly dialog (pick separator from dropdown).
- Applied in-place (result replaces the selected cell).
- Supports multi-line cells.
Dialog:
- Source range — cells to join.
- Separator —
,,;,-,\n, custom. - Skip empty cells — tick to skip empty.
- Output cell — cell where the result is written.
Join Merged
DVD Addin → group Cells and Rows → click Join Merged.
Specifically for merged cells: join values across consecutive merged cells, with separator between merge groups.
Useful when: a table has merged cells grouping by Region, each group has multiple items → you want one summary cell listing all.
Multilevel Numbering
DVD Addin → group Cells and Rows → split-button Multilevel.
→ See Numbering → Multilevel.
License gating
Most "Cells and Rows" commands are free. Premium commands: Merge by key, Auto Group, Copy/Paste visible.
→ See All commands — license matrix.
Related
- Numbering — number sequence after deleting empty rows.
- Sheet List — manage sheets alongside cell ops.