Data Import & Export

Fix Excel Sort Shows A to Z Instead of Sort by Date for Imported Data

WPS Editor Oct 1, 2026 869 views

Question details

The user is unable to sort imported data chronologically because Excel presents alphabetical sort options (A to Z) instead of date-specific sort options.

How to Fix Excel Sort Showing A to Z Instead of by Date

Product: Excel
Device & OS: not provided
Scenario: Sorting imported transaction dates to organize data from oldest to newest.
Observed behavior: Excel displays 'Sort A to Z' and 'Sort Z to A' instead of 'Sort Oldest to Newest', typically because the imported dates are being stored and recognized as text rather than valid date values.

Before you start

Before troubleshooting, widen the column containing your dates to check their alignment; by default, spreadsheet software aligns plain text to the left and valid dates (which are numbers) to the right.

Solution 1Recommended

Convert Text to Valid Dates Using Text to Columns

Use the Text to Columns feature to force Excel to parse and recognize the imported text values as actual serial dates.

When data is imported from external sources or CSV files, dates are frequently formatted as text strings. The Text to Columns wizard can quickly convert an entire column of text into proper dates.

Select the target column

Click the column header letter to select the entire column containing the problematic dates.

Open Text to Columns

Navigate to the Data tab on the ribbon and click on the 'Text to Columns' button.

Configure wizard settings

Choose 'Delimited' in the first step and click Next. Uncheck all delimiters in the second step and click Next.

Set column data format to Date

In the third step, select 'Date' under the Column data format section. Choose the format that matches your text (e.g., MDY or YMD) from the dropdown list, then click Finish.

Convert Text to Valid Dates Using Text to Columns

Test the Sort Again: After conversion, highlight the column and go to the Data tab. The Sort option should now display 'Sort Oldest to Newest'.

Solution 2

Use Paste Special to Convert Formats

Multiply the text dates by 1 to convert them into serial numbers that Excel natively recognizes as dates.

Solution 3

Update Excel to the Latest Version

If valid dates still do not provide date-specific sort options, an outdated software version might be causing a bug.

Easily Format and Sort Imported Data in WPS Spreadsheet

WPS Spreadsheet features robust data recognition tools designed to handle imported data flawlessly. It easily converts text strings into valid dates and offers complete Microsoft Excel format compatibility.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the improperly formatted dates.
2. Select the date column: Highlight the cells or the entire column that currently sorts from A to Z.
3. Use Text to Columns: Go to the Data tab on the top ribbon and click 'Text to Columns'.
4. Apply the Date format: Follow the straightforward prompt to format the column as 'Date', then apply the change.
5. Sort by date properly: Click the Sort icon in the Data tab. You will now see the correct 'Sort Oldest to Newest' option.

Smart data parsing that recognizes dates upon importIntuitive Text to Columns wizard for fast format correction100% compatibility with Microsoft Excel (.xlsx and .xls) formatsFree, lightweight, and user-friendly interface

Frequently Asked Questions

Why are my imported dates showing up as text?

When you import data from databases, banking sites, or CSV files, systems often export dates in non-standard formats or with hidden leading apostrophes. This causes your spreadsheet software to interpret the data as plain text rather than numerical date values.

Can I use a formula to convert text to dates?

Yes. You can use the DATEVALUE function. Enter =DATEVALUE(A1) into a blank adjacent column to extract the serial number from the text date. Afterward, format the new column as 'Date' and copy-paste the values over the original text.

How do I change the default date format to match my region?

Select the cells containing your dates, right-click, and choose 'Format Cells' (or press Ctrl+1). Under the Number tab, select 'Date'. You can then select your desired locale and choose a regional format from the list provided.

Excel & Data Problems