How Excel tables are sorted by date of birth

Excel is sorted by date of birth as follows:

Method One: Auxiliary column + formula

For example, the following table is a list of employee birth dates for a company, the name is in the A2:a18 area, and the date of birth is in the B2:b18 area.

Name Date of birth

Wang Dong 1980-2-29

Wu Yu 1985-3-12

Zhou Li 1988-12-5

Sun Yuanyuan 1987-6-8

Zhou 1984-2-29

Sun Li 1985-4-26

Guoping 1981-12-31

Carili 1980-3-1

Sun Lu 1981-2-28

Zhou Xiangdong 1988-1-1

Wu Tingting 1984-9-10

Wang Xiaoyi 1983-5-15

Zhang Qiqi 1982-4-7

Gu Cheng 1986-3-25

Tan Guanghui 1984-8-7

Dai Red 1981-7-16

Pei Dongxia 1984-3-1

Using c column as the secondary column, enter the formula in cell C2:

=text (B2, "MMDD")

Then drag the fill handle to fill the formula into the C3:c18 area.

Select any cell in the C2:c18 range and click the A→z button in the toolbar to sort in ascending order.

As you can see, the name columns are sorted by month day, and the secondary columns are deleted.

Method Two: Use a PivotTable report

Excel 2003:

1. Select any cell in the A1:b18 area, click the menu data → PivotTable and PivotChart report, and in the dialog box, click the Finish button.

2. Drag the "date of birth" field and the "Name" field to the row area, and the Name field to the right of the date of birth field.

3. Ungroup Items: Right-click any cell in the date of birth field and select field Settings from the shortcut menu that pops up.

In the PivotTable Field dialog box that pops up, select None.

4. Right-click any cell in the Birth date field again, select "Group and show Detail → combo" On the pop-up shortcut menu, pop the Group dialog box, select Day under Step size, and the month and day are selected at the same time. Click the OK button.

Excel automatically groups and sorts names by month and day.

Excel 2007:

1. Select any cell in the A1:b18 area, select the Insert tab in the Ribbon, and select PivotTable → PivotTable report in the tables group. Click the OK button in the pop-up dialog box.

2. In the PivotTable Field List, first tick the date of birth, and then check the Name field. In row labels, the birth date field is above the name field.

3. In the PivotTable report, right-click any date and select Group from the shortcut menu that pops up. The Group dialog box pops up, under step size, select Day, when month and day are selected. Click the OK button.

Excel automatically groups and sorts names by month and day.