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.)
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.
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
Tags: InDesign, InDesign tables
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…
Whew, indeed, David, thanks for the update. One step closer to InDesign tables behaving in a way that doesn’t scare me off!
I think you just saved my sanity, and my employer thanks you for it. Thanks so much for sharing!
What a testimonial! Glad we were able to help, Lyndsey.
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:
Let us know if that solves your formatting frustration.
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:
Let me know if that gets you closer to a solution. And thanks for the great conversation!
cw
Thanks so much for this Colleen. I have tried that and it works perfectly.
Thanks again, I really appreciate it!
Bronson.
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.
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.
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
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.
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.
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:
Anyone else have experience with this process and its idiosyncrasies?
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.
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
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!
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!