Manage several Google sheets – copy, hide, protect, etc.

Sheets Manager from Power Tools lets you manage multiple Google sheets within one spreadsheet quickly and easyly. You will copy or move multiple sheets from one spreadsheet to another; duplicate, hide/unhide & protect/unprotect multiple sheets; color, rename, reorder or delete sheets; and even see the number of all tabs in the file.

Before you start

If you're about to duplicate, copy, or move sheets, please bear in mind that Google Sheets limits the total number of possible cells in your file up to 5 million cells.

How to use Sheets Manager

Start the add-on

  1. Since Sheets Manager is part of Power Tools, run it from the Google Sheets menu: Add-ons > Power Tools > Start:
    Start Power Tools.
  2. Click the Sheets Manager icon on the Smart Toolbar:
    Sheets Manager on the Smart Toolbar.
    or go to the Process group and you'll see Sheets Manager there:
    Sheets Manager icon in the Process group.

The add-on sidebar will appear with a list of all sheets available in the current spreadsheet along with a toolbar full of different options.

How to select multiple sheets to manage them all at once

Though you can handle each sheet individually, there's an option to select multiple sheets and perform different actions with all of them at once.

You can select several sheets in two ways:

  • Click the Selection mode option:
    Click the Selection mode to pick multiple sheets.
    and you'll see checkboxes before each sheet name. Tick the boxes next to those sheets that you want to manage:
    Select all sheets that you want to handle.

    Tip. Click on Selection mode again to hide checkboxes.
  • Alternatively, you can press and hold Ctrl (to select each sheet individually) or Shift (to select a group of adjacent sheets) on your keyboard while left-clicking on the names of the sheets with your mouse. All selected sheets will be highlighted gray in the add-on tree view:
    Select sheets by keeping Ctrl pressed.

    Tip. You can see how many sheets you selected at the very bottom of the add-on pane:
    3 sheets selected out of 7.

How to rename a sheet

  1. Hover your mouse over the sheet you want to rename and click the Rename icon with a pencil:
    Rename sheet icon.
  2. Enter the new name and press Enter to save it:
    Enter and save a new sheet name.

How to reorder sheets within the file

To move the tab(s) closer to the beginning or to the end of the list, select the sheet(s) and drag & drop them to the required position:
Reorder Google sheets in a file.
Or right-click the selected sheet(s) and make use of 4 options from the context menu:
4 options of the context menu to move the sheet(s).

  • Move up to move the sheets to the left.
  • Move down to move the sheets to the right.
  • Move to top will put the sheets first in the file.
  • Move to bottom will put the sheets last in the file.

How to hide sheets in Google Sheets

There are 2 ways to hide the sheet(s) in Google Sheets:

  • Select all sheets that you want to hide and press Hide on the toolbar:
    The icon to hide the sheets.
    or click on the down-arrow next to the option and choose Hide sheet(s) from there:
    Hide sheets from the drop-down menu.
  • Alternatively, right-click the selected Google sheet(s) and pick Hide from the context menu:
    Hide sheets via the context menu.
    The font color of all hidden sheets will turn light gray:
    Distinguish hidden sheets by their font color.

    Tip. You can view hidden sheets in the add-on list by opening the same drop-down and ticking off Show hidden sheets accordingly:
    Show hidden sheets in the add-on tree view.
    Note. If you select both types of sheets — hidden and unhidden, the option will always offer to Unhide them first. But you can always find both options — Hide sheets and Unhide sheets — by clicking the down-arrow:
    Hide and unhide options in the drop-down menu.
    and in the context menu:
    Hide and Unhide options in the context menu.

How to unhide sheets in Google Sheets

There are 2 ways to unhide hidden sheet(s) in Google Sheets using our Sheets Manager.

Tip. Hidden sheets are written in a light gray font.
  • Select all sheets that you want to unhide and choose the Unhide option on the toolbar:
    Unhide sheets icon on the toolbar.
    or click on the down-arrow next to the option and select Unhide sheet(s) from there:
    Unhide sheets from the drop-down menu.
  • Another way to unhide sheets in Google Sheets is to select them in the add-on tree view, right-click and pick Unhide from the context menu:
    Unhide sheets via the context menu.

    Note. If you select both types of sheets to handle — hidden and unhidden, the option will always offer to Unhide them first. But you can always find both options — Hide sheets and Unhide sheets — by clicking the down-arrow:
    Hide and unhide options in the drop-down menu.
    and in the context menu:
    Hide and Unhide options in the context menu.

How to color-code Google sheets

To color code any tab in the Google spreadsheet, select the sheet in the add-on tree view and click Change color:
Change sheet color tool.
You'll see a color palette offering to pick a color or reset any previously selected one:
Color code Google sheets.

How to lock Google sheets

You can protect (or lock) Google sheets in 2 ways:

  • Select all sheets that you want to lock, click the Protect icon (the one with a padlock) and choose a way to limit the editing:
    2 options for protecting Google sheets.

    • Pick Restrict editing if you want to prevent other users from making any changes to the sheet.
    • Or opt for Show warning if you don't really mind the changes but want other users to see a corresponding warning and confirm if they want to edit.
  • You will also find the same options in the context menu if you right-click any of the selected sheets:
    Protect Google sheets and limit the editing from the context menu of the tool.

As soon as the sheets are protected, a padlock will appear next to their names:
View protected sheets in the add-on tree view.

How to unlock Google sheets or change editing permissions

  1. Select all protected sheets (those with a padlock next to them).
  2. Click the Protect icon (the one with a padlock as well) and either change a way to limit the editing or unlock the sheets completely:
    Restrict editing, show warning or unlock the sheets completely.

    • Restrict editing will prevent other users from making any changes to the sheets.
    • Show warning will make other users confirm if they want to edit the sheet.
    • Unprotect will remove any protection and let any user edit the sheets freely.
Tip. You will see the same options in the context menu if you right-click one of the selected sheets:
Change protection or unlock the sheets from the context menu.
Note. The same options will also be available if you select both types of sheets in the tree view — protected and unprotected.

How to duplicate multiple Google sheets

  1. Select all sheets that you want to duplicate within the current spreadsheet.
  2. Click Copy to > Current spreadsheet:
    Duplicate Google sheet by copying them to the current spreadsheet.

All selected sheets will be duplicated and their copies will appear in your file and at the end of the add-on list.

How to copy Google sheets to another spreadsheet

  1. Select all sheets that you want to copy to another spreadsheet.
  2. Click Copy to and pick one of the options:
    Copy Google sheets to another spreadsheet.

    • Choose Current spreadsheet to duplicate selected Google sheets within the current file.
    • Select New spreadsheet and the add-on will create a brand new file in your Drive and copy chosen Google sheets there. You will be offered a link to open that new spreadsheet right away:
      Get a link to a new spreadsheet where 5 sheets have been copied to.
    • If you opt for Existing spreadsheet, you will need to hand-pick a file to copy the sheets into.
      Tip. There's a Search field available for you to quickly locate the desired spreadsheet just by entering its name / a part of its name:
      Select a spreadsheet to copy the sheets into.
      The add-on looks for matches in your Drive, in all Shared Drives (if any), and among the files that have been shared with you.

      Once selected, hit Copy and wait till you see the confirmation message with a link to open that spreadsheet:
      The resulting message with a link to open an existing spreadsheet.

    • Last but not least is Multiple new spreadsheets. It will copy each selected Google sheet into a completely new spreadsheet. The add-on will create a new folder (named after your spreadsheet) next to your current file in Drive and put all these new spreadsheets there. You will also have a link to go to that new folder quickly:
      Open the new Drive folder straight from the resulting message.

How to move Google sheets to another file

To move several Google sheets to another file entirely (cut from the current file and paste in another one), select the sheets of interest, click the Move to option, and pick a destination:
Options to move sheets.

  • The New spreadsheet option will create a brand new file in your Drive and transfer all selected sheets there. Once it happens, you will get a link to open that new spreadsheet:
    The resulting message after moving sheets to a new file.
  • Existing spreadsheet will first let you select the desired spreadsheet from Drive:
    Select a spreadsheet to move the worksheets into.
    Then it will move all selected sheets there.

    Tip. Instead of browsing from folder to folder manually, make use of the Search field. The add-on will show you all files with partial/complete name matches found in your personal Drive, all Shared Drives (if any), and spreadsheets that have been shared with you.

    You will also have a link to access that file right away:
    Open the existing file with the moved sheets right away.

  • Multiple new spreadsheets will move each selected sheet into a new file. The add-on will create a new folder next to your current file in Drive and put all these new spreadsheets there. You will also have a link to open that new folder named after your spreadsheet quickly:
    Get a link to open a new Drive folder.

How to delete Google sheets

To delete Google sheets from the file, select them in the add-on and click the corresponding icon on the add-on toolbar:
Delete sheets icon.
The sheets will be completely removed from both the spreadsheet and the add-on list.

How to check the number of all sheets in the file

There's a sheet count available in the footer of the add-on:
See selected/all sheets in the file.

  • The former number says how many sheets you selected to handle.
  • The latter shows the total number of sheets in the spreadsheet.

Related pages