MIcrosoft Excel - Entering a 3D Range

Entering a 3D Range.

When you have a set of worksheets you wish to summarise into one total sheet.

For example, we have a workbook with five worksheets: "Summary", "North", "South", "East" and "West". We want to add all the values in cell A1 from "North", "South", "East" and "West" and return the total onto "Summary".

Select the cell on the “Summary” sheet where you wish the total for each of the months.

Enter an “=” to start the formula.

Select the “North” sheet, click into the cell you wish to add. The formula at this stage should look =North!A1

Type a “+” after the A1

The formula at this stage should look =North!A1+

Repeat stages 2,3,4 until all  the sheets have been included into the formula. The formula at this stage should look =North!A1+South!A1+East!A1+West!a1

If each of the regions were in a different workbook the formula would include the workbook name as well.

Example : =[Workbook Name]Sheet name!$Cell reference$ Example : =[East]Sheet1!$A$1

A “$” sign is added automatically when linking external workbooks (different Workbooks), this will make the cell reference absolute.

If you intend to copy the formula to a range of cells you must remove the “$” before the formula is copied.

Tip: To remove the “$” quickly, highlight the formula in the Formula bar, press the

F4 key until all the “$” are removed.

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London