Excel tables placed in InDesign: text & blank cells showing up as "-1"

Report · Jun 10, 2020

Hi! Hoping someone might have encountered this before - I'm working in InDesign CC 2020, and I need to place tables from Excel 2016.

I've set up Cell Styles and Table Styles, so that when I go to place a table I can check off the "Show Import Options" box and select the table style I want.

I need to bring about 150 tables into my InDesign file, with specific column widths in mind. So, I created an Excel file that is set up just like the Excel files I'll be using- except the values are left blank. I brought that into InDesign (with my table styles applied) and adjusted all the column widths.

That way, I can copy/paste this 'template' table onto each page of my InDesign document, and simply re-link the table content on each page to their corrosponding Excel files. (this lets me avoid adjusting columns individually for 150 different tables, as the column widths are retained when re-linking).

So each InDesign page would by default have a table that linked to my blank template, and then I would go through and re-link each table to the Excel file containing the needed data.

This was working perfectly in all my tests. Then, once we prepared to start going ahead with the project, some cells were populating in InDesign as simply "-1" (without the quotation marks). I've found that this is happening when the cell is supposed to just have text in it, and also when the cell is supposed to be blank.

Oddly- this problem doesn't occur if I choose 'Formatted Table' when I'm placing them. Only if I choose 'Unformatted Table' does the error occur. I thought there might be something wrong with my table styles, so I tried creating a basic table in InDesign, just changing the row colors for a couple of cells, and setting that as a new table style. When I placed a table as an 'Unformatted Table' and selected this new table style, it also generated the error.

Really hoping there is a solution to this, it's not practical for me to place all 150 tables as 'formatted tables' and manually set the table styles / column widths. Not to mention the data will be updated freqently, and I'd need to redo the whole process every time.

I can't share any of the files or screenshots unfortunately, as the data is confidential. I realize that may make it much harder to find a solution.

Report · Jun 12, 2020

Hi SBexon,

you said:

"…I can't share any of the files or screenshots unfortunately, as the data is confidential. I realize that may make it much harder to find a solution."

Well, yes. So I have to guess: Does your Excel file contain any formulas?

If so: Get rid of them by normalizing the table's contents to simple text before placing in InDesign.

Regards,
Uwe Laubender

( ACP )

Report · Jun 16, 2020

Thank you for answering! My apologies I didn't get a notification that there was a reply.

That does work if I copy/paste the data into InDesign (I didn't even need to change the content into Text), so worst case scenario I can do this. My manager is ideally wanting to be able to just update the links in InDesign instead of copying and pasting though unfortunately.

Also - I posted a new question on the board before realizing you'd replied, but I have just today found out through testing that the issue is ONLY happening when the Excel file I'm using is pulling data from another Excel file. Specifically, InDesign will not pull data from excel if it’s a linked text value. Other data can be linked without issue.

Report · Sep 30, 2020

I'm having the exact same issue, have you managed to solve it? This seems to be a bug.