Excel Tip: Manipulating 3-D Worksheets
For those of you who have an interest in working with multiple spreadsheets within a single Excel file, here's a quick tip for manipulating numbers in a situation where you have several sheets of identical format, but with different numbers.
Say you have 12 active sheets, with sheet 1 as the summary sheet and sheets 2-11 providing the detail, and you want to add the contents of Cell D5 from sheets 2-11 and place the sum in cell D5 of sheet 1.
- Step 1: Place your cellpointer in cell D5 of sheet 1 and initiate the formula by clicking the AutoSum button on the standard toolbar. At this point the formula will appear as: =SUM()
- Step 2: Click on the tab for sheet 2, then click once in cell D5 of sheet 2. At this point your formula will read: =SUM(Sheet2!D5)
- Step 3: Hold down the Shift key, then click the tab for sheet 11. At this point the formula will read: =SUM(Sheet2:Sheet11!D5)
- Step 4: Press Enter (or click AutoSum again) to finalize the formula.
Voice of the Editor
Which isn’t completely true. I mean, occasionally I drop by when I manage to sneak out of the nonstop frat party over at Going Concern, but I’m mostly a wallflower over there. I’m happy to say that I’ve been given express permission (or explicit orders, if you like) to wander over here to AccountingWEB more often.
Why is that, you might ask? My job is to replace the irreplaceable Gail Perry as Editor-in-Chief. What does that mean? I don’t really know! I think it’ll be fun getting a feel for things, throwing in my own thoughts here and there, and listening to the discussions you’re having about the accounting profession.