Link or Embed – Create a Dynamic link connecting content and a Excel workbook

Linking versus Embedding Excel data into a word document, do you understand the differences?  They may seem similar but the truth is they generate different processes and accomplish unique functions.  The main differences are, where the data is stored and how you choose to update the data after placed into a Word document.

Linking a file/data creates a link between the original data source (Excel spreadsheet) and the file ( Word document).  When a change is made to the data source the destination file is updated.

Embedding a file places a copy of the original data source into the Word document or the destination file. A change to data source will not be reflected in the destination file.  In effect, embedding creates a static copy of the data.

Insert a Linked Object - Excel Data into a Word document

1.  From Excel, select the data and press Ctrl + C (copy)

2.  From Word, click in the location where the data is to be placed.  From the Home tab, click on Paste and select Paste Special from the drop down menu.  

3.  From the Paste Special dialog box, select Paste link. Within the As list, select Microsoft Office Excel Worksheet Object and click on OK.

Edit/ update date the data in Excel.  You can manually open the Excel document or you can save time and launch the Excel file directly from word.

4.  Right-click the data and select Linked Worksheet Object, from the sub menu, select Edit Link. 

Or the fastest method--from Word double click the data to open the Excel data source file!

Embed an Object - Excel data into a Word document

**Remember- when you embed an excel object, data in the Word document doesn't change if you update the Excel worksheet.

1.  From Excel, select the data and press Ctrl + C (copy)

2.  From Word, click in the location where the data is to be placed.  From the Home tab, click on Paste and select Paste Special from the drop down menu.  

3.  From the Paste Special dialog box, select Paste.  Within the As list, select Microsoft Office Excel Worksheet Object and click on OK.

Additional Resources:

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

About.com - http://wordprocessing.about.com/od/workingwithotherprograms/l/blexcellink.htm

Transpose-->switch columns and rows Excel 2007

The data is in rows or in columns and you want to rearrange or switch the data from one to the other.  Microsoft Excel 2007 provides a command to complete the process simply with just a few steps. 

To rearrange data from rows to columns, select the cells that contain the data. 

1.  From the Home tab, within the Clipboard group, click on Copy. (Ctrl +C)

2.  On the worksheet, select a cell  destination for the column into which you want to transpose the copied data. 

3.  From the Home tab, within the Clipboard group, click on the down arrow below Paste, and select Transpose. 

4.  Success!  Delete the data from the copy area. 

Additional Resource:
http://office.microsoft.com/en-us/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx

Copy single column of values to an adjacent column- Excel 2007

We all know how to copy data in Excel, in fact there are many different ways to do it. In an effort to work smarter, not harder- copy data and eliminate a few clicks of the mouse by using the keyboard instead. 

1.  Select the column of data to be copied and the adjacent column. 

2.  Press Ctrl + Shift + . (period character), insuring both columns are highlighted prior.

Note: this trick also works with rows--> Press Ctrl + Shift + , (comma character)

3 more Excel questions, not just the basics anymore

I was asked during an Excel 2007 Basic training session the following questions.  All good questions, and as I am preparing my response, I thought perfect item for my blog.  And so my blogging adventure after one week is hitting home.  Here you go SJFC community, the answers you requested:  I am starting with the easiest first…

  

How do I bring up or display the “Hot Keys”?

Strictly a keyboard user, you can select commands on the Ribbon by using Keyboard shortcuts.  

Press the Alt key, followed by letter keys displayed on the Ribbon for preferred Excel command without using the mouse.

 

Shortcut letters and numbers appear on the Ribbon.

 

Press the letter to select a tab, press a letter or letters to select a command.

Press ESC to go back one step at a time.

Press a letter or use the arrow keys on the keyboard to select the desired option. 

 

Additional resources, listing keyboard shortcuts for Excel:

• http://www.keyxl.com/aaa33ef/136/Microsoft-Excel-Software-keyboard-shortcuts.htm
• http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx   

How do I convert text to UPPER or lowercase?

 

Enter, =UPPER(“text”) and press Enter.  This function will convert ‘text’ to uppercase.  The text needs to be surrounded by quotes.

Example:

=UPPER(“robin a. schmid”) would be displayed as Robin A. Schmid

Same holds true for =LOWER(“text”)

 

How do I alphabetize all worksheets within the workbook?

In order to accomplish this task you will have to enter code within the VB (Visual Basic) editor.

1.  With Excel open, press Alt + F11.  This will launch Visual Basic

 

2.  From the Insert menu, select Module and copy/paste the code below into the window.

Sub Sortem()

For x = 1 To Worksheets.Count

For y = x To Worksheets.Count

If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then

Sheets(y).Move Before:=Sheets(x)

End If

Next

Next

End Sub

3.  Close the VBA editor and click on the Developer tab.

     

      **If the Developer tab isn’t displayed on the ribbon…

•   Click the Microsoft Office Button, and then click Excel Options
•   Click Popular, select the Show Developer tab in the Ribbon check box and click on OK.

4.  Click on Macros, within the Macro dialog box, select the macro and click on Run.

**Remember to save the workbook as a Macro enabled Workbook to maintain the macro.  

Text to Columns - Excel 2007

A worksheet containing data in one column; you may find it necessary to separate the cell contents into two columns. In the image below, you maybe required to separate the first and last names into two columns, when using the data for a mail merge.

1.  Select your data, in this example A2:A10

2.  From the Data tab, within the Data Tools group, click on Text to Columns.

Text to Columns button

3.   From the Convert Text to Columns Wizard dialog box, select Delimited and click on Next.

4.  From the Convert Text to Columns Wizard dialog box, select Space, clear tab check mark and click on Next.

5.  From the Convert Text to Columns Wizard dialog box, select Text, click on the second column of data, select Text and click on Finish.

In the image below, finished results show data moved last names into it's own column.

Excel is awesome- it helps you work smarter, not harder.  Can this tip help you?