Right Wing Nation

Working with multiple worksheets

I discussed spreadsheet design, where I said:

Separate related data on different worksheets. If you have charted your data, place the chart on its own worksheet. If you have the same data for different months, place each month on its own worksheet. Label the worksheet tabs (right-click on the worksheet tab) clearly, so nobody has to guess what each may hold (note that this includes you, when you find that file you created a few months ago, and open it).

I’m going to expand upon that here.

Excel allows you to work with more than one worksheet at a time. We call this manipulating multiple worksheets. You can copy cell contents and formatting across worksheets, and yes, you can even calculate across worksheets. This is an extremely useful, time-saving feature.

This is the first of several times I’m going to say this, and please, when I say it, pay close attention: This is potentially dangerous. Stay awake, and if you are working with multiple worksheets, glance at the bottom of the screen before you do anything, to see if your worksheets are grouped or not.

To show you what I mean, let’s say we are keeping a spreadsheet for our department, in which we list all of our department’s sales staff, their total sales, and calculate their earned commissions. Because we have organized our spreadhseet logically, we have each month on its own worksheet. Here is July 2006:

Look at the graphic, first at the bottom of the screen. You can see that the worksheet tabs are labeled, this one, Jul-06. The next is Aug-06, then Sep-06, up to Dec-06 (we’ll get to that last worksheet in a few minutes).

We have the commission rate at the top. Below it, we have, from left to right in columns A, B, and C, Employee ID (so we can identify the employee), Sales Total (for that month), and Commission. If we click on another worksheet, say Dec-06, you see that it looks like a mirror image of the Jul-06 worksheet, save that the commission rate is different, and of course, the sales totals are different (because it’s a different month).

This looks like a lot of work. After all, there’s all that redundant information, like the labels, and the employee IDs. But it wasn’t a lot of work. I only typed it in once, and filled it across all the worksheets. I’ll recreate the Excel spreadsheet so you can see how I did it.

 

Grouping and ungrouping worksheets

Just like you can select a range of cells, you can select a group of worksheets. First, label the worksheet tabs (either right-click on the tab and click rename, or double-click on the tab).

Before we go on, it’s time for that warning again: This is potentially dangerous. When working with multiple worksheets, keep an eye on the worksheet tabs so you always know if worksheets are grouped or not.

Click on the first worksheet (Jul-06 in the example) to select it. Then while holding the SHIFT key down, click on the last worksheet you want to group (Jul-Dec-06 here). All of those worksheets are now highlighted white, as you can see:

Now, pay close attention. When you have worksheets grouped, what you do to one, you do to all of them (by the way, if you want to group non-contiguous worksheets, hold down the CTRL key while you select them). This is what makes this dangerous: If you delete the contents of one worksheet while you have it grouped with others, the corresponding data in ALL of the worksheets will be deleted. Like I said, be careful.

To ungroup worksheets, either click on a worksheet that is not grouped, or right-click, then click on ungroup. You need to know how to do that before we move on, but for now, we’re going to leave our worksheets grouped.

Now, I go to one of the worksheets — any one, it doesn’t matter, because what I do to one, I do to all — and I type in all of the labels. If I do that, then go to one of the other grouped worksheets, all of the lables are there, in the corresponding cells! Then, I type in the employee IDs, because they are the same on all of the worksheets. I then add any formatting, like centering and bolding labels, shading and putting a border around the commission rate cell, and adjusting column widths.

I’m going to do something that might seem strange. In cell C5, I’m going to put in the formula to calculate the commission (=B5*$B$2), then I’m going to grab the autofill handle (that little square in the lower right corner of the cell) and drag it down. And of course, because I had not entered any data for sales total, I get all zeroes.

Quick, can anybody tell me why I did that rather odd thing, that is, why I didn’t type in the sales totals first?

That’s right. Because the sales totals are going to be different for each month, but the commission is calculated the same way.

Now, it’s time to put in the sales totals, but since they are different each month, it’s absolutely mandatory that I ungroup the sheets first.

Once I’ve added the sales total data, I have all six months entered, calculated, organized, and formatted. If I had forgotten to ungroup my worksheets first, I would now be extremely frustrated that I had wasted all that work. Also, since the commission rate changed a couple of times, I’ll go to the appropriate worksheets and change those rates.

Now, let’s address that last workeet, labeled Jul-Dec-2006. It looks almost the same, but with a few important differences.

This worksheet is for the annual totals, so there is no commission rate. The sales totals and commissions are larger, because they’re the totals of July through December (yes, I know, I should have created worksheets for all 12 months, but the graphics would have been twice as wide, and frankly, I was lazy).

One more time: This is potentially dangerous, especially when you’re entering calculations. Know whether your worksheets are grouped or not before you enter or change data.

We entered the formula to calculate the commissions while the worksheets were grouped because the calculation for each cell is the same: the sales total multiplied by the commission rate.

In Excel, you can calculate across worksheets, that is, you can use the value of one cell in one worksheet in a calcuation in another cell on another worksheet. I’ll show you first the easy mouse-click way to do this, then I’ll show you how to enter it manually.

Do. Not. Group. Worksheets. The formulas you’re going to enter on this last sheet are different from the formulas on the others.

Select that last worksheet. If there are data under Sales Total and Commission, you had it grouped when you entered that data, and that’s okay. Just select the range B5:C24, right-click, and click clear contents.

Click in cell B5 to select it: You’re going to enter the first annual sales total formula here. Start as you always would. Type:

=sum(

Now, click on the Jul-06 worksheet, then click on the B5 cell there. Hold down the SHIFT key, and click on the Dec-06 worksheet tab. Press ENTER.

Bingo! You have just added the cells B5 in those six worksheets into that cell!

Remember that warning? If instead of clicking the Dec-06 tab when entering that formula you click on Jan-Dec-06, Excel will freak out and give you an error, because you cannot add the contents of a cell IN the same cell (it’s known as a circular reference.

Grab the autofill handle and drag it down.

Now, we’ll enter the total commisions, in exactly the same way. Click in C5 on the Jul-Dec-2006 worksheet. Again, start as you usually would:

=sum(

Now click on the Jul-06 tab, then cell C5. Hold down the SHIFT key, and click the Dec-06 tab, then press ENTER. Grab the autofill handle and drag it down.

If you look at the contents of C5 on Jul-Dec-06, you will see this:

The names of worksheets in formulas or functions are always enclosed in single quotation marks, and they are separated from the cell addresses with an exclamation point (or bang, in old-fashioned computerese). In this function, the argument range is C5 on each of the worksheets in the function, that is, Jul-06 to Dec-06. To finish up, again making sure your worksheets are not grouped, you can change the labels on that last worksheet.

You can also use the fill button on the ribbon to copy the same data to grouped worksheets, but frankly, the way I’ve presented it is superior, since there are certain things the fill button will not do (like column widths), and Microsoft still hasn’t addressed it.

As long as you’re very careful, grouping worksheets can save you a lot of time and work. But if you’re not careful, you can easily end up creating hours of needless work for yourself. So heed the warnings!

Trackback URI | Comments RSS