Learn how to unlock selected cells and then protect the worksheet—optionally allowing some formatting—with a password so that you can only make changes to unlocked cells.
- [Instructor] If you work with lists in Excel, it's quite likely you want to sort the list. And sorting typically means rearranging the order of rows within a list. Looking at a worksheet called Sorting, it's in the Workbook Ten, Data Management Features. As you look at the list, you can probably see that the data appears to be in descending order based on what's in either column H or J, not necessarily sure, but we might want to rearrange it, for example, sort by Department. Now, although there is that rare occasion where you might wanna sort by moving columns left and right, for most people most of the time, sorting means, what? Moving a row, or this part of a row, up and down within a list.
If you have other data on the worksheet, and I do to the right, if you don't want that to be part of the sorting process, make sure it's isolated from your data, at least one empty column. It's unlikely that you'd have any data below it, but if that's the case, keep it separated, as well. When we sort, we rearrange the data on the basis of what's in one or more columns. And before sorting, you should always make sure that your list is contiguous. You can click on a cell, double-click its bottom edge, it should take you to the bottom. Now, it would stop if there's an empty cell.
It's okay if you have an empty cell here and there, but you don't want to have an empty row all the way across. If you work with the data frequently, you probably know that's not the case anyway. You can also quickly press Control + A and then Control + Period a few times. This will simply get you oriented with the data if you haven't seen it in a while. Control + Period moves the active cell around the corners of the range. And you don't have to do that all the time, but just occasionally, to remind yourself. When you're ready to sort, you can leave it highlighted or simply click within it, and although you can start on the Home tab, second button from the right, Sort and Filter, you can start there, use Custom Sort.
It's more likely you'll go to the Data tab where we find other data management tools. Use the larger button here, Sort. This dialog box pops up. First thing to check out, make sure Excel recognizes that your top row is a header row. Now, usually, it's gonna be there, and Excel usually figures it out, sometimes you have to check this, or if you had no header, you might need to uncheck it. Let's say we want to rearrange this list by Department. Sort by Department. Now, because a lot of people are going to be in one department, we might want to add a second level, meaning, as we look at just the data in that department, let's put it in order by Status, and maybe a third level, even.
We can add four, five, six. The older versions of Excel only allowed three. Now, we can go as high as, believe it or not, 64. Let's suppose we do a third level here, maybe descending order by years. When you select these fields, also look to the right. For text fields, typically, you probably would want them ascending order, A to Z alphabetical. If it's a numeric field, you might want largest to smallest. We could even add a fourth level here, maybe by name. And that would be A to Z, as well. Sorting is fast, often it seems instantaneous.
Click OK, the major grouping is by Department, and we see the departments. Then it changes to the next department, and so on. That first department, Account Management, has lots of people, and it's grouped by the Column D entry, Status. Here's a whole bunch of full-time people. What order are they in? Years of service, that's Column F. And how are they ordered, those people there? Alphabetical, by their names, over in Column A. After a few sorts, you quickly get used to the idea of a multilevel sort. As you get more comfortable with sorting, occasionally you wanna sort just on the basis of what's in one column.
Click over in Column A on any cell, but not the heading. If we want to rearrange this list based on Column A data, alphabetical by name, click the single A, Z button. And now, it's in that order. It's important to note here, too, that if you later sort by Department, maybe you'll click here and use A, Z, Excel remembers that employee name order. It will rearrange the data based on Department, but within each department, they're gonna be alphabetical by name. Same thing would happen if we now sort by Status. It will remember that Department was the previous order.
There are all the Contract people, they're in order by Department within that by Employee Name. Also with sorting, although less likely to be needed, you can sort, not necessarily on cell values, well, that's most common, you can also sort by Cell Color, Cell Color Background, Font Color, or if you're familiar with conditional formatting, you can sort on that basis, as well, too. For that rare time when you might want to sort by rearranging columns, and it certainly wouldn't be with data like this, you'd go to the Options tab and choose Sort Left to Right.
Sorting is commonly needed. It's one of the most frequently-used things we use in Excel and easily accessible by way of the Sort button as we've seen on the Data tab.
- Navigating Excel tabs and menus
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros