Lesson 10: Data consolidation and 3D references



First things first - 3D references

Uh, my spreadsheets are two dimensional remember? And so are yours. I just have rows and columns, that's it. I'm just saying because if you think I'll purchase a pair of expensive 3D glasses just to analyze my data, you're entirely mistaken.

Calm down my friend and let me explain. The rows and columns indeed make up the first two dimensions of your spreadsheet. The third dimension refers to different sheets. Cell A1 in sheet X is different from cell A1 in sheet Y, hence the analogy with a third dimension. You can even visualize this. Imagine all sheets being piled up. I'm sure you can now see all cells A1 aligned vertically.

Oh! I see now. So, we're going to work with data contained in different sheets at the same time?

Correct! And even in different workbooks, but that's for later on. In the first example we'll analyze today, we'll suppose you keep data from all your sales people into a single Excel workbook, each sheet containing data from one single sales engineer. The sheet tells you how much hardware, software and services they sold per month.


The exam question is, how would you make a global overview of all your sales?

Easy! I make a new spreadsheet, organize it the same way and sum up the data from the different sheets:


That works indeed.

I hear a "but" coming.

But... What do you do if you have 30 sales people instead of 3? Or if there's someone new joining your team?

Good question. I suppose I just adapt my formulas.

And that is what everybody would do that hasn't heard about 3D references. Here's what you should have written:


As you can see, I used "Tom:Ellen!B2" as argument for the SUM function. This is what is called a 3D reference as it combines multiple spreadsheets. To be even more precise, it is a reference to all spreadsheets contained between spreadsheet "Tom" and spreadsheet "Ellen". Bonus: when adding a sheet between these two, it is automatically taken into account in your consolidation sheet.

Interesting. Does the same trick apply when data is contained in different workbooks?

No, it doesn't. When in this case, you still have to use your formula, update links in order to have the information refreshed etc. But that is food for another lesson. However, you can use another Excel function which is called data consolidation.

Data Consolidation

The fun part with the consolidation feature is that it solves two issues of the 3D references part:

  1. It works on different workbooks and

  2. It works even when the data in your worksheets is not organized in the same way there it looks at the labels.

Let's illustrate this with another example. This is Ellen's document:


And this is Tom's:


As you see, both are almost identical. However, the Services part is the first row in Tom's overview whereas Ellen placed it last.

Damn. Your 3D references won't work and I'll have to be very careful when entering my formula.

I know. That is why you should let Excel work for you and roll all figures automatically into a master worksheet. Follow these next steps carefully to enjoy Excel's magic. First, I recommend opening the workbooks containing the data you want to consolidate. Once this is done, open a new empty workbook - this one will be your master workbook and contain the consolidated data - and click on the consolidate button you'll find under the Data tab:


Following Dialog Box appears:


Let's discuss the first dropdown first, called "Function". As you'll be able to experience yourself, data consolidation is not restricted to just summing numbers up. You can as well aggregate them by computing an average, retrieving the maximum or minimum values etc. In this example we'll sum the data.

Next, you've to tell Excel in which worksheets or workbooks the data you want to consolidate is stored. This is called a reference. Once you selected the data table (including labels for rows and columns), you need to click add and repeat for each data table containing data to consolidate. You'll see all selected references listed in the "All references" listbox:


Last but not least, the "Use labels in" option buttons. If you don't check the "Top row" and "Left column" options, Excel will sum up all cells that have the same position. This is called "Consolidation by position". Well, they have not been very creative when choosing that name, have they? Obviously, we don't want to do that as we know that Tom decided to list his Services business first, whereas Ellen, Mary and Marc have it at the bottom of their file. So, we have to tell Excel to use the Row labels and match the correct rows before summing up. This is called "Consolidation by category". Otherwise, if we don't tick the box, Tom's Services part will be summed with the other people's Hardware results.

How about the "Top row" option in your example? Does it make any sense to check it?

No, not really, you're right as the month are in the same order in all files already. But there's no harm in selecting it. Just remember we want Excel to sum cells that have the same labels.

You haven't discussed the "Create links" option yet.

You're right. If you check that option, Excel creates a link to your source data (meaning the consolidated data is not static - where update needs to be done manually - but will be dynamically updated whenever your source data changes). It will then as well automatically create an outline:


Isn't that handy?


Good to know, and to remember...

  • For 3D references to work, the worksheets should be identical i.e. the data contained in cell B3 in sheet X should correspond to cell B3 in sheet Y;

  • The consolidation feature works on sheets contained in the same or different workbooks;

  • Needless to say that when consolidating by category, the worksheets can have different layouts but the data labels should be the same;

  • You cannot create links when source and destination areas are on the same sheet.

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.



You are not established in Belgium? That's not an issue: did you know that Cintellis provides remote services to customers located all over the world?



About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com