Excel for Architects - Part 3

 This is the third part of the Excel for Architects series. You can find the first part of the series here and the second part here.

Linking Excel Files

Much like you can use Xref in AutoCAD to create links between drawings, you can link cells in an Excel worksheet to another worksheet or another workbook. You can also link an Excel file to a Word or Powerpoint document. With this feature, you can break up your documents into easily editable pieces. By linking workbooks, multiple people can work on the files simultaneously. If you’re preparing a large spreadsheet or presentation, this can be a real time-saver. If data changes in the original file, the linked files are automatically updated. This ensures that your spreadsheets and charts are always up-to-date.

Using Linking Formulas

There are many ways to link data in Excel. The easiest way is to use a linking formula to link one cell to another. You can link to a cell in the same worksheet, a different worksheet, or even a different workbook.

To create the link, do the following:

1. Click on the cell where you want the linked data.
2. Type = (equal sign) to start the formula.
3. Click on the cell with the value you want to link.
4. Press the return key. Excel will insert the cell’s column and row name into the formula.

If the cell you want to link is in another worksheet, type = (equal sign) in the cell you want to link into then switch over to the worksheet and select the cell you want to link. Excel will insert the worksheet name and the cell’s column and row as part of the formula.

Linking to a cell in another Excel file is the same as linking to another worksheet. Activate the cell you want to link into. Type = (equal sign) then switch over the file with the cell you want to link. Excel will insert the file and worksheet name as well as the cell’s column and row as part of the formula.

Updating Links

By default, Excel will always update a link whenever the original value changes. If you want to manually control when cells are updated, you can change the setting in the Formulas ribbon. Select “Calculations Options” in the Calculation section and set the value to “Manual”. Whenever you want to update the cells, go to the Formulas ribbon and select “Calculate Now” in the Calculation section.

Formulas > Calculation Options

You can sever the link between the cells by converting the formula in the linked cell to a constant value. This is similar to binding Xrefs in AutoCAD. Simply activate the cell with the link you want to bind. Press F2 to activate the formula. Press F9 to recalculate the value then press the Enter key. This will replace the link with the calculated value and sever the link.

Linking To Other Applications

Much like you can link to other worksheets and workbooks in Excel, you can also link your spreadsheets to other Microsoft Office applications like Word and PowerPoint. There are two methods you can use to bring in Excel files – linking and embedding. Similar to the methods explained above, linking creates a dynamic link to the Excel data. Any changes to the original Excel file are updated in the Word or PowerPoint file. Embedding inserts the Excel data directly into the other document. Embedded data can be edited directly using Word or PowerPoint but it will update when the original Excel file changes. For the purpose of this example, we’ll look specifically at linking Excel files.

Linking Excel and Word

Linking Excel to Word is very easy. In Excel, highlight the cells you want to link with Word and copy them to the clipboard (Ctrl+C or Home > Copy). In Word, click on Home > Paste > Paste Special. Select “Microsoft Excel Worksheet Object” from the list in the Paste Special dialog and choose the “Paste link” checkbox. Click OK to insert the link.

Paste Special Dialog

Word will automatically update the link whenever the original file changes. To manually update the link, right-click the spreadsheet in Word and choose “Update Link” from the context menu.

Linking Excel and PowerPoint

Linking Excel to PowerPoint works the same way as linking to Word. Simply highlight the cells you want to link then copy them to the clipboard. In PowerPoint, go to Home > Paste > Paste Special and paste the Excel data as a link. The Excel data will update automatically though you can also update it manually by right-clicking the spreadsheet and choosing “Update Link”.

Note that whenever you open a file with links, the application will alert you that there are links present and ask if you want to update the links.

PowerPoint update links

If you don’t like seeing this message every time you open the file, you can set PowerPoint to automatically update links. Go to File and select the “Edit Links to Files” button on the lower right-hand corner.

 PowerPoint – Edit Links

Select the links from the list and choose the “Automatic Update” check box. You can also change the source or break the link through this dialog box.

Conclusion

Using links in Excel is a great way to ensure accuracy in your spreadsheets. By entering your data once and then linking to the original source, you only have to update the original value and all subsequent links will update automatically. Likewise, you can break down complex spreadsheets into individual parts and use links to combine them into the final form. This keeps the data manageable and allows several people to work on the files simultaneously. The same goes for linking spreadsheets to other applications. Create the link in Word or PowerPoint then update the Excel file as needed. When you’re ready to publish the document, simply update the Excel link and you’re ready to go!

Check out the following links for more information on linking Excel files:

http://office.microsoft.com/en-us/excel-help/create-and-manage-links-to-other-workbooks-HA001054812.aspx

http://office.microsoft.com/en-us/word-help/link-or-embed-an-excel-worksheet-HA010120810.aspx