About different workbooks

I guess everybody knows what a 3D formula is but for those who do not know I will explain.

A 3D formula is a formula that gets its data from different worksheets.
For example. I have four workbooks, one per quarter (1). Each workbook contains the totals for the different months from that quarter, and an overall total (A).

To calculate the total sum of each quarter in the worksheet, select this worksheet (2) and select a cell where you want the sum to be displayed (3).
Then we start our formula with the equal sign (=), You select the worksheet Q1 and select the cell with the total (4). You type a plus sign (+), and do the same for the Q2 worksheet, selecting the “total” cell. Next, you type a plus sign again, and do the same for worksheet Q3. And finally you type the last plus sign, and again do the same for the worksheet Q4. Next, click the “Enter” button, and you have all the totals of the different quarters added.

Now, when you look at the formula (5), you see that the reference to a worksheet between single quotes and that it concludes with an exclamation point (!).

The same can be done, but across different workbooks. Select the cell in the new workbook, then the old workbook and then select the cell in the old workbook.

As you notice in the formula bar, each cell is now also preceded by square brackets for a reference to the workbook (1).

About different applications

We can even copy data from an Excel file in another application. If you then change the data in the workbook, the change automatically copies data, as in the example Word document.

First, open both documents.

Select the cell you wish to copy in Excel and click the shortcut Ctrl C on your keyboard (1).

Then go to your Word document, place the cursor in the document where you want to place the data, and click the downward pointing arrow under “Paste” (2). Select “Paste Special” in the drop-down menu (3).

For those of us who work with the 2003 version, click “Edit” in the menu bar and choose “Paste Special”.

In the “Paste Special”, select “Paste Link” (1) and choose “Unformatted text” (2) if you wish to use the formatting of your Word document. But, if you want to use the format of the Excel file, choose “HTML format” in the list.

Change the data in the Excel file and the Word document is automatically adjusted.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats