'%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 a Repeating Text and Number Sequence in Excel
Question details
The user needs an Excel formula to generate a repeating sequence of alphanumeric text that increments a number every three rows while cycling through specific suffixes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a custom repeating sequence of text and numbers in a spreadsheet.
- Observed behavior
- The goal is to automatically calculate and generate a custom sequence (e.g., A1, A1D, A1X, A2, A2D, A2X) without typing each value manually.
Determine the exact starting cell for your sequence, as the formula relies on absolute and relative row references to calculate the increments correctly.
Use a Combined Formula with QUOTIENT, ROW, and INDEX
This formula mathematically calculates the row difference to increment the sequence number and cycle through an array of text suffixes.
The formula combines text strings with mathematical functions. The QUOTIENT function handles the number incrementing every three rows, while the INDEX and MOD functions cycle through the specific text suffixes.
Click on the cell where you want your sequence to begin (for example, E7).
Type or paste the following formula into the formula bar: ="A""IENT(ROW(E7)-ROW($E$7),3)+1&INDEX({"","D","X"},MOD(ROW(E7)-ROW($E$7),3)+1)
Press the Enter key. The cell should now display the first item of your sequence (e.g., A1).
Click the cell again, hover over the small square at the bottom-right corner (the fill handle) until your cursor becomes a cross, and drag it downwards to fill the remaining rows.
Generate Advanced Sequences in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, INDEX, and MOD functions, making it incredibly easy to manage complex text and number sequences.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook.
- 2. Paste the formula: Select your starting cell and paste the sequence formula into the formula bar.
- 3. Apply and autofill: Press Enter, then click and drag the fill handle down to populate your sequence.
Frequently Asked Questions
How do I change the sequence prefix from 'A' to something else?
In the formula, locate the "A" at the very beginning and replace it with your desired text. For example, change it to "Item-" or "B" depending on your needs.
Can I adjust the formula to increment the number every 4 rows instead of 3?
Yes. Change the divisor number in both the QUOTIENT and MOD functions from 3 to 4. You must also add a fourth text suffix to your INDEX array, such as {"","D","X","Y"}.
Why does my sequence show an error when I move the starting cell?
The formula relies on a fixed starting point using absolute references (like $E$7). If you move the formula to a different starting cell, you must update those references in the formula to match the new location perfectly.
Are these sequence formulas supported in WPS Spreadsheet?
Absolutely. WPS Spreadsheet is fully compatible with the ROW, QUOTIENT, MOD, and INDEX functions, meaning this formula will execute identically to how it does in Microsoft Excel.
