Manipulating Sheets in Excel, Part II
In last week's AccountingWEB Weekly Earful, we discussed methods for manipulating the sheets within an Excel workbook file. There is a lot more that can be said on the subject, so we've decided to have another Sheets session this week.
You've already learned about what those sheet tabs mean, the little Sheet1, Sheet2, Sheet3 tabs that appear at the bottom of your spreadsheet. In Manipulating Sheets in Excel, Part I, we talked about how to:
move among the sheets
Increase the number of sheets
Decrease the number of sheets
Rearrange the sheets
Change the default for the number of sheets
In this session, we will learn about creating formulas that refer to data from different sheets, using the mouse to refer to cells on other sheets in your formulas, and a quick multi-sheet formula trick. We'll also learn about how to format multiple sheets in a workbook file.
Creating a formula that refers to data from another worksheet
You can make a reference to a cell in another sheet by typing the cell reference yourself or by clicking on the cell when it's time for that cell to be referenced in your formula. When entering the cell reference, the format is: SheetX!CellCR, where SheetX is the name that appears on the sheet tab containing the cell to which you wish to make reference, C is the column designation of the cell, and R is the Row designation of the cell. The exclamation point separates the sheet name from the cell reference.
For example, if you want to use the contents of cell B12 on Sheet4 in a formula on Sheet1, refer to that cell as Sheet4!B12 in your formula.
Saving time with the mouse
The mouse can help you construct formulas that refer to cells in other worksheets. An easy way to enter the cell reference in your formula rather than typing it yourself is to go to the cell and click on it while you are constructing the formula.
For example, if you want to create a formula in cell C2 on Sheet1 that multiplies the cell B12 of Sheet4 by cell A26 on Sheet1, follow these steps to construct the formula:
- With Sheet1 active, click on cell C2, then begin entering your formula by typing the equal (=) sign.
- Click the sheet tab for Sheet4, making that the active sheet. With Sheet4 visible, click on cell B12. Notice the construction of the formula as it appears in the formula bar at the top of the screen: at this pint the formula bar displays =Sheet4!B12.
- Continue the formula by entering the * multiplication sign. The formula construction proceeds. Sheet4 remains the active sheet.
- Type A26, or click the sheet tab for Sheet1 and click on cell A26.
- Press ENTER or click the check mark that appears on the formula bar to complete the calculation.
Note: If you press enter in Step 5, your cellpointer moves one cell lower, to cell C3. If instead you click the check mark to complete the formula, your cellpointer remains in cell C2 where the formula resides.
Create a quick formula that sums the same cell in several sheets
One of the nicest results of working with groups of sheets is the ability to create formulas that drill down through the layers of your sheets, extracting the contents of a single cell or a group of cells from all selected sheets. You work with cells in multiple sheets in the same way as you work with cells in a range on a two-dimensional sheet, by referencing the first and last cell of the range and letting Excel include all the cells in-between.
When preparing to add the contents of the same cell on several sheets, think of the cell as a range of cells that spans the collection of sheets. Think of calling for the contents of cells A1 on Sheet1, Sheet2, Sheet3, and Sheet4 as using the contents of cells A1 on Sheet1 through Sheet4. The cells still form a range, but the range drills down through four sheets instead of across some cells on a single sheet.
Enter the addition formula that will add cells A1 in Sheet1 through Sheet4 and place the contents in cell A1 of Sheet5 by following these steps:
- Click on the cell in which you wish the formula to appear. In this case, click on cell A1 in Sheet5.
- Begin the formula by entering =sum( or by clicking on the AutoSum button on your toolbar.
- Click on the tab containing the first cell in the range, and then click on the cell you wish to include in the formula. In this case, click on the Sheet1 tab and on cell A1.
- Holding down the SHIFT key, click on the tab of the last sheet on which there is a cell you wish to include in the range. In this case, click on the Sheet4 tab.
- Complete the formula by pressing ENTER, or by clicking the check mark that appears on the formula bar, or by clicking on the AutoSum button again.
Formatting several worksheets at once
Before applying any formatting that will appear on several sheets, select the sheets as a group.
Select adjacent sheets for inclusion in your group by clicking on the tab of the leftmost sheet in the group, holding down the SHIFT key, and clicking on the tab of the rightmost sheet in the group. The two sheets on which you clicked, as well as all the sheets in between, are members of the group. The tabs of grouped sheets change color when they are selected.
To include nonadjacent sheets in your group, hold down the CTRL key while clicking the tabs of additional sheets you wish to include. Each sheet on which you click will be included in the group.
When all sheets to receive the formatting have been selected, apply your formatting on one of the selected sheets. Anything you enter on one sheet will appear on all the sheets in the selected group. This includes type styles, such as boldface or italics, font selection, justification, such as centering or right-alignment, number formatting, shading, borders, underlining, and so on. In addition, any text or numbers you enter on one sheet will appear on all sheets in the selected group.
When you have made all of your universal entries, turn off the group selection by holding down the SHIFT key and clicking once on the tab of the active sheet. At this point, any additional formatting or entries that you make will apply to only the active sheet.
Copy formatting from existing sheets to new sheets
It's really easy to copy formatting to a new sheet within your workbook. Follow these steps:
- Click the Select all button in the upper left corner of a formatted worksheet. This is the little box that appears to the left of the letter for Column A and above the number for Row 1.
- Click on the Format Painter button on the Home toolbar.
- Click on the sheet tab of the worksheet that is to receive the formatting.
- Click on the Select All button of the worksheet that is to receive the formatting. All of the formatting (and none of the text or number entries) from the original worksheet will be copied to the new worksheet.