# How to return the category from a merged cell above in Excel

Repeat the last category until a new one appears: `=IF(A2="",C1,A2)`, filled down.

**Excel:** `=IF(A2="",C1,A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Category | Item | Category (filled) |
| Fruit | Apples |  |
|  | Bananas |  |
|  | Pears |  |
| Dairy | Milk |  |
|  | Yogurt |  |
| Bakery | Bagels |  |
|  | Sourdough |  |
|  | Muffins |  |
|  | Croissants |  |

Formula in C2, filled to C10: `=IF(A2="",C1,A2)`

Result: Fruit; Fruit; Fruit; Dairy; Dairy; Bakery; Bakery; Bakery; Bakery

## How it works

When a merged cell is unmerged or read by a formula, only its top cell holds the value; the rest are empty. The formula takes the category from column A when there is one and otherwise repeats the result from the row above (`C1` for row 2), so each item carries its group's category down.

## Other ways

- `=XLOOKUP(TRUE,A$2:A2<>"",A$2:A2,,0,-1)`: Looks back up column A for the nearest filled cell, without referring to the result column. Useful inside other formulas.

## Common mistakes

- Sorting or filtering merged ranges fails. Unmerge the cells and fill the category into every row first.
- If the first data row has no category, the formula repeats the header. Make sure each group starts with its name.

## Questions

**How do I fill the blanks with the value above without a formula?**

Select the column, use Go To Special > Blanks, type `=` and the cell above, and press Ctrl+Enter. Then paste values.

**How do I sum by category afterwards?**

Use the filled column in SUMIF: `=SUMIF(C2:C10,"Bakery",amounts)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/category-from-merged-cell-above