I don't think there is a universally accepted formal answer to this. This answer is my own style, which is reflected by Wikipedia.
From Wikipedia's article on the numero sign,
№ is a typographic abbreviation for the word number(s) indicating an ordinal numeration, especially in names and titles.
Use № only when you are using numerals (not when the number is spelled out), and only when you are referring to the number as an identifier/designation rather than as a quantity.
So I would advocate for:
- Observation № 22
- Now serving № 8
- Insert part № 3 into slot 7
I would advocate against:
- № of males in sample 5
- Now serving № eight
- Wow, that was home run № 3!
You also brought up using numero signs in tables. It is considered acceptable to bend style guidelines when it comes to layout and other graphic design; if "№" fits where "number" does not, feel free to use the shorter version.
(There is also an archaic usage for the numero, in which it may be used to indicate quantity, as "№" does have a definition in which it means "in number," but I've never seen this usage in practice.)