Home life · Updated
How to Organize Rows in Excel: Sort & Filter Guide
Quick answer Sort and filter rows in Excel to rearrange data alphabetically, numerically, or by custom rules, and to display only the rows you need.
What You’ll Need
- A computer with Microsoft Excel (2016 or later, or Microsoft 365).
- The spreadsheet file containing the rows you want to organize.
- Basic familiarity with selecting cells and using the ribbon menu.
Step-by-Step
- Select your data range – Click and drag to highlight all the cells you want to organize, including headers. If your data is in a contiguous block, click any single cell inside it; Excel will automatically detect the range when you sort.
- Open the Sort dialog – Go to the Data tab on the ribbon. In the Sort & Filter group, click Sort. This opens the Sort dialog box where you can set multiple levels.
- Choose the primary sort column – In the Sort dialog, under Column, select the column you want to sort by from the dropdown. For example, if you want to organize rows by last name, choose the column containing last names.
- Pick the sort order – Under Sort On, keep Cell Values for most sorts. Then under Order, choose A to Z (or Z to A), Smallest to Largest, or Largest to Smallest depending on your data type.
- Add more levels (optional) – Click Add Level to sort by a second column, then a third, and so on. For instance, sort by Department first, then by Last Name within each department. The order of levels matters: the top level is the primary sort.
- Confirm header row – Make sure My data has headers is checked in the Sort dialog. This prevents the header row from being sorted along with the data.
- Apply the sort – Click OK. Excel rearranges the rows according to your criteria. If you only selected a single cell, Excel expands the selection to the entire data range automatically.
- Use filters for quick organization – To temporarily view only certain rows, select your data range, go to the Data tab, and click Filter. Dropdown arrows appear in each header. Click an arrow to filter by specific values, text, or numbers. To remove the filter, click Clear in the Sort & Filter group.
Common Mistakes
- Sorting only one column – If you select just a single column and sort, Excel will reorder that column independently, breaking the relationship between rows. Always select the entire data range or click inside the data table before sorting.
- Forgetting to check the header row – If the header is included as data, it will be sorted into the middle of your rows. Ensure the My data has headers checkbox is ticked.
- Sorting with blank cells – Blank cells in the sort column can cause unexpected placement. Excel typically places blank rows at the bottom, but if you have blanks in the middle, they may be grouped oddly. Fill in missing values or filter them out first.
- Using filters without clearing them – If you filter rows and then sort, only the visible rows are sorted, which can scramble your data. Clear all filters before sorting.
Tips
- Use custom sort orders – For non-alphabetical orders (like High, Medium, Low), go to Sort > Order > Custom List and create a custom list.
- Sort by color – If you’ve color-coded cells, you can sort by cell or font color using the Sort On dropdown.
- Create a table – Convert your data range to a table (Ctrl+T) before sorting. Tables automatically expand and keep formatting, and filters are built in.
- Undo is your friend – If a sort goes wrong, press Ctrl+Z immediately to revert.
- Freeze header row – After sorting, freeze the top row (View > Freeze Panes) so headers stay visible when scrolling.
Troubleshooting
Sort didn’t work as expected – Check if your data has merged cells, which can interfere. Unmerge all cells in the range, then try again. Also ensure no hidden rows or columns are present.
Data got mixed up – If rows are scrambled, you likely sorted only a single column. Undo (Ctrl+Z) and reselect the entire data range before sorting.
Header row is sorted – Go back to the Sort dialog and tick My data has headers. If you already sorted, undo and redo with the checkbox checked.
Filters are not showing – Make sure you clicked Filter on the Data tab. If you’re using a table, filters are automatic. If filters still don’t appear, select the data range and try again.
Numbers sort as text – If numbers appear as text (e.g., left-aligned), convert them to numbers: select the column, go to Data > Text to Columns, and finish. Then sort again.
Frequently Asked Questions
How do I sort rows without affecting other columns?
You can’t sort a single column without affecting the entire row’s order. To keep rows intact, always select the full data range. If you need to sort only one column independently, copy it elsewhere first.
Can I sort rows alphabetically by last name when the name is in one cell?
Yes, but you may need to split the name into first and last columns first using Text to Columns or by using formulas. Alternatively, sort by the full name if the last name is the first word.
How do I sort rows by date in Excel?
Select the date column, go to Sort, and choose Oldest to Newest or Newest to Oldest. Ensure dates are in a recognized date format (not text).
What is the difference between sorting and filtering?
Sorting rearranges the order of all rows based on a column. Filtering hides rows that don’t meet criteria, but does not change the order. You can use both together.
How do I sort rows in Excel on a Mac?
The steps are similar: select data, go to the Data tab, click Sort. The dialog is the same. Filters are also available under the Data tab.
Can I sort rows by multiple columns at once?
Yes, use the Add Level button in the Sort dialog. Each level adds a secondary sort criterion. The order of levels determines priority.
Related
- How to Stretch an Image in Canva (Easy Steps)
- How to Stretch a Photo (Easy Step-by-Step Guide)
- How to Stretch a Hat: 6 Easy Methods
Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.