Page 80 - Excel Timesaving Techniques for Dummies

14_574272 ch12.qxd 10/1/04 10:38 PM Page 65
65
Let’s Do It as a Group!
Tab) to complete their entries. When I reach cell A9 Rather than enter all the column and row headings
in the last row of the cell selection and press Enter for the initial sales table on the first worksheet and
to complete its entry, Excel automatically advances then copy these headings to the other 11 worksheets,
the cell pointer to B2 — the top row of the next col- a quicker way is to select all 12 worksheets, and then
umn. After pressing the Enter key twice to move just by entering the headings in the active worksheet,
down this row to the blank cell in B4, I’m all set to you’re also entering them in all the other selected
enter the description of the piece given the Code sheets.
Number 12-305 (it’s a 36-inch round table).
Figure 12-5 illustrates how this works. To begin this
I continue along in this manner entering the furni- new workbook, I added nine additional worksheets
ture descriptions down the rest of column B, the to the default three. Next, I selected all 12 sheets
retail prices down column C, and the discount per- (by right-clicking the tab of Sheet1, the active sheet,
centages down column D. After that, to complete and then selecting Select All Sheets on its shortcut
the table, I click E4 (which collapses the cell selec- menu). This puts the program into Group mode so
tion to just that cell) and enter the master formula that the common worksheet title and all row head-
that calculates the spring sale price (using the ings that you see in this figure are not only entered
table’s retail price and discount amount), which into cell B1 and the range A3:A14 in Sheet1 but in all
I then copy down the rest of the column using the other 11 selected sheets as well.
AutoFill. (See Technique 13.)

Let’s Do It as a Group!

When you select more than one worksheet at a time
(see Technique 10), Excel goes into what is called
Group mode (indicated by the appearance of
[Group] after the filename on the title bar of the
Excel window). When the program is in Group mode,
any entry you make in the active worksheet is also
made in all the other selected sheets.
You can put this Group feature to good use when
building spreadsheets that naturally use multiple
worksheets and which require the same entries in
the same cells of each sheet. For example, suppose • Figure 12-5: Entering the same column and row headings
you’re creating a spreadsheet that tracks sales over in each of the selected sheets in Group
mode.
the 12 months of the current fiscal year and you
want to put each of the 12 monthly sales tables on a
Figure 12-6 confirms the fact that Group editing really
separate worksheet (because this makes it so much
works. (I swear no special effects were used in the
easier to consolidate their data when you want to
taking of this screen shot.) For this figure, I clicked
create a table showing the total annual sales). Each
the tab for Sheet10. Doing this not only immediately
table requires the same row and column headings
deselected all the other sheets and switched the pro-
and will occupy the same region of cells on its sheet.
gram out of Group mode but instantly revealed that