I played a sign-heavy build in all 3 games. In the first game, I made heavy use of Aard, Quen and Axii. Aard is the only sign you get at the start so I naturally used it quite a bit, and it's very useful for knocking down enemies for the instant kill. I started using Quen more after I got the upgrade for the healing effect which made it much more useful, and Axii for turning enemies on each other was mostly fun, but also useful in situations where they had high numbers. I found Yrden absolutely useless in the first game and didn't upgrade it; in just about any case I could use Yrden there was always a better alternative.
In the second game, signs are just overpowered in my opinion. The very long range fireball effect of Igni allowed me to kill enemies at a distance and with investment in the magic tree and the right gear to boost vigor, I could spam Igni and turn Geralt into a living fireball machine gun. Axii was probably my second most favoured sign but Yrden actually became useful; it was very situational, but in the situations where it came in handy I could not have won without it. For example, (SPOILER) the first fight against Letho at the end of the first Act would not have been possible with my sign build otherwise as his frequent Quen use basically nullified the effects of my other signs, but stunning him with Yrden and then using my sword was a lifesaver.
Unexpectedly, Yrden became one of my most used signs in the third game. I abused the Magic Trap alternate form by just placing it on the ground and running around it until the enemy died; it basically dealt with every contract monster since you tend to fight them one-on-one and the magic trap attack staggers them for enough time that you can place a second if the first isn't enough. Igni is a lot less overpowered but I actually like it this way because I think it made fights a little too easy in 2 if you have a magic build; I still used Igni extensively in fights where I actually used my sword because it was helpful to lay down that extra bit of damage and keep enemies distracted by setting them on fire. Axii was slower to cast in 3, which also made it more fair, but I still used it quite a lot.
I think I used Axii the most over the course of all 3 games. Part of the reason is because it just suits my playstyle as I enjoy causing confusion among enemies using illusion/mind control type magic in other games as well. It is genuinely very useful for my almost exclusively sign-focused build too because my combat ability isn't quite as good as the game expects it to be at many points, so it's helpful to have an extra ally or two on your side. It's not as good as Igni in 2 or Yrden in 3, but through all 3 games it's the one sign I kept using consistently regardless of the title.