The way to work with a number of worksheets in a Microsoft Excel workbook

Discover ways to reap the benefits of Excel’s means to hyperlink and sync a number of worksheets in the identical workbook.

Top rear view of accountant man and woman working on computer analyzing income statement

Picture: Thanadol Benyasirisataporn, Getty Photos/iStockphoto

If most of your Excel workbooks include solely a single worksheet, you are lacking out on the ability and adaptability of utilizing a number of sheets in a single workbook. By storing a number of Excel worksheets in the identical workbook, you possibly can tie all of them collectively to save lots of effort and time. You may carry out the identical information and formatting adjustments on all of your worksheets in a single fell swoop. You may create formulation in a single worksheet that reference information in one other sheet. And if any information adjustments in a single worksheet, it additionally adjustments in any linked worksheets.

What’s Sizzling at TechRepublic

On this tutorial, I am utilizing the newest taste of Excel courtesy of Workplace 365, however the info provided will work equally up to now couple of editions of Excel. For instance the examples, I created a workbook with 5 completely different sheets for product gross sales. The primary 4 Excel worksheets show outcomes from completely different areas: North, South, East, and West. The fifth worksheet is for totals from every of the opposite sheets. Every sheet has columns for the primary six months of the 12 months and rows for 4 completely different merchandise.

LEARN MORE: Workplace 365 Shopper pricing and options

The very first thing we’ll do is apply formatting to key rows and columns within the 5 sheets. However as an alternative of formatting every sheet individually, we’ll choose all of them and format them in a single shot.

To pick multiple sheet, we’ll use the acquainted Ctrl or Shift maneuver. You might be in any sheet for this to work. To pick non-contiguous sheets, resembling the primary sheet, the second sheet, and the fourth sheet, maintain down the Ctrl key and click on on the tab for the primary sheet, then the second sheet, after which the fourth sheet. To pick a number of contiguous sheets, maintain down the Shift key, click on on the tab of the primary sheet you need to embody, after which click on on the tab of the final sheet. On this case, we need to choose all 5 sheets within the workbook. So maintain down the Shift key, choose the tab of the primary sheet, after which choose the tab of the final sheet. The sheets are actually all chosen (Determine A).

Determine A

figure-a.jpg

Let’s change the formatting. Choose the lively cells within the prime row. Apply any formatting of your selection, resembling a particular model or a mixture of attributes (font, measurement, daring, italics, colour, and so on.). Do the identical with the cells within the first column that include the product names (Determine B).

Determine B

figure-b.jpg

Click on on one of many chosen tabs to show off the choice of all of them. Click on on every of the tabs, and you may see that the formatting you utilized seems on every sheet (Determine C).

Determine C

figure-c.jpg

We need to whole every of the columns. For this, maintain down the Shift key. Choose the tab for the primary sheet and the tab for the fourth sheet. Sort the phrase Totals within the clean cell on the backside of the primary column (Determine D).

Determine D

figure-d.jpg

Transfer your cursor to the clean cell on the backside of the second column. Click on on the AutoSum icon on the Residence Ribbon and ensure the 4 cells above are referenced within the method. Press enter to use the method (Determine E).

Determine E

figure-e.jpg

Transfer your cursor to the decrease proper nook of the cell with the method. Drag your cursor to the following 4 cells to repeat the method to every one (Determine F).

Determine F

figure-f.jpg

To show off the choice, maintain down the Shift key and click on on the tab for the primary sheet. Click on on every of the sheets to see that the totals have been utilized to all of them (Determine G).

Determine G

figure-g.jpg

We will fill the cells within the Totals sheet with the totals from every corresponding cell within the different sheets. To do that, transfer to the Totals sheet and put your cursor within the first clean cell within the second column. Sort an = signal to begin a method. Click on on the tab of the primary sheet after which click on on Cell B2. Discover that the method says: =North!B2. Click on within the Formulation bar and kind a + signal to proceed the method. Click on the second sheet and choose B2. Sort one other + within the method bar. Proceed this course of till the method reads: =North!B2+South!B2+East!B2+West!B2. Press Enter and the method and whole ought to seem within the Totals sheet in Cell B2 (Determine H).

Determine H

figure-h.jpg

Within the Totals sheet, transfer your cursor to the decrease proper nook of the cell with the method. Drag your cursor to the following 4 cells to repeat the method to every one. Then drag the complete prime row of the cells with the formulae to the 4 rows beneath to use the totals to every cell (Determine I).

Determine I

figure-i.jpg

Additionally see

Affiliate disclosure: TechRepublic might earn a fee from the services and products featured on this web page.

Leave a Reply

Your email address will not be published. Required fields are marked *