Blog

Three Dimensional Formulas are, by far, the most flexible of the three methods of data consolidation that we’re discussing in this post. With Three Dimensional Formulas, the placement of the data your are summarizing, or consolidating, has no bearing on the final outcome. Moreover, it doesn’t matter in which worksheet or workbook file the detail data is stored.

And, you probably already know how to create formulas. Creating a Three Dimensional Formula is as simple as being able to point and click on the data you want to summarize.

Let’s look at an example.

I have a workbook that tracks the sales of Widgets, Ball Bearings and Hammers for each of four Regions (East, West, North & South). I already have the detail data by Region for each of the three products broken down by quarter. Each product already has their own worksheet tab (see sample below).

Here’s what my formula might look like in Excel:

=SUM ( Widgets!B6+‘Ball Bearings’!B6+Hammers!B6 )

In other words, on my summary tab, the above formula would total the sum for cell B6 on the Widgets worksheet, cell B6 on the Ball Bearings Worksheeet and cell B6 on the Hammers worksheet. The reason that the worksheet name “Ball Bearings” is surrounded by apostrophes is that there is a space in the worksheet name.

Subscribe to the Keystone OnDemand blog to be the first to learn about future Microsoft Office 2010 and SharePoint Training news and special subscriber offers.