Basically to model anything on a sphere (usually the Earth). Spherical harmonics are orthogonal functions on the sphere, just like Fourier modes are orthogonal functions in normal space. They solve Laplace's equation on the sphere.
Gravity, geomagnetism, and many other things are often given in terms of spherical harmonics.
It doesn't mean you're stupid! You're probably familiar with the concept of two lines (or vectors) being orthogonal (perpendicular) to each other. e.g., two 2-d vectors (0, 1) and (1, 0) are perpendicular. This is equivalent to saying that their dot product is 0:
0*1 + 1*0 = 0
Those are finite, 2d vectors. They also happen to be orthogonal unit length (orthonormal) basis vectors, because you can write any 2-d vector with linear combinations of those:
(2, 1) = 2*(1, 0) + 1*(0, 1)
We could do the same with any pair of non-parallel 2d vectors, but it's much easier to have an orthonormal basis.
The same intuition almost completely carries over to infinite-dimensional vector spaces. Here, vectors are well-behaved functions on the sphere.
The inner product (or dot product) of two such functions (e.g. f and g) is the integral of their product over the sphere, which pretty much multiplies the value of both f and g at every point, and adds up the pointwise values by area weighting (some other orthogonal function series are orthogonal under other weightings or spaces).
Spherical harmonic functions form an orthonormal basis over the sphere (viewed as an infinite-dimensional vector space), just like those two vectors do over a 2d space. So, two different spherical harmonic functions have an inner product of zero. This makes a lot of things much easier!
Fourier modes are very similar. Any nice periodic function on the real line can be written as a sum of the trigonmetric basis functions, called its Fourier series.*
Any 3D shape/distribution can be expressed as some infinite mixture of Cartesian x-y-z (plane) waves, but if your object is closer to being radially symmetric then it might be more appropriate to express it as a mixture of azimuth-declination & radius waves instead. Technically you can choose any esoteric shape to split of your three degrees-of-freedom and your description will be mathematically identical as long as your DoF basis don’t have redundant parts, but usually we tend to use either Cartesian or spherical descriptions, and the frequency-domain (reciprocal space) description of those choice of symmetry corresponds to [xyz plane waves] or [spherical harmonics (angular part) + Spherical Hankel functions (radius part)]
Spherical harmonics are used all over the place in climate science.
But two big examples are Global oscillations like NAO&ENSO (so ocean currents and atmospheric pressure mostly), and analyzing wind-patterns on the globe/sphere.
Gravity, geomagnetism, and many other things are often given in terms of spherical harmonics.