- Foundation
- About 12 min
- 5 practice questions
Managing worksheets and moving around quickly
Rename, add, move and protect worksheets, freeze panes so headings stay visible, and use cursor and entry shortcuts.
If something looks wrong, please tell us.
A workbook often grows from a single list into several sheets, and a long list means a lot of scrolling. This lesson covers the tools that keep a workbook tidy and the shortcuts that save time. Most of them are standard in the common spreadsheet programs.
Working with worksheets
- Rename: double-click the sheet tab and type a meaningful name, such as Jan or Marks.
- Insert: add a new sheet with the plus button on the tab bar or the insert command.
- Delete: removes a sheet and its data, so confirm carefully.
- Move or copy: drag the tab, or use the move-or-copy command to place a sheet in another position or make a duplicate.
- Tab colour: colours a tab so that related sheets are easy to spot.
- Hide: hides a sheet, row or column from view without deleting it.
Keeping headings in view
When a list has hundreds of rows, the headings scroll out of sight. Freeze Panes locks chosen rows or columns so they stay visible while the rest scrolls. Freezing the top row is the usual choice. It changes only the view; the data is not altered.
Protecting and printing
Protect Sheet stops people changing locked cells, optionally with a password, which helps keep formulas safe. For printing, you can set a print area so that only the chosen cells print. Gridlines are the faint lines between cells and are normally not printed unless you ask.
Shortcuts for moving and entering
| Keys | Action |
|---|---|
| Ctrl + Home | Go to cell A1 |
| Ctrl + End | Go to the last used cell (bottom-right of the used area) |
| Ctrl + Arrow key | Jump to the edge of the current block of data |
| F2 | Edit the contents of the active cell |
| Alt + Enter | Start a new line inside the same cell |
| Ctrl + ; | Enter today's date |
| Ctrl + A | Select the current block of data; press again for the whole sheet |
| Ctrl + S | Save the workbook |
Worked examples
Example 1. A workbook holds twelve monthly sheets and one summary sheet. It has 12 + 1 = 13 sheets. A summary formula such as =Jan!B5+Feb!B5 adds cell B5 of two of them.
Example 2. A workbook has 3 sheets. The user inserts 2 new sheets and deletes 1. The count is 3 + 2 - 1 = 4 sheets.
Example 3. A mark list has 200 rows and the heading row is row 1. To keep the headings visible while scrolling, choose Freeze Panes and freeze the top row. Scrolling to row 150 still shows row 1.
Example 4. A column holds data in A1 to A10. The user clicks A1 and presses Ctrl + Down arrow. The cursor jumps to the last filled cell, A10, rather than moving one cell at a time.
Example 5. A teacher wants only the marks table, not the working notes, to print. She selects the table and sets it as the print area. Only those cells print.
Freeze keeps headings in view; protect keeps formulas safe; print area limits what prints.
Key takeaways. Rename sheets with clear names, freeze panes for long lists, protect sheets that hold formulas and learn Ctrl + Home, Ctrl + End and F2 for fast movement.
Check your understanding
-
1. Which feature keeps the heading row visible while you scroll a long list?
- Freeze Panes
- Protect Sheet
- Print Area
- Merge Cells
Show answer
Answer: A. Freeze Panes locks rows or columns in view. Protect Sheet stops changes, and Print Area limits printing.
-
2. Which keys take you to cell A1 straight away?
- Ctrl + End
- Ctrl + A
- Ctrl + S
- Ctrl + Home
Show answer
Answer: D. Ctrl + Home goes to A1. Ctrl + End goes to the last cell that holds data.
-
3. A workbook has 3 sheets. Two are inserted and one is deleted. How many sheets now?
- 6
- 4
- 2
- 5
Show answer
Answer: B. 3 + 2 - 1 = 4 sheets.
-
4. Which key lets you edit the contents of the active cell?
- F1
- F5
- F2
- F7
Show answer
Answer: C. F2 puts the active cell into edit mode. F1 opens help, and F7 starts a spelling check in many programs.
-
5. What does Alt + Enter do inside a cell?
- Starts a new line here
- Saves the workbook file
- Moves to the next cell
- Closes the open window
Show answer
Answer: A. Alt + Enter starts a new line inside the same cell. Enter alone would move to the next cell.
Sign in to keep track of the lessons you finish.