In the Wikipedia article you linked, if you consider the section about rotations, you'll find that they partly answer your question: When applying a rotation to a spherical harmonic of degree $l$, this rotated spherical harmonic can itself be expressed as a linear combination of spherical harmonics of degree $l$, with coefficients given by this fairly complicated Wigner D-matrix.
Interpreting the visualization might indeed be a bit tricky, especially since it may not be clear how you go from the visualizations on these page to the original functions.
For example, take a look at the real spherical harmonics for $l = 1$. In the visualization on top of the page, you get that their images correspond to the three dumbbell-shaped objects in the second row. But of course, the spherical harmonics take values on the sphere, so their images aren't literally these dumbbells.
Quoting the subtext of the image:
"The distance of the surface from the origin indicates the absolute value of $Y^m_l(\theta,\phi)$ in angular direction $(\theta, \phi)$." Thus, what this image actually represents is that for $l = 1$, the three real spherical harmonics are functions which are positive on one half of the sphere and negative on the opposite half, where the three different spherical harmonics correspond to viewing the halves of the sphere on the $x$-axis, the $y$-axis and the $z$-axis, respectively.
Now, if you rotate one of these spherical harmonics, you basically rotate these half spheres where the function takes positive/negative values. Indeed, if you rotate, for example, the $x$-half spheres into the $y$-half spheres, that corresponds to applying a rotation to the $Y_l^{-1}$ which made it into $Y_l^0$. Now, with that thought in mind, personally, I find it more believable that if I rotate my half spheres along some different axis, that I can then represent the result as a weighted sum of my $x$-, $y$- and $z$-half spheres. So, in this crude sense, any decomposition of a sphere into two half-spheres given by a rotated spherical harmonic is just a linear interpolation between the decompositions of the sphere into $x$-, $y$- and $z$-half spheres, corresponding to your original, unrotated spherical harmonics.
If you did not understand that last paragraph, however, I do not blame you, it feels a bit esoterical to me as well, it was just a way to make the images on Wikipedia plausible for my own brain, which might well work different from yours :) It is difficult to make a visual interpretation of these fairly difficult functions intuitive. Maybe it resonates with you regardless! But rest assured that you are not misunderstanding the spherical harmonics as representations of $SO(3)$.
Best Answer
I have already answered this on PSE. l is absolutely not associated with just rotations on the inclination θ, which, of course, do not commute with azimuth rotations.
Your second sentence here encapsulates your misconception! l is the representation index, and is associated with any and all rotations, of all angles, 𝜃 and 𝜙, in a given representation characterized by the Casimir invariant, $${\mathbf L}\cdot {\mathbf L} = l(l+1)~{\mathbb I}_{2l+1}.$$ Since this invariant commutes with rotations, any rotation will not mix its eigenspaces characterized by different l s: it will only scramble the m s, which is the very point (and genius!) of Wigner's rotation matrix.
Rotations in 𝜙 do not mix m components, so they are diagonal D matrices, but rotations in 𝜃 do, which is why Wigner defined them.
So, all rotations are considered and meaningful for a fixed l.
As a result, the composition of two rotations amounts to mere matrix multiplication of two Ds with a common label l, ie, the active indices are just the ms. That's why we are talking matrices. They act on $2l+1$-dimensional subspaces, for which $$ \sum_{m=-l}^l |l~m\rangle \langle l~m | $$ serves as the identity.
The $Y^m_l(\theta, \phi)= \langle \theta, \phi|l~m\rangle$ are a mere representation switch from compact angles to integer indices, and, geometrically, amount to rotations from the middle of the z axis to a given point on the sphere for a given inclination and azimuth, $$ D^{l}_{m 0}(\alpha,\beta,\gamma) = \sqrt{\frac{4\pi}{2l+1}} Y_{l}^{m*} (\beta, \alpha ) . $$ As a result, the action of Ds on the Ys is effectively the composition of two rotations!
But we saw above that compositions of rotations are within each l subspace. The rest ought to be evident. To be sure, the linked expression mixes Euler angles and spherical/celestial angles, but, by taking simple examples, you may illustrate the point to yourself...