Why grouped reports need filling
Many reports print a label once and leave it blank for the rows that follow. A sales report shows “North” beside the first month, then leaves the Region cell empty until “South” begins. That is easy to read on paper, but it breaks almost everything you might do next in Excel. Sort the sheet and the blank rows lose their region. Filter for “North” and you see one row instead of twelve. A pivot table puts most of the amounts under “(blank)”, and a VLOOKUP or SUMIFS on Region misses every row without the label.
Filling the blanks with the value above turns the report into a proper list in which every row stands on its own.
How to fill blank cells manually in Excel
Go To Special and Ctrl+Enter
- Select the cells in the label columns, from the first data row to the last. Avoid selecting whole columns, or Excel fills all the way to the bottom of the sheet.
- On the Home tab, choose Find & Select › Go To Special, pick Blanks and click OK. You can also press F5 or Ctrl+G and click Special.
- With the blank cells still selected, type
=and press the Up arrow. The active cell now shows a formula such as=A2. - Press Ctrl+Enter. Every selected blank receives the same relative formula, so each one points at the cell directly above.
- Select the columns again, copy them and use Home › Paste › Paste Values to replace the formulas with their results.
Step 5 is easy to forget and it matters. Until you paste values, the cells are formulas: sorting the sheet makes them point at the wrong rows and changes your labels silently.
Caveats of Go To Special
- It only selects truly empty cells. A cell holding a space, or an empty string left by a formula, is not “blank” to Go To Special and is skipped.
- Merged cells get in the way. Reports often merge the label across a block. Unmerge them first with Home › Merge & Center › Unmerge Cells, then follow the steps above.
- Spacer rows get filled too. If the report has empty lines between sections, the method fills those with the label above as well.
Power Query
Load the table with Data › From Table/Range, select the label columns and choose Transform › Fill › Down, then Close & Load. Fill Down only fills null values. Empty text from a CSV is not null, so use Transform › Replace Values to replace nothing with null first.
Pivot tables
If the report is a pivot table you control, there is no need to fill anything. Choose PivotTable Design › Report Layout › Repeat All Item Labels and the labels appear on every row. Once a pivot has been pasted as values, you are back to the manual methods.
Where this tool helps
Accounting and ERP exports. Reports from Tally, SAP and similar systems often print the ledger, customer or cost centre once per block. Filling the blanks gives you a list you can sort, filter and total.
Pivot output pasted as values. Someone sends a summary that was once a pivot table. Filling the outer labels makes it usable again as data.
Before a lookup or a new pivot. Lookups and pivot tables need the key on every row. Filling first avoids results that quietly leave rows out.
Survey and form exports. Some tools write the respondent or section only on the first line of a group of answers.
How the tool behaves
- Group-label columns are picked for you. The file check looks at the first three columns for ones that are filled in the first row, are not mostly numbers and have blanks below a value in rows that otherwise hold data. It selects those and says “We picked the columns that look like group labels.”
- Empty rows act as a boundary with “Stop at empty rows” on, so the last label of one section is never copied into the next.
- Cells with only spaces count as blank by default.
- Values keep their type. A filled date is a real date and a filled number is still a number.
- Blank cells at the very top stay blank, since there is nothing above to copy.
The summary confirms what changed, for example “Filled 12 blank cells in Region and Representative with the value above.” A CSV comes back as CSV and an Excel file as .xlsx.
If your report uses merged cells, a title above the header or repeated header rows, run the Fix messy columns tool as well. Afterwards, Remove blank rows clears out any spacer lines between sections.