Function Problems

How to Append Sequential Letters to Excel Data Every Five Rows

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a method to append sequential alphabetical letters (A, B, C, etc.) to existing Excel cell values in blocks of five rows, starting immediately after the header row.

Product: Excel
Device & OS: not provided
Scenario: Categorizing or structuring a continuous dataset into uniform blocks of five rows by appending a sequentially changing letter to the cell contents.
Observed behavior: The user wants to assign 'A' to the first five data rows, 'B' to the next five, and continue this pattern dynamically without manually typing the letters.

Before you start

Verify that your data does not contain merged cells in the target column and that the dataset begins exactly on row 2, just below the header row.

Solution 1Recommended

Use an Excel Formula with Dynamic Arrays

Quickly generate and append sequential letters in adjacent cells using modern Excel dynamic array functions or standard math-based row calculations.

Formulas are the safest way to manipulate strings because they leave the original data intact. You can use dynamic array functions to spill the results, or use a mathematical formula to calculate the letter based on the current row number.

Select the Output Cell

Select a blank cell adjacent to your first data row, such as cell B2.

Enter the Formula

To generate the requested values using a dynamic array, enter the following formula: `=A2&DROP(REDUCE("",{65,66},LAMBDA(s,c,VSTACK(s,IF(SEQUENCE(5),CHAR(c))))),1)`. Alternatively, for older versions, use `=A2&CHAR(65+INT((ROW()-2)/5))`.

Apply to All Rows

Press Enter to apply the formula. If you are not using a dynamic spilling array, click and drag the fill handle down to apply it to all necessary rows in your dataset.

Solution 2

Apply a VBA Macro to Append Letters In-Place

Use a VBA script to directly modify the existing cell values by appending the calculated sequential letters.

Efficient Data Management with WPS Spreadsheet

Easily Categorize and Modify Data with WPS Office

WPS Spreadsheet fully supports advanced formulas, dynamic arrays, and VBA/macros, allowing you to manipulate strings and append sequential characters with ease.

1. Open your File: Launch WPS Spreadsheet and open the dataset you wish to modify.
2. Apply the Formula: Select the adjacent column and enter the formula `=A2&CHAR(65+INT((ROW()-2)/5))` to calculate the correct block letters.
3. Fill the Column: Double-click the fill handle in the bottom-right corner of the cell to automatically apply the formula to the rest of your data rows.

100% compatible with Microsoft Excel file formats (.xlsx, .xlsm)Full support for advanced string functions like CHAR, ROW, and dynamic arraysIntegrated VBA environment for running custom data manipulation macrosLightweight application with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

How can I change the formula if my data starts on row 5 instead of row 2?

Adjust the row offset in the formula or macro to match your starting row. For example, if using the mathematical row calculation, change `(ROW()-2)` or `(r-2)` to `(ROW()-5)` or `(r-5)`.

Will the appended letters update automatically if I add new rows?

If you use an Excel formula and copy it down (or if your data is formatted as an Excel Table), the appended letters will update automatically. However, VBA macro results are static and the script must be run again to process new rows.

Can I append lowercase letters instead of uppercase?

Yes. In the formula or macro, change the starting ASCII value from 65 (which represents an uppercase 'A') to 97 (which represents a lowercase 'a'). The sequence will then output a, b, c, etc.

Why does the macro show an error when appending letters to empty cells?

This usually happens if the loop exceeds the actual data range or processes entirely blank rows. Ensure your VBA loop specifically checks if the cell is non-empty (`If Range("A" & r).Value <> "" Then`) before appending the characters.

Excel & Data Problems