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)$.
Here are two reasons the group generated by $U$ is defined as the set of finite products, which I will denote by $\Pi(U)$:
1.) The group generated by $U$ is usually taken to be the smallest group containing $U$; it is evident the set $\Pi(U)$ satisfies this criterion, since it is clearly closed under the group operation (finite products of finite products of elements of $U$ are, after all, themselves finite products of elements of $U$) and the taking of inverses, and contains the identity element $e$ since
$e = xx^{-1}, \; x \in U; \tag 1$
thus $\Pi(U)$ is a group; and any group containing $U$ must contain $\Pi(U)$ if it is to be closed under the group operation and inversation. Indeed, $\Pi(U)$ is often though of as he intersection of all groups containing $U$; in this sense it is the smallest group containing $U$.
2.) We really can't define infinite products of elements of $U$ anyway, in a purely algebraic sense; to do so generally requires some notion of $convergence$ of a sequence of products such as
$x_1x_2, x_1x_2x_3, x_1x_2x_3x_4, \ldots; \tag 2$
but convergence lies in the realm of topology, so we would have to adopt some appropriate topological structure to give meaning to such infinite products.
Well, there are two of my main reasons for accepting the definition of the group generated by $U$ as $\Pi(U)$. The comment stream attached to the question itself contains more useful insights, cf. the remarks of ThorWitch and Captain Lama.
Best Answer
Since you're asking about the Lie groups $SO(3)$ and $SO(4)$, you're looking at the wrong definition of rank.
You don't want the rank of a group meaning the minimal number of generators; for an uncountable group, that rank is uncountable, as you suspected.
Instead you want the rank of a Lie group, and I quote from that link: "For connected compact Lie groups... the rank of the Lie group is the dimension of any one of its maximal tori."