How to Link Excel Cell Values in Word Without Losing Formatting
Question details
The user needs to link specific Excel cell values into a Word paragraph and refresh the data without altering the existing Word text formatting.
- Product
- Word, Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic documents or reports in Word that display live data figures (like revenue for different years) pulled directly from an Excel spreadsheet.
- Observed behavior
- When the linked Excel data is updated and the Word document is refreshed to retrieve the latest values, the formatting of the text within the Word document changes unexpectedly.
Ensure both your Word document and Excel spreadsheet are saved to a stable location on your computer, as moving the files later can break the data links.
Apply Formatting Switches to the Word Link Field
Modify the link field codes in Word to force the linked Excel data to inherit the Word document's native text formatting.
By default, linked objects may attempt to retain their source formatting from Excel. Adjusting the field codes with specific text formatting switches tells Word exactly how to display the data within your sentence.
In your Word document, press 'Alt + F9' on your keyboard. This toggles the document view to display the underlying field codes instead of the linked Excel values.
Find the specific LINK field code for the Excel value. Click inside the bracketed code and replace the existing switches at the end with the following string: \a \f 5 \t
Press 'Alt + F9' again to hide the field codes and return to the normal document view.
Right-click the linked value in your sentence and select 'Update Field'. The data will refresh with the latest Excel values while perfectly preserving your paragraph's formatting.
Easily Link Data Between WPS Spreadsheet and WPS Writer
With WPS Office, integrating dynamic data from Spreadsheets into Writer documents is intuitive and provides robust controls to maintain your document's formatting. It fully supports Microsoft Office document formats.
- 1. Copy Data from Spreadsheet: Open your data file in WPS Spreadsheet, select the cell containing the value you want to link, and press 'Ctrl + C' to copy it.
- 2. Use Paste Special in Writer: Switch to your WPS Writer document. Place your cursor in the sentence, click the drop-down arrow under the 'Paste' button on the Home tab, and select 'Paste Special'.
- 3. Select Paste Link: In the dialog box, choose the 'Paste Link' option, then select 'Unformatted Text' to ensure the value adopts the styling of your current paragraph.
- 4. Update Links Anytime: Whenever the source spreadsheet changes, simply right-click the value in WPS Writer and choose 'Update Link' to instantly fetch the new data without losing formatting.
Frequently Asked Questions
Why does my linked Excel data change font when updated in Word?
By default, Word may try to retain the original source formatting from Excel. If the field code lacks the proper text or formatting switches, refreshing the link overrides the paragraph's native Word styling.
How do I view field codes instead of the linked values in my document?
You can toggle the display between the field codes and their results by pressing 'Alt + F9' on your keyboard. Alternatively, right-click the specific link and choose 'Toggle Field Codes'.
Can I link multiple Excel cells into a single Word paragraph?
Yes, you can copy and paste-link multiple individual cells into different parts of a single paragraph. Each will act as its own field code, and you can apply the formatting switches to each one individually.
What happens to the links if I move the Excel file to another folder?
Moving or renaming the source Excel file will break the dynamic link in Word. If this happens, you must right-click the link, select 'Linked Worksheet Object' or 'Edit Links', and update the source path to the new file location.
