Why does an aerosol can get really cold when it's used excessively?

Expending the energy in the can (from spraying out the contents) reduces the temperature in the can.

That is only temporary as the can reabsorbs heat from the air.

When gases expand they become colder, because in order to expand the gas must exert a force on the environment, and as the environment yields to this force the gas does work on, or puts energy into, the environment. This energy must come from the gas, which therefore loses kinetic energy, which is equivalent to temperature (or rather, temperature is the average kinetic energy of each particle).

Part of the propellant in the can is in a liquid state and some is in a gaseous state. When you spray, the gaseous part comes out the nozzle and some of the liquid in the can evaporates to make more gas. This makes the remaining liquid a little colder. As more and more of the (relatively) warmer gas leaves the can, the colder liquid is left behind.

To qualify, I think @PerryDolia‘s answer is much better than mine; I didn’t realize that gases were pressurize to the point of condensation in an aerosol can, and the decrease in temperature due to evaporation upon depressurization is probably much more significant than the effect I described, especially since my explanation assumes that the gas expands against resistance, such as in an elastic container, and the resistance posed by atmospheric gases is probably fairly insignificant.