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
Assume we have a clean table, without formatting.
Apply conditional formatting, like color scales.
So the table looks like that.
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.
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.
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.