Why format painter is not working in Excel ???

Format Painter not working after Paste Special

Hello, Sometimes we have huge list to analyses data. So we have going through many formulas, formats, process etc to examine data easily. And by doing so we face many errors. One of the error is format painter not working on few occasion and specially when value paste data which have formula in it and answer is in number format. Also we can't use that cell value or range value in other formula.
If we use the same then you will get #N/A error instead of answers. If you apply format painter from other cell then it'll not work.

Why Error Occurred ??

When you Paste Special formula then Green Triangle appears in a corner of cell. The reason behind is cell's contents violate one of Excel error checking rules. Excel Rule are turned on by default. 

In our case it is because numbers formatted as text data. When we use this cell or range then formula will not work as formula searching for number format and not text format.

What to do ??

Double Click -

If you double click on  cell then you can see error will disappear. But what if, if you have huge range in your excel and getting same issue then double click on each cell is not possible.

Convert to Numbers - 

If you click on a cell containing a green triangle the error options pop-up appears next to it. First select all range which contain green triangle and then click on yellow diamond option and select Convert to Numbers.

Now all the errors have been disappear and also formula will work which depends on these cells.
  
Text to Column -

If error still display in cell. Then follow below process.

Select Range => Data Tab => Text to Column 

Convert text to Column Wizard dialog box will open. Simply click on Next, Next and Finish.
and Errors are not seen anymore.

Video Tutorial - 

Hope you understand. Thank you. :)

Thank you !! Enjoy Excelling !! Have a Nice Day !!