As seen in the previous section “What is a worksheet” once we have a brand new worksheet, we can do some commands in regards to that worksheet. To access a list of the quick commands, right click on the name of the worksheet.
List of worksheet commands
- Insert – same as the + icon. Inserts a new Worksheet
- Delete – deletes the selected worksheet (careful with this one)
- Rename – change the name of the worksheet
- Move or Copy – moves or copies the worksheet left or right (it can also be dragged and dropped)
- View Code – reveals additional VBA code related to the worksheet
- Protect Sheet – Protects the sheet for editing
- Tab Color – changes the color of the tab on the bottom
- Hide/Unhide – hides a sheet from view
- Select All Sheets – selects all the sheets in the workbook (in order to perform actions in bulk
How to insert a new Worksheet and more
The Insert action is used to insert a new worksheet. Similar with the + sign, if you press Insert, you get the option to insert a black worksheet. While the + sign immediately inserts a new worksheet (the most common thing to do), with the right-click Insert button, one can insert more items
How to insert an online template
One of the elements you can insert in the second tab is a template. There are local templates or online templates
Using the templates, one can have quick access to worksheets that are commonly used. Like this Loan Amortization Schedule.
How to delete a worksheet
With the Delete option, one can delete worksheets no longer needed. As the warning says, this is a permanent action. There is no option to Undo this. Only Delete something when you are sure it is not needed anymore. If you just want to get it out of the way temporarily, use the Hide option below.
How to rename a worksheet
Rename works very similar with double-clicking on the worksheet name. It will position the cursor in the box for quickly renaming the worksheet.
How to move or copy a worksheet
With Move or Copy you can move or copy the worksheet to the same workbook or to another open workbook. You can also create a copy / duplicate the selected worksheet
How to view the VBA code of a worksheet
Excel has more advanced features of adding VBA (Visual Basic) code to a worksheet. With VBA you can add actions to be done when events are occurring. For example, one can write a code to perform a data refresh when the worksheet is open.
There are many other things that can be done with VBA. You can see more in the VBA section of this site.
Clicking on View Code is one way to access the code attached to this worksheet and to open the integrated VBA editor.
How to protect a worksheet
Protecting a sheet is a quick way to protect certain features of the worksheet with a password. If you share the worksheet with others and you don’t want them to mess up your formulas, your charts or other features, you can protected by clicking on Protect Sheet. You can see from the screenshot below that you can specify what actions can users do (selected ones) and can’t do (unselected ones)
How to change the tab color of a worksheet
The Tab Color option is a quick formatting feature. You can have each tab colored in a different color for better visualization and appearance.
How to hide or unhide a worksheet
Hiding a worksheet is sometimes needed when it contains information that we don’t want people to see. While you can protect a sheet as well as hide it, hiding sometimes can be used just to get sheets that perform calculations out of the way. Maybe you want to share a report with someone but don’t want them to see all the raw data. In that case, you could hide the Data tab but leave the other ones visible.
This way the data is still there but it’s not in the plain view.
To hide a sheet, right click on the sheet tab and click Hide.
To Unhide it, right click on any other sheet that is visible and click “Unhide”. Notice that “Unhide” is only selectable if the workbook has hidden sheets.
Select the Sheet you want to unhide and click ok
How to Select all the Worksheets
With Select all Sheets, one can select all of the worksheets in the workbook and perform some of the actions above. For ex. if you want to protect all the sheets, instead of doing it one by one, you can do “Select All Sheets” and then Protect Sheet