Here's what I found: if I have some cells with valid numeric entries such as 5 or =3+4 and then apply the ribbon command HOME/Number/Text , the format of the cell itself follows the rules for text, such as center vs. left alignment. However, the formula remains valid and the cell displays the result (7) . Further, if I turn on DATA/Filter, the filter pop-up menu includes the "Number Filter" option and not the "Text filter" option.

However, if I first select empty cells and apply HOME/Number/Text , and only then enter numbers into the cells, the formula is not processed, and the Filter option displayed is "Text filter." This suggests to me that the Number formatting tool doesn't re-evaluate what's already in the cell. Is this a bug, or is there some reason this was intended to happen?

FWIW, this is in Excel Office Professional Plus 2013 .

  • Is this a bug, or is there some reason this was intended to happen I guess it's hard to answer unless we worked on the project :S – Dave Jan 5 '17 at 15:37
  • I think it is a bug, very common, very annoying. – Branislav Kollár Jan 5 '17 at 15:40

This is only speculation, since we'd need someone who actually worked on the Excel team to be sure.

That said, changing the format of a cell should never be a destructive action. Formatting should affect display and data entry only. That Excel acts this way and acts this way consistently strongly suggests it's a conscious design decision.

You might intentionally want to change formulas into text, but for everyone who wants to intentionally do that, someone else will be seriously annoyed if a mere formatting change blew away their carefully-crafted formula. That someone else might even be you on a different day. (Copy, paste values if you really want to.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.