11 Techniques to Learn Excel and Lose Weight

Lesson 4 - Sheets

A workbook also known as an Excel file, can have one or more sheets.

A sheet or a WorkSheet usually contains related data

Beginners should work with only one sheet and as you get more advanced work with multiple sheets.

I've had spreadsheets that have had over 150 sheets.

Add a sheet

press the (+) at the bottom

Rename a sheet

Right click the sheet

Select Rename

Type new sheet name

Delete a sheet

Right-click the sheet tab and select Delete

Move a sheet

Select the sheet

Hold down mouse

Move it to its new position

Copy a sheet

Right-click sheet tab

Select Copy...

Select [] checkbox Create a copy

To same workbook do nothing

To different workbook select from the "To book:" drop-down and select "New Book" or name of a workbook

Select "Before sheet of where you want the sheet

If in the same workbook it will add (2) to the sheet name

Find a sheet

Right-click over the | < > |

select the sheet from the Activate box

Find data on a sheet

Press Ctrl+F for the find dialog

If you have multiple sheets you must click Options and change "Within" from Sheet to Workbook

If data is in a hidden row or column the find will fail

Select [Find All] to see a list of all occurrences

Select [Find Next] to find the next item that matches

Match case - if checked "Bob" is not the same as "bob"

Match entire cell contents - if checked will only find the item if an exact match

Tab Color

You can set the color of the tab. This is useful if you want to indicate that the sheet belongs to some category

Right-Click Tab

Tab Color

Select the color from the Color picker

Referencing a sheet

You reference a sheet in formulas. So if data is in Sheet1 and you're on Sheet2 you enter

=Sheet1!A1

Advanced Techniques

The following techniques are a bit to complex for a beginner but come back after you're ready

Add a Sheet

Press Shift+F11

Sheet name is limited to 30 characters

Certain characters aren't allowed (* [ ] \ ? /) - If you type and nothing happens

Sheet name can't already exist, must be unique

Hide/Unhide a sheet

Sometimes you have data that you no longer want to see but don't want to lose so you can hide the sheet. If you send the sheet to someone the data will still be there, so be careful.

You can also store values that you don't want someone accidentally changing.

Right-click the tab(s) to hide and selec Hide.

To unhide you right-click on any tab and then select Unhide. A dialog will display that you can select the sheet to unhide.

Default sheet name

When you add a sheet it will have the default name of "Sheet1"

Create a Table of Contents Sheet

Insert a new sheet (Alt+F11)

Move to the front of the workbook

Rename it Table of Contents or TOC

Copy sheet name to clipboard

Paste in the name to empty cell on the TOC sheet

Press Ctrl+K to create a hyperlink

Select ....

Press OK

Selecting Multiple Sheets

You can select multiple sheets by holding the Shift or Ctrl button down while select sheets.

Shift - Selects a range of sheets

Ctrl - Selects individual sheets that don't have to be consecutive

Hiding/Showing Tabs

You can hide/show the tabs via the Excel Options

Advanced - Display options for this workbook - Show Sheet Tabs

or Alt+F,T,A,Alt+B,Space bar, Ok

Next/Previous Sheet

You can use the shortcut keys Ctrl+PgDn to go to the previous sheet and Ctrl+PgUp to the next sheet.

Sheets FAQ

I entered a sheet name and nothing is happening
Sheet name is too long
Already exists
Has a character that's not allowed/*