SLNC logo
SPECIAL LEARNING NEEDS CONSULTANT
...Making learning and technology easy, accessible, affordable and available for everyone


ARTICLE PAGE

USING 3D REFERENCES TO ANALYSE DATA OVER MULTIPLE WORKSHEETS


Written by Olufemi S. A. Bayode on 05 Aug 2020 @ 12:44:12 with comments

Excel

You can analyse data through many sheets in a workbook by using a 3D reference. In other words, if you have many worksheets in a workbook with a single cell or range of cells with data in them which you want to sum, multiply, use a function on, etc, you can do this by referring to the range of worksheets to be included followed by the range of cells to be acted on. The cells must be in the same place in the workbook on all worksheets. For example, with your cursor in the cell you want the result to appear in on the worksheet you want it on, to sum all of the figures in cell C3 in worksheets 5 to 40 you would use the formula:
=SUM(SHEET5:SHEET40!c3)
Therefore, if the figures in all of the 35 referenced sheets in C3 had been the number 10, your result would be 350. Should you want to find the average the range of cells from A1 to F10 in worksheets 3 to 10, you would use the function:
=AVERAGE(sheet3:sheet10!A1:F10)


You can use any of the functions of Excel in a 3D reference, e.g. MAX, MIN, COUNT, etc, but you should not try to use a 3D reference in an array formula. If you add or delete a sheet or cell in the range of worksheets included in your 3D reference, Excel will recalculate the results of the new range of sheets/cells. Please, check for any workbook with multiple worksheets to practise this lesson. You may also create a multiple worksheets in a workbook by inserting new sheets and filling them up with different data.


Like, Tweet, Share and Comment.


Time Uploaded: 05 Aug 2020 @ 12:44:12

Author: Olufemi S. A. Bayode