Paste with Formatting

Conditional formatting in Excel is one of the things every analyst should know well. When you have to compare loads of data the best way is to plot them, but even better to colorcode the values. That is pretty easy in Excel, as long as you can use conditional formatting (highlight a range, then conditional formatting > color scales > pick a scale you like).

But there is a problem when you want to copy a part of bigger colorcoded table while maintaining the colors. At the moment I do not know any way of doing that beside one workaround, which can be summarised as

  • copy table to Word or Outlook;
  • copy it back to Excel.

    Assume we have a clean table, without formatting.

    Clean table

    Clean table

    Apply conditional formatting, like color scales.

    Apply conditional formatting

    Apply conditional formatting

    So the table looks like that.

    Formatted table

    Formatted table

    At this point copying the table anywhere in the workbook (even using paste special and option keep source formatting) will copy the conditions, so if you change values in the table, or copy only a part of it, the formatting will change.

    Paste special - Excel context menu

    Paste special – Excel context menu

    That is why now we copy data to Outlook (it also works with Word). Because then, the option keep source formatting behaves differently. So we paste the table keeping the source formatting to an empty Outlook email.

    Paste special - Outlook

    Paste special – Outlook

    Now we can copy the table back from Outlook to Excel, and viola! We can now change the values (see screen below) and the colouring stays exactly the same as in the source table. The only drawback is that the borders got thicker, but that is much easier to fix than colouring the table manually.

    Paste back to Excel

    Paste back to Excel

  • Leave a Reply