Linking Worksheets - Excel 2007 - Keyboard

Creating a Summary Sheet in MS Excel 2007

A fairly common task in Excel is to create a worksheet that pulls in data from other worksheets in the same workbook. For example, your workbook may have one sheet for each month with a final worksheet used to summarise values across the whole twelve months.

If you are one of those brave souls that enjoys building your formulae manually, how you deal with this kind of task generally depends on how well organised your data is - especially whether the values you require occupy the same cell across each worksheet. To understand how to tackle some of this, let's run through some examples. First though,we need some data...

Creating the Example Data in Excel

I'm using MS Excel 2007 here so, if you are using something different, you may need to adjust some of the steps:

1. Open MS Excel 2007, by default you will be focused in a new workbook with the standard three worksheets already open. You can move between each worksheet using the keystrokes of CONTROL PAGE DOWN and CONTROL PAGE UP.
2. Focus on sheet one and move the cursor to cell A1. (In any worksheet remember that the cursor is moved using the ARROW KEYS while you move to a specific cell with CONTROL G.)
3. In cell A1, type January then press ENTER to automatically move to cell A2. Type in a number here and press ENTER again to move to cell A3. Repeat the process a few times, so that you have a bunch of figures in a few of the cells in column A. I have chosen to populate cells A2 on to cell A6.
4. Use ALT to move to the upper ribbon. Put focus on the Home tab if it isn't already then DOWN ARROW to the lower ribbon. Press TAB to move all the way along to the Format option in the Cells Group Box.
5. With focus on the Format option use the SPACEBAR to open the menu. ARROW DOWN to the Rename Sheet option and press SPACEBAR to confirm.
6. Type in the new name - I chose January for mine - and press ENTER to finish.
7. Now use CONTROL PAGE DOWN to move to the second worksheet and repeat the process, but this time around replace January with February.
8. With the worksheet for February complete, move to the final sheet - this will be the summary sheet. With that in mind, label the worksheet "Summary".
9. When you have finished you should have a three sheet workbook. Sheet 1 is titled January, with a heading in cell A1 and, directly underneath that, a few cells with some numbers. Sheet 2 is similar, except it is titled and headed up for February. Sheet 3 is titled Summary and should be blank.
10. With all that in place make sure the workbook is saved. Then focus back into cell A1 in the third worksheet.

Referencing a Single Cell Value in a Different Worksheet

Sometimes you only need to pull a value into the spreadsheet from a specific cell in a different worksheet. In this case you would use the following:

=Sheet_Name!Cell_Reference (equals sign, sheet name, exclamation mark, cell reference)

Okay, try inputting the following examples to get some confidence building the formula manually:

=January!A2
=February!A3

In the first example, what you should find is that the value in cell A2 on the January worksheet is pulled through to the cell in worksheet three. The second example will return the value in cell A3 in the February worksheet. Use the data you have created to experiment with another few examples of your own - remember that you can easily delete any cell content using the DELETE key.

Creating a Same Cell Summary Total

Expanding on the above you can easily create summary totals across your group of worksheets by calling on the SUM function. If you have anything in cell A2 on the Summary sheet delete it first then input the following to test it out - the formula should add up the values that exist in cell A2 across the two worksheets:

=SUM(January:February!A2)

Notice with this formula you define the range across the worksheets first and then, after the exclamation mark separator, define the specific cell on each worksheet you require the sum of.

This technique is commonly used in workbooks that have one sheet for each month. For example, this formula =SUM(January:December!A2) will add all of the values in each A2 cell across the January to December worksheets. So, mental note to self, make sure you design your worksheets consistently because using a range with the SUM function makes things a lot easier!

Creating a Different Cell Summary Total

But what if you need to total values that aren't located in the same cell across your worksheets? In this case, things do become a little bit more inelegant and the best way to demonstrate this is with a fitting example using our data. Try this for size:

=SUM(January!A2,January!A4,January!A5,February!A2,February!A3)

Nothing special going on there, it is essentially just one long list of the different cells across the different worksheets. Now, while it is valid and certainly does the job, the more you have to type in the more potential there is for typo's. However, if you have no other option...

It's a Wrap

Let's finish up here by going over the three approaches I've covered in this post one more time:

1. =January!A2 - Returns the specific value of cell A2 on the worksheet titled January.
2. =SUM(January:February!A2) - Gives the sum of the values in cell A2, across the range of worksheets titled January to February.
3. =SUM(January!A2,January!A4,January!A5,February!A2,February!A3) - Returns the sum of the specific cells listed, across the different worksheets indicated.

Now it is over to you, happy experimenting with those summary worksheets!