• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

The magic of Excel 3-D references

Get PDF file

by on
in Excel Training,Office Technology

Q. Is there an easier way to create a formula across work sheets than clicking on each cell you want to add to the formula?

A. Yes! It’s called a 3-D reference. It works best when you have several identically formatted work sheets, same column headers and same key data (column A). For example, say you have three work sheets with first through fourth quarter sales data for three separate business units, all generating product and service income. You’d like to summarize the number from all three on one sheet. Copy the key data, in this case, Product Income and Service Income, to the same position on your Summary Sheet. Then, create a column title for your summarized numbers. In the cell for the first value, type =SUM, or use the Alt+= shortcut. Then, click in the first place you’ll find this information, hold your Shift key down and select the last work sheet with this information. Press Enter.

Your formula would read something like =SUM(‘ABC:ACME!’F2). Your work sheets must be continuous. If your data setup is more complex than this, you might look into Consolidation (Data tab, Data Tools group).

Leave a Comment