Link InDesign tables to Excel (Master InDesign Tables - Part 2/3)

by Stefano Bernardi

Note: This is Part 2 of a three-part series on InDesign tables. Part 1 is about the three different ways to create a table in InDesign. Be sure to subscribe to our newsletter to keep up with future posts or major updates.

How to link an Excel file to InDesign

This is the feature in InDesign I love the most. You can import a linking spreadsheet that you can update from Excel. If you are not very practical with InDesign tables please have a look at part 1 of this series before reading the post.

Problem:

Let’s say that you have a product catalogue or a price list and sometimes you need to apply changes to the product prices listed in a table.

Wrong way:

If you just import the Excel file into InDesign, then when it’s time to change the prices you’ll have to:

  • either import the Excel file into InDesign back again
  • or edit the pricing manually

This is NOT something I would love to do any time there is a price update.

Linking an Excel spreadsheet to InDesign (the right way)

In this case, the best way to create the table is to import the Excel file into InDesign, making sure that InDesign creates a link with the spreadsheet.

This is how you do so:

Set the Preferences

Click on InDesign (on MacOS) or Edit (on Windows) in the main menu bar, go to Preferences in the menu that opens, and click on File Handling...
Import an Excel file into InDesign linking the spreadsheet: click on File Handling

Check Create Links When Placing Text and Spreadsheet Files in the File Handling window and click OK.
Import an Excel file into InDesign: Check the option for linking Excel spreadsheets

Now let’s import a new Excel file into InDesign.

Place the Table

Let’s click File > Place (Otherwise you can press Cmd+D on Mac Os  or Ctrl+D on Windows)
Import an Excel file into InDesign: click on Place...

Check Show Import Options in the import window (If it’s not already checked), select the file and click Open to import the Excel file into InDesign.
Import an Excel file into InDesign: Check Show Import Options

This is important: As we’ve seen in the Import of the Excel file above, here you can personalize a few settings. In this specific case I have already created a Table Style (more about this in part 3), and I am going to use it.

Be aware that if in this step you don’t apply a Table Style, any time the linked Excel changes you will lose the layout properties of the Table placed in InDesign.

Import an Excel file into InDesign: linking spreadsheet

Click OK and then draw a text-frame in InDesign to place your table.
Import an Excel file into InDesign: create the text-frame

This is the table in InDesign linked to the Excel spreadsheet.
Import an Excel file into InDesign: linked table

We want to set the first line as the header.

Select the first row, right-click and select Convert to Header Rows.
Import an Excel file into InDesign: convert to header rows

I’ll also adapt the columns width. This is the table we just created importing a spreadsheet from Excel to InDesign.
Import an Excel file into InDesign: imported table

Improve Your InDesign Skills

Sign up today for free and be the first to get notified on new tutorials and tips about InDesign. Immediately get this useful InDesign Shortcut Cheat Sheet sent to your inbox.

Changing the data inside the spreadsheet

Let’s say that you want to change your prices in Dollars (they are in Euros right now).

You need to:

  • change the € symbol to the $ symbol
  • convert all the prices to their value in dollars

Inside Excel

We start from this table.
Import an Excel file into InDesign: table in €

Let’s make this change inside the Excel file. The first thing we change is the € to the $ symbol.
Import an Excel file into InDesign: table in $

Then with an expression, I’ll change the value of the prices and I’ll save the file in Excel.
Import an Excel file into InDesign: updated table in $

Back in InDesign

Back in InDesign we see this warning triangle which tells us that the table in the Excel file has been modified.
Import an Excel file into InDesign: warning-triangle

Double-click on the triangle to automatically update the linked table.
Import an Excel file into InDesign: warning-triangle

Perfect! The table in InDesign is updated!
Import an Excel file into InDesign: updated table

 

You may wonder, “What happens if I add a row to the Excel spreadsheet?”

 

Import an Excel file into InDesign: edited table in Excel

Here is the table in InDesign before (left) and after (right) the update. As you can see, the updated table lost its header settings.
Import an Excel file into InDesign: previous version and updated version

Again, select the first row, right-click and select Convert to Header Rows.
Import an Excel file into InDesign: convert to header rows

Important: While the width of your columns will be maintained each time the table is updated in Excel and the changes are applied in InDesign, if you make important changes to the spreadsheet (like adding a row), you will always need to set the header back.

Conclusion

Please be aware that with the Create Links When Placing Text and Spreadsheet Files feature active, any time you import an Excel file with the Place command you’ll create a linked table. To unlink the table, just click on the link of the table inside the Links window and select Unlink.

As you see, linking an Excel spreadsheet into InDesign is quite simple, yet very powerful. One of the handiest ways I use this feature is when I have to localize documents to the US market.

The Excel I use is generally like this:

  • A spreadsheet in €
  • A spreadsheet in $
  • A spreadsheet for variables (like the change value between € and $)

And my process is:

  • I create the Italian version (which has a linked table in €)
  • then I create the English version with Redokun and change the link of the table to the spreadsheet in $

This way, any time there is a pricing change in my spreadsheet in €, the changes are automatically made into the spreadsheet in $, and both my Italian and English documents are updated.

Must-know InDesign Keyboard Shortcuts

Command  OS X Windows
Place a file (image, spreadsheet, etc) into InDesign Cmd + D Ctrl + D

Useful resources

Here a useful video by Lydia on linking an Excel spreadsheet to InDesign: https://www.youtube.com/watch?v=yjEa0Zk75-o

In the next post (part 3)

In Part 3 we will learn how to use InDesign Table Styles with a quick exercise. We will create the styles we need to lay out the table we’ve seen in this post (part 2).

Be sure to subscribe to our newsletter to keep up with future posts or major updates. If you liked the post, I'd really appreciate you sharing it!

Stefano Bernardi
Stefano Bernardi

Stefano has worked on numerous mid to large–sized InDesign projects for Alstom, DeLonghi, Philips, and many others before starting Redokun in 2015.
As Redokun’s Co-Founder, Stefano spends most of his time helping customers to optimize their InDesign work-flow. He also holds in-house InDesign courses for companies in the Venice, Italy area.

19 Tips To Optimize Your InDesign File For Translation (Free Ebook)

Free InDesign Ebook
A short guide for InDesign users who work on multilingual projects. It'll give you an exhaustive look into the issue of creating an InDesign document ready for translation.

Get your multilingual projects done faster with Redokun. Free for 14 days.