Excel Tip: Manipulating 3-D Worksheets | AccountingWEB

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.

View more helpful tips!

Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.