Show
It's often useful to freeze header rows in place in Microsoft Excel to remember what type of data is in various columns as you scroll or search through a lengthy spreadsheet. You can also make sure these headers are omitted and left in place if you use Excel's sort functions or order the rows in your spreadsheet using an Excel alphabetize formula.
Use Excel's "Freeze Panes" functionality to lock rows or columns in place. Use Excel sort features to organize data in alphabetical or numerical order.
It's often useful to freeze header rows or columns in Excel while navigating through a spreadsheet. Otherwise, you may need to repeatedly scroll to the top or left of the sheet to remember what's in what row or column.
To do this, use Excel's Freeze Panes function. If you want to freeze just one row, one column or both, click the View tab, then Freeze Panes. Click either Freeze First Column or Freeze First Row to freeze the appropriate section of your data. If you want to freeze both a row and a column, use both options.
If you want to freeze more than just one row or one column, click the cell in the spreadsheet that's just to the right of the last column you want to freeze and just below the last row you want to freeze. Then, click the View tab and Freeze Panes. Click Freeze Panes again within the Freeze Panes menu section.
Excel can sort data in a spreadsheet in alphabetical or numerical order.
Often, you'll just want to sort all of the data in a spreadsheet except for the header by the values in a certain column. To do this, use your mouse to click on and select a cell in the column you want to sort by. Then, click the Data tab. Click the AZ button to sort the data in alphabetical order, or in numerical order from smaller numbers to larger ones. Or click the ZA button to sort in the opposite order, so larger numbers and words later in the alphabet come first. Generally Excel will detect if there are headers present and leave those rows frozen.
If you only want to sort a portion of the spreadsheet, select the relevant section, including any headers. Then click the Sort button in the Data tab. Use the drop-downs to select the column or columns to sort by and check the box "My data has headers" if that applies. Use the Order drop-down menu to choose which order to sort the data in, whether largest to smallest or smallest to largest. Click OK when you are done.
You can also use the Sort button for greater customization when you're sorting an entire spreadsheet or if Excel is not detecting your header row for some reason.
If data are grouped together by row, you generally will want to sort the entire spreadsheet, or at least each column in a set of rows, so that data remains grouped together rather than having some columns sorted and some not.
How to sort in Excel list by row or column. Tips show how to avoid painful mistakes when sorting your spreadsheets. How to sort multiple columns, rows, or sort in custom order. Videos, and Excel workbook. Avoid Sorting Trouble: Check Your Data1) Make a Backup of Your DataBe sure to make a backup copy of your Microsoft Excel file, before you start sorting the data. Then, you can go back to the saved version, if anything goes wrong. Tip: For a quick and easy backup copy, get my free Excel Backup tool. This tool makes a backup copy in the current folder, and does NOT affect the active workbook. The backup tool is in xlam format, so it's easy to install on your computer - just like any other Excel file. 2) Check Your DataBefore you sort data in Excel, be sure there are no blank rows or columns within the data range on the worksheet. The steps below show how to do that. Why is it important to check your data?
How to Check For Blank Rows or ColumnsTo help prevent sorting problems, follow these steps before you sort Excel data:
3) Fix Blank Rows and Columns
Then, after you fix any blank columns or rows:
Quick Sort With Sort ButtonsIn Excel, you can quickly sort your data by using the A-Z (ascending order) and Z-A (descending order) buttons on the Ribbon's Data tab. Follow these steps to sort with the Quick Sort command buttons:
Check the ResultsImmediately after sorting, and before you do anything else:
If anything looks incorrect, or out of order:
Sort Buttons on Quick Access ToolbarIf you sort frequently, you can add the Sort buttons to your Quick Access Toolbar (QAT). That makes it even easier to do a quick sort in Excel. There are "how to" steps on the Quick Access Toolbar page. that show how to add buttons. Then, to use those Sort icons, follow the steps in the Quick Sort with A-Z Buttons section, but use the QAT buttons instead. Sort Two or More ColumnsIf you want to sort 2 or more columns in an Excel table, use the Sort dialog box, where you can set up a multi-level sort. In this example, the table contains personal data, and it will be sorted by 3 columns:
Follow these steps to safely sort the data set by the 3 columns:
Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.
The data will be sorted in the order that you specified. In the screen shot below:
Sort in a Custom OrderIn the Sort dialog box, or on the Excel Ribbon, you can select a sort order, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom order, such as month order, or weekday order. In this example, we'll sort a column with weekday names, using the Excel Ribbon command. Watch the steps for doing a custom sort in the Sort Custom Order video, or follow the written instructions, below the video. Sort in a Custom OrderIn the Sort dialog box, or on the Excel Ribbon, you can select a sort order, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom order, such as month order, or weekday order. In this example, we'll sort a column with weekday names, using the Excel Ribbon comman To sort in a custom order, follow these steps:
The Day column is sorted in weekday order, instead of alphabetical order, so Sunday appears at the top of the list. Sorting a RowInstead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu. You can see the steps in this short Sort by Row video, and read the detailed instructions below. Sorting a RowInstead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu. To sort by a row, follow these steps:
The data is sorted by the values in the selected row. Sort Data by Cell IconWatch this short video to see the steps for adding cell icons, and sorting by the selected cell's icon. There are written steps below the video. When you create a named Excel table, or apply an AutoFilter to a list, each heading cell gets a drop down arrow. Click that arrow, and you'll see a variety of sorting and filtering options for the data. If you add conditional formatting icons to one of the columns, you can also sort by those icons. In the screen shot below, Traffic light icons are being added to the Quantity column. Sort by Selected Cell IconAfter adding icons, the quickest way to sort by a specific icon is:
The list is sorted, to move all items with the selected icon to the top of the list. Other items are not sorted, and the items that were moved to the top of the list are left in their original order, within that group. Sort With Heading Drop Down ListAfter you have added cell icons, you can also sort by icon from the drop down list in the heading.
VLOOKUP Sorting ProblemA VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example: =VLOOKUP('Order Form'!B5, Products!$B$2:$C$6,2,FALSE) NOTE: This problem can occur with other functions too, such as an INDEX/MATCH lookup formula. Watch this video to see the steps for fixing the problem, and download the VLOOKUP Sorting problem sample file to follow along. The written instructions are on the How to Use Excel VLOOKUP - Examples page. Sort Sample WorkbookTo try the sorting techniques, get the Sort sample workbook. The file is in xlsx file format, and is zipped. It does not contain any macros. More TutorialsSort Data With Macros Sort Data - Excel 2003 Basics Excel Add-in Advanced Sorting Pivot Table Sorting Sort a Row in Excel Numbers Don't Sort Correctly |