Most times when i use magic i use conjuration and destruction as my one two punch. I use illusion for sneaking but is it effective when not sneaking

Scenario- stumble into bandit camp. If i cast fury won't that just make my enemy more aggresive? Does it work on falmer/vampire/constructs

Fury will make them attack indiscriminately, including each other. It works best when cast on the one at the back, who'll find other targets before you.

If you get lucky, you'll only have one enemy left, but you still need a way to finish that one.

The mind control spells of illusion are very powerful but take a big commitment. You really have to fill up the illusion perk tree and dual cast. The limits on foes affected can be pushed up pretty well via perks and dual casting, but I also actually take steps to limit how high my character can level - for the purpose of also limiting the levels of foes so those mind control spells can always work. With the proper perks, the spells work on most anything (except dragons).

Casting frenzy type spells works best from long range and in stealth.

You still need a pure killing ability - I like the bow for that. Conjuration can work also.

If you get charged by a single brute, you can use pacify to get some distance, probably regain stealth and snipe them out.

If you get charged by a group, you can use fear to make them all run away and give you time to regain stealth and plan your reattack.

I once took my archer into a cave where, behind a narrowing of the passage, 2 bandits were discussing domesticating a wolf they had in the cage around the same bend. Next to him is a pull chain. "Hmm, this could be interesting." He casts Fury on one bandit and pulls the chain to release the very NOT domesticated wolf. It was a glorious few seconds of carnage.

So if i cast fury as long as i keep 1 unaffected bad guy in front of me it should work?

Should have been using this on the 3 guys that teach me a lesson

You don't have to leave 1 guy unaffected (although I guess that would be most efficient). Me, I spam Fury on everything that moves when a fight starts...

And yes, I've used it agains the thugs, as well as the bandits who disguise themselves as Imperial troops. Most satisfying. I've also used it in Northwatch Keep, with happy results. All that said, I have to strongly second Acadian's point about investing heavily in the skill tree. Otherwise, you might discover the hard way that your tricks that worked so well on your previous level suddenly don't affect anything.

Fury/Frenzy spells also have the side effect of forcing the NPC to sheathe/redraw their weapon, which buys you a bit of time.