Two of Excel’s primary functions are to allow you to manipulate and view data from different perspectives, and one of the program’s simpler yet more powerful tools for doing this is the Kind function.
Whether it’s simple ascending/descending sorts, sorting by more than one variable to display data in groups or to maintain row integrity, or simple alphanumeric sorts to keep your table data organized, sorting data in Excel is easy. an essential skill.
Few programs are better suited to sorting table data than Excel, and your sorts can range from simple and relatively straight forward to very advanced. While Excel’s sorting ability — which you can do with the right dataset and a little knowledge of the program’s inner workings — is indeed robust and in-depth, today’s tech tip focuses on two basic types, namely:
- Sort data by a single column
- Sort data by multiple columns
Spreadsheets are, of course, composed of columns and rows of cells, or tabular data, with each column containing a logical division of facts, figures, or other details by category, such as, for example, names, addresses, currencies, parts numbers, and so on, depending on the type of spreadsheet. . Rows, on the other hand, show people, objects or figures next to each other or in the same instance or event.
Depending on the type of spreadsheet and the data it contains, such as a table of names, addresses, phone numbers, and other pertinent data, rows often resemble database records.
When you sort rows of data, each row must maintain its integrity, without accidentally moving data from one row to another, which, as you’ll see later, is where sorting data by multiple columns comes in handy.
Sort on a single field
You can sort the records in your spreadsheet by row, and you can sort the cells in records by columns. You can of course specify ascending or descending sort order. By default, ascending/alphanumeric, the program arranges text from A until z and numbers from smallest to largest. Sorting with descending sort order naturally reverses the older of z until Aor so that larger numbers start at the top.
As with many functions in Excel, there are a few ways to do a simple sort; however, all you need for this sort of sort is in the right-click flyout menu, as shown below.
- Right-click a field in the column by which you want to sort the spreadsheet to open the pop-up menu.
- Scroll down and move the cursor over Kind
to open the submenu.
- Click Sort A to Z for ascending or Sort Z to A descending (Note that Excel does not sort the data in cells in row 1; the program assumes that this row contains your column labels or headings.).
This simple sort works for many types of data, except when your rows and columns contain identical or duplicate data. In these cases, you must sort by two or more columns, which are listed next.
Sort records by multiple fields
Depending on the complexity of your data, you may need to sort by more than one column. Perhaps the best example is sorting a database alphabetically by last name. Suppose your data contains multiple people with the same last name. In these cases you have to make sure that Linda Johnson comes before Lydia Johnson and Cherri Anderson after Charles Anderson… You get the idea.
You can set up custom searches with multiple criteria from the Sort dialog, like this way.
- Click a cell in the first column of data you want to sort.
- Click Facts just below the title bar to open the data ribbon. (The ribbon, of course, is the row of contextual tabs at the top of the application window.)
- In the Sort and filter section, click on the Kind to open the Sort dialog box.
- Click on the sort on drop-down list and choose the first column name to sort by. (Note that Excel displays the contents in row 1 of the column, which in this case contains the column labels. You can disable this option by unchecking My data has headers checkbox.)
- Click Add level to add another column to the sort order.
- Click on the then by drop-down list and choose the next column to sort by.
- To repeat Steps 6 and 7 if necessary to configure the parameters of your species.
If you click around the Sort dialog box, you’ll see several options for changing your sorts, although some of them, unless you know exactly how they affect your sort, are most likely to produce unwanted results.
- Click Okay†
- If you get a Sort Warning dialog box, select: Expand the selection and then click Kind.
In addition, when you choose a column, Excel analyzes the contents of the cells in that column and makes educated guesses about which values the sort on and To order fields. Unless you have a specific (and logical) reason to change it, don’t. (Unless, of course, you’re experimenting. I always encourage that – and Excel’s Undo function works great.)
As you can no doubt imagine, this is just the beginning of how to sort data in Excel. However, it’s important to remember that unless you’re careful, using the wrong parameters can put your data side by side and completely change its position in the spreadsheet. Sorting is quick and easy to set up. The good news is that Undo is fast too. Don’t be afraid to Undo and try again. And again.