How to work with multiple worksheets in a Microsoft Excel workbook

Learn how to take advantage of Excel's ability to link and sync multiple worksheets in the same workbook.

Image: Thanadol Benyasirisataporn, Getty Images/iStockphoto

If most of your Excel workbooks contain only a single worksheet, you're missing out on the power and flexibility of using multiple sheets in a single workbook. By storing multiple Excel worksheets in the same workbook, you can tie them all together to save time and effort. You can perform the same data and formatting changes on all your worksheets in one fell swoop. You can create formulas in one worksheet that reference data in another sheet. And if any data changes in one worksheet, it also changes in any linked worksheets.

What's Hot at TechRepublic

In this tutorial, I'm using the latest flavor of Excel courtesy of Office 365, but the information offered will work similarly in the past couple of editions of Excel. To illustrate the examples, I created a workbook with five different sheets for product sales. The first four Excel worksheets display results from different regions: North, South, East, and West. The fifth worksheet is for totals from each of the other sheets. Each sheet has columns for the first six months of the year and rows for four different products.

LEARN MORE: Office 365 Consumer pricing and features

The first thing we're going to do is apply formatting to key rows and columns in the five sheets. But instead of formatting each sheet individually, we're going to select them all and format them in one shot.

To select more than one sheet, we're going to use the familiar Ctrl or Shift maneuver. You can be in any sheet for this to work. To select non-contiguous sheets, such as the first sheet, the second sheet, and the fourth sheet, hold down the Ctrl key and click on the tab for the first sheet, then the second sheet, and then the fourth sheet. To select multiple contiguous sheets, hold down the Shift key, click on the tab of the first sheet you want to include, and then click on the tab of the last sheet. In this case, we want to select all five sheets in the workbook. So hold down the Shift key, select the tab of the first sheet, and then select the tab of the last sheet. The sheets are now all selected (Figure A).

Figure A

Let's change the formatting. Select the active cells in the top row. Apply any formatting of your choice, such as a specific style or a combination of attributes (font, size, bold, italics, color, etc.). Do the same with the cells in the first column that contain the product names (Figure B).

Figure B

Click on one of the selected tabs to turn off the selection of them all. Click on each of the tabs, and you'll see that the formatting you applied appears on each sheet (Figure C).

Figure C

We want to total each of the columns. For this, hold down the Shift key. Select the tab for the first sheet and the tab for the fourth sheet. Type the word Totals in the blank cell at the bottom of the first column (Figure D).

Figure D

Move your cursor to the blank cell at the bottom of the second column. Click on the AutoSum icon on the Home Ribbon and make sure the four cells above are referenced in the formula. Press enter to apply the formula (Figure E).

Figure E

Move your cursor to the lower right corner of the cell with the formula. Drag your cursor to the next four cells to copy the formula to each one (Figure F).

Figure F

To turn off the selection, hold down the Shift key and click on the tab for the first sheet. Click on each of the sheets to see that the totals have been applied to all of them (Figure G).

Figure G

We're going to fill the cells in the Totals sheet with the totals from each corresponding cell in the other sheets. To do this, move to the Totals sheet and put your cursor in the first blank cell in the second column. Type an = sign to start a formula. Click on the tab of the first sheet and then click on Cell B2. Notice that the formula says: =North!B2. Click in the Formula bar and type a + sign to continue the formula. Click the second sheet and select B2. Type another + in the formula bar. Continue this process until the formula reads: =North!B2+South!B2+East!B2+West!B2. Press Enter and the formula and total should appear in the Totals sheet in Cell B2 (Figure H).

Figure H

In the Totals sheet, move your cursor to the lower right corner of the cell with the formula. Drag your cursor to the next four cells to copy the formula to each one. Then drag the entire top row of the cells with the formulae to the four rows below to apply the totals to each cell (Figure I).

Figure I

Microsoft Weekly Newsletter

Be your company's Microsoft insider with the help of these Windows and Office tutorials and our experts' analyses of Microsoft's enterprise products. Delivered Mondays and Wednesdays

Sign up today Sign up today

Also see

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.