Microsoft Excel 2007 tutorial: sort data in a range of cells or table


Sort data in a range of cells or table

VIII. Sort data in a range of cells or table

Sorting data helps you rapidly visualize and understand data better, organize and find data that you want, and finally you can make an effective decision.

You can sort text data (A-Z and Z-A), numbers (lowest to highest and highest to lowest), and dates (earlier to oldest and oldest to earlier) in one or more columns. You can also sort by a customer list, such as Large, Medium, and Small, or by format such as cell color, font color and icon set. Most of sort operations are column sorts, but you can also sort by rows.

For Excel table, sort criteria are saved with the workbook so that you can reapply the sort each time that you open the workbook. However, sort criteria are not saved with workbook when you use a range of cells. If you want to save sort criteria with the workbook so that you can frequently and regularly reapply a sort when you open the workbook, use a table instead. It is especially important for the multicolumn sorts or for the sorts with complex criteria that you take long time to create.

VIII.1. Sort text

1. Select a column containing alphanumeric data in a range of cells or table that you want to sort.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Sort A to Z or  Sort A to Z.

Excel 2007 sort data

If you want to do a case-sensitive sort by doing the following:

1. Repeat step one above.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

Excel 2007 custom sort command

3. In the Sort dialog box, click Options button.

4. In the Sort Options dialog box, select Case sensitive check box, and then click Ok button twice (one in this dialog box and another one in the Sort Options dialog box ).

Excel 2007 select case sensitive

Notes: There are two issues that you have to solve:

- Check that all data is stored as text: If the column that you want to sort contains numbers stored as numbers and numbers stored as text, your work is to format all of them as text. If you do not, the numbers stored as numbers are sorted before the numbers stored as text. To all selected data as text, do the following:

1. On the Home tab, in the Font group, click Format Cell Font dialog box launcher.

Excel 2007 Format Cell Font dialog box launcher

You can also use keyboard shortcut by pressing Ctrl+ Shift +F.

2. In the Format Cells dialog box, click Number tab.

3. Under Category, click Text, and then click Ok button.

- Remove any leading space: Data that are imported from another application might have leading spaces inserted before them. To sort the data, you need to remove their leading spaces first.

VII.2. Sort numbers

1. Select a column containing numeric data in a range of cells or table that you want to sort.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Sort Smallest to Largest or  Sort Largest to Smallest.

Excel 2007 sort number

Note: For sorting numbers, there is one issue that you need to solve: check that all numbers are stored as number. If you sort the column might containing numbers stored as text and not as numbers, you will get the expected result. To convert numbers stored as text to numbers, read this page: www.worldbestlearningcenter.com/index_files/Excel-2007-convert-text-numbers-to-numbers.htm.

VIII. 3. Sort dates or times

1. Select a column containing dates or times data in a range of cells or table that you want to sort.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Sort Oldest to Newest or  Sort Newest to Oldest.

Excel 2007 sort dates or times

Note: For sorting dates or times, there is one issue that you need to solve: check that dates and times are stored as dates or times. If you sort the column might containing dates or times stored as text and not as dates or times, you will get the expected result. In Excel to sort dates or times correctly, all dates or times must be stored as dates or times serial number. If the Excel cannot know a value as date or time, the date or time is stored as text. To covert dates or times to text, read this page: www.worldbestlearningcenter.com/index_files/Excel-2007-convert-text-dates-to-dates.htm.

VIII. 4. Sort by cell color, font color, or icon

You can sort data in a range of cells or table column by cell color or font color if your data are formatted by these colors. You can also sort by an icon set if you have formatted data with a conditional format.

1. Select a column containing data in a range of cells or table that you want to sort.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

Excel 2007 custom sort command 

The Sort Warning dialog box is displayed.

Excel 2007 Sort Warning dialog box

3. Click Sort button.

The  Sort dialog box is displayed.

Excel 2007 Sort dialog box

4. Below Column, in the Sort by box, select the column that you want to sort.

5. Under Sort On, select one of the following sort types:

▫ Click Cell Color if you want to sort by cell color.

▫ Click Font Color if you want to sort by font color.

▫ Click Cell Icon if you want to sort by icon set.

Note: When you select Cell Color, Font Color, or Cell Icon under Sort on, in the Sort dialog box, below Order, there is a change as the figure below:

Excel 2007 the change of Sort dialog box

Below Order, there are a button and a box.

6. Below Order, click the arrow next to the button, and then select Cell Color, Font Color, or Cell Icon depending on the type of format.

7. Below Order, select how you want to sort in the box next to the button. Do one of the following:

▫ Select On Top to move the cell color, font color, or icon to the top.

▫ Select On Bottom to move the cell color, font color, or icon to the bottom.

Note: If you want to specify the next cell color, font color, or icon to sort by, click Add Level and then repeat steps four through six. Make certain that in the Then By box you select the same column of the column in the Sort by box.

VIII.5. Sort by a custom list

Microsoft Office Excel provides the built-in custom lists, including day-of-the-week and month-of-the-year that you can use for sorting. If the built-in custom list is not your need, you can create a custom list by yourself.

1. Select a column containing data in a range of cells or table that you want to sort by a custom list.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

Excel 2007 custom sort command 

The Sort Warning dialog box is displayed.

Excel 2007 Sort Warning dialog box

3. Click Sort button.

The  Sort dialog box is displayed.

Excel 2007 Sort dialog box

4. Below Column, in the Sort by box, select the column that you want to sort.

5. Below Order, select Custom List in the box.

Excel 2007 select custom list

The Custom Lists is displayed.

Excel 2007 custom list dialog box

6. Click the custom list that you want in the Custom list list. If you want to create your own custom list, do the following:

1. Enter the values in a range of cells, and the order for the values are top to bottom. For example:

Excel 2007 example custom list

2. Select the range (A1:A3) that you type above.

3. Click the Microsoft Office Button, click Excel Options, click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists button.

Excel 2007 create custom list

4. In the Custom Lists dialog box, click Import, and then click OK twice.

Note: You can only create a custom list base on a value, such as text, number, and date or time, not base on a format, such cell color, font color, and icon. The first character of a custom list must not begin with a number and its maximum length is 255 characters.

7. Click Ok button twice.

VIII.6. Sort rows

1. Select a row containing data in a range of cells or table that you want to sort.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

Excel 2007 custom sort command 

The Sort Warning dialog box is displayed.

Excel 2007 Sort Warning dialog box

3. Click Sort button.

The  Sort dialog box is displayed.

Excel 2007 Sort dialog box

4. Click Options button.

5. In the Sort Options dialog box, below Orientation, select Sort left to right, and then click Ok button.

Excel 2007 sort row

The Sort dialog box will change Column to Row. See the figure below:

Excel Sort dialog box change column to row

6. Below Row, in the Sort by box, select the row that you want to sort.

7. Below Sort On, do one of the following:

By Values:

1. Select Values.

2. Below Order, do one of the following:

- Select A-Z  or Z-A for text data.

- Select Smallest to Largest or Largest to Smallest for number data.

- Select Newest to Oldest or Oldest to Newest for date or time data.

By Cell Color, Font Color, or Icon

1. Select Cell Color, Font Color, or Cell Icon.

2. Under Order, click the arrow next to the button, and then select a cell color, font color, or cell icon.

3. Below Order, in the box next to the button, select On Left or On Right.

VIII.7. Sort by more than one column or row

You can sort data in a range of cells or table by more than one column or row. Do the following:

1. Select two or more columns in range of cells or table.

2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

Excel 2007 custom sort command 

The Sort Warning dialog box is displayed.

Excel 2007 Sort Warning dialog box

3. Click Sort button.

The  Sort dialog box is displayed.

Excel 2007 Sort dialog box

4. Below Column, in the Sort by box, select the first column that you want to sort.

5. Below Sort On, select the sort type. Do one of the following:

▫ Select Values to sort by text, number, or date or time.

▫ Select Cell Color, Font Color, or Cell Icon to sort by format.

6.  Below Order, select how you want to sort. Do one of the following:

▫ Select A-Z  or Z-A for text data.

▫ Select Smallest to Largest or Largest to Smallest for number data.

▫ Select Newest to Oldest or Oldest to Newest for date or time data.

▫ Select Custom List to sort basing on a custom list.

7. To add another column to sort by, click Add Level, and then repeat steps four through six.

8. To copy a column to sort by, select the entry, and then click Copy Level.

9. To delete a column to sort by, select the entry, and then click Delete Level.

10. To change the order of the sorted columns, select the entry, and then click Move Up Excel 2007 move up button or Move Down Excel 2007 move down button button.

Notes:

- You can sort by up to 64 columns.

- To get the best result, the range of cells that you sort should have column headings.

VIII.8. Sort one column in a range of cells without affecting others

When you use this feature in a range of cells, you should be careful because it may produce the result that you don't want, such as moving cells in that column away from other cells in the same row. And you cannot do the following steps in a table.

1. Select a column in a range of cells that contains two more columns.

2. To select the column that you want to sort, click the column heading.

3. On the Home tab, in the Editing group, click Sort & Filter, and then click one of the available sort commands.

4. The Sort Warning dialog box is displayed.

5. Select Continue with the current selection.

Excel 2007 sort one column

6. Click Sort button.

7. In step three, If you select Custom Sort, the Sort dialog is displayed; and you can select any sort options that you want in the dialog box. And then click Ok button to complete the sorting process.

If the results are not your need, click Undo command.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.