$\mathrm{SO}(3)$ is a wonderful group, but it quickly got bored rotating vectors in space. It wanted to be used to help solve differential equations, and so desperately wanted to act on functions. Then one day, it realized it could act on functions by acting on their input.
For instance, it contains a 90 degree clockwise rotation in the $x$-$y$ plane. It takes $(x,y,z)$ and replaces it with $(y,-x,z)$.
Well, the function $2x+3y+7z$ can also be rotated! We just replace $x$ by $y$, $y$ by $-x$, and leave $z$ alone: $2x+3y+7z$ is rotated into $2y+3(-x)+7z = -3x + 2y + 7z$. If we consider the action on all these linear $Ax+By+Cz$ it becomes clear that $\mathrm{SO}(3)$ acts by 3×3 matrices on the vector space with basis $\{x,y,z\}$.
Yay, $\mathrm{SO}(3)$ can act on functions now, and it acts on those linear functions as 3×3 matrices.
What about quadratics? Well we could have $2x^2 + 3xy + 7y^2$. That same 90 degree rotation takes it to $2(y)(y) + 3(y)(-x) + 7(-x)(-x) = 7x^2 - 3xy + 2y^2$. Yay, now $\mathrm{SO}(3)$ acts as 6×6 matrices on the quadratic polynomials $Ax^2+Bxy+Cy^2+Dxz+Eyz+Fz^2$ spanned by $\{ x^2, xy, y^2, xz, yz, z^2 \}$.
It turns out though that $\mathrm{SO}(3)$ doesn't swirl these functions around very thoroughly. If you take a function, and rotate its input, all it does is rotate the laplacian. In particular, notice that it takes any harmonic polynomial (one with 0 laplacian) to a harmonic polynomial. For quadratic polynomials, this is particularly easy to describe:
When you use $\mathrm{SO}(3)$ to rotate $Ax^2+Bxy+Cy^2+Dxz+Eyz+Fz^2$, it leaves the laplacian, $A+C+F$, alone. So it will never rotate $x^2$ into $xy$. In fact, it leaves the 5-dimensional space spanned by $\{ x^2-y^2, y^2-z^2, xy, xz, yz \}$ invariant. If we write things in these coordinates, then we get $\mathrm{SO}(3)$ acting as 5×5 matrices. In fact it acts irreducibly.
What happened to the 6th dimension? Well, it is spanned by a very silly function, the sphere: $x^2+y^2+z^2$. If you rotate the sphere, you get the sphere. On this one dimensional space, $\mathrm{SO}(3)$ is represented by 1×1 matrices. Well, actually, matrix. Every single rotation in $\mathrm{SO}(3)$ acts as the identity matrix $[1]$, since every rotation leaves the sphere alone.
If we use colors, like pink, lime, and periwinkle to describe vectors, then a polynomial like $5x^2-5y^2 + 6x + 3$ is composed of a pink term, $5x^2-5y^2$, where $\mathrm{SO}(3)$ acts in a 5×5 manner, a lime term, $6x$, where $\mathrm{SO}(3)$ acts in a 3×3 manner, and a periwinkle term, $3$, where $\mathrm{SO}(3)$ acts in a 1×1 manner.
A polynomial like $7x^2+5y^2$ is a little trickier to see its colors (it is a good thing $\mathrm{SO}(3)$ is so clever), $7x^2+5y^2 = 4(x^2+y^2+z^2) + 3(x^2-y^2) + 4(y^2-z^2)$. The fist term $4(x^2+y^2+z^2)$ is periwinkle where $\mathrm{SO}(3)$ acts in a 1×1 manner, but the next two terms $3(x^2-y^2) + 4*(y^2-z^2)$ are pink where $\mathrm{SO}(3)$ acts in a 5×5 manner.
There is a lot of background here, and again, the best way to learn it is to find a good textbook on the representation theory of finite groups. But here's a brief sketch.
Any finite group $G$ has a finite list of irreducible representations. Maschke's theorem guarantees that any (say, complex, finite-dimensional) representation decomposes into a direct sum of irreducible representations. More concretely, this says that given any representation, we can simultaneously block-diagonalize all of the elements of $G$ so that the blocks correspond to irreducible representations.
$S_2$ has a $2$-dimensional representation $V$ given by its action as permutation matrices. This representation decomposes into a direct sum $V_0 \oplus V_1$ of the trivial representation and the nontrivial (sign) representation. We can say that the elements of $V_0$ transform under the trivial representation, and the elements of $V_1$ transform under the sign representation. (This seems to be physics / chemistry terminology. In mathematics we would just say that $V_0$ is, or perhaps is isomorphic to, the trivial representation, and so forth.)
Okay, so how do we find this decomposition? Write $V = \text{span}(a, b)$ where the nontrivial element $g$ of $S_2$ exchanges $a$ and $b$. Then $g$ fixes the vector $a + b$, so $a + b$ spans the trivial subrepresentation. Next, we want to find a vector that $g$ multiplies by $-1$, and such a vector is given by $a - b$. So $V$ decomposes into a direct sum $\text{span}(a + b) \oplus \text{span}(a - b)$ where $S_2$ acts by the trivial representation on the first summand and by the sign representation on the second summand.
There is a more general projection formula here, but to learn what it is, again, you really should find a good textbook on the representation theory of finite groups.
Best Answer
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)$.