74
Knowing how to efficiently sort tables by value in Excel, avoid common mistakes, and make the most of the function in different versions is an essential skill for any data analysis.
Sorting tables by value in Excel
To sort an Excel table by value, proceed as follows:
- Select the data range you want to sort. Make sure that all related data (e.g. rows with multiple columns) are also selected so that no data is separated during sorting.
- Right-click on the selection and select “Sort” → “Custom Sort” from the menu.
- Select the desired column to sort by. Make sure that the “Sort by” field is set to “Values”.
- Sort options: Ascending: Values from smallest to largest or A to Z. Descending: Values from largest to smallest or Z to A. Custom: Define your own order, e.g., specific text values or priorities.
- By color or icon: Sort based on cell background color, font color, or icons from conditional formatting.
- Confirm with “OK” to apply the sort.
- Keyboard shortcuts in Excel: Sort ascending: Alt + A + S + A (Windows); Sort descending: Alt + A + S + D (Windows); Mac: Cmd + Shift + R (sorts by column, variable depending on Excel version).
Sorting multiple columns simultaneously in Excel
It often makes sense to sort multiple columns at once, e.g., first by Department and then by Name. The following priority applies: Excel sorts first by the highest priority, then by the second, and so on.
- Open “Custom Sorting”.
- Click on “Add Level” to add another column.
- Select the column and sort type.
- Repeat this for all other priorities.
- Confirm with “OK.”
- Common problems: Data rows being split up: If you only select one column, Excel will sort that column alone. Therefore, always select the entire data range. Empty cells: These can affect the sorting; check that all relevant cells are filled.
Sorting in pivot tables and differences between Excel versions
In addition to classic tables, you can also sort pivot tables to quickly create evaluations.
Below, we show you how sorting works in pivot tables and what differences there are between the Excel versions for Windows, Mac, and Online.
- Pivot tables: In pivot tables, you can sort rows or columns directly by values or categories. To do this, click on the drop-down arrow next to the column or row header and select “Sort A to Z / Z to A” or “Custom”. You can also sort by totals, averages, or other calculations displayed in the pivot table.
- Differences between Excel versions: In Windows , all sorting options (including custom, by color/symbol) are available. In Mac , almost all functions are available, although the menu navigation may vary slightly. In Excel Online, basic sorting by value, ascending/descending, is possible, but custom orders and color sorting are partially restricted.
- Additional tip: Use filters in the header to sort only certain values without changing the entire table.
