How to copy a cell's formatting using a formula?

For example, cell A1 contains the text "Hello World" which is in bold.

In cell A2, I use the formula =A1. Therefore cell A2 now also contains "Hello World", but it is not in bold. How can I modify the formula to also copy the formatting (in this case, bold) of A1?

A more complex example is strikethrough properties, i.e. A1 contains "Orange/~~Red~~". How do I show the same content in cell A2 dynamically, so that any changes made in A1 will update A2 as well?

Alvin Lim

Posted 2012-10-17T09:17:58.073

Reputation: 71

1Has to be an option, I don't know anyway to solve this just with formulars, so without VBA or some other Magic, this won't work. There might be some way to use conditional formatting for this, but this will have quite some restirctions to it. – Jook – 2012-10-17T11:21:28.717

Answers

Using only formulas and no custom add-ons nor VBA: you can't. At least not with Excel up to 2007 (and quite probably all later versions as well)

First of all, there is no built-in formula that catches any form of formating.

Second, formulas only display their values. They don't influence formatting unless you use Conditional Formatting. But even if you do, there is no easy option to "copy formatting from the other cell". Besides, conditional formatting is limited in what it can format compared with "manual mouse clicking".

Adam Ryczkowski

Posted 2012-10-17T09:17:58.073

Reputation: 752

so is there any alternative? – Alvin Lim – 2012-10-19T09:03:57.507

1Yes. As I've written: VBA or custom add-ons. – Adam Ryczkowski – 2012-10-19T13:39:11.813

1@pnuts Yes. A good remark. I didn't think about it! – Adam Ryczkowski – 2013-04-04T08:55:34.680

-2

Copy the cell then hit Alt-E-S-A-Enter Alt-E-S this opens up the paste special then A copies all properties

finn green

Posted 2012-10-17T09:17:58.073

Reputation: 1

The OP is asking clearly how to do it with a formula not using the keyboard. – Ignacio Soler Garcia – 2020-02-26T15:24:46.153