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!


Already a member? log in here.

Editor's Choice

Upcoming CPE Webinars

Nov 24This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Dec 3The materials discuss the concepts and principles in the AICPA’s new special purpose framework.
Dec 9A key component to improving your firm’s workflow efficiency while enhancing your profitability at the same time is how you leverage emerging technologies.
Dec 9Kristen Rampe will cover how to diffuse the tension in challenging situations in this one-hour webinar.