How to work with multiple worksheets in a Microsoft Excel workbook

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

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

Image: Thanadol Benyasirisataporn, Getty Images / iStockphoto

If most of your Excel workbooks contain only one worksheet, you are losing the power and flexibility of using several worksheets in a single workbook. By storing multiple Excel worksheets in the same workbook, you can link them all to save time and effort. You can make the same data and format changes in all your worksheets at once. You can create formulas in a worksheet that refer to data in another sheet. And if any data changes in a worksheet, it also changes in any linked worksheet.

What's Hot at TechRepublic

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

MORE INFORMATION: Prices and features of Office 365 Consumer

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

To select more than one sheet, we will use the well-known Ctrl or Shift maneuver. It can be on 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 of the first sheet, then the second sheet and then the fourth sheet. To select several contiguous sheets, hold down Shift, click on the tab of the first sheet you want to include, and then click on the tab on the last sheet. In this case, we want to select the five sheets of the workbook. Hold down Shift, select the first sheet tab and then select the last sheet tab. Now all sheets are selected ( Figure A ).

Figure A

  figure-a.jpg "data-original =" https://tr4.cbsistatic.com/hub/i/ r / 2019/08/21 / 2be5095d-b8bc-4718-a93a-688087803ca6 / resize / 770x / a9cc8ab13b74ce1a580aeaca58c6b3ab / figure-a.jpg

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

Figure B

  figure-b.jpg "data-original =" https: // tr3 .cbsistatic.com / hub / i / r / 2019/08/21 / 8d1cb1c2-dbdb-4d4b-89d0-91496d564d37 / resize / 770x / a2ebdae0268c1fc2cc18d76db3a5173c / figure-b.jpg

Click on one of the selected tabs to disable the selection of all of them. Click on each of the tabs and you will see that the format you applied appears on each sheet ( Figure C ).

Figure C

  figure-c.jpg

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

Figure D

  figure-d.jpg "data-original =" https: // tr1 .cbsistatic.com / hub / i / r / 2019/08/21 / 46370e6f-d793-40df-bf34-a2fd0458da26 / resize / 770x / 4e65efe5986ca942cd40d85e20df3470 / figure-d.jpg

Move the cursor to the blank cell at the bottom of the second column. Click on the Autosuma icon on the Starter Ribbon and make sure that the previous four cells are referenced in the formula. Press Enter to apply the formula ( Figure E ).

Figure E

  figure-e.jpg "data-original =" https://tr1.cbsistatic.com/hub/i/ r / 2019/08/21 / 72ca477d-79c1-4b02-b95d-03e39bcfe5bb / resize / 770x / 5d6d6786085bf1fd9e1dd557431fb00f / figure-e.jpg

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

Figure F

  figure-f.jpg "data-original =" https: // tr4 .cbsistatic.com / hub / i / r / 2019/08/21 / 0ed4c7cc-c445-45db-accb-637b3fec2dc5 / resize / 770x / 4253df9ece1f755bbe5cc0be5f8ec78d ​​/ figure-f.jpg

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

Figure G

  figure-g.jpg "data-original =" https: //tr2.cbsistatic.com/hub/i/r/2019/08/21/377425c5-59e9-42f7-8b4c-f8763bc87db7 /resize/770x/af78f018095830b0c33c93beb908de59/figure-g.jpg

See also

Affiliate Disclosure: TechRepublic can earn a commission for the products and services shown on this page.

For More Updates Check out Blog, Windows Softwares Drivers, Antivirus, Ms Office, Graphic Design Don’t Forget to Look Our Facebook Page Get Into Pc like us & follow on Twitter- @getinpc

About The Author