Microsoft Excel In 3-D!
Excel 3-D formulas are great for workbooks that ‘roll up’ individual
worksheets, but they require a bit of care in setting up and maintaining.
In the sample workbook there are four regions
that report their expenses individually. A summary sheet rolls up all of the
regions into a corporate total. Typical formulas for the rollup sheet look
like:
=North!B3+South!B3+East!B3+West!B3
=North!B4+South!B4+East!B4+West!B4
Entering these formulas into a large number of cells is very tedious and
prone to error. You can use
AutoFill to help out, but for a lot of discontinuous cells or a number of different formulas (sum, average, etc…)
AutoFill is little help.
You can use a 3-D formula to make the workbook easier to set up and easier
to maintain in the long run. Go to the cell on the summary sheet that is to
roll up all of your sheets and type =SUM(
Look down at the worksheet tabs and click on the tab for the first sheet you
want to roll up, and then HOLD DOWN THE SHIFT KEY and click on the last
worksheet tab. You should see that all of the tabs are highlighted as in in
the image.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
Finally, click in the cell that contains the information to be summed
and hit return. Note that the cell must be in the same place on each of the
regional
sheets! If you view the formula that has been entered, you’ll see the
following:
=SUM(North:West!B4)
What this tells you is that cell B4, and only B4 is being
summed on the worksheets from North to West.
Not to belabor the point, but take a careful look at all of the
individual sheets that are to be rolled up and make sure that no one region
has added or subtracted any rows or columns. The addition of rows or columns
must take place on all sheets. For instance, if a row containing union dues
is required in the North region, include the row in the other regions too,
just
leave the amounts blank.
After you have used 3-D formulas for a while, you’ll get comfortable with
the syntax and you can type the formula directly into the cell like
=AVERAGE(North:West!B12) or even use 3-D named ranges to refer to a group of
cells on different worksheets. See Excel help under 3D reference.
|
|