Access and Excel both offer several built-in, easy-to-implement sorting features. Try them out before reinventing the wheel—you might be surprised how efficient these tools are. Approaching a sort in Access and Excel is often similar; that’s why I’m covering them both.

I’m using Access 2016 and Excel 2016 (desktop versions) on a Windows 10 64-bit system. You can use any Ribbon or menu version to apply basic sorting options. We’ll be working with the same records in Access and Excel, and you should have basic knowledge of both applications. You can work with your own data or download the demonstration files, which include the data in .xlsx and .xls format and a delimited text file you can import into Access. Access Web Apps isn’t appropriate for this discussion because it’s on the chopping block; advanced sorting (#3) isn’t supported by Excel’s browser version.

SEE: 20 pro tips to make Windows 10 work the way you want (TechRepublic PDF)

Quick disclaimer

Access developers usually add customized sorting forms to keep users from modifying data at the table level. If you’re the only one using your database and you feel comfortable using Datasheet view, do so. I don’t recommend allowing users to access tables. Instead, you can present data in a datasheet form, which supports the same features.

You’ll probably want to position a query between the table and the datasheet for the best results. The datasheet form shown in Figure A is based on the Plants Query object. The form protects the underlying data by setting the following form properties:

• Allow Additions: No
• Allow Deletions: No
• Allow Edits: No

The datasheet form provides sorting options using a familiar interface. Property settings protect the underlying data from unintentional changes. Now, let’s review sorting and filtering basics, beginning with the simplest option. One more thing: The data is the same in both Access and Excel, but the data in Access isn’t normalized.

Figure A

This datasheet form lets you sort without modifying the underlying data.

SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)

1: Simple sort

The Home tab (in Access) has many options in the Sort & Filter group. The simplest option sorts any field in ascending or descending order. Simply click inside the field and then click either Ascending or Descending. Figure B shows an ascending alphabetical sort by common names—it took only two clicks. Click Remove Sort, or not—but the option’s available.

Figure B

With two clicks, you can sort plant records by common names.

When sorting, Access displays a small arrow next to the field’s dropdown list (circled). Pay close attention to these symbols and remove previous sorts before new sorts, unless you want the previous sort considered. If Ribbons aren’t available, you can find the Ascending and Descending sort options in the field’s dropdown.

In Excel, you have an extra click. Click anywhere inside that field (column) and then choose Sort A To Z from the Sort & Filter dropdown in the Editing group. To undo a sort in Excel, press Ctrl+Z (before you do anything else).

2: Simple plus

The simplest sort considers values in only one field. To sort by multiple fields in the datasheet form, hold down the Shift key and click additional fields to add them to the selection. Then sort. As you can see in Figure C, the records sort by height and then by color. You’re limited to sorting left to right, and you can’t sort by noncontiguous fields.

Figure C

Use the Shift key to sort by multiple fields.

As a rule, blanks sort first. In addition, a Yes/No field will sort Yes values to the top of an ascending sort.

You can temporarily move (drag) a field to set things up for a sort and then return them—or not. If you’re using this datasheet form for only this purpose, it’s doubtful that its structure will affect anything else. Figure D shows perennials and annuals sorted by their status as natives (and others), then by color, and then by height. In this way you can quickly find a native perennial that’s blue and three to four feet high.

Figure D

Reposition fields before sorting.

Excel also supports multiple-field sorts, and the routine is the same. To sort by noncontiguous fields, move them so they are adjacent before sorting. Both Access and Excel offer advanced sorting options, which you might find easier to use.

3: Advanced

Sorting can be quick and easy—or it can be complicated. By repositioning existing fields, you can sort by multiple fields, but you might not want to move things around. When that’s the case, both Access and Excel offer more advanced sorting options. First, let’s create an advanced filter that sorts the Access data by season, color, and then height:

1. Choose Advanced Filter/Sort from the Advanced dropdown in the Sort & Filter group (on the Home tab).
2. Add Season, Color, and Height to the grid and set each field’s Sort setting to Ascending (Figure E).
3. To see the results (Figure F), choose Apply Filter/Sort from the Advanced dropdown. (This figure displays a partial view of the summer bloomers because it’s the largest group and shows the most sorting.)

Figure E

Specify the fields and sort settings.

Figure F

There are three sorts in this view.

The seasons sort alphabetically. Within each season group, the colors sort alphabetically. Within each color group, the heights sort numerically—seemingly. This last group poses a bit of a problem because this is a text field, not a numeric field. Access doesn’t know that 6-18 inches is smaller than 1.5-3 feet. In this case, better data input constraints might solve the problem. For instance, you might enter everything in inches or in feet instead of entering a mix of the two.

Access will keep the sort in place (even in your form) until you click Remove Sort or run a new sort. If you plan to reuse the sort, save it by choosing Save As Query from the Advanced dropdown (with the advanced sort open), giving the new query a meaningful name, and clicking OK.

Now, let’s see how Excel does the same thing by creating a custom sort:

1. From the Sort & Filter dropdown, choose Custom Sort.
2. In the resulting dialog, choose the first sort field, Season, from the Sort By dropdown and adjust the other options as necessary. Values and A to Z are the defaults, so you won’t need to change them for this example for any of the levels.
3. Click Add Level and add the second sort field, Color.
4. Click Add Level and add the third sort field, Height (Figure G).
5. Click OK. Use Ctrl+Z to undo the sort if you want to (or not).

Figure G

Add sort levels.

Stay tuned

This article covers built-in sorting features that work similarly in Access and Excel. Next month, we’ll continue by reviewing built-in filtering features.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read…