Hi Guys ! one of Excel’s greatest tricks is the dimensional or 3D reference. This feature, which allows you to create formulas that refer to the same cell or range on multiple worksheets, simplifies the creation of complex documents like monthly inventories or sales reports. In Excel 2013, Microsoft has beefed it up with enhanced options and formulas.To illustrate the power and flexibility of 3D worksheets, we’ll create a Year-to-Date project that calculates the utility bills for a small business for each month, with the year-to-date totals on the first sheet. This workbook (which contains of all the worksheets in this project) calculates the columns, rows, and multiple spreadsheets three-dimensionally.Because retyping the same data is counterproductive, we’ll create the month of January first, add the formulas for the columns and rows, and then copy this sheet 12 times (one for each month, plus the year-to-date sheet).
2014. Excel changes the format to Jan-14. Right-click the mouse in that cell and choose Format Cells from the dropdown list. Choose Date from the format options, and you’ll notice that there is not a format listed for the month spelled out with a four-digit year. Choose Custom and in the Type field box above the list enter mmmm yyyy, then click OK. Now your title says 'January 2014.'
This small business has four designers who rent space. The rent is divided equally, but the utilities and other fees are calculated by percentages of use, therefore each designer pays a different price. Be sure to use the actual percent sign when you enter these numbers.
In cells A4 through G4 enter this data:
Total, Monthly, Carrie C, Marilyn H, Pat B, Donna A, and Percent Total. In cells A5 through G5 enter this data: Utilities,Totals, 28%, 32%, 17%, 23%, and Verified. Select cells A4 through G5 and clickCenter from the Ribbon bar on the Home menu tab. Note: You can center both horizontally and vertically.
In cells A6 through A15, enter this information:
Electricity, Gas, Water, Garbage,Shop Phone, Internet, Alarm Service, Maintenance, Cleaning Services,TOTALS. Adjust column widths to fit the data entered. In B6 through B14, enter these numbers: 646, 510, 211, 56, 165, 98, 55, 335, and 400. Select cells B6 through G15, then click Center.
ADD AND COPY FORMULAS:
sum(B6*C5) and click Enter. With your cursor on C6, press F2 to edit the formula. Position your cursor before the 'B' in 'B6' (between the left parenthesis and the B), then press F4 three times—until you see a dollar sign appear in front of the letter 'B.' Still in edit mode, move the cursor to the left of the letter C in C5 and press F4 twice (until you see a dollar sign appear in front of the number 5), then press Enter. Your formula in C6 should look like this: =sum($B6*C$5). This little trick locks the pieces of the formula (that is, column B and row 5) that you don’t want to change (called absolute reference) and therefore prevents lots of editing.
eposition your cursor to C6. Click Copy from the Ribbon bar (or press Ctrl+C). Move your cursor to C7, select cells C7 through C14, then press Enter. With C7 through C14 still selected, click Copy again, move your cursor to D7, select D7 through F14, then press Enter. All the cells will calculate.
Now, to verify that all the calculations are correct (in case there’s a typo somewhere), position the cursor in G6 and enter this formula:
=sum(C6:F6) and press Enter (you can also highlight these ranges and let Excel fill in the cell locations). With your cursor on G6, click Copy, select cells G7 through G14, and press Enter. If the numbers in column G match the numbers in column B exactly, then your formulas are all correct.
ADD 3D WORKSHEETS FORMULAS:
ALSO:
READ MORE: CLICK HERE
NEW PC GAMES: CLICK HERE
EmoticonEmoticon