'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Autofill Dates with Blank Cells Between Each Date in Excel
Question details
The user needs to fill consecutive dates down a spreadsheet column while keeping a specific number of blank cells (e.g., four empty rows) inserted between each date entry.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a structured spreadsheet layout where date headers or entries must be separated by multiple blank rows to allow for manual data entry.
- Observed behavior
- A dynamic array formula or cell reference logic is applied to generate a column where valid date values appear sequentially, separated by exactly four empty cells.
Determine your exact starting cell and confirm the precise number of blank rows required between each date, as this spacing will dictate the formula parameters you use.
Use a Dynamic Array Formula (Microsoft 365)
Generate the sequence of dates and blank spaces automatically in a single step using the LET function.
If you are using Microsoft 365, you can use dynamic array functions to generate both the dates and the spaces simultaneously without manually dragging formulas.
Click on the cell (for example, A1) where you want the first date of your sequence to appear.
Type the following formula into the formula bar: =LET(dates,TEXT(SEQUENCE(3,1,"1/1/2024",1),"dd-mm-yyyy"),spaces,SEQUENCE(1,5,0,1),items,TOCOL(dates&"|"&spaces,1,FALSE),MAP(items,LAMBDA(x,IF(RIGHT(x,1)="0",TEXTBEFORE(x,"|"),TEXTAFTER(x,"|"))))). Customize the start date "1/1/2024" and spacing "SEQUENCE(1,5,0,1)" as needed.
Press the Enter key. The formula will automatically spill down the column, inserting the consecutive dates separated by the specified number of blank cells.
Use Simple Cell References
Manually set up the first sequential reference and copy it down if you are using an older version of Excel.
Use a Helper Formula Column
Create a helper column to conditionally populate the dates and then paste the final values.
Easily Manage Dates and Formulas with WPS Spreadsheet
WPS Office offers a powerful, user-friendly spreadsheet application that fully supports complex date formulas, custom autofill options, and seamless Excel file compatibility.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet or create a new blank workbook.
- 2. Enter your date formula: Use the simple cell reference method (=B2+1) or conditional IF statements to quickly generate your spaced dates.
- 3. Save in Excel format: Click 'Save As' and choose the standard .xlsx format to ensure your spacing and formulas remain compatible with any spreadsheet software.
Frequently Asked Questions
Can I change the number of blank cells between each date?
Yes. If using the manual cell reference method, simply place the =B2+1 formula in a different row (e.g., placing it in B6 instead of B7 leaves exactly three blank cells). If using the dynamic array formula, change the parameters within the SEQUENCE function.
Why isn't the dynamic array formula working in my spreadsheet?
The LET and SEQUENCE functions are only supported in Microsoft 365, Excel 2021, and newer versions of WPS Office. If you are using an older version (like Excel 2016 or 2019), the formula will return an error. You should use the simple cell reference method instead.
How do I remove the underlying formulas but keep the dates and blank cells?
Highlight the entire column containing your dates, copy it by pressing Ctrl+C, right-click the first cell of your target column, and select 'Paste as Values' (often represented by an icon showing '123'). This replaces all formulas with static data.
