Welcome to Managing Sheets and Data. In this section we will cover the following topics:
Insert, Rename, and Delete Sheets
Duplicate, Move, and Hide Sheets
Sort Data
Filter Data
Data Validation
Protect Spreadsheets
Insert, Rename, and Delete Sheets
Spreadsheets can contain multiple sheets, so you can group similar data together.
Insert a Sheet
You may need to add additional data to a spreadsheet that belongs in a separate sheet.
Click + to the left of the sheet tabs.
Insert, Rename, and Delete Sheets.
A new sheet is added to the spreadsheet.
Rename a Sheet
By default, sheets are given generic names, such as Sheet 1, Sheet 2, etc. You can give them more meaningful names.
Click the sheet tab you want to rename.
Click the sheet tab’s arrow.
Select Rename.
Insert, Rename, and Delete Sheets.
Enter a new name and press Enter.
Insert, Rename, and Delete Sheets.
The sheet is renamed.
Delete a Sheet
If you no longer need a sheet, delete it from the spreadsheet.
Click the sheet tab you want to delete.
Click the sheet tab’s arrow.
Select Delete.
Insert, Rename, and Delete Sheets.
Click OK.
Insert, Rename, and Delete Sheets.
The sheet is deleted.
Duplicate, Move, and Hide Sheets
If an existing sheet contains information you need in a new sheet, you can duplicate it and save time with data entry.
Duplicate a Sheet
Click the sheet tab you want to duplicate.
Click the sheet tab’s arrow.
Select Duplicate.
Duplicate, Move, and Hide Sheets.
(Optional) Rename the duplicate sheet.
Duplicate, Move, and Hide Sheets.
The selected sheet is moved or duplicated.
Move a Sheet
It’s easy to change the order of sheets in a spreadsheet.
Click and drag the sheet to the desired location.
Duplicate, Move, and Hide Sheets.
The sheet is moved to a new location.
Hide a Sheet
When you have a spreadsheet that’s shared with other people, you may want to hide certain sheets so information isn’t visible.
Click the sheet tab you want to hide.
Click the sheet tab’s arrow.
Select Hide sheet.
Duplicate, Move, and Hide Sheets.
You can also right-click a sheet tab and select Hide sheet from the menu.
Sheets hides the sheet. Keep in mind, this doesn’t protect the spreadsheet. Anyone with access to the file can easily unhide the sheet.
Unhide a Sheet
If you no longer want the sheet hidden, you can make it visible once again.
Click View on the menu bar.
Select Hidden sheets from the context menu.
Select the sheet you want to unhide.
Duplicate, Move, and Hide Sheets.
You can also right-click any sheet tab and select Unhide from the menu.
The sheet is unhidden.
Sort Data
When you sort the data in your sheets, it’s easier to quickly find what you’re looking for.
Sort by One Column
Let’s first check out how to sort by a single column.
Select a cell in the column you want to sort.
Select Data on the menu bar.
Select a sort order.
Sort A to Z: Sorts the column in ascending order.
Sort Z to A: Sorts the column in descending order.
Sort data
The column’s data is sorted.
Sort by Multiple Columns
You can also sort data within multiple columns.
Select the cell range you want to sort.
Select Data on the menu bar.
Select Sort range.
Sort data
The Sort range dialog box opens. Here, we have to specify which column we want to sort the range by.
(Optional) Check the Data has header row check box.
Select the first column you want to sort by and select a sort order.
Click Add another sort column to sort by additional columns.
Sort data
Click Sort.
Sort data
The data is sorted within the selected columns.
Filter Data
By filtering data, you can view only the information that’s important. This is really helpful when you’re working in huge spreadsheets.
Filter Data
Select a cell within a data range.
Click Data on the menu bar.
Select Create a filter.
Filter Data
A filter button is added to each column header.
Click the filter arrow for a column.
Specify the items you want to filter.
Click OK.
Filter Data
The data within the range is filtered based on the criteria you selected.
Advanced Filters
You can also set up advanced filters that filter data by specific conditions. For example, you can filter data so that only values that are greater than a certain amount will appear.
Click the filter arrow for a column.
Click Filter by condition.
Click the condition list arrow.
Select a condition.
Filter Data
Set that condition’s value.
Click OK.
Filter Data
The data is filtered and only shows the values that meet the conditions you specified.
Clear a Filter
If you no longer need the filter, clear it to see all of the data again.
Click Data on the menu bar.
Select Turn off filter.
Filter Data
The filter is removed, and you can see all the cells in the data range again.
Data Validation
You can help users enter accurate and appropriate information into your spreadsheets with Sheets’ data validation feature. Data validation restricts the type of information that can be entered in a cell and can provide the user with instructions for entering information in a cell.
Create a Validation Rule
Select a cell or cell range.
Click Data on the menu bar.
Select Data Validation.
Data validation.
Click the Criteria list arrow.
From this list, we can choose to create a list of values to select from, or require numbers, text, or dates. You can also create a formula to validate data, or create a check box cell.
Select a criterion.
Data validation.
Configure the selected criteria options.
Here we can select whether to require numbers to be in a range, not in a range, or less than, greater than, or equal to a number.
Choose how to handle invalid data.
(Optional) Add validation help text.
Data validation.
Click Save.
Data validation.
If we enter a value outside of that range, an error marker will appear in that cell. Hovering your mouse cursor over it will display the error message we created.
Edit or Remove Data Validation
You can also edit any data validation rules that you’ve created, or remove them altogether.
Select the cell or cell range that contains the data validation.
Click Data on the menu bar.
Select Data Validation.
Data validation.
Edit or remove the existing validation.
Data validation.
The data validation rule is updated or removed.
Protect Spreadsheets
Prevent unauthorized changes to data by protecting your spreadsheets. In a protected spreadsheet, none of its contents can be changed. However, it’s also possible to set up the spreadsheet so that only certain cells and elements can be changed after it’s protected.
Protect a Spreadsheet
Click Data on the menu bar.
Select Protected sheets and ranges.
The Protected sheets & ranges pane opens on the right.
Click Add a sheet or range.
Protect spreadsheets.
(Optional) Enter a description.
Specify what you want to protect:
Range: Protect a cell or cell range from edits.
Sheet: Protect a sheet from edits. To leave a range of cells unprotected, check the Except certain cells box.
Click Set permissions.
Protect spreadsheets.
Specify how you want to limit editing:
Show warnings when the spreadsheet is edited.
Specify who can edit the spreadsheet.
Click Done.
Protect spreadsheets.
The spreadsheet is protected and no changes can be made to the specified content.
Edit or Delete Protection
You can also edit which areas of your spreadsheet are protected, or delete protection from your spreadsheet altogether.
Click Data on the menu bar.
Select Protected sheets and ranges.
The Protected sheets & ranges pane displays the protected ranges in this spreadsheet.
Click the protected area you want to edit or delete.
Protect spreadsheets.
Edit the protection or delete it by clicking the trash icon.
Protect spreadsheets.
The protection is edited or removed from the spreadsheet.