Function Problems

How to Create Excel Lists by the First Letter of Names

Maira MehtabMaira Mehtab Nov 10, 2026 868 views

Question details

The user wants to distribute a master list of names into multiple separate worksheets, grouping them alphabetically by the first letter of each name (e.g., A-D on one sheet, E-J on another).

How to Create Excel Lists by the First Letter of Names
Product
Spreadsheets
Device & OS
not provided
Scenario
Organizing a large dataset, such as an employee directory or student roster, by alphabetical categories across different tabs for easier navigation and readability.
Observed behavior
Using dynamic array formulas to automatically extract, filter, and display names beginning with specific letters without manually copying and pasting the data.
Before you start

Ensure your master data is located in a known sheet and range (e.g., Sheet1 from cell A2 to A9999), and verify that your version of Excel or WPS Office supports dynamic array functions like FILTER and LET.

Solution 1Recommended

Use the LET and FILTER Functions for a Range of Letters

This solution is recommended for extracting names that fall within a continuous alphabetical sequence, such as A through D.

Dynamic array formulas provide a seamless way to filter data across worksheets. By combining the LET function (to define variables) and the FILTER function, you can automatically extract records based on criteria without complex macros.

1
Navigate to the destination worksheet

Click on the worksheet tab where you want the filtered list to appear (e.g., Sheet2 for names starting with A to D).

2
Enter the dynamic formula

Select the starting cell (e.g., A2) and type the following formula: =LET(names,Sheet1!A2:A9999,first,LEFT(names),FILTER(names,(first>="A")*(first<="D"),"No names found")).

3
Adjust the cell ranges

Change 'Sheet1!A2:A9999' to match the exact sheet name and cell range of your original master list. Modify "A" and "D" to fit the alphabetical range you want for this specific sheet.

4
Press Enter to execute

Press Enter. The formula will automatically 'spill' down the column, instantly displaying all names from the master list that begin with the specified letters.

Use the LET and FILTER Functions for a Range of Letters
How this works: The LEFT function extracts the very first letter of each name, and the mathematical operators (>="A" and <="D") test if the letter falls alphabetically within the specified range. The multiplication asterisk (*) acts as an AND logic operator.

Easily Filter and Organize Data in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas, including FILTER, LET, and UNIQUE. You can use identical formulas from Excel to instantly categorize and divide your master lists alphabetically across multiple tabs without missing a beat.

  1. 1. Open your master list in WPS: Launch WPS Spreadsheet and open the document containing your complete list of names.
  2. 2. Create a new worksheet tab: Click the '+' icon at the bottom to add a new sheet for your alphabetical category.
  3. 3. Apply the dynamic formula: Type the exact =LET(...) and =FILTER(...) formulas into the top cell and press Enter. The filtered data will populate immediately.
Fully compatible with Microsoft Excel's dynamic array functions.Lightweight, fast execution even when processing thousands of rows.Instant 'spill' behavior automatically updates lists when source data changes.Familiar user interface requires no learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #NAME? error when using the LET or FILTER function?

This error occurs if your version of the software does not support dynamic array functions. You need Office 365, Excel 2021, or the latest version of WPS Office to successfully use the LET and FILTER formulas.

Can I sort the filtered results alphabetically within the new destination sheet?

Yes, you can easily alphabetize the extracted list by wrapping the entire formula in a SORT function. For example: =SORT(LET(names,Sheet1!A2:A9999,first,LEFT(names),FILTER(names,(first>="A")*(first<="D"),"No names found"))).

How do I handle accidental leading spaces in my source data that break the formula?

If some names have invisible spaces at the beginning, the LEFT function will capture a blank space instead of the actual first letter. To fix this, nest the TRIM function inside to clean the text: change LEFT(names) to LEFT(TRIM(names)) in your formula.

Will the destination sheets update automatically if I add new names to the master sheet?

Yes, as long as the newly added names fall within the row range defined in your formula (e.g., A2:A9999). To make the range infinitely flexible without setting a row limit, consider formatting your master list as an Official Table (Ctrl+T) and referencing the Table Column instead.