Skip to main content
M
Microphonic
Known Participant
March 23, 2019
Question

Importing Excel Table with Cell Colours

  • March 23, 2019
  • 5 replies
  • 5039 views

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

    This topic has been closed for replies.

    5 replies

    M
    MicrophonicAuthor
    Known Participant
    March 26, 2019

    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

    Dave Creamer of IDEAS
    Dave Creamer of IDEAS
    Adobe Expert
    March 25, 2019

    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.

    David Creamer: Community Expert (ACI and ACE 1995-2023)
      vinny38
      vinny38
      Brainiac
      March 25, 2019

      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
      BobLevine
      Adobe Expert
      March 25, 2019

      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?

      L
      Laubender
      Adobe Expert
      March 25, 2019

      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

        M
        MicrophonicAuthor
        Known Participant
        March 23, 2019

        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
        BobLevine
        Adobe Expert
        March 23, 2019

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

        M
        MicrophonicAuthor
        Known Participant
        March 23, 2019

        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
        BobLevine
        Adobe Expert
        March 23, 2019

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

        M
        MicrophonicAuthor
        Known Participant
        March 23, 2019

        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

        L
        Laubender
        Adobe Expert
        March 23, 2019

        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

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices