Spreadsheets

Fill down missing dates in statement rows where the date is blank

Fill blank dates in statement rows with the date above using Go To Special, a helper formula or Power Query Fill Down, and avoid the traps that wreck data.

Editorial illustration for Fill down missing dates in statement rows where the date is blank

Select the date column's data range, press F5, choose Special, then Blanks, type an equals sign, press the Up arrow, and finish with Ctrl+Enter. Every blank date now points to the date above it. Convert the result to values and you have a complete date column.

Why are dates blank in the first place?

Some statements print the date once and leave it blank for the other transactions of that day, the way a printed ledger would. That is fine on paper, but a spreadsheet needs a date on every row, or filters by month, pivots and SUMIFS will skip them. Before you fix anything, scan the file and make sure the blanks sit under a date, not in the middle of nowhere.

How does the Go To Special method work?

  1. Copy the sheet to a backup tab.
  2. Select the date cells only, from the first data row to the last, say A2:A300.
  3. Press F5, click Special, tick Blanks and press OK. The empty cells are highlighted, with the first one active.
  4. Type =, press the Up arrow once so the formula reads something like =A5, and press Ctrl+Enter, not plain Enter.
  5. Ctrl+Enter fills the same relative formula into every highlighted cell, each pointing to the cell above it.
  6. Select the whole date column, copy, and use Paste Special, Values, to turn formulas into real dates.

Because each filled cell points to the one above it, a run of three blanks under 14 Jan all become 14 Jan.

Is there a formula alternative?

Yes, and it leaves the raw column alone. Put the cleaned date in column G. In G2 enter =A2, and in G3 enter =IF(A3="",G2,A3), then fill down. When the original cell is blank, it repeats the date above in the new column, otherwise it takes the new date. The raw dates stay untouched next to the cleaned ones, handy for auditing.

Whichever method you choose, fill the dates before sorting. Sorting a list with blank dates throws the continuation rows to the bottom, away from the day they belong to, and you cannot tell afterwards which rows were attached to which date.

A quick worked example with invented rows: say A2 holds 14-Jan-2025, A3 and A4 are blank, A5 holds 15-Jan-2025 and A6 is blank. After filling, A3 and A4 read 14-Jan-2025 and A6 reads 15-Jan-2025. That is five transactions on two days, each with its own date, and a monthly SUMIFS will now count all of them.

What does Power Query's Fill Down do?

In the editor, select the Date column and choose Transform, Fill, Down. Every null takes the value of the cell above. One catch: the cells must be null. Empty cells in an Excel sheet load as null, but an empty cell in a CSV can load as an empty string, which Fill Down ignores. If nothing changes, first use Transform, Replace Values, replace an empty value with null, then fill. This route repeats automatically each month, so it is the right choice for a recurring file.

What if the filled dates look like numbers?

Set the column's format to Short Date from the Home tab. If the entries are left-aligned and will not reformat, they are text; fix that first with Data, Text to Columns, choosing Date with the right order, DMY for Indian files, and Finish. Always spot-check a day above 12, because 03/04 can be read as 3 April or 4 March depending on how the file was opened.

Quick checks

  • Count the blank dates before and after: after filling, none should remain.
  • Sort a copy by date and confirm days run in the expected order.
  • Add up debits and credits and compare them with the statement summary.
  • Keep the original file untouched.

Should the routine become a chore, SpendixAI's statement import is built around PDF, CSV or Excel uploads, with a review of the rows before saving.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports