Sort On Multiple Columns Overview
In addition to performing a
quick sort based on a single column of data, Excel allows you to sort on
multiple columns by defining multiple sort keys.
In multi-column sorts, the
sort keys are identified by selecting the column headings in the Sort dialog
box.
Sort On Multiple Columns Example
In the example below, the
following steps were followed to sort the data in the range H2 to L12 on two
columns of data - first by name, and then by age.
1. Select
the range of cells to be sorted
2. Click
on the Data tab of the ribbon.
3. Click
on the Sort icon on the Sort & Filter group.
4. Under
the Column heading in the dialog box, choose Name from the drop down list to
first sort the data by the Name column
5. The
Sort On option is left set to Values - since the sort is based on the actual
data in the table
6. Under
the Sort Order heading, choose Z to A from the drop down list to sort the Name
data in descending order
7. At
the top of the dialog box, click on the Add Level button to add the second sort
option
8. For
the second sort key, under Column heading, choose Age from the drop down list
to sort records with duplicate names by the Age column
9. Under
Sort Order heading, choose Largest to Smallest from the drop down list to sort
the Age data in descending order
10. Click
OK in the dialog box to close the dialog box and sort the data
As a result of defining a
second sort key, in the example above, the two records with identical values
for the Name field were further sorted in descending order using the Age field,
resulting in the record for the student Ganesh aged 25 being before the record
for the second Ganesh aged 25.
Sort By Date Or Time Overview
In addition to sorting text
data alphabetically or numbers from largest to smallest, Excel's sort options
include sorting date values.
The sort orders available for dates are:
Ascending order - oldest to newest
Descending order - newest to oldest
Quick sort vs. Sort dialog box
Since dates and times are just
formatted number data, for sorts on a single column - such as Date Borrowed in
the example in the image above - the quick sort method can be used successful.
For sorts involving multiple
columns of dates or times, the Sort dialog box needs to be used - just as when
sorting on multiple columns of number or text data.
Sort By Date Example
To perform a quick sort by
date in ascending order - oldest to newest -
for the example in the image above, the steps would be:
1. Highlight
the range of cells to be sorted
2. Click
on the Home tab of the ribbon
3. Click
on the Sort & Filter icon on the ribbon to open the drop down list
4. Click
on the Sort Oldest to Newest option in the list to sort the data in ascending
order
5. The
records should be sorted with the oldest dates in the Borrowed column at the
top of the table
Dates And Times Stored As Text
If the results of sorting by
date do not turn out as expected, the data in the column containing the sort
key might contain dates or times stored as text data rather than as numbers
(dates and times are just formatted number data).
In the image above, the record
for A. Peterson ended up at the bottom of the list, when, based on the
borrowing date - November 5, 2014 - , the record should have been placed above
the record for A. Wilson, which also has a borrowing date of November 5.
The reason for the unexpected
results is that the borrowing date for A. Peterson has been stored as text,
rather than as a number.
follow us on http://arivilm.blogspot.in/
like
us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment