Locked lesson.

Upgrade

About this lesson

How to sort data and protect sheets and ranges.

Exercise files

The exercises below will open in a new tab. When signed in to your Google account, go to File --> Make a copy to get an editable copy of the file.

Sorting and Protecting Data Solution
Link Sorting and Protecting Data
Link

Quick reference

Sorting and protecting ranges

Google Sheets gives the option of sorting data, as well as protecting certain cells from being edited.

When to use

Use “Sort data” when you have a set of data and you want to change how it’s organized. Use “protect range” when you want to prevent certain cells from being edited, either by yourself or by others.

Instructions

Sorting data

  • Select the cells you want to sort
  • Click “Data” - “Sort range” or right click, and click “Sort range”
  • Check the box “Data has header row” if the first row of the data contains headers
  • Click which column to sort by
  • Click whether to sort in order (A-Z) or reverse order (Z-A)
  • If necessary, click “+ Add another sort column” to add another sorting rule (this will be used to break ties)
  • Click “Sort”

Protecting cells

  • Select the cells you want to protect
  • Click “Data” - “Protected sheets and ranges” or right click, and click “Protect range”
  • Optional: Enter a description
  • Choose which cells are protected
    • Range: Protect the selected range
    • Sheet: Protect everything on the chosen sheet except the selected range
  • Click “Set permissions”
    • Show warning when editing this range: Displays a warning when you change something in the selected cells. NOTE: This warning can be turned off for 5 minutes at a time
    • Restrict who can edit this range: Choose who has permission to edit the selected cells.

NOTE: Add email addresses to give people permission

  • Click “Done”
Login to download
  • 00:04 One of the most common functions you will wanna perform when working with
  • 00:08 a Spreadsheet, is the ability to sort information.
  • 00:12 Here you see I have some sample data here with several different headings at
  • 00:16 the top.
  • 00:17 I have information which includes dates, location, names, and units.
  • 00:22 And I may want to look at this information in a few different ways.
  • 00:27 Let's say, for example,
  • 00:28 that I would like to view this table based on representative in alphabetical order.
  • 00:34 To do so, we want to click and
  • 00:35 hold so we select every cell in this particular table.
  • 00:40 Then we wanna go up and select data from the menu.
  • 00:44 And we’re gonna select Sort range.
  • 00:46 Now, the first choice we need to make here is if we have selected the header
  • 00:51 row or not.
  • 00:52 In this case, I have, so I want to tell it that the data has a header row included.
  • 00:58 If I had just selected the data itself, I would leave this check box unchecked.
  • 01:03 Next, we get to select which of the columns we would like to sort by.
  • 01:08 So by selecting this drop down, I'm gonna select Rep, as in Representative.
  • 01:14 Remember, those are the names in my table that I would like to view this data from.
  • 01:19 Lastly, we get to choose if we would like to view it from A to Z or
  • 01:24 in the reverse, from Z to A.
  • 01:26 And these same options, ascending and descending,
  • 01:30 would apply even if we had selected a numerical value.
  • 01:34 I'm gonna select A to Zed and then I'm gonna select Sort.
  • 01:38 And now, you can see it has sorted all of my information
  • 01:42 based on alphabetical order in the Rep column here.
  • 01:48 Now, let's say if I'd like to look at this form a different way.
  • 01:51 Perhaps I'd like to view this data based on who sold the most units.
  • 01:56 Once again, we're gonna select the entire table > Data > Sort range.
  • 02:04 Again, I'm gonna check data has a header row because I did make that as a part of
  • 02:08 my selection.
  • 02:10 In this time I'm gonna select Units.
  • 02:13 But instead of unit from the least amount of units to greatest,
  • 02:17 I wanna see the most units first.
  • 02:20 So I'm gonna select this radio option.
  • 02:23 I'm gonna select the sort button, and
  • 02:25 now I can see that Jones has sold the most units here of pencils,
  • 02:30 all the way down to the least at the bottom of the table.
  • 02:35 Now, another function that you may wish to perform when dealing with Spreadsheets,
  • 02:40 is the ability to protect certain cells.
  • 02:44 This can come in handy if you don't want yourself or
  • 02:47 others to accidentally change information within a table.
  • 02:52 Let's say for example that I don't want to adjust
  • 02:55 any of the data here within the Units column.
  • 02:59 To do so, I want to select all of this information, and
  • 03:03 once again, we're gonna select Data from the menu.
  • 03:07 This time we're gonna go to Protested sheets and ranges, and
  • 03:12 on the right-hand side you will see a new menu appear.
  • 03:16 We can enter a description for this protected sheet if we want.
  • 03:20 It may be a helpful reminder when we come back to this area in the future.
  • 03:24 I'm just gonna leave it blank for now.
  • 03:27 And we do have the option to protect either a range of cell or
  • 03:31 the entire sheet.
  • 03:33 I'm gonna keep it with range, and
  • 03:34 as you can see, because I've already selected those cells, they appear here.
  • 03:40 Lastly, I'm gonna select Set permissions.
  • 03:43 Now, once I select that option, I have two more choices to make.
  • 03:47 First, is do I want to show a warning when editing this range so
  • 03:52 I will be notified if I go or someone else goes to attempt to edit this range or
  • 03:58 do I want to restrict who can edit this range.
  • 04:01 Perhaps I'm okay if I edit this range but
  • 04:04 I don't want others to edit this range either.
  • 04:08 And you can make those customized settings here.
  • 04:11 In this case, I'm gonna select the first one.
  • 04:14 I'm gonna say, Show a warning when editing this range, and I'm gonna select Done.
  • 04:20 My changes have been saved.
  • 04:22 And now, let's say I go to this cell and I start to type in a new value.
  • 04:27 I hit Enter, and I get a Heads up!, message.
  • 04:31 It says, you're trying to edit part of the sheet that shouldn't be changed
  • 04:35 accidentally, edit anyway?
  • 04:37 I can approve it by saying ,OK, and my new changes will be applied, or
  • 04:42 I can hit cancel perhaps I needed this reminder that I don't wanna be
  • 04:47 changing that information.
  • 04:49 I can also check this checkbox which will not show this message again for
  • 04:54 the next five minutes.
  • 04:56 So depending on what you are trying to change,
  • 04:59 you can decide what is the best choice for you.

Lesson notes are only available for subscribers.

Gift this course