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.
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.
That is really interesting.
Can you achieve a similar result by using views in Excel?
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.
Wow … must try this out! Thanks Keith.
Great information!!!!
@Keith – I smell a followup blog post…
Thanks. I live on Excel spreadsheets. And just like the ink tip, every little bit helps.
Ed
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?
@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?
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.
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?
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…
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.
@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…