I've only been to three Football matches in the last 5+ years but I did go to Tamworth last year which only has about 15 seats so standing was the only option. It is definitely different, though it depends on how steep the stand is. If you're standing basically on the same level as the pitch, the view is pretty poor, but you are closer obviously.
I also have the displeasure of visiting various old Rugby grounds where standing is the norm for away fans. It does improve the atmosphere and your overall experience but I do prefer my season ticket seat at the DW Stadium over any standing possibilities. Probably because I'm lazy but I do prefer to see the game rather than dance around and make noise.