It really depends on the game. Some games tend to be more GPU-limited, while others are CPU-limited.
ARMA3 for example shows big performance difference depending on the clock speed. That test wasn't even in the multi-player portion of the game either, which is vastly more demanding on the CPU. Planetside 2 and Kerbal Space Program are a couple more games where overclocking makes a big difference. Flight Simulator X is another, despite being 7 years old now.
All of those games have one thing in common though; they can't fully utilize 4 cores. In KSP's case it only uses 1 core.
Except for PS2, all of those games also do a lot of physics simulation. If all game engines could fully use 4+ cores then there would probably be very few games where CPU overclocking makes a difference, but unfortunately that still isn't the case.
As others have mentioned OCing also yields huge performance gains in non-gaming applications. When it comes to video encoding your encoding speed will scale proportionally to your CPU overclock (ie a 50% CPU overclock will give you 50% faster encoding times). It can also make your rig more future proof. Even if you are GPU limited in a game now, when faster graphics cards come out in the future that may no longer be the case.
Most modern CPUs will underclock/undervolt themselves when they aren't being used as well, so you don't use any extra power/heat most of the time. For example my
[email protected] downclocks itself to 1.6ghz any time it's not being used heavily