Yes it makes them look way better, depending on how messed up your teeth were to begin with before braces. Braces make you teeth come together and close any gaps up, they suck to have but once you get them off your smile is 100% better, and your teeth look 100% better.
When they get their braces off? Nope. I had braces for 3 years and when I got them off, it took weeks for people to actually notice. Some of my friends noticed sooner, but some just didn't notice at all.
If you knew and remember what the person looked like without braces, the condition of their teeth specifically, then it's pretty amazing how straight someone's teeth are afterward.
I have a friend who couldn't get over how straight my teeth looked when I got my braces off. That's probably because she has braces, too, and fantasizes about getting them off, lol.