Thanks for coming to InDesignSecrets.com, the world's #1 resource for all things InDesign!

Easier import of complex Excel spreadsheets

Imagine you have a large Excel spreadsheet, and you need to import sections of the spreadsheet into InDesign as separate tables. Rather than saving multiple copies of the spreadsheet, each containing the specific fragment you need, or laboriously copy and pasting into InDesign, there’s a better way.

When you choose File > Place and click the “Show Import Options” button, the dialog below appears. Normally, you’d have to use a “Cell Range” such as B6:F18 to designate which of the cells in the larger spreadsheet you want to import. But finding and remembering those coordinates can drive you batty.

A better way is to use a “named range”. In Excel, just select the cells that you want to place into InDesign, and then choose Insert > Name > Define (Mac Excel 2008) or right-click and choose Name a Range (Windows Excel 2007). Then type a name for the range and click the OK button. You can have as many different named ranges as you want in a single spreadsheet.

Then, in InDesign, choose File > Place and click Show Import Options. In the “Cell Range” drop down list you will see each of your named ranges, ready to place in your layout.

One caveat: InDesign can place spreadsheets saved in either the new .xlsx format, or the older .xls format. But named ranges will only appear in InDesign if you save the file in the older .xls format.

Tags
Related Articles
Comments

23 Comments on “Easier import of complex Excel spreadsheets

  1. Great post! And definitely a time saver for most spreadsheets. In addition to using the Insert menu, you can name a selection in Excel also by just entering a name in the Name field of the Formula Bar at the top & hitting Return/Enter.

    I just wish InDesign would support Name Ranges that are made up of non-consecutive row or columns. Those names don’t even appear in the Import Options dropdown menu.

  2. Fritz, Views are different than Ranges, but are also useful in InDesign.

    For example, one of the things that “named views” are useful for in Excel is that you can hide certain rows/columns, and then save that as a view. Then, when you place the spreadsheet, you can either choose a named view or ignore the view. This can be used in conjunction with Named Ranges.

    So for example, I could hide some rows in the middle of a spreadsheet, and then save that as a View. Then I could also create one or more named Ranges. Then I would unhide the rows before I save the file. Then when I place the spreadsheet, I can ignore the view if I want to see all the rows, or select the named view if I want the rows hidden. In addition, I can select a named Range of cells regardless of which view I’ve chosen.

    This also provides a way to bring non-consecutive rows or columns into InDesign as Anne-Marie was asking for.

  3. Is there any way to change the name of the target range for an area of ‘place linked’ information once the link has been created?

  4. @Steve H.: I don’t know of any way to change the name of the target range once a spreadsheet has been placed in InDesign with the “create links” preference chosen so that a link is created to the spreadsheet file. I think this could be done with a script though. Can you tell me a little bit more about what you are trying to accomplish?

  5. We have a item we have placed with a target range of A1:B2. We want to replicate the item with a copy\paste and then change the target of the new item to A5:b6. Thanks.

  6. Apparently I can only use my Excel Named Ranges for the first sheet in excel. This is a problem as i have a workbook with many sheets and different named ranges on each.

    When i switch the sheet in the ID excel import options box none of my named ranges show up. They all show up when it is selected as the first sheet, but it takes that range from the first sheet then, not the sheet the range was actually designed for.

    I have tried defining the ranges as both local to the sheet and global to the whole workbook in Excel but same story, apparently only named ranges for the first sheet actually work.

    Makes importing tables super annoying when you have to manually put in the coordinates for the ranges. Is this a bug?

  7. Seems that Excel import process (filter) in CS5 is not capable doing anything more complex than linking a coupel of named ranges from the first worksheet. Namely, it doesn’t support .xslm (macro-enabled) Excel 2007 – 2010 files, it doesn’t see named ranges in .xlsx (Excel 2007-2010 format), it replaces all linked ranges with the same range but from the first worksheet (.xls Excel 2003 format) from time to time. Sigh…

  8. Don’t forget, you can link the files too (Preferences Panel), then if the data changes it can be updated automatically.
    Unfortunately, any formatting you have done, is lost. However, if you have made table styles, it is easy to reapply.

  9. @Olwen Bruce, if you lay-out the table with cell styles, and you place them in a linked text frame they will keep the style.

    eg. drag a new text box, place the Excel table (make sure you have checked the ‘link documents’ box in prefs). Now cut the entire box, and paste them into the text-box that you want it on the page.

    No idea why Adobe makes a run around…

  10. The name ranges across multiple worksheets are still supported if you save the file down to an .xls format, but not in .xlsx.

    In .xlsx it lists all name ranges under the first worksheet when you go through your place options, which obviously won’t work. *very sad face*

    I am hoping that this is a bug and Adobe will be fixing it?

  11. Is it possible to place multiple sheets in one Excel file one go and then have the tables auto flow through multiple text boxes?

    thanks!

  12. Hi, is there a way to create labels using this technique so there is one master page and index the list to make each name appear on a separate page? (hopefully that makes sense)!

  13. Just checked, and it appears that Adobe fixed the xlsx problem in InDesign CC… I made a named range (in Mac Excel 2011), saved it as xlsx, and the named range appears in InDesign CC’s show import options dialog box. Yay for progress.

  14. In CS6 if you can’t see defined name ranges in a .xlsx check the below:
    - Name ranges when defined in a document must have no spaces, or special characters the only thing ALLOWED is an underscore _
    - Sheet names must not have and special characters or spaces
    - ALLOWED are underscores_ or full stops .
    - If you use a fullstop . then add another numer after it, it thinks the range has some become a number so you can not add any letters after a full stop For eg P1.L1 will not work P1.1 will work

    _ Underscores work in all instances, just remember to take out any spacing

  15. The tables I need to import are large and many: hundreds, with fields going up to 120 x AR in Excel. But over 50 x F the formatting disappears. OSX 6.8, Excel 2004, CS5. I used to be able to do much larger but can’t remember how – cut and paste I think! Wth only 50 x F I have a nightmare on hand! Help!!
    Charles
    PS Love the podcasts!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>