Admin [ad_1]
3D references let you evaluate data across multiple Microsoft Excel sheets, but be careful when using range referencing because sheet position matters. Learn how to avoid two common pitfalls.
Image: PixieMe/Shutterstock
Microsoft Excel’s 3D formulas let you summarize the same cell on multiple sheets. You can enter each sheet by name, which might be tedious and isn’t dynamic; if you add a new sheet, you must modify the formula. Range referencing is quicker and dynamic, but then you must consider each sheet’s position. For instance, a formula referencing the range Monday:Friday will evaluate all sheets positioned between the Monday and Friday sheets. It’s certainly quicker, but with that ease comes possible errors when you reposition, add or delete sheets. In this article, we’ll quickly review 3D formulas and two possible pitfalls when using range referencing in a 3D formula.
SEE: 83 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. For your convenience, you can download the demonstration .xlsx and .xls files. Excel Online supports 3D formulas.
About 3D formulas
A 3D formula references cells or ranges across two or more sheets. For instance, you might track sales by the month or region where the sales for each month or region are stored on different sheets: Jan, Feb, Mar, … ; North, South, East, West. To include values from all those sheets, you need a 3D formula such as
=SUM(Jan:Dec!E6)
or
=SUM(North:West!E6)
where this function is entered into cell E6 of a totaling sheet.
If 3D formulas are new to you, don’t worry; they’re not complicated. The one rule that you must obey is that all the sheets share the same structure. In a nutshell, you don’t want the value in Jan!E6 to be a total for James in one sheet and Martha in another. E6 in each sheet should contain the sales for the same individual. This will become apparent as we work through the examples.
The sheet structure
Before we can expose the pitfalls, we’re going to need data. Figure A shows a simple set of sheets that contain sales values for three regions. Each sheet is structured the same; only the values differ. The values in C3 on each sheet belong to James and only James. C4 is only June; C5 is only Rosa. I mentioned earlier that the sheet structure must be exactly the same on each sheet you plan to include in the 3D formulas.
Figure A
This workbook stores sales values by regions.
Figure B shows a fourth sheet that totals the sales values for all three regions by personnel. The 3D formula in C3
=SUM(East:Central!C3)
returns the total sales for James, $3,990. The relative function copied to C4 and C5 return the totals for June and Rosa, respectively. Right now, these totals are correct and will update accordingly if any values in the three source sheets change.
Figure B
This sheet uses a 3D formula to total sales values for James, June, and Rosa.
Right now things look good, but what happens if you move or delete a sheet? Let’s find out.
1. Move or delete a sheet
The 3D formulas on the Totals sheet reference the East, West and Central sheets. You can rearrange those three sheets, but only so far. For example, let’s put the sheets in alphabetical order: Central, East and West. In Figure C, you can see that the totals are now lower. When you moved Central to alphabetize the three sheets, you removed Central from the reference East:West. The same thing would happen if you deleted the Central sheet.
Figure C
Moving sheets can have unplanned consequences.
If you really need to rearrange the sheets, you must update the 3D formula to include all three sheets by changing the reference as follows:
=SUM(Central:West!C3)
Moving a sheet out of the referenced range will change your totals. There will be no error to warn you, either. It might be exactly what you want to do; but it might not. Knowing the consequences of moving or deleting a sheet in the middle of a 3D reference is vital to maintaining the integrity of your data.
2. Move or add a referenced sheet into the range
If moving a sheet out of the range can mess up totals, it makes sense that moving a sheet into the range can, too. Let’s suppose you add a fifth sheet that shares the same structure for a new region. Figure D shows the values for the new South region. If you position that sheet between Central and West, the totals on the Totals page will include the south values. If you position the sheet before East or after West, the Totals formulas will not include them. Neither position is wrong, but awareness is key to getting the results you want. As before, you could update the reference on the Totals page, or simply position the sheet accordingly.
Figure D
Where you position this new sheet matters.
3D formulas are easy to use, but you must be aware of the consequences of how a sheet’s position can end up returning incorrect data. Be careful when you reposition, add, or delete sheets if you have 3D formulas in a sheet.
Microsoft Weekly Newsletter
Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays
Also see
[ad_2]
Source link
