Software

Teach your Excel users not to fear math across the sheets

To help keep your Excel users from trying to fit too much information into a single worksheet, follow this tutorial. It explains how a TechRepublic member's budget problem was solved by performing calculations in one sheet using data from another.


Recently a TechRepublic member we’ll call CJ wrote to me asking for help with her budget spreadsheet. CJ had set up a workbook with four sheets, one sheet for each quarter of 2003. In each sheet, she entered the dollar amounts budgeted to be spent by each department.

CJ wanted to be able to track the actual amounts someplace and see the differences between budgeted and actual in another place. I recommended a solution I thought was pretty straightforward:
  1. Make copies of the budgeted sheets, and enter the actual dollars spent in the copies.
  2. Make another set of copies of the original sheets to store the differences.
  3. Calculate the differences with formulas that subtract the values in the budgeted sheets from the values in the actual sheets.

That solution sounded reasonable to CJ, but she had one question: “How do you perform calculations across worksheets?”

CJ isn’t alone. A lot of experienced spreadsheet jockeys try to cram too much data into a single worksheet because they don’t realize that “trans-sheet” calculations are possible. In this tutorial, I’ll explain how to use a formula in one sheet to refer to data in another sheet. (I’ll use Excel 2000 to capture the screen shots, but the technique is identical for all versions of Excel.)

A sheet by any other name
Here’s the executive summary of the technique. To refer to a cell in another sheet within the same workbook, use this format:
sheet_name!cell_reference

The exclamation point is required punctuation. Replace sheet_name with the name of the sheet and cell_reference with a cell address.

Let’s look at a simple example. The default sheet names are Sheet1, Sheet2, and so on. Suppose you’re working in Sheet2 and you want to refer to the entry in cell A2 in Sheet1. To do so, you’d use an expression in the form +Sheet1!A2.

Now let’s see how this technique helped CJ accomplish her mission.

The budget example
To illustrate how trans-sheet math works, let’s look at CJ’s project. Figure A shows a simplified version of CJ’s budget spreadsheet. (CJ’s actual sheet had nearly a hundred line items and dozens of departments.)

Figure A
I’ll use this sample data to illustrate how to set up trans-sheet calculations.


To make a copy of the sheet named Q1Budgeted, just right-click the sheet tab and then choose Move Or Copy from the context menu. When the Move Or Copy dialog appears, click the option (Move To End) and activate the checkbox labeled Create A Copy, as shown in Figure B.

Figure B
Right-click a sheet tab and choose Move Or Copy to display this dialog.


When you click OK, Excel will create a copy of the current sheet and place the copy at the end of the list of worksheets, as shown in Figure C. In CJ’s case, she used Q1Actual for the second sheet. (To rename a sheet, just double-click the tab, type the new name, and press [Enter].)

When the actual numbers start coming in from the departments, all CJ has to do is go to the Q1Actual tab and key in those values over the top of the budgeted numbers.

Figure C
After copying the worksheet, you can rename it by double-clicking the tab and typing a new name.


At this point, you create one more copy of the original worksheet and name it Q1Difference. Then, in cell B2, enter the formula
+Q1Budget!B2-Q1Actual!B2

The beautiful part about this formula is you only have to enter it once! Then copy it to all of the other cells under the “department” columns. The sheet references stay the same, but Excel automatically adjusts the cell references relatively so that each copy of the formula refers to the correct cells in the other two sheets.

Figure D shows what our Q1Difference sheet looks like after we copied the formula. At this point, all the values in the budgeted and actual sheets are identical, so they zeroed each other out in the difference sheet. However, as soon as you start entering different values in the actual sheet, the trans-sheet calculations will be updated automatically.

Figure D
We entered our trans-sheet formula one time and then copied it to the other cells in our difference sheet.


Another example: Year-to-date calculations
Here’s another popular application of trans-sheet math. Suppose you store data in sheets named for each month, and you want to generate a year-to-date total. In your YTD sheet, you’d use a formula in the following form:
=SUM(January!B2,February!B2,March!B2,April!B2,May!B2,June!B2,July!B2, August!B2,September!B2,October!B2,November!B2,December!B2)

If you want to average the same set of values, you’d just use the AVERAGE function instead of SUM.

About moving sheets around
Here’s another tip that can help you manage multiple sheets. Suppose you want to rearrange the sheets within your workbook. To do so, just click-and-drag the sheet tab. As Figure E shows, when you drag the mouse, the cursor will change to an arrow with a blank page behind it. In addition, a black arrowhead facing down will indicate the position where the sheet will “land” when you release the mouse.

Figure E
The circled area shows what the cursor looks like when you click-and-drag to move a sheet to a new position.


Why not do all the calculations in one sheet?
Some of you may wonder why CJ didn’t just put all of her budgeted and actual amounts in the same sheet. Then she wouldn’t have to worry about performing trans-sheet math, right?

Theoretically, CJ could have crammed all of her data into one sheet. However, she wanted to make it easy to print all three sets of values in separate reports. If she had stored the budgeted, actual, and difference values in one sheet, she would have been forced to set up different print ranges or hide columns when she wanted to print one of the three sets of values.

The trade-off, of course, is that she now has 12 sheets in the budget workbook: a budgeted, actual, and difference sheet for each of the four quarters. But she thinks that’s easier to manage than hundreds of columns in a single sheet.

About named ranges
Some of you may wonder whether you can refer to named ranges across sheets, and, of course, you can. Here’s the skinny. Suppose you’ve assigned the range name myvalue to cell A2 in Sheet1. A range name can be used only once in a workbook. So in any sheet, you can enter +myvalue, and Excel will return whatever is in the cell you named myvalue.

It doesn’t hurt anything to include the sheet reference to access the data in a named range. For instance, you could use +Sheet1!myvalue, and you’d get the same result as if you’d used +myvalue. However, with named ranges, the sheet reference isn’t necessary.

Marks of Excel-lence
To comment on this column, ask an Excel question or share some Excel advice of your own, please post a comment or drop us a note.

 

Editor's Picks

Free Newsletters, In your Inbox