Étiquette : processing_lists

Sorting allows you to arrange data in alphabetical or numerical order, either ascending or descending. Microsoft Excel can sort rows as well as columns of worksheet lists. When sorting text in a table, you can sort a single column or the entire table. In addition, it is possible to sort by multiple words or fields in one table column, as well as for a selected range of the list.

To quickly sort by the desired field, simply place the cell pointer in the required column of the list with data, go to the Data tab, and in the Sort & Filter group click either Sort Smallest to Largest or Sort Largest to Smallest. On the other hand, if you select the Sort button in the Sort & Filter group on the Data tab, a dialog box will open, where you can specify sort keys (columns or rows), the sort order, and some additional parameters.

NOTE
On the Home tab, in the Editing group, there is also a Sort & Filter button, whose commands also allow you to sort a list of data.

In MS Excel, the following sort order is used:

• Numbers (from – to +);
• Text and formulas;
• The value FALSE;
• The value TRUE;
• Error values;
• Empty values.

When using sorting, keep in mind:

• The sorting order of data in MS Excel depends on the Windows regional settings.
• If it is necessary to arrange numeric values in alphabetical order, you should either place an apostrophe before the numeric values, format numbers as text, or enter the number as a formula (for example: = »345″).
• When sorting lists containing formulas, remember that relative references in formulas may lead to incorrect results when records are moved. Therefore, it is better to use formulas with absolute references in lists.
• To return to the original list, insert an additional index field before the list, containing an increasing numeric sequence with any step (e.g., 1, 2, 3, …). By selecting a cell in this column and sorting the list in ascending order, you can restore the original order of the list.
• Custom sort order allows you to arrange data in a predefined sequence (for example, days of the week, months, etc.). To do this, use the Custom Lists window , which is called by the Custom List command in the Sort dialog box. Click the order field in the column of this window and choose the Custom List command.

• Dates and times must be entered in the proper format or with the help of date or time functions, since MS Excel uses an internal representation of these values for sorting.
• Sorting by multiple fields is set in the Sort dialog box, starting from the top level. You can change the sort level using the Move Up and Move Down buttons in that dialog box.
• MS Excel can sort not only rows but also columns, as well as a selected range in the list. Furthermore, sorting can be carried out taking into account the case of the entered characters .

Lists in MS Excel are tables, whose rows contain homogeneous information. The rows of a table are called records, and the columns are called fields of records. Each column is assigned a unique field name, which is entered in the first row of the list — the header row.

As a rule, when working with lists you encounter the following ranges:

• Data range — the area where the list data is stored. Related data is written in separate rows, and each column corresponds to its own list field with a unique field name.
• Criteria range — an area on the worksheet where criteria for searching information are specified. In the criteria range, the field names are indicated, and space is allocated for entering selection conditions.
• Extract range — the area into which MS Excel copies the selected data from the list. This range may be located on the same sheet as the list, or on another sheet of the workbook.

Records – Fields – Header Row

Entering data into a list is done, for example, directly into the worksheet cells (i.e., into the empty rows below the header), or by using a data form. To do this, click the list button on the Quick Access Toolbar and choose More Commands. Then, in the Excel Options window, select Quick Access Toolbar on the left, and on the right, in the Choose commands from list, select All Commands. Find the Form command in the list below and add it to the Quick Access Toolbar.

As mentioned earlier, with data placed in a list you can perform: sorting, data filtering, and data analysis.