This week’s trivia…

Microsoft’s infamous paper clip office assistant, known as Clippy or Clippit, made his debut in Excel 97. Poor little guy was officially kicked out ten years later with the release of Office 2007, because everyone thought he was annoying.

Photo by Eric Ligman

This week’s tip…

In my opinion, one of Excel’s most powerful features is the way it allows different spreadsheets of a workbook to interact with each other. A few years ago in a previous job I was managing the healthcare benefits, so obviously I used Excel to help me stay organized. I had each employee’s FSA spending in a separate spreadsheet, rife with its own set of formulas and beautiful formatting. Once I had that organized, I wanted an all-up view of everyone’s FSA spending for the year, so I added another spreadsheet. I remember sighing with exhaustion when I (mistakenly) thought I would have to click back and forth between each spreadsheet, double-checking every employee’s FSA numbers for every month of the year, and then manually entering them into my all-up chart. Then, I discovered something magical. You can pull data from other sheets than the one your formula sits in. Let me explain…

For the sake of this example I’m only using 2 employees. Here they are.

And here’s my all-up list, waiting to be filled with formulas:

For the total elections, I just want it to copy the data from the corresponding cell in the other sheets. I click on the cell I want the info in and I type the equal sign.

While the cursor is still live (blinking) in that cell, I click on the Employee1 sheet. Note that the formula bar has already updated itself to read and that both the Company and Employee1 sheet tabs are highlighted at the bottom.

Then I click on the cell with the data I want it to pull from. Again notice the formula bar has updated itself.

Then I press the Enter key, and voila! It takes me back to the Company sheet with the data entered.

I do the same thing to pull the information for January.

Now, rather than manually repeating that step, I can simply copy the formula from Jan for the other months of the year. In the Company sheet, I hover over the lower corner of the January cell into my cursor becomes a +, and then I drag it down the other cells in the Employee1 column.

Then I would repeat these steps for Employee2, and so on. You’ll see if you click in any of the cells that the formula maintains the same =Employee1!B# format.

Now, I could obviously just add the numbers using a formula within this sheet, doing the following:

  • I click in the Total Used box, and enter the following formula:
  • I click in the Total Remaining box, and enter the following formula:

However, for the sake of the example, we’re going to pretend that instead I wanted to pull the data from the other sheets in the workbook. I do this the same way I did when I was simply copying the cell data, except now I’m using a formula.

For Employee 2, I click in the Total Used box in the Company tab, and type =SUM(

Then I click on the Employee2 spreadsheet and select the cells with the monthly spending amounts. Note how the formula has automatically adjusted itself.

I press Enter, and it takes me back to the Company sheet, with the data entered!

So you see you can play with these formulas and steps in a variety of ways, and you have all the spreadsheets in a workbook at your disposal to pull the data from. J