InDesign Secrets: Linking a table to an Excel spreadsheet for easy updating

Published by | Thursday, May 10th, 2012

In this week’s free InDesign Secrets episode, Anne-Marie Concepcion shows you how to place Excel spreadsheets into your InDesign documents as tables, and walks you through the process of linking your table and your original Excel spreadsheet to avoid having to manually update and reformat every time a colleague updates the spreadsheet in Excel.

I have to admit I find this tip vitally compelling, particularly because tables in InDesign are still a bit of a mystery to me. (I have Diane Burns’ InDesign Tables in Depth course in my queue for this very reason.) Once I establish how I want the formatting to work in a given table, the last thing I want is to have to re-establish said formatting on an entirely new table. Of course, the second to last thing I want is to painstakingly make manual data changes to an existing InDesign table (no matter how pretty I made it.)

InDesign table example.

Example of a table with formatting created in InDesign.

In this video, Anne-Marie explains the process for creating linked tables that save you from the hassle of manual updating or reformatting. Your first step is to set the Preferences so that InDesign knows you want spreadsheets to come in as linked files. In the File Handling portion of the Preferences dialogue box, you’ll select the Create Links When Placing Text and Spreadsheet Files option in the Links section.

InDesign Preferences dialogue box example.

With this option selected, when data in your native Excel sheet is updated,  you will see a notification in the Links panel letting you know that your spreadsheet has been modified. Just double-click on that notification, and the data elements of your table that have been modified will update automatically, with no need for you to reformat your table. (OK, sometimes you may need to reestablish the header row or adjust cell size.)

If you are looking for more consistent table results, Anne Marie recommends creating a table style before placing your Excel file, and then applying that table style when you are placing your Excel table into InDesign. If you are new to table styles, I recommend checking out Michael Murphy’s InDesign Styles in Depth, in this case, specifically chapter six which covers table and cell styles.

Note that you may not always want every single spreadsheet table you place to update automatically, so remember to uncheck the Create Links option as soon as you are done working with the table you want linked, or find yourself facing updates where you don’t expect them.

Meanwhile, exclusively for members of lynda.com, Anne-Marie’s partner in InDesign secrecy, David Blatner, has a movie that shows you how to create electronic sticky notes in InDesign.

Stay tuned, Anne-Marie and David will be back in two weeks with more InDesign Secrets.

Interested in more?
• Start your 7-day free trial to lynda.com today
• The entire InDesign Secrets bi-weekly series
• Courses by David Blatner and Anne-Marie Concepcion on lynda.com
• All lynda.com InDesign courses

Share this:Share on Facebook7Tweet about this on Twitter8Share on Google+0Pin on Pinterest9Share on LinkedIn0

lynda.com - start learning today

Tags: ,


24 Responses to “InDesign Secrets: Linking a table to an Excel spreadsheet for easy updating”

  1. Very happy to report that the header and footer rows are now maintained when you update the links in CS6! So you don’t have to reapply them each time. Whew…

  2. Whew, indeed, David, thanks for the update. One step closer to InDesign tables behaving in a way that doesn’t scare me off!

  3. Lyndsey says:

    I think you just saved my sanity, and my employer thanks you for it. Thanks so much for sharing!

  4. Bronson Yanddall says:

    When I style my text after I insert the Excell file, and then update the Excell file, and then Update the Indesign file in the ‘Links’ panel, the text styles ALL change to one of the styles I used to style the text. Is there any way I can keep my text formatting when I update the Excell file?

    • Brosnon, I sent your question to Anne-Marie for her InDesign expertise. Here’s what she said:

      To ensure the formatting of text stays intact after you update the Excel link, you need to *not* use local formatting, and instead use Table Styles (which are made up of Cell Styles, which can have Paragraph Styles associated with them).

      So you create the paragraph styles for your table, then attach them to cell styles, then make a table style that automatically uses those cell styles.

      Hope that helps!

      AM

      Let us know if that solves your formatting frustration.

      • Bronson Yanddall says:

        Hi Colleen,

        thanks so much for your reply. Yes I am aware of the applying of Paragraph Styles through Table styles, but I have a different Paragraph style for each column and I have 5 columns. So the Table styles don’t appear to have an option to preserve Paragraph styles when updating the text and when you have different styles for each column. It would be so great if Anne-Marie new how to do this.

        Regards,
        Bronson

        • Hi Bronson.

          I admire your intrepid use of tables. Full disclosure, when it comes to InDesign Tables I seem to be unnaturally incompetent; they make my head hurt before I even get to the challenging stuff. So I hit up Diane Burns, author of our InDesign Tables In Depth course, for advice. (She’s the one person who made me think maybe, probably, tables could do some cool stuff if I just paid attention.)

          Here’s Diane’s advice:

          It sounds like there is some confusion between table styles and cell styles.

          Cell styles let you assign a paragraph style, along with other cell attributes such as fill, inset, etc.

          Then, Table styles let you assign a particular cell style to parts of your table. But only to 5 parts of the table: the header row, the left-most column, the right-most column, the footer row, then all the cells in the middle (body cells).

          The best in this case would probably be to create a table style that formats the 1st and 5th column with specific cell styles (the left-most and right-most columns). Then, create a separate cell style, with the appropriate paragraph style, for the 3 columns in the middle. Not completely automatic, but still, wouldn’t take much time to do.

          This is covered in Chapter 4 of my tables title, with this particular issue addressed in “Using cell styles to ‘clean up’ table styles”

          Let me know if that gets you closer to a solution. And thanks for the great conversation!

          cw

          • Bronson Yanddall says:

            Thanks so much for this Colleen. I have tried that and it works perfectly.
            Thanks again, I really appreciate it!
            Bronson.

  5. John says:

    Thanks for the article Colleen, it’s a big help.

    I have a question I’m hoping you can answer. While I understand I can link to an excel document, am I able to somehow link to a specific tab within that document? And potentially even specify which cells, somehow? I know I’m probably wishing for too much here, but better to ask I figure!

    Thanks again.

    • You’re welcome, John and yes you can choose a specific tab and even cells.

      The key to the trick is to check the Show Import Options box in the Place dialog box when you go to place your Excel file.

      InDesign Place Import Options

      Once you click Open in the Place dialog box and you have those options turned on, you’ll get this secondary dialog box where you can choose Sheet (which is really one of the tabs in my document) or even specify a cell range for import.

      Indesign-Excel-options

      Wish granted!

      Note: I could not get ID to place an .xlsx file at all, is that an unusual experience? I had to open my .xlsx and save it down for the Place command to work at all.

      cw

  6. John says:

    Thanks again Colleen, looks like this will be a huge time-saver down the stretch as we update over a dozen price books with thousands of sku’s. Not familiar with the .xlsx issue (yet at least!).

    While playing with this I ran into another issue that I realized was a simple answer, which I’ll post here just in case anyone finds this on a search engine like me. If your placed table shows red dots in a column, that column is just too small for the content in the cells and if you make it larger it’ll show up.

  7. Bronson Yanddall says:

    Hi Colleen,

    I don’t suppose Diane Burns knows how to insert images into the Excell document cell so it appears in the Indesign document? I inserted a JPG into an excell cell but it doesn’t appear in Indesign, and I also inserted the JPG into a Indesign cell but when other text in Excell was edited and then updated in Indesign the JPG disappears. Please let me know if I have to pay for this info, since I am doing this for the company I work for.

    Regards,
    Bronson.

    • Hi, @Brandon.

      I was able to bring some graphics from an Excel file into an InDesign table (although I admit I was sort of amazed it worked!) The trick was to make sure the Show Import Options checkbox was checked in the Place dialog box, then make sure the Include Inline Graphics checkbox was checked in the Import Options dialog box.

      InDesign Import Options dialog box

      As to how I placed the images in Excel, I used Excel for Mac’s Insert command. The import to InDesign process seemed to work for photos and symbols. As near as I can tell, cell placement in InDesign depended on where the image’s top handle had been in Excel. But the triangle shape (not an existing file, but something created on-the-fly in Excel) is missing entirely in InDesign. Here are my before and afters:

      Excel table with images imported to InDesign

      Anyone else have experience with this process and its idiosyncrasies?

      • Bronson Yanddall says:

        Hi Colleen,

        thanks! I have tried this and it appears that Inline Graphics can only be added when you use “Formatted Table” and “Formatted Once” selected in the Import Options, and when you change the stlyes and then edit and update the Excell file, the styles in Indesign will revert back to the Formated Table Style the table originally imported as. But I will keep trying. I may just have to go without images.

        Thanks,
        Bronson.

  8. John says:

    Colleen,

    We had some great success with a couple of our price books and finished with no issues. We started on a much larger price books and got through two worksheets just fine, but once we got to a third worksheet we ran into an issue where it’s telling me we didn’t enter a ‘Valid cell range’ even though it’s clearly the correct sheet and cell range.

    It’s odd because, as I said, two pages worked just fine and all of a sudden on this page we ran into an issue. The same issue existed when I tried on a worksheet further into the book. I’m not seeing any difference between the worksheets at all. I’ve attached a screenshot below. Have you encountered this before? Any ideas?

    Thanks a lot!

    John

    http://imageshack.us/a/img819/3448/linkingissue.jpg

  9. John says:

    Oh – and I just copied the cells I need from that worksheet and placed it on a worksheet that worked for me, and it worked fine!

  10. Lisa says:

    Hi Colleen

    I am having an issue with the text not showing up after I import it. I’ve followed the steps above that shows how to select specific cells from a specific sheet in the workbook. The numbers import fine, but the text doesn’t (for example, menu items and pricing). I’ve tried to copy and pasting the text portion into the table, but when I make changes in the excel document and update ID, the text values in the table update to being blank again.

    Is there a step that I’m missing?

    Thanks!

  11. Henriette says:

    Hi Colleen

    Love your tutorials!!:-) However, after linking to my cells the links do not appear in the Links Panel – what can I do to make them show in the Panel?? Right now I have to place every cell when there’s an update in my Excel!!

    Hope you or someone else can help me.

    Thanks!

  12. Justin says:

    Thanks so much for this info! I have one question – is there a way to import the color of cells into the InDesign cells? Much akin to recreating a “heat map” table? So, say if I have set my Excel file to color all cells displaying a numerical range of 1-4, they are blue, 5 -6, green, etc….Is there a way to bring that formatting into the InDesign table?

  13. Michael says:

    Is there a way to link an excel file to a table in InDesign 6 that will automatically update the InDesign file when an excel change is made? Or do you always have to go and manually update the link?

  14. SAndy says:

    Hi, I use illustrator CS6 is there a script or plugin that will allow linking a table to an Excel spreadsheet for easy updating for illustrator.

  15. William says:

    This is a wonderful way to be alerted when the excel file has been updated and the person(s) updating have neglected to inform the designer. Thank you. However, for some reason I’m unable to update the table in InDesign. I see that the table has been modified both on the table and in the links panel but double-clicking does nothing and when I right click on the link in the link panel, “Update Link” is not an option. Have I missed a step?

Leave a Reply