13 3-D Reference Sheet – Information and Practice
What are sheets?
Sheets are separate, individual spreadsheets. To access sheets, look at the bottom of the screen of a spreadsheet and notice the tab called “Sheets.” A “workbook” is comprised of any number of sheets. Sheets can be added by clicking the plus sign next to the tab “sheets.”
Sheet tabs can be changed from the word “sheets” to any label. To change a name on a sheet, double click the sheet tab and insert a new label name.
In the example below, a company has business in several cities. Each city has a separate sheet in a workbook. The sheets have been renamed New York, Chicago, Tampa and Summary. Each sheet has the same format, that is each sheet is set up with the same cell references. Notice that Quarter 1 sales for New York is in cell A4; Quarter 1 sales for Chicago and Tampa are also in A4 on that sheet.
What is 3-D referencing?
The 3-D referencing is used to summarize specific information on each of the sheets and place the total on a separate summary page.
For example, in the illustration above, the total of Quarter 1 in cell A4 for each of the cities can be quickly summarized and placed on the Summary sheet in a different position such as B3.
It would look like this:
How do I develop the formula?
When summarizing data, you are actually adding. The SUM formula works well for a 3-D referencing exercise. In a regular worksheet when one adds multiply columns, a range is selected. When AutoSum is used, that automatically references a range of numbers. If AutoSum is not used, a range of numbers can be selected by highlighting the column or row to add. In both the AutoSum feature or using the SUM feature, the formula shows a colon between the first and the last cell reference. For example, =SUM(B4:B10) would add up rows 4 through 10 in column B.
The part that is different is 3-D referencing is incorporating the different sheets. To get the Quarter 1 Sales for three different cities- New York, Chicago, and Tampa – (in the example above), start by placing your mouse cursor in the cell of the Summary sheet where you want the result to show. In the example above, I put my mouse cursor in B3. It doesn’t matter what cell, but you do have to start on the Summary Sheet. Then follow the next steps.
1. Type the equal sign followed by the word SUM and an open parenthesis.
2. Click the first city sheet (New York). Hold down the SHIFT key and click the last city (Tampa). In the formula, you will notice that the colon was placed automatically between New York and Tampa which would also include Chicago.
3. Next click the cell reference on the New York, or first sheet, that contains the amount you want to total. In this case, click on A4.
4. Enter.
5. The total (100698) appears on the Summary Sheet in cell B3 where you started the formula.
6. The formula displays in the formula bar above the worksheet.
The formula is =SUM(‘New York:Tampa’!A4)
7. Notice the exclamation point before the cell reference. T