How to Lock Rows in Google Sheets for Sorting
If you’ve ever used any kind of spreadsheet program before, you know how easy it is to accidentally throw off everything by making one tiny change, or confuse yourself by navigating away from headings or data you need to see.
One way around this is to lock a row or column, which can help make things much easier for you. Knowing how to lock rows in Google Sheets for sorting can save you a lot of time, effort, and frustration, and it’s easy to do!
Open your spreadsheet and follow the step-by-step guide below to lock any Google Sheets row. It’ll guide you through the entire process so it’s simple, painless, and makes all of your data easier to use.
Google spreadsheet freeze row
You might’ve heard the phrase “freezing” or “locking” related to spreadsheets, but not really know what it means. Why would you want to freeze a row? There’s one big reason: Ease of use.
Freezing rows help the spreadsheet to be reader-friendly
Knowing how to lock rows in Google Sheets for sorting keeps the locked row in place on your screen. If you have an area of your spreadsheet that you need to reference often (a list of column headings, for example), it keeps you from either getting confused or having to constantly scroll back and forth.
You can lock a row, and, no matter where you scroll, it’ll stay in place so you can refer back to it at a glance. It is also one way to make your data more readable to users.
Freeze up to five columns and ten rows
If you need to lock multiple columns or rows in place, don’t worry. Google Sheets allows you to freeze as many as five columns and up to ten rows at a time.
How to freeze rows or columns
You know why you might need to freeze a row or column. Now, here’s how:
1. Open a spreadsheet in Google Docs
You might want to open a new or existing spreadsheet so you can follow along. It’ll make it easier to repeat the process later on when you need to.
2. Select the row or column you want to freeze
Select a cell in the row or column that you want to lock.
3. Click view > freeze
With the cell selected, look at the Google Sheets menu. It will have several options, including File, Edit, View, Insert, and so forth. Click on View. You should see a dropdown menu right below it. The first option in this menu should be Freeze.
4. Select how many rows or columns you want to freeze
If you move your cursor to Freeze, it’ll produce another menu to the right. This will allow you to choose how many rows and columns you want to lock in place. Once you choose an option, your Google Sheets freeze row or column will stay in one spot for you. You’ll also notice a gray border that divides your locked areas from unlocked areas.
How to unfreeze rows or columns
You might not need to keep your columns or rows frozen forever. You might even find that you accidentally lock a row or column that you didn’t mean to. Fortunately, fixing this is easy.
1. Select a row or columns
Select the Google spreadsheet freeze row or column that you want to unfreeze. We’re pretty much going to do the same process as we did to lock it, with one minor change.
2. Click view > freeze > no rows or no columns
Remember the dropdown menu that allowed you to freeze the row or column? Navigate back to it by going to View, then Freeze. In the menu beside Freeze, there should be an option to freeze “No rows” or “No columns.” Click on that, and it should be unfrozen again.
How to sort in Excel and keep rows together
You’ve learned how to lock rows in Google Sheets for sorting, but how do you sort data while keeping your rows where they’re supposed to be? How does this work in Excel? There are a few differences between these two programs, but you can sort and keep rows together in both.
Use the sort function to sort a column and keep rows
Say you have a set of data that you need to sort based on criteria from a single column. If you sort by that column, Excel or Google Sheets may sort only that column and not any of the data around it. Expanding your selection lets you sort by a single column while making sure all of your rows end up where they need to be. Here’s how:
1. For Excel: Select the column you want to sort
In Excel, click on the column heading that you want to sort. This will select everything in that column.
2. Click Data > Sort
Next, look at the Excel options. Click on Home, then go to Sort & Filter. This will give you options to sort largest to smallest, smallest to largest, or by custom parameters.
Sort warning dialog
After choosing your sorting method, Excel will show you a sort warning dialog box. It should say, “Microsoft Excel found some data next to your selection. Since you have not selected this data, it will not be sorted.” This is to let you know that the column will be reordered, but nothing outside of it will change.
As a result, the data in that column will no longer be associated with the data around it. This can make things very confusing if you’re trying to organize an entire spreadsheet by date, time, or price, for example.
3. Check the Expand the Selection option
You’ll be presented with two options: Expand the selection, or continue with your current selection. Choose “Expand the selection.” This will force the sorting to include data outside of the column you’ve selected. This is the most crucial part of how to sort in Excel and keep rows together — if you don’t expand the selection, it won’t maintain your rows when it sorts.
4. Click sort
Once you click Sort, the data will be reordered based on the information in the column you picked. As long as you expand your selection, all of the rows will end up where they ought to.
1. For Google Sheets: Select your range
Doing this in Google Sheets is a bit different. You need to start by selecting the range of rows and columns that you want to sort. Knowing how to lock rows in Google Sheets for sorting can make your life easier here since you’ll be able to keep your header rows in place while you scroll.
2. Sort dialog
With your range selected, go to Data, then Sort range. You’ll get a dialog box asking you to specify your sorting criteria. Make sure that the box marked “Data has header row” is checked.
3. Specify the sort values
As long as “Data has header row” is checked, you’ll see a dropdown box with the headings of your various columns. Make sure it’s set to the column you want to sort by.
4. Choose the sorting order
Next to that, there should be two options: A → Z (or smallest to largest), and Z → A (largest to smallest). Select the one that you want.
5. Click Sort
Next, you’ll see two buttons: Cancel and Sort. Double-check to make sure your selections are correct and click Sort.
The selected column has been sorted while the rows are still intact
Voila! Now you can organize any spreadsheet you need to.
How to sort by multiple columns in Google Sheets
You use a Google spreadsheet filter to make collaboration on a single sheet easier. You know how to lock rows in Google Sheets for sorting. You’ve learned how to sort data by a single column. What if you need to include multiple columns in your sorting criteria?
Say you want to organize a sales spreadsheet by date and total. How do you do that without having to re-order everything manually?
1. Select your range
For this, you’re going to start with the same basic steps as you would if you were sorting by a single column. Begin by picking the range of data that you want to organize. (Knowing how to lock rows in Google Sheets for sorting can make this a bit easier if you have a lot of data, since you won’t need to scroll back and forth to double-check your column headings.)
2. Sort dialog
With this range selected, go to Data, then Sort range. You’ll get the same Sort dialog box as before. Make sure “Data has header row” is checked.
3. Add another sort column
Along with all of the information mentioned previously, there’s also a button marked “Add another sort column.” Click it. This will add a second column to your sort criteria.
4. Choose your sorting values
You’ll have two options for sorting data. Google Sheets will sort by one column first, then by the second. This is very helpful if, for example, you want to organize sales figures by date then price, or need to organize inventory by category then SKU.
5. Choose the sorting order
Just like when we were sorting by a single column, you’ll be asked if you want to sort from smallest to largest, or largest to smallest. Choose whichever order you like. You can even set one column to one order, and the second column to a different order if you need to.
6. Click Sort
Make sure all of your sorting values and other options are correct and click Sort.
Your spreadsheet is organized
There you go! Your spreadsheet should be ordered just the way you want it, while keeping all of your rows and data intact.
Spreadsheets have a reputation for being finicky, but it’s not really true. If you know your way around them, and how to lock rows in Google Sheets for sorting, you can organize and re-organize your data however you need to.
Freezing your heading row or first column keeps you from having to scroll back and forth and makes things easier to read. Knowing how to select your data range, choosing the right sort criteria, and double-checking your criteria before you click Sort is all you need for well-organized spreadsheets.
The above article may contain affiliate links, which help support Clever Sequence