33.1 The Three Ways to Move Excel Data into Word

When you copy from Excel and paste into Word, the relationship between the two files depends on the Paste method you choose. Understanding the difference is critical.

Choosing the Right Method

33.2 Static Paste — Copying Excel Data into Word

Static paste copies data from Excel into Word with no connection to the source. It is the simplest method and the safest for external distribution.

Paste as a Word Table

1. In Excel: select the range → Ctrl+C
2. In Word: click the insertion point → Ctrl+V — or — Home → Paste
3. Excel data pastes as a native Word table that you can format using Word's Table tools
4. The Paste Options button (📋) appears bottom-right of the pasted table — click it to choose:

Paste Special for More Control

1. In Word: Home → Paste dropdown → Paste Special… — or — Ctrl+Alt+V
2. Select the format:
   • Microsoft Excel Worksheet Object: embeds the data (not static — see Section 33.4)
   • Formatted Text (RTF): static table with formatting preserved
   • Unformatted Text: plain text only
   • Picture (Enhanced Metafile): high-quality vector image — best for complex formatted tables that must look exact
   • Bitmap: raster image — lower quality than Enhanced Metafile
   • HTML Format: web-formatted table

33.3 Paste Link — Live Connection from Word to Excel

A Paste Link creates a live OLE (Object Linking and Embedding) connection. The Word document contains a reference to the Excel file; when the data in Excel changes, the Word document can update to reflect it.

Creating a Paste Link

1. In Excel: select the range or chart → Ctrl+C
2. In Word: Home → Paste dropdown → Paste Special… (Ctrl+Alt+V)
3. In the Paste Special dialog: select "Paste link" radio button (left side)
4. Choose the format:
   • Microsoft Excel Worksheet Object — linked live object (shows the Excel data, double-click opens Excel for editing)
   • Formatted Text (RTF) — linked as formatted text that updates to new values
   • Unformatted Text — linked plain text
   • Picture (Enhanced Metafile) — linked as a live image — the most reliable and visually consistent option
5. Click OK

How Paste Link Looks in Word

• The linked object appears in the document like any normal table or image
• Right-clicking it shows "Update Link" and "Linked Worksheet Object" options
• A grey border may appear when the linked object is selected

Updating the Link

• Manual update: right-click the linked object in Word → Update Link. The object refreshes to the current values in the Excel file.
• Automatic update on open: when you open the Word document, a dialog asks "This document contains links. Do you want to update them?" → click Yes. The linked objects refresh from the current Excel values before you see the document.

Managing Links in Word

1. Word: File → Info → Edit Links to Files — or — right-click a linked object → Linked Object → Links…
2. The Links dialog shows all OLE links in the document with their source file and update method
3. Options:
   • Update Now: manually refresh the selected link
   • Change Source…: redirect the link to a different Excel file (e.g., after the source was moved)
   • Break Link: converts the linked object to a static picture or value — irreversible; use before distributing externally
   • Automatic / Manual: set whether the link updates automatically when the document is opened, or only when you manually request it

33.4 Embedding Excel Objects in Word

Embedding stores a complete copy of the Excel data inside the Word file. No connection to the original file exists — but double-clicking the embedded object opens a fully functional Excel interface inside Word, allowing direct editing with Excel's formulas and functions.

Embedding an Existing Excel Range

1. In Excel: select the range → Ctrl+C
2. In Word: Ctrl+Alt+V (Paste Special) → select "Paste" (not Paste link) → choose Microsoft Excel Worksheet Object → OK
3. The data appears in Word as an embedded Excel object
4. Double-click the object to open the Excel editing environment inside Word (the Word ribbon is replaced by Excel's ribbon)
5. Click outside the object to return to Word

Inserting a Blank Excel Object (New Embed)

1. In Word: Insert tab → Text group → Object… → Object… (the first option)
2. Create New tab → select Microsoft Excel Worksheet from the list → OK
3. A blank Excel spreadsheet opens inside Word — build your table using full Excel functionality
4. Click outside to return to Word; the calculated table displays in the document

Inserting an Existing Excel File as an Embedded Object

1. Word: Insert → Object → Create from File tab
2. Browse to the Excel file → untick "Link to file" (to embed) → OK
3. The entire workbook is embedded inside the Word document (increases file size significantly)

When Embedding is Useful

• The Word document will be distributed to people who need to edit the numbers but do not have the original Excel file
• Self-contained templates (e.g., a quotation template sent to clients where the client fills in quantities)
• Archive documents where calculations should be preserved and auditable without needing an external file

33.5 Linking Excel Charts into Word

Excel charts can be linked into Word exactly like data ranges. A linked chart in Word updates automatically when the underlying Excel data changes — making board report charts that always reflect current numbers.

Pasting a Linked Chart

1. In Excel: click the chart to select it (click once on the chart border, not inside a chart element) → Ctrl+C
2. In Word: Ctrl+Alt+V → Paste Special → select Paste link → choose Microsoft Excel Chart Object or Picture (Enhanced Metafile) → OK
3. The chart appears in Word; right-click → Update Link to refresh from the latest Excel data

Chart Paste Options via the Paste Button

After a normal Ctrl+V paste of a chart into Word, the Paste Options button offers additional choices:

33.6 Mail Merge — Generating Personalised Documents from Excel Data

Mail Merge combines a Word template with an Excel data list to generate a separate personalised document for each row of data. In South African offices it is used for payslips, client statements, salary notification letters, PAYE confirmation letters, stock invoices, and certificate generation.

The Two Components

• The Excel data source: a spreadsheet with column headers in row 1 and one record per row below. Each column becomes a merge field (e.g., FirstName, Surname, Salary, Department, TaxNumber).
• The Word template: a document with merge field placeholders (e.g., «FirstName», «Salary») that Word replaces with the corresponding data from each row.

Step-by-Step Mail Merge in Word

1. Open (or create) the Word template document
2. Mailings tab → Start Mail Merge group → Start Mail Merge → choose document type:
   • Letters: one full document per record (e.g., salary letters, payslips)
   • E-mail Messages: one email per record sent via Outlook
   • Labels: address labels from a list
   • Directory: one consolidated list document (e.g., a phone directory)
3. Mailings → Select Recipients → Use an Existing List…
4. Browse to the Excel file → click Open
5. The Select Table dialog shows the sheets in the workbook → select the sheet containing the data (e.g., "Employees$") → tick First row of data contains column headers → OK
6. Place the cursor where you want a merge field in the document
7. Mailings → Write & Insert Fields → Insert Merge Field → choose the field (e.g., FirstName, Salary)
8. The placeholder «FirstName» appears in the document. Repeat for all required fields.
9. Mailings → Preview Results: click to see how the actual data looks in the document. Use the navigation arrows to preview each record.
10. Mailings → Finish & Merge:
    • Edit Individual Documents…: creates a new Word document with all merged records as separate pages — can be reviewed, edited, and printed from Word
    • Print Documents…: prints directly without creating a document
    • Send Email Messages…: sends each personalised email via Outlook (requires Outlook and a valid email column)

Formatting Numbers and Dates in Mail Merge

A common mail merge problem: salary amounts display as "32000" instead of "R 32,000.00" and dates display as "45000" (the Excel date serial number) instead of "15/03/2025". Fix this with merge field switches:

Filtering and Sorting Records

• Mailings → Edit Recipient List: opens the Mail Merge Recipients dialog
• Filter…: add conditions (e.g., only merge records where Department = "Finance")
• Sort…: sort the merged output by a field (e.g., surname alphabetically)
• Deselect individual recipients: untick specific rows to exclude them from the merge

33.7 Preparing Excel Data for Mail Merge

The structure and quality of the Excel data source directly determines how smooth the mail merge is. A well-prepared data source eliminates most mail merge problems.

Data Source Requirements

• Row 1 must contain column headers — these become the merge field names. Use meaningful names without spaces (FirstName not "First Name"; TaxNumber not "Tax Number")
• One record per row — no merged cells, no blank rows within the data
• No totals row at the bottom — it will become a merged record
• The data must be on the first sheet, or you must select the correct sheet in the Select Table dialog
• Convert the data range to an Excel Table (Ctrl+T) — the table name can be selected in the Select Table dialog and automatically expands as records are added

Pre-Formatting Numbers and Dates in Excel

Alternatively to merge field switches, format the data in Excel before merging:

• Add a helper column: =TEXT(B2,"R #,##0.00") → outputs "R 32,000.00" as formatted text → use this column as the merge field instead of the raw number column
• For dates: =TEXT(D2,"dd/mm/yyyy") → outputs "15/03/2025" as text → always displays correctly in Word without needing a switch
• For SA ID numbers (must be text, not a number): ensure the ID column is formatted as Text in Excel before data is entered

Common Mail Merge Errors and Fixes

33.8 Practical SA Integration Scenarios

Scenario 1: Monthly Management Report (Linked Tables and Charts)

Scenario 2: Payslip Generation via Mail Merge

Scenario 3: Client Statement Letters (Filtered by Status)

33.9 Quick Self-Check

Q1: What is the difference between Paste Link and Embed? Give a scenario where each would be the appropriate choice.

✓ Paste Link creates a live connection between Word and the source Excel file. When the Excel data changes, the Word document updates (on open or on manual Update Link). The Excel file must remain accessible — if it is moved, renamed, or deleted, the link breaks. Paste Link is ideal for a monthly management report template where the same Word file is reused each month: update the Excel figures, open Word, click Yes to update, and the report reflects current data. Embed stores a complete copy of the Excel data inside the Word file. No connection to the original file — editing is done directly inside Word via a mini-Excel interface. The Excel source file is not needed. Embed is ideal for a quotation template sent to clients who need to adjust quantities or figures themselves without having access to your internal spreadsheet. The client receives a self-contained .docx with full Excel editing capability inside it.

Q2: You paste a linked Excel table into a Word report. A week later you try to update the link but Word shows a "Cannot update link" error. What are two possible causes and how do you resolve each?

✓ Cause 1: The Excel source file has been moved, renamed, or deleted. Resolution: in Word, right-click the linked object → Linked Object → Links → select the broken link → Change Source → browse to the new location or new name of the Excel file → OK. The link reconnects to the correct file. Cause 2: The Excel file is currently open by another user (exclusive access) or is on a network location that is temporarily unavailable. Resolution: close the Excel file (or ask the other user to close it), verify the network connection, then right-click the linked object in Word → Update Link. If the network path has changed permanently, use Change Source to point to the new path. Always store the source Excel file in a location that will not change — a shared SharePoint library is more stable than a local desktop or mapped network drive.

Q3: You are running a mail merge for 200 employee salary letters. The salary column in Excel shows R 35,000.00 (correctly formatted) but in the Word merge preview it shows 35000. How do you fix this?

✓ Mail merge ignores Excel's cell formatting — it reads the raw numeric value. Two solutions: Solution 1 (Field switch): in the Word template, right-click the salary merge field → Toggle Field Codes. The field shows as { MERGEFIELD Salary }. Edit it to { MERGEFIELD Salary \# "R #,##0.00" }. Press F9 to refresh. The merge will now format the number correctly. Solution 2 (Excel helper column, simpler): in Excel, add a new column "SalaryFormatted" with the formula =TEXT(B2,"R #,##0.00") and copy it down. This produces text values like "R 35,000.00". In the Word merge, use the SalaryFormatted field instead of the Salary field. No switches needed — the formatted text is passed directly. Solution 2 is more straightforward and avoids having to remember field switch syntax, but it adds helper columns to the Excel file.

Q4: Before emailing a Word report containing linked Excel tables to the board of directors, what step must you take and why?

✓ Break all links before distributing. In Word: File → Info → Edit Links to Files → select all links → Break Link → confirm. All linked Excel objects become static images or values with no connection to the source files. This is essential because: (1) Recipients do not have access to your internal Excel files — if links are not broken, Word shows a "Cannot update link" error every time they open the document, which is unprofessional. (2) Security: a linked file exposes the path to your internal file system (e.g., \\Server\Finance\Payroll.xlsx) which may reveal sensitive information about your network structure. (3) Integrity: without breaking links, a recipient could accidentally update the links to different data if they have a file with the same path structure locally. Always break links and optionally export the final document as a PDF for the cleanest, most secure distribution.

Q5: You want to send personalised overdue account statement letters to only those clients in your Debtors.xlsx whose "Status" column says "Overdue". How do you filter the mail merge to include only those clients?

✓ After connecting the mail merge to Debtors.xlsx: Mailings → Edit Recipient List → the Mail Merge Recipients dialog opens showing all records. Click Filter → in the Filter and Sort dialog: Field = Status, Comparison = Equal to, Compare to = Overdue → OK. Only records with Status = "Overdue" are now included in the merge (the others are deselected — you can see their checkboxes are unticked). Click OK to close the recipient dialog. When you preview results or complete the merge, only the overdue accounts receive letters. This filter does not change the Excel data — it only controls which rows the mail merge processes. To send emails instead of printed letters: Finish & Merge → Send Email Messages → set the To field to the Email column → enter the subject line → Send via Outlook.

Q6: Describe how to create a payslip mail merge for 50 employees. What columns should the Excel file have, what does the Word template need, and what is the recommended workflow from start to distribution?

✓ Excel file (Payroll.xlsx) columns: EmpName, IDNumber, Department, Position, BasicSalary, Overtime, Bonus, GrossEarnings, UIF, PAYE, MedicalAid, OtherDeductions, NetPay, PayPeriod. Add TEXT() helper columns for formatted output: SalaryFmt =TEXT(BasicSalary,"R #,##0.00"), NetFmt =TEXT(NetPay,"R #,##0.00"), PeriodFmt =TEXT(PayPeriod,"MMMM yyyy"). Ensure IDNumber column is Text-formatted (so leading zeros are not dropped). Word template (Payslip.docx): design the payslip layout with company letterhead → insert merge fields at all variable positions: «EmpName», «IDNumber», «Department», «SalaryFmt», «NetFmt», «PeriodFmt» etc. Workflow: (1) Update Excel with current month payroll data. (2) Open Word payslip template. (3) Mailings → Start Mail Merge → Letters → Select Recipients → Payroll.xlsx → Sheet1. (4) Preview results to verify formatting. (5) Finish & Merge → Edit Individual Documents → All → a 50-page merged document opens (one payslip per page). (6) Review for errors. (7) Print the document or File → Export → Create PDF/XPS to save as a single PDF. For individual PDF payslips per employee, use a third-party mail merge add-in or a VBA macro that splits the merged document into individual files.

✓ Module 33 Complete — You Have Learned:

• Three paste methods — Static Paste (snapshot, no connection, safe for external distribution), Paste Link (live OLE connection, updates from Excel, requires source file), Embed (copy stored inside Word, full Excel editing inside Word, self-contained); decision table for choosing the right method per scenario
• Static paste — Ctrl+V into Word; Paste Options (Keep Source Formatting, Use Destination Styles, Keep Text Only, Picture); Paste Special (Ctrl+Alt+V) for Enhanced Metafile, RTF, Unformatted Text
• Paste Link — Ctrl+Alt+V → Paste link radio button; format options (Worksheet Object, Formatted Text, Picture Enhanced Metafile); update (right-click → Update Link; auto-update on open dialog); managing links (Word → File → Info → Edit Links: Update Now, Change Source, Break Link, Automatic/Manual); breaking links before external distribution
• Embedding — Paste Special → Paste → Microsoft Excel Worksheet Object; Insert → Object → Create New (blank Excel inside Word); Create from File (existing workbook); double-click to edit; use cases (editable quotation templates, self-contained archives)
• Linking charts — copy chart → Ctrl+Alt+V → chart paste options; Paste button dropdown (5 chart paste options including Use Destination Theme & Link Data as recommended for live reports); Update Link
• Mail Merge — two components (Excel data source + Word template with merge fields); step-by-step (Mailings → Start Mail Merge; Select Recipients; Insert Merge Field «FieldName»; Preview Results; Finish & Merge); document types (Letters, Email via Outlook, Labels, Directory)
• Merge field formatting — Toggle Field Codes; number switch \# "R #,##0.00"; date switch \@ "dd/MM/yyyy"; Ctrl+F9 to insert field codes; F9 to refresh; alternative: TEXT() helper columns in Excel
• Filtering/sorting recipients — Mailings → Edit Recipient List → Filter (e.g., Status = Overdue); Sort; deselect individual rows
• Excel data preparation for merge — headers in row 1, no spaces in column names, no blank rows, no totals row; Excel Table for auto-expansion; TEXT() helper columns for pre-formatted numbers/dates; ID numbers as Text; close Excel before running merge
• Common errors — numbers without formatting (add switch or TEXT helper), dates as serial numbers (date switch), IDs with no leading zeros (Text format), "Cannot open data source" (close Excel first), blank records (remove blank rows)
• SA scenarios — monthly board report (linked tables + charts, break before distribution); payslip generation (50 employees via Letters merge with formatted helper columns); client statement letters (filtered merge for Overdue status only, email delivery via Outlook)