How to Link Worksheet Data with 3D Formulas

How to Link Worksheet Data with 3D Formulas

Anyone who frequently works with Excel spreadsheets knows the importance of being able to create a single formula to calculate all data gathered from multiple worksheets. For example, adding values from a created cell B5 when you already have several similar regional worksheets requires this formula:

=North!B5+South!B5+East!B5+West!B5

These formulas are cumbersome, laborious, and their references are non-dynamic. If you add another worksheet to work with, you have to manually update the formula which may be prone to errors.

Luckily, you can avoid all this and make your work easier with all your gathered data from several worksheets using a single 3D formula. 3D Excel references will replace the tedious process using short flexible formulas. The Excel 3D formulas are also commonly referred to as cubed formulas. You can use them to calculate information from several worksheets and show the result on a single selected formula cell.

Read to learn more, or watch this video to find out how you can create 3D formulas in Excel.

How to Create a 3D Formula for Excel

3D formulas refer to a similar cell or a range of Excel worksheets. Inputting this formula:

=SUM(North: West!C15), allows you to sum up all the existing data in cell C15 presented on worksheets marked North: West.

The main consideration is that each cell will contain a similar type of data, like worksheet total, in the identical location on every worksheet in the 3D reference. The Excel 3D formulas will pass through every worksheet to summarize or calculate the date.

Therefore, you will require a consistent layout between your worksheets to build a working 3D formula. There are not several applications for it, but it is the perfect way to create links that help to summarize information with 3D references quickly.

Steps to Building an Excel 3D Formula

Inserting a 3D formula into your worksheets is straightforward. The steps to follow are:

1. Move the worksheet where you wish to summarize your data. To display data from each worksheet, ensure they have a similar structure or layout as the worksheets you want to be referencing. If you are only looking to summarize the totals, the worksheet could have a different layout, except the individual worksheet to share will have the same structure.
2. Enter the required formula until the exact point you require a value from another worksheet to complete it. While it is possible to apply any function using these formulas, the most common and simplest is the SUM marked as =SUM(
3. Navigate to the first worksheet required for reference and click it.
4. Now click the cell containing the values. You will refer to the formula
5. Press [Shift], then activate the final Excel worksheet required for reference. This will select all the sheets from the first to the last. They will all light up or highlight.
6. Press[Enter] to complete the operation. Excel will add the closed brackets automatically.

Now the formula is active and working. You can complete it by:

Select the cell you wish to copy the formula from the master’s or summary worksheet and copy it to other related cells. You can apply the copy commands or fill in handles for this process. The Excel logic will copy it to other cells, so you only create the formula once.

The next and last step is to add the totals and any other calculations required to get accurate results from your data.

Wrapping Up

Working with Excel is seamless when you have the skills of creating 3D formulas. You can use it effectively when you want to summarize similar data you have on multiple worksheets.

Do you have any inquiries about Microsoft Office 365 and any of the products in Microsoft Office? At K2 Technologies, we offer expert Microsoft support to help you be more productive. Contact us today to check out additional shortcuts, tips, and techniques for Excel.