When it's out, which it isn't yet, you go to your Library and click All Games, then select Tools. It will appear in that submenu when it's out. (Probably in about 2-3 hours)
You will be able to use it offline, but I believe you will have to have steam on your computer to use it.
Well, you'll need Steam to test the mod in the first place. You don't want to upload a mod you just made without ever testing it works in the game right?
You can download third party programs to avoid steam entierly.. I dont know if that is allowed though.
Since (I think) 1.2 it isn't possible to run the Skyrim executable without Steam anymore. With vanilla it was possible to run the executable directly, but they patched it, if I'm correct