is now part of CreativePro.com!

How to Preserve the Appearance of Text When Updating Tables

10 0

It is always frustrating when a table formatted in InDesign needs to have its contents changed for whatever reason. Take for instance the following table that has largely been created with table and cell styles with the exception of the yellow rows.

tablepic01

Unfortunately, the data in the table is incorrect and needs to be replaced with a newly supplied Excel file. The amount of rows and columns are the same, so it should be a matter of placing the table into InDesign and cutting and pasting the data, shouldn’t it?

Let us try what looks like the easiest solution: cut and paste directly from Excel over the top of the old figures. The header row is the same, so only the changed cells are copied from Excel and the same selection is made in InDesign and pasted. Did it work?

tablepic02combined

More after the jump! Continue reading below
Free and Premium members see fewer ads! Sign up and log-in today.

No… instead the data has pasted as a table with the first table cell. What about instead of paste, we try using the paste without formatting option instead? No… the same thing happens.

New strategy. Paste the data into a new text frame into InDesign, then copy and paste the data from there. Did that work?

tablepic04combined

It pasted, but the formatting of the text in the table has been lost, and some colors in the table have changed. How about the paste without formatting option? In this instance, nothing is being pasted!

Solution 1: Change a key preference

Let’s go back a few steps and try copying the data from Excel again, but before pasting the data into InDesign, change a key preference from the Clipboard Handling section of the Preferences dialog box. For the option When Pasting Text and Tables from Other Applications, make sure Text Only is selected, and click OK.

tablepic07combined

It worked! The numbers have changed while the table formatting remains the same.

However, there is a consequence of changing this preference: from this point forward until the preference is changed again, any text that is copied and pasted from another application will be pasted as text only, and that isn’t good if you want to maintain formatting for other items that will be copied and pasted in the future, so you might want to reset the option back to All Information.

Solution 2: Change an import option

Another solution lies in placing the Excel file into InDesign rather than copying it. For this method, select the file using File/Place, making sure Show import options is turned on, and click Open. In the new dialog box that appears, particularly in the Formatting portion, look for the Table: dropdown and select Unformatted Tabbed Text and click OK.

tablepic08combined

Note that only the text has been copied and nothing else – the column strokes have been replaced by tabs, and the rows have been replaced by returns. The text can be placed as a new text frame and its contents copied. From here, highlight the same selection to change and use the Paste Without Formatting option.

This will also work if the table was supplied in a word file, the import option just looks a little different.

tablepic10combined

But this last method involves taking in a whole file, when only a portion of the chart needed to change. This is inconvenient if the table is only a small part of a much larger Excel file.

Solution 3: Copy to a text-only editor

My favorite solution does not rely on InDesign preferences being changed, nor does it rely on importing a complete file. Instead, it works by pasting into a text editing program first. So once again, the text will be copied from Excel, but this time the data will be pasted into a text editing program – in this instance, a Mac application called TextWrangler (one alternate Windows application would be Notepad).

tablepic11combined

Again, note that only the text has been copied and nothing else. Select the text in the text editor, copy it, return to InDesign, select the cells to be replaced, and hit paste. Voila – Success!

As a bonus

The other good news is that this method works from table contents that have been cut and pasted from Word, Excel, Web pages, or tables that are already in InDesign.

The catch

Unfortunately, this method too has its drawbacks, including the fact that type being copied must:

  • Be in a table where the cells being copied/pasted don’t contain merged cells;
  • Not contain any tabs, soft returns, or hard returns.

Why not linked tables?

It is worth noting that using the File Handling Preference of “Create Links When Placing Text and Spreadsheet Files”, the tables can be updated from one file to another, provided the tables in InDesign are properly formatted using table, cell and paragraph styles.

tablepic12combined

Take the bold italic type for example. In the first instance, the type is formatted using a character style. Once updated, the style is lost. The same is true if the type were formatted with a paragraph style, or locally styled. The left-hand side show the table before updating the link, and the right hand side after the link is updated.

tablepic13combined

However, if the cells that contain the text are formatted with a cell style that contains the appropriate paragraph style, then the formatting remains.

tablepic14combined

It appears that this affects only the text, given the yellow fills in the table are locally formatted (i.e. not part of any table or cell style).

However, this requires very strict styling rules be maintained, and that the file taken in originally had no manipulations made to it apart from its appearance (e.g. no destructive changes such as deletions made while in InDesign). It is also inconvenient if only a portion of an Excel file contained a relevant table that had to be imported into InDesign. It is for this reason that I think this solution would not be very effective in most real-world situations, but is still worth mentioning.

A prepress operator and graphic designer for a South Australian commercial printer, with close to 20 years of experience in the trade. He is also a regular contributor to this site and InDesign Magazine, and hosts his own prepress blog "Colecandoo".
  • Chris Ryland says:

    Don’t want to hijack this article and don’t mean to be overly commercial, but our WordsFlow plugin was designed to do exactly this with Excel spreadsheets: merge in changes, respecting work on either side. It can also handle adding/deleting/moving rows and columns.

    https://emsoftware.com/products/wordsflow/

    David Blatner of InDesign Secrets reviewed the initial version here

    https://creativepro.com/import-word-documents-edit-in-word-and-indesign-and-keep-linked-for-updating.php

    and we’ve made a lot of improvements since then.

    Hope this is helpful.

  • Bret Perry says:

    Why copy to a text-editor? –just use command-shift-v ! (paste without formatting in InDesign)
    cntl-shift-v on Windows

    On Mac I actually use Plain Clip.app (free) to strip formatting (and optionally turn tabs to spaces and eliminate multiple spaces and leading and trailing spaces) – And I use free version of FastScripts to assign Plain Clip a keyboard shortcut. Then it’s just a keystroke to strip formatting in any app.

    • About “paste without formatting” – in the article I did write that I DID TRY THIS and it DIDN’T WORK – it doesn’t work in tables. If you read the article again you will see that it was attempted in first two goes.

      The default in preferences for pasting from other applications is “text only” but many users do change this, thus what was happening in the opening shots of the post.

      As for Plain Clip, that looks like another good alternative.

  • Frans van der Geest says:

    >just use command-shift-v !

    Right, even if you have Preserve styling on in preferences this works and, as a menu item also in the Edit menubar!
    Most people are not aware of this option (or seem to keep forgetting it is there ;-) )

  • Jose Pelayo says:

    I’m importing an excel table into InDesign and some of the cells are not being displayed the same way they are displayed in excel. To be specific, I have superscript and normal characters in one cell. In excel, it displays the way I want it to but when I import it to InDesign, the text shrunk. Everything in the cell is the size of the superscript. Any suggestions or solutions to this issue?

    • Jose: It’s hard to know what could be going wrong, but when I save an Excel document as .xlsx and place into InDesign, I need to turn on Show Import Options and set the Table Formatting popup menu to Formatted Table, or else it strips out the formatting.

  • Rombout Versluijs says:

    Indesign should have a option to link and keep formatting text. This is all what the app is about, i dont understand why they dont have this option.

  • >