Excel Tip: Ten Tips for Working With Multiple Sheets
There are several tricks and shortcuts you can employ when you want to use multiple sheets within one workbook. Here are a few of our favorites - feel free to add a comment and share your own tricks with the crowd!
- Insert new sheets.
Are you unhappy because only three sheet tabs appear at the bottom of your screen? Choose Insert, Worksheet from the menu and add another (and another, and another…). To speed things up a bit, click on Sheet1, hold down Shift, click on Sheet3 and three sheets are selected. Choose Insert, Worksheet and you'll insert three sheets.
- Rename your sheets
Sheet1, Sheet2, Sheet3 - rather boring don't you think? Double-click right on the sheet tab and enter your own descriptive name.
- Rearrange your sheets
You can change the order of the sheets quickly by dragging a sheet tab to the right or left, dropping it off whenever you're satisfied with the new location.
- Switch from one sheet to another
Click on the tab of the sheet you want to view. Prefer the keyboard approach? Ctrl PageUp and Ctrl PageDown move you from one sheet to another and you never have to touch the mouse.
- View more sheet tabs
You may have opened 12 sheets, but only a few tabs are visible at the bottom of the screen. That horizontal scroll bar is hogging all the room. Place your mouse over the left edge of the horizontal scrollbar and drag to the right. More sheet tabs will appear.
- Choose sheets for a group
Having a party and want to invite all (or some) of the sheets? Click the tab of the first sheet to be included. Select adjacent sheets by holding down the Shift key and clicking the tab of the last adjacent sheet. All sheets from first to last will be selected. Select non-adjacent sheets by holding down the Ctrl key while you click on each sheet that goes with the group.
- Turn off the group selection
You're finished treating the selected sheets as a group and want to break up the gang. Click once on any sheet other than the top (active) sheet, and only that sheet will be selected. If it's the top sheet you wanted to click on, hold down the Shift key while you click on that top sheet, and that will deselect the others.
- View multiple sheets simultaneously
It's a drag, so to speak, having to click back and forth from one tab to another. View additional sheets by first opening a new window for each sheet you want to view (choose Window, New Window, for each additional sheet you want to view). Then, choose Window, Arrange, and select from the viewing options of Tiled, Horizontal, Vertical, Cascade. Click OK and each window you created will be visible; you can click on a different sheet tab in each window.
- Remove unwanted sheets
If you were a bit too enthusiastic with inserting new sheets, and find you have more than you need, you can quickly remove a sheet by right-clicking on the unwanted sheet, choosing Delete, and confirming with OK.
- Sum numbers across multiple sheets
Take advantage of having data on multiple sheets by combining numbers across all the sheets in your formulas. Refer to a previous AccountingWEB Excel tip for detailed steps.