There's no "on" and "off". The following explanation isn't completely accurate but I think it's a good enough approximation to understand what's going on. In particular, I'm really talking about what's called "linear polarization", which, as you can guess, isn't quite what a circular polarizer deals with.
Polarization refers, roughly speaking, to the direction in which the light waves are wiggling. Consider a rope that has one end tied to the wall. If you wiggle the free end up and down, you'll create a wave in the rope, with everything in it wiggling up and down. But you could also wiggle the end from side to side, or at any other angle you choose.
A polarizing filter only lets light through that's wiggling in a particular direction (or, rather, in a particular fairly narrow range of directions). To continue the rope analogy, consider passing the rope through a board that has a slot cut in it. If you make a wave in the rope that's moving in the same direction as the slot, the wave will pass through the slot; but if you make a wave in a different direction, the wave will just happen between the slot and your hand. Of course, in the polarizing filter, it's the equivalent of the slot that's rotated, rather than the light waves.
Light can be either completely polarized (all the waves wiggling in the same direction) or completely unpolarized (all wiggling in random directions) or somewhere in between (all directions present but some directions more common). Whenever you put light through a polarizing filter, it blocks out most of the light that isn't wiggling in the direction the filter allows. Most ordinary light is unpolarized: if you put that through a polarizing filter, it just blocks out some fraction of the light coming in. That's why you didn't see anything in your tests.
However, the light from the blue part of the sky away from the sun is quite strongly polarized. Face at 90° from the sun (e.g., face west or east with the sun in the south) and look at the sky through the polarizer. Rotate the polarizer to the angle that makes everything as bright as possible. Your polarizer is now aligned with the light from the sky and it's letting nearly all of it through. However, as you rotate the polarizer, you'll see that the sky gets much darker, while the land stays at about the same level of brightness. That's because you're blocking out a lot of the polarized light from the sky but not the unpolarized light bouncing off the ground. Turning the polarizer a quarter turn should take it from brightest to darkest.
The other main source of polarized light is reflections from non-metallic objects. If you look at reflections off glass or water through the polarizer, you'll see the same effect: turn the polarizer so the reflections are at their brighest and then, as you turn it through a quarter turn, the reflections will fade away. Another example is reflected glare from foliage, as demonstrated in this blog post.
Perhaps the most dramatic demonstration is to look at an old-style calculator LCD display through the filter. At one angle, the display will look completely normal; a quarter-turn later, it'll be completely black.
Finally, yes, the effect will be visible through the viewfinder, as long as that's showing an image through the lens. It's a "pure" optical effect and doesn't rely on the sensor at all. In fact, all the demonstrations I suggested above are easiest if you just hold the filter in your hand and look through it without using a camera at all! :-)
Thanks to MichaelT for reminding me about foliage.
