Importing Excel Table with Cell Colours

[Microphonic]

Hi again InDesign Wizards.

I'm trying to place a linked Excel chart into InDesign, that I can freely edit formatting on but crucially retains the cell colours, which are all different shades corresponding to data values.

Ideally these cell colours would update along with the data if the source Excel file is changed too.

I've had no luck so far; when placing the table and maintaining formatting it keeps the typeface used etc but the cells are not coloured.

It's such a big table manually recolouring by referencing the original would take hours.

Any ideas?

Thanks in advance,

Sam

[BobLevine]

Place it as a formatted table. Be aware that any changes you make in InDesign will be lost if you update the link.

[Microphonic]

Sorry but it's not working - please see screenshots below for my import settings:

And this is the result: (InDesign on the left, Excel on the right) As you see, the coloured cells have not been replicated in InDesign.

Thanks for your help,

Sam

[Laubender]

Hi Sam,

check if the imported Excel file is indeed an InDesign table.

It could happen that the import filter is creating a text frame with tabbed text instead of a table with table cells ( it happend a few times to me* ). Best show a screenshot with Frame Edges showing and Hidden Characters showing.

* I did not find the reason for this issue.

Regards,
Uwe

[Microphonic]

Thanks but it is a table as when I go to Table > Delete Table it does just that

[BobLevine]

Try saving the Excel file as XLS instead of XLSX.

[Microphonic]

Thanks, but didn't work

[Microphonic]

I found a work around but it's not ideal - copying and pasting table directly into InDesign messes up the colours but there are colours. Copying it into Word and then Copying it from Word and pasting into InDesign works. Problem is of course the table is not linked to if the data changes I will have to do all this again...

[BobLevine]

Well, if you absolutely need to make no changes in InDesign, why not place a PDF?

[Microphonic]

Ah, so export chart from Excel as a PDF, then place PDF in InDesign, then if data changes export another PDF and relink to the new PDF?

[BobLevine]

Yes. Placing the formatted table should work, but without seeing the files, it’s hard to know what’s up there.

But the PDF would work just as well.

[vinny38]

Yeah pity...

Indesign doesn't retain conditional formatting.

You need a way to "convert" this conditional formatting into a "hard" one.

Basically, this question would better fit in an Excel Forum, since I'm pretty sure there are VBA solutions for it.

Now, here's a macro-free suggestion:

In Excel, copy your table, then click on the "clipboard icon", ("Presse-papier" in French in the following example), then double click on the element on the list.

Since your table should still be selected, this will paste the values, keeping the cells colors and removing the conditions...

From there, you can import your formatted table in Indesign.

Of course, if you need to change values afterwards, you must remember to reapply your conditional rules before redoing the copy/paste clipboard process.

Illustration:

[BobLevine]

I missed that the colors were conditionally styled.

But I do have a suggestion on a previous post. If the table is copy / pasted in, do the colors, even if incorrect, get added to the swatches panel?

[Laubender]

Hi Bob,

just tested this.

Excel:

Colored some cells with Excel Standard Colors.

Selected the cells.

Copied the cells.

InDesign

Set the Clipboard Handling to allow formatted contents

Pasted the copied cells.

Result: An InDesign table.

All used colors were inserted in the Swatches Panel.

Some were even named like "Brown", "Red", "Gold", "Lime" or "Sky Blue".

Others were named very strange. E.g. "RTF r0 g102 b204"

Excell sheet:

Here the cell A4 that I colored with "Lime" in Excel is selected with InDesign:

MS Excel 2011 and InDesign CC 2019 on Windows 10.

Regards,
Uwe

[Laubender]

Hi Vinny,

now I tried a Excel sheet with a condition.

Copy/paste did not work as expected.

Resulting in cells with wrong colors:

My workround to get rid of the condition:

From Excel copy/paste the cells to MS Word, copy/paste from Word to InDesign:

Regards,
Uwe

[Laubender]

vinny38  wrote

…

Now, here's a macro-free suggestion:

In Excel, copy your table, then click on the "clipboard icon", ("Presse-papier" in French in the following example), then double click on the element on the list.

Since your table should still be selected, this will paste the values, keeping the cells colors and removing the conditions...

…

Hi Vinny,

unfortunately that did not work with my Excel 2011 on Windows 10.

So I needed yet another step with Word 2011 to get rid of the condition.

Maybe I did something wrong by double-clicking the contents of the clipboard in Excel's clipboard handling frame?

Or perhaps a preference in Excel I did not set?

Regards,
Uwe

[vinny38]

Hi Uwe.

Yes, Excel to Word then Word to Indesign works fine.

OP did write about this technique but pointed that -of course- link is not dynamic.

This is why is suggested the "Excel clipboard" tweak.

Please note I tested it with Excel 2010 - Indd CS6 - Windows 7.

But to make it "work", you need to place the Excel file, not pasting the cells.

Tried with a couple of conditional swatches. Works fine. Conditional icon sets are not retrieved though, but that's another story...

Kinda long color names though ^^

[Laubender]

Hi Vinny,

thanks for clarifiction with place and link the Excel file in InDesign.

Also interesting to see the different naming conventions of the colors.

If we copy/paste from Word a color name will be constructed differently compared to place and link.

Did not try your precise description with the "Excel clipboard" tweak for place and link, yet.

Will come back and report…

Regards,
Uwe

[Dave Creamer of IDEAS]

Have you looked at DTP Tools Active Tables (part of a subscription to multiple plugins)?

There is a screen capture from the plugin. The "conditions" are only binary for positive and negative numbers however.

[Microphonic]

Thanks all - have a meeting imminently where I need to show the design with correct cell colours so although not ideal for now I've used the copy to Word trick just so I can show colleagues the table in place. When I have some more time I will try some of the suggestions above as making the link dynamic will save a lot of time going forward.

Thanks for all your help,

Sam